Sleep using the GROUP BY and RETURN ENTITY LISTs

Question

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

 SELECT b FROM Book b GROUP BY volumeCode; 

I have the following code:

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

But this criterion only returns volumeCode (list of strings). I need to get a list of Book s. So I tried using 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 a list of null values. Can this be done using criteria?

Answer 1

First of all, the projector filters the amount of data retrieved, if you need more data, you must add these properties to the projection.

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 "Bean Alias", it matches the alias with the properties of your java bean "Book.java".

Edit:

Without a transformer, if the projection has more than one property, the result is as follows:

 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 } 

That's why you got a cast exception about a transformer, trying to match each alias with the property name of your java bean.

Answer 2

cz_Nesh. sorry for my first answer. I am reading the Hibernate api and reading some Hibernate source code that I find. 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, not List EmpUserImpl, why? I see the criterion of the parent class CriteriaSpecification, which I find.

 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? he says that this result transformer is selected implicitly, calling setProjection () is average, when you use the .setProjection criteria, the result will not be displayed in EmpUserImpl because the ResultTransformer changes to “PROJECTION” with “ROOT_ENTITY.” it will package using Projection (e.g. select name, oid ..). therefore, if you want to return List EmpUserImpl, you need to set Projections.property ("name"). as ("name"). (if you only need a name with a 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()); } 

he can work. I hope this can help you.

Answer 3

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