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  lt operator gt  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

User · Answer

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  gt  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

User · Answer

If you have to do group by using hibernate criteria use projections groupPropery like the following    Autowired private SessionFactory sessionFactory  Criteria crit   sessionFactory getCurrentSession   createCriteria studentModel class   crit setProjection Projections projectionList                add Projections groupProperty  studentName   as  name    List result   crit setResultTransformer Criteria ALIAS TO ENTITY MAP  list     return result

User · Answer

You can use the approach  Ken Chan mentions  and add a single line of code after that if you want a specific list of Objects  example       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                                     setResultTransformer Transformers aliasToBean SomeClazz class     List lt SomeClazz gt  objectList    List lt SomeClazz gt   criteria list

User · Answer

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

[java] Hibernate Group by Criteria Object

Examples related to java

Examples related to sql

Examples related to hibernate

Examples related to hibernate-criteria