java - hibernate criteria count over with group by

Question

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

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

Answer 1

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

Disclaimer

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

Solution

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

This generates SQL that looks like this:

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

Without LIMIT 1 the result would be

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

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.

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?
}