I had to implement a select query, which was using different tables, with hibernate. It seemed easy enough, but it just would not work. The select statement worked in sql developer, but just not as a query with a managed entity. I started debugging and searching for the error without luck. Then I thought about the problem: I have this select statement, which i know works. I am only interested in the output and I will never insert an element. As a matter of fact: I am even forbidden to manipulate the data inside the DB. So… why the hell am I trying to use a managed entity?

Solution which took only five minutes:

( From [16.1.5. Returning non-managed entities][1])

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.

Sometimes it’s just more efficient to thing about the problem, instead of the solution.