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?

JB Nizet · Accepted Answer · 2012-08-07 22:03:23Z

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 7, 2012 at 22:03

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lukas Eder Over a year ago

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?

JB Nizet Over a year ago

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

Lukas Eder Over a year ago

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

Michael come lately · Accepted Answer · 2018-02-15 21:49:16Z

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, 2018 at 21:49

Michael come lately

9,6418 gold badges70 silver badges97 bronze badges

answered Mar 21, 2016 at 2:40

Stan Sokolov

2,3101 gold badge25 silver badges24 bronze badges

1 Comment

JRA_TLL Over a year ago

Nice idea but works only if database columns are named exactly like the entity fields.

Popa Andrei · Accepted Answer · 2015-03-01 08:56:34Z

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 1, 2015 at 8:56

Popa Andrei

2,4991 gold badge23 silver badges26 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related