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?

Ziul · Answer 1 · 2013-05-16T14:05:02.477

8

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.

edited May 16 '13 at 14:05

answered May 15 '13 at 17:42

Ziul

883
1
13
24

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. – cz_Nesh May 15 '13 at 20:59
Ehm, I delete line with transformer O:-)... After adding this line back I still get List of null objects. – cz_Nesh May 15 '13 at 21:03
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 – cz_Nesh May 17 '13 at 15:20
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. – Ziul May 17 '13 at 19:45
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? – Vlastimil Ovčáčík Apr 04 '16 at 18:31

score 5 · Answer 2 · answered Apr 24 '15 at 03:40

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.

I'm getting the list of entire objects. But all the fields are null. Do you know why that's happening? — Manoj Shrestha, Dec 22 '18 at 04:00

score 2 · Answer 3 · edited Nov 20 '14 at 08:33

2

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

edited Nov 20 '14 at 08:33

ROMANIA_engineer

54,432
29
203
199

answered Nov 20 '14 at 07:10

Mazrul

21
2

1

@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 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](http://stackoverflow.com/questions/10961048/setresulttransformer-in-criteria) – Mazrul Nov 20 '14 at 10:04
Add this to your post. We didn't know why this helped you initially – rayryeng Nov 20 '14 at 14:48

Hibernate criteria Using GROUP BY and RETURN ENTITY LIST

3 Answers3

Linked