休眠标准以分组方式计算 [英] hibernate criteria count over with group by

问题描述

我有一个带有 user 实体和 users 表的spring app.我想按特定字段(不是按组，但总计总计)对所有用户进行分组.在sql中将是:

I have a spring app with the user entity and the users table. I would like to get a number of all users grouped by certain fields (not per group but in total). In sql It would be:

select
count(*) OVER () as totalRecords
  from users u
  group by
    u.first_name,
    u.last_name,
    u.age
  order by u.age DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

但是我真的不能使用休眠标准来做到这一点.我可以做类似的事情:

But I really can't do that using hibernate criteria. I could do something like:

public Long getTotalCount() {
        ProjectionList groupBy = projectionList();
        groupBy.add(groupProperty("firstName"), "first_name");
        groupBy.add(groupProperty("last_name"), "last_name");
        groupBy.add(groupProperty("age"), "age");
        groupBy.add(Projections.rowCount());

        return (Long) getSession().createCriteria("User")
                .setProjection(groupBy)
                .uniqueResult();
    }

但这不是我想要的.它确实对每个组进行计数，我想对 group by 子句

but it's not what I want. It does counting per each group, I would like to count rows that are the result of the group by clause

推荐答案

我只花了几个小时试图找到一种方法，最后使它正常工作.

I just spend couple hours trying to find out a way and finally got it working.

免责声明

使用简单标准API不可能进行最佳查询.最佳选择是 SELECT COUNT(*)FROM(按此处查询分组)或 SELECT COUNT(*)OVER().都不可能.为了获得最佳查询，请尽可能使用普通SQL.就我而言，无法使用普通SQL，因为我已经构造了一个非常复杂的逻辑来构建标准，并且我想使用相同的逻辑来解决聚合计数(以解决分页页数).

It is impossible to do an optimal query with plain criteria API. Optimal would be either SELECT COUNT(*) FROM ( group by query here ) or SELECT COUNT(*) OVER (). Neither is possible. To get an optimal query, use plain SQL if possible. For my case using plain SQL was not possible, because I have constructed a very complex logic that builds criteria and I want to use the same logic for resolving the count of aggregate also (to resolve count of pages for pagination).

解决方案

首先，我们将以下内容添加到用作条件基础的所有实体中:

First we add the following to all Entities that are used as base of criteria:

@Entity
class MyEntity {

private Long aggregateRowCount;

@Formula(value="count(*) over()")
public Long getAggregateRowCount() {
    return aggregateRowCount;
}

public void setAggregateRowCount(Long aggregateRowCount) {
    this.aggregateRowCount = aggregateRowCount;
}

条件构建看起来像这样:

Criteria building looks like this:

Criteria criteria = // construct query here
ProjectionList projectionList = // construct Projections.groupProperty list here
projectionList.add(Projections.property("aggregateRowCount")); // this is our custom entity field with the @Formula annotation
criteria.setProjection(projectionList);
criteria.setMaxResults(1); 
criteria.setResultTransformer(AggregatedCountResultTransformer.instance());
List<?> res = builder.criteria.list();
if (res.isEmpty()) return 0L;
return (Long) res.get(0);

这将生成如下所示的SQL:

This generates SQL that looks like this:

SELECT groupbyfield1, groupbyfield2, count(*) over()
FROM ...
GROUP BY groupbyfield1, groupbyfield2
LIMIT 1;

如果没有LIMIT 1，结果将是

Without LIMIT 1 the result would be

field1 | field2 | count
a      | b      | 12356
a      | c      | 12356
...    | ...    | 12356

但是我们添加了LIMIT 1( criteria.setMaxResults(1); )，因为第一行已经包含了行数，这就是我们所需要的.

but we add the LIMIT 1 (criteria.setMaxResults(1);) because the first row already contains the number of rows and that is all we need.

最后，我们的AggegatedCountResultTransformer:

Finally, our AggegatedCountResultTransformer:

class AggregatedCountResultTransformer implements ResultTransformer {

private static final AggregatedCountResultTransformer instance = new AggregatedCountResultTransformer();

public static ResultTransformer instance() {
    return instance;
}

@Override
public Object transformTuple(Object[] values, String[] fields) {
    if (values.length == 0) throw new IllegalStateException("Values is empty");
    return values[values.length-1]; // Last value of selected fields (the count)
}

@SuppressWarnings("rawtypes")
@Override
public List transformList(List allResults) {
    return allResults; // This is not actually used?
}

这篇关于休眠标准以分组方式计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！