Counting results with Hibernate Criteria with multiple joins/associations

We struggled to find the answer to a Hibernate question recently. We are using Hibernate Criteria on an entity with multiple associations and showing these results in a table. The query to return the results of a specific page in the table is simple and works great.

Criteria crit = createCriteria(getResourceClass());
crit.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
crit.setFirstResult(start);
crit.setMaxResults(length);
crit = crit.createCriteria("roles");
crit = crit.createCriteria("principals");
crit.add(Restrictions.in("id", ids));
return (List) crit.list();

Because multiple roles can be added to the entity with the same principals this would normally cause duplicate results but thankfully the result transformer does its job. The problem came when we tried to get the total number of results as this was displayed also in the table.

The initial attempts to use the rowCount projection failed, we assumed this would work in conjunction with the distinct root entity transformer to return the exact number of rows.

crit.setProjection(Projections.rowCount());
...
return (Long) crit.uniqueResult();

After some experimentation we discovered the answer. Replace the CriteriaSpecification.DISTINCT_ROOT_ENTITY result transformer with CriteriaSpecification.PROJECTION and use the Projections.countDistinct projection against an entity field, in our case the name field.

crit.setProjection(Projections.countDistinct("name"));
crit.setResultTransformer(CriteriaSpecification.PROJECTION);

This was not straight forward and we failed to find any references to this method online. We hope that this helps others solve the same problem and avoid some head scratching.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>