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?

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.

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.

Answer 3

I think you can use : criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);