How do you put raw-sql projections in an object using Hibernate?

Question

Let's say I have a query that begins with the following projections:

SELECT t.term as term, count(g.id) as amount

This is raw sql, and I am using createSqlQuery on the Hibernate session object. What I'd like to do is take these projections and put them in an object that has a "term" and "amount" properties.

With HQL, we can use "select new ClassName(...)", but this doesn't work with a raw SQL query.

How do we do it? I get back a bunch of [LObject's... and I have no idea what to do with them. If I can get Hibernate to put them into some kind of non-entity value object, that'd be great.

Thanks!

Answer 1

Check http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querysql.html#d0e13696

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, DOG_ID FROM CATS").addEntity(Cat.class)

Assuming your object with the term and amount properties has only those 2 object attributes a similar query with your raw SQL should return a list of those objects which you can then iterate through one by one.

Answer 2

If I can get Hibernate to put them into some kind of non-entity value object, that'd be great.

You can tell Hibernate to return non-managed value objects from a native SQL query by applying a "result transformer". From the reference documentation:

16.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.