Hibernate: Group By and Count

One of the best features of Hibernate are the Criteria Queries, which allow you to define parametric and easy way to query your data. In this post, I would like to summarize the usage of the class Projections allow you to group your result set by applying a projection to your Criteria query. For example, if you like to "group by" and to "count" the number of instances for a parameter "par", you can use the following lines of code:

Criteria criteria = criteria();

ProjectionList projList = Projections.projectionList();
projList.add(Projections.groupProperty("par"), "parAlias");
projList.add(Projections.rowCount(), "countAlias");
criteria.setProjection(projList);

criteria.setResultTransformer(Transformers.aliasToBean(ObjDto.class));

Some remarks:

• It is important to underline the usage of the "groupProperty" method to apply the grouping on the parameter "par" and the usage of the method "rowCount()" to count the number of instances (i.e. SQL count(*))
  
• It is also fundametal to note the usage of the aliases in both methods: countAlias and parAlias. These aliases are used by the ResultTransformer
• The Data Transfer Object (DTO) class ObjDto contains only two attributes: countAlias and parAlias
• Finally, it is important to see the usage of the setResultTransformer to copy the result of the query on our DTO bean class called ObjDto

The Projection class allows you to aggregate and group your Criteria queries in an easy way. Morevoer, they can simplify and reduce your lines of code!