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

Johan picture Johan · Aug 7, 2012 · Viewed 27.4k times · Source

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:

  1. I've tried using q.addEntity(Y.class) but this fails with: org.hibernate.MappingException: Unknown entity com.mycompany.Y
  2. 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.
  3. 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?

Answer

JB Nizet picture JB Nizet · Aug 8, 2012

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.