Category Archives: Hibernate

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.

Quick and easy hibernate logging

Wow has it been that long. I’ve been busy building the Hypersocket Framework and the many projects based on it now. We’ll get more chatty about those soon but in the meantime I’d like to record my quick and easy way to enable hibernate logging just by using log4j.

# logs the SQL statements
log4j.logger.org.hibernate.SQL=debug
# Some more useful loggings
# Logs SQL statements for id generation
log4j.logger.org.hibernate.id=info
# Logs the JDBC-Parameter which are passed to a query (very verboose)
log4j.logger.org.hibernate.type=debug
# Logs cache related activities
log4j.logger.org.hibernate.cache=debug