Hibernate Group by Criteria Object

Question

I would like to implement the following SQL query with Hibernate Criteria:

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name <operator> value GROUP BY column_name 

I have tried to implement this with Hibernate Criteria but it didn't work out.

Can anyone give me an example how this can be done with Hibernate Criteria? Thanks!

Answer 1

Please refer to this for the example .The main point is to use the groupProperty() , and the related aggregate functions provided by the Projections class.

For example :

SELECT column_name, max(column_name) , min (column_name) , count(column_name) FROM table_name WHERE column_name > xxxxx GROUP BY column_name 

Its equivalent criteria object is :

List result = session.createCriteria(SomeTable.class)                            .add(Restrictions.ge("someColumn", xxxxx))                           .setProjection(Projections.projectionList()                             .add(Projections.groupProperty("someColumn"))                             .add(Projections.max("someColumn"))                             .add(Projections.min("someColumn"))                             .add(Projections.count("someColumn"))                                ).list(); 

Answer 2

GroupBy using in Hibernate

This is the resulting code

public Map getStateCounts(final Collection ids) {     HibernateSession hibernateSession = new HibernateSession();     Session session = hibernateSession.getSession();     Criteria criteria = session.createCriteria(DownloadRequestEntity.class)             .add(Restrictions.in("id", ids));     ProjectionList projectionList = Projections.projectionList();     projectionList.add(Projections.groupProperty("state"));     projectionList.add(Projections.rowCount());     criteria.setProjection(projectionList);     List results = criteria.list();     Map stateMap = new HashMap();     for (Object[] obj : results) {         DownloadState downloadState = (DownloadState) obj[0];         stateMap.put(downloadState.getDescription().toLowerCase() (Integer) obj[1]);     }     hibernateSession.closeSession();     return stateMap; }