Hibernate criteria Using GROUP BY and RETURN ENTITY LIST

Question

I'm trying to use GROUP BY in my criteria. I need to do this:

SELECT b FROM Book b GROUP BY volumeCode;

I have following code:

    Criteria c = s.createCriteria(Book.class);
    c.setProjection(Projections.projectionList().add(Projections.groupProperty("volumeCode")));
    List<Book> result = c.list();

But this criteria returns only volumeCodes (a list of Strings). I need to get a list of Books. So I tried to use Transformers:

    Criteria c = s.createCriteria(Book.class);
    c.setProjection(Projections.projectionList().add(Projections.groupProperty("volumeCode")));
    c.setResultTransformer(Transformers.aliasToBean(Book.class));
    List<Book> result = c.list();

This code returns list of null values. Is it possible to do that with criteria?

Division

Answer 1

First of all, the projecton filters the amount of data retrieved, if you want more data, you should add those properties to the projection too.

Example:

c.setProjection( Projections.projectionList()
    .add( Projections.property("id").as("id") )
    .add( Projections.property("descripction").as("description") )
    .add( Projections.groupProperty("volumeCode").as("volumeCode") ));

Now, the transformer does what it says "Alias to Bean", it does an alias match with the properties of your java bean "Book.java".

Edit:

Without the transformer, if the projection has more than one property, the result comes out like this:

for(Object[] item:criteria.list()){
    System.out.println( (String)item[0] ); //ID
    System.out.println( (String)item[1] ); //Description
    System.out.println( (String)item[2] ); //Volume code
}

Thats why you were getting the cast exception, about the transformer, try to match every alias with the property name of your java bean.

Boice

Hello Ziul, thx for your reply. With your advice I get List of objects but Java throws Exception when I try to cast any object from the list to the Book. – Division

Ehm, I delete line with transformer O:-)... After adding this line back I still get List of null objects. – Division

Thank you for editing your post. I don't wanna get only one property but whole object. I need to get the same list of objects like after calling SELECT b FROM Book b GROUP BY volumeCode so the result should be List<Book> – Division

Then you need to get the transformer working, just add every property of your Bean to the projection with a matching alias (same name for alias and property), and it will work. – Boice

When you GROUP BY volume code, shouldn't you also group by all the other properties, id and description? Or put them in some aggregation function? – Upolu

Answer 2

cz_Nesh. sorry about my first answer. i read Hibernate api and read some Hibernate source code i find that. if you use this code

session.createCriteria(EmpUserImpl.class).list();  

it will return List EmpUserImpl. if you use this code

        criteria.setProjection(Projections.projectionList()
            .add(Projections.groupProperty("company").as("company"))
            .add(Projections.property("name").as("name"))
            .add(Projections.property("company").as("company")));
        List list = criteria.list();

it will return List ,is not List EmpUserImpl why? i see the criterion's parent class CriteriaSpecification i find that .

public interface CriteriaSpecification {

/**
 * The alias that refers to the "root" entity of the criteria query.
 */
public static final String ROOT_ALIAS = "this";

/**
 * Each row of results is a <tt>Map</tt> from alias to entity instance
 */
public static final ResultTransformer ALIAS_TO_ENTITY_MAP = AliasToEntityMapResultTransformer.INSTANCE;

/**
 * Each row of results is an instance of the root entity
 */
public static final ResultTransformer ROOT_ENTITY = RootEntityResultTransformer.INSTANCE;

/**
 * Each row of results is a distinct instance of the root entity
 */
public static final ResultTransformer DISTINCT_ROOT_ENTITY = DistinctRootEntityResultTransformer.INSTANCE;

/**
 * This result transformer is selected implicitly by calling <tt>setProjection()</tt>
 */
public static final ResultTransformer PROJECTION = PassThroughResultTransformer.INSTANCE;

/**
 * Specifies joining to an entity based on an inner join.
 *
 * @deprecated use {@link org.hibernate.sql.JoinType#INNER_JOIN}
 */
@Deprecated
public static final int INNER_JOIN = JoinType.INNER_JOIN.getJoinTypeValue();

/**
 * Specifies joining to an entity based on a full join.
 *
 * @deprecated use {@link org.hibernate.sql.JoinType#FULL_JOIN}
 */
@Deprecated
public static final int FULL_JOIN = JoinType.FULL_JOIN.getJoinTypeValue();

/**
 * Specifies joining to an entity based on a left outer join.
 *
 * @deprecated use {@link org.hibernate.sql.JoinType#LEFT_OUTER_JOIN}
 */
@Deprecated
public static final int LEFT_JOIN = JoinType.LEFT_OUTER_JOIN.getJoinTypeValue();

}

can you see the public static final ResultTransformer PROJECTION ? it say that This result transformer is selected implicitly by calling setProjection() is mean when you use criteria.setProjection,the result will not List EmpUserImpl,because ResultTransformer is change to "PROJECTION" from "ROOT_ENTITY".it will packaging by Projection(like select name,oid .. ). so, if you want to return List EmpUserImpl you need set Projections.property("name").as("name").,(if you need name just set name). this is my code .

        Criteria criteria = session.createCriteria(EmpUserImpl.class);
    criteria.setProjection(Projections.projectionList()
            .add(Projections.groupProperty("company").as("company"))
            .add(Projections.property("name").as("name"))
            .add(Projections.property("company").as("company")));
    criteria.setResultTransformer(Transformers.aliasToBean(EmpUserImpl.class));
    List<EmpUserImpl> list = criteria.list();
    for (EmpUserImpl empUserImpl : list) {
        System.out.println(empUserImpl.getName());
    }

it can work . i hope it can help you.

Kellogg

I'm getting the list of entire objects. But all the fields are null. Do you know why that's happening? – Systematist

Answer 3

I think you can use:

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

Timofei

@rayryeng I encounter the same problem trying to get SELECT p FROM person p GROUP BY personId using criteria but using setProjection it just convert it to sql equivalent of select distint(personId) from person and return list of personId wheres i need the whole person object and List<Person> list = session.createCriteria("Person","p") .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); return the list of person object. as for why and how hope this will help here – Timofei

Add this to your post. We didn't know why this helped you initially – Entice