hibernate criteria group by sub-property

Question

assuming following:

public class Enterprise
{
   private int id;
   private String name;
}
public class Site
{
   private int id;
   private String name;
   private Enterprise enterprise; //@ManyToOne relation
}
public class Area
{
   private int id;
   private String name;
   private Site site; //@ManyToOne relation
}

I would like to do execute query using hibernate criteria grouping result by enterprise.

    Criteria criteria = session.createCriteria(Area.class);
    criteria.setProjection(Projections.projectionList()  
                    .add(Property.forName("id").as("id")) 
                    .add(Property.forName("name").as("name"))
        .add(Projections.groupProperty("site.enterprise")))
        .setResultTransformer(Transformers.aliasToBean(Area.class));
/*
 * more conditions
 */
    List<Area> areas = criteria.list();

When executing this hibernate returns exeption: org.hibernate.QueryException : could not resolve property: site.enterprise ...

Is there some elegant way how to do this. Thank you in advance.

Tony Day · Accepted Answer · 2013-01-08T09:41:35.630

You may need to set the alias first I believe:

criteria.createAlias("site", "s")

And then use:

Projections.groupProperty("s.enterprise")

You may also need to group on a field and not the entity, so:

site.enterprise.name // or id

This may require you to create an alias for enterprise too but you may not need to.

UPDATE 1: You could end up with something like this:

Criteria criteria = session.createCriteria(Area.class);
criteria.createAlias("site", "s")
        .createAlias("s.enterprise", "e")
        .setProjection(Projections.projectionList()  
                .add(Property.forName("id").as("id")) 
                .add(Property.forName("name").as("name"))
    .add(Projections.groupProperty("s.e.name")))
    .setResultTransformer(Transformers.aliasToBean(Area.class));

UPDATE 2: Call me crazy but from the example above, you may be taking a very long-winded approach by not using Hibernate to its full potential - it seems like you're trying to use it as you would SQL. Hibernate already knows about your entities, their fields and relationships. In my eyes, this is equivalent to the above:

Criteria criteria = session.createCriteria(Area.class);
criteria.createAlias("site", "s")
        .createAlias("s.enterprise", "e")
        .add(Projections.groupProperty("s.e.name"));

// Any other conditions.

List<Area> areas = criteria.list();

I'm not even sure why you're grouping it at the moment...

Hi Tony! It works perfectly. I would tag your answer as useful but I don't have enough reputation:) Thanks — stebetko, Jan 07 '13 at 10:57
Glad it works! You can accept the answer by clicking the tick under the vote buttons. — Tony Day, Jan 07 '13 at 10:57
`.add(Projections.groupProperty("s.e.name"))` how did you convert PropertyProjection to Criterion here ?? — Madushan Perera, Feb 12 '16 at 05:49

hibernate criteria group by sub-property

1 Answers1