java - 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