hibernate标准计数超过group by

Question

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;

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();
    }

Answer 1

我只是花了几个小时试图找到一个方法，最后让它发挥作用。

免责声明

用普通标准API做一个最优的查询是不可能的。最佳选择是select COUNT(*)FROM（此处按查询分组）或select COUNT(*)OVER()。两者都不可能。若要获得最佳查询，请尽可能使用纯SQL。对于我的情况，使用普通SQL是不可能的，因为我构造了一个非常复杂的逻辑来构建标准，并且我还希望使用相同的逻辑来解析聚合计数（以解析分页的页计数）。

@Entity
class MyEntity {

private Long aggregateRowCount;

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

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

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);

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

如果没有限制1，结果将是

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

但是我们添加了限制1(Criteria.setMaxResults(1)；)，因为第一行已经包含了行数，这就是我们所需要的。

最后，我们的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?
}