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!

Pascal Thivent · Accepted Answer · 2010-07-06 08:20:29Z

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.

potatopeelings · Accepted Answer · 2010-07-06 03:03:56Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

16.1.5. Returning non-managed entities

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related