CODEWITHSUNDEEP
javasqlhibernatehibernate-criteria
Bomberlatinos9
Bomberlatinos9

Reputation: 810

Hibernate Group by Criteria Object

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!

Upvotes: 44

Views: 129898

Answers (4)

Ajinz
Ajinz

Reputation: 487

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;

Upvotes: 2

darkconeja
darkconeja

Reputation: 348

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<SomeClazz> objectList = (List<SomeClazz>) criteria.list();

Upvotes: 12

Ken Chan
Ken Chan

Reputation: 90427

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();

Upvotes: 91

Ramkailash
Ramkailash

Reputation: 1850

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;
}

Upvotes: 17

Related Questions