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:

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 1

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.

Answer 2

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);
}