How to best map results from an SQL query to a non-entity Java object using Hibernate?

Question

I have a Hibernate managed Java entity called X and a native SQL function (myfunc) that I call from a Hibernate SQL query along these lines:

SQLQuery q = hibernateSession.createSQLQuery(
                     "SELECT *, myfunc(:param) as result from X_table_name"
             );

What I want to do is to map the everything returned from this query to a class (not necessarily managed by Hibernate) called Y. Y should contain all properties/fields from X plus the result returned by myfunc, e.g. Y could extend class X and add a "result" field.

What I've tried:

I've tried using q.addEntity(Y.class) but this fails with: org.hibernate.MappingException: Unknown entity com.mycompany.Y
q.setResultTransformer(Transformers.aliasToBean(Y.class)); but this fails with: org.hibernate.PropertyNotFoundException: Could not find setter for some_property. X has a field called someProperty with the appropriate getter and setter but in this case it doesn't seem like Hibernate maps the column name (some_property) to the correct field name.
q.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); returns a Map but the values are not always of the type expected by the corresponding field in X. For example fields in X of type enum and Date cannot be mapped directly from the Map returned by the SQL query (where they are Strings).

What's the appropriate way to deal with this situation?

score 16 · Accepted Answer · answered Aug 07 '12 at 22:03

16

See the chapter of the documentation about SQL queries.

You can use the addScalar() method to specify which type Hibernat should use for a given column.

And you can use aliases to map the results with the bean properties:

select t.some_property as someProperty, ..., myfunc(:param) as result from X_table_name t

Or, (and although it require some lines of code, it's my preferred solution), you can simply do the mapping yourself:

List<Object[]> rows = query.list();
for (Object[] row : rows) {
    Foo foo = new Foo((Long) row[0], (String) row[1], ...);
}

This avoids reflection, and lets you control everything.

answered Aug 07 '12 at 22:03

JB Nizet

678,734
91
1,224
1,255

2

Eek, a cast to `Long`. How do you know in advance that `row[0]` is going to be a `Long`? Is there some way to enforce data type conversion through the Hibernate APIs? – Lukas Eder Sep 07 '13 at 13:27
@LukasEder: Follow the link to the documentation, it's explained there. But using SQL queries with Hibernate should be extremely exceptional. Most of the time, you use HQL/criteria queries, and the type is deduced from the type of the field in your entity. – JB Nizet Sep 09 '13 at 07:22
Thanks for the info. I should've completely read your answer... Nonetheless, it's still a bit of a pain that the type info has to be repeated – Lukas Eder Sep 09 '13 at 07:40

score 7 · Answer 2 · edited Feb 15 '18 at 21:49

7

Easy. Cast the rows to Map<String, Object>:

final org.hibernate.Query q = session.createSQLQuery(sql);
q.setParameter("geo", geo);
q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
final List<Map<String, Object>> src = q.list();
final List<VideoEntry> results = new ArrayList<VideoEntry>(src.size());
for (final Map<String, Object> map:src) {
    final VideoEntry entry = new VideoEntry();
    BeanUtils.populate(entry, map);
    results.add(entry);
}

edited Feb 15 '18 at 21:49

Michael

8,362
6
61
88

answered Mar 21 '16 at 02:40

Stan Sokolov

2,140
1
22
23

Nice idea but works only if database columns are named exactly like the entity fields. – JRA_TLL Nov 11 '16 at 13:20

score 0 · Answer 3 · answered Mar 01 '15 at 08:56

0

First of all you need to declare the entity in the hibernate configuration xml file something like this: ..... class="path to your entity"

Or you can do the same thing programatically before you make the query.

answered Mar 01 '15 at 08:56

Popa Andrei

2,299
21
25

How to best map results from an SQL query to a non-entity Java object using Hibernate?

3 Answers3