mapping Hibernate query results to custom class?

Question

Following up on a question I posted yesterday: How to populate POJO class from custom Hibernate query?

Can someone show me an example of how to code the following SQL in Hibernate, and get the results correctly?

SQL:

select firstName, lastName from Employee 

What I'd like to do, if it's possible in Hibernate, is to put the results in their own base class:

class Results {     private firstName;     private lastName;     // getters and setters } 

I believe it's possible in JPA (using EntityManager), but I haven't figured out how to do it in Hibernate (using SessionFactory and Session).

I'm trying to learn Hibernate better, and even this "simple" query is proving confusing to know what form Hibernate returns the results, and how to map the results into my own (base) class. So at the end of the DAO routine, I'd do:

List<Results> list = query.list(); 

returning a List of Results (my base class).

Answer 1

select firstName, lastName from Employee  query.setResultTransformer(Transformers.aliasToBean(MyResults.class)); 

You can't use above code with Hibernate 5 and Hibernate 4 (at least Hibernate 4.3.6.Final), because of an exception

java.lang.ClassCastException: com.github.fluent.hibernate.request.persistent.UserDto cannot be cast to java.util.Map     at org.hibernate.property.access.internal.PropertyAccessMapImpl$SetterImpl.set(PropertyAccessMapImpl.java:102) 

The problem is that Hibernate converts aliases for column names to upper case — firstName becomes FIRSTNAME. And it try to find a getter with name getFIRSTNAME(), and setter setFIRSTNAME() in the DTO using such strategies

    PropertyAccessStrategyChainedImpl propertyAccessStrategy = new PropertyAccessStrategyChainedImpl(             PropertyAccessStrategyBasicImpl.INSTANCE,             PropertyAccessStrategyFieldImpl.INSTANCE,             PropertyAccessStrategyMapImpl.INSTANCE     ); 

Only PropertyAccessStrategyMapImpl.INSTANCE suits, in opinion of Hibernate, well. So after that it tries to do conversion (Map)MyResults.

public void set(Object target, Object value, SessionFactoryImplementor factory) {     ( (Map) target ).put( propertyName, value ); } 

Don't know, it is a bug or feature.

How to solve

Using aliases with quotes

public class Results {      private String firstName;      private String lastName;      public String getFirstName() {         return firstName;     }      public String getLastName() {         return lastName;     }      public void setFirstName(String firstName) {         this.firstName = firstName;     }      public void setLastName(String lastName) {         this.lastName = lastName;     }  }  String sql = "select firstName as \"firstName\",      lastName as \"lastName\" from Employee";  List<Results> employees = session.createSQLQuery(sql).setResultTransformer(     Transformers.aliasToBean(Results.class)).list();  

Using a custom result transformer

Another way to solve the problem — using a result transformer that ignores method names case (treat getFirstName() as getFIRSTNAME()). You can write your own or use FluentHibernateResultTransformer. You will not need to use quotes and aliases (if you have column names equal to DTO names).

Just download the library from the project page (it doesn't need additional jars): fluent-hibernate.

String sql = "select firstName, lastName from Employee"; List<Results> employees = session.createSQLQuery(sql)         .setResultTransformer(new FluentHibernateResultTransformer(Results.class))         .list(); 

This transformer can be used for nested projections too: How to transform a flat result set using Hibernate

Answer 2

See AliasToBeanResultTransformer:

Result transformer that allows to transform a result to a user specified class which will be populated via setter methods or fields matching the alias names.

List resultWithAliasedBean = s.createCriteria(Enrolment.class)             .createAlias("student", "st")             .createAlias("course", "co")             .setProjection( Projections.projectionList()                     .add( Projections.property("co.description"), "courseDescription" )             )             .setResultTransformer( new AliasToBeanResultTransformer(StudentDTO.class) )             .list();  StudentDTO dto = (StudentDTO)resultWithAliasedBean.get(0); 

Your modified code:

List resultWithAliasedBean = s.createCriteria(Employee.class, "e")     .setProjection(Projections.projectionList()         .add(Projections.property("e.firstName"), "firstName")         .add(Projections.property("e.lastName"), "lastName")     )     .setResultTransformer(new AliasToBeanResultTransformer(Results.class))     .list();  Results dto = (Results) resultWithAliasedBean.get(0); 

For native SQL queries see Hibernate documentation:

13.1.5. Returning non-managed entities

It is possible to apply a ResultTransformer to native SQL queries, allowing it to return non-managed entities.

sess.createSQLQuery("SELECT NAME, BIRTHDATE FROM CATS")     .setResultTransformer(Transformers.aliasToBean(CatDTO.class)) 

This query specified:

• the SQL query string
• a result transformer The above query will return a list of CatDTO which has been instantiated and injected the values of NAME and BIRTHNAME into its corresponding properties or fields.