JPA query result as POJO

As we all know, JPA is not the greatest thing when it comes to OLAP style queries or queries that go through many relationships. The reason is, when you only need some very specific data, it is a waste to create thousands of POJOs and then use Java to extract and aggregate what's needed.

So what we need here is some way to write a query that only extracts needed information and returns that as a POJO.

First try: using a JPQL View object

SELECT NEW com.company.MyPojo(o.rel.blah, o.rel2.blah2, o.rel3.rel33.blah3) FROM Object o
 
This works nice, as long as it is possible to write the query in JPQL. This may not be easy, because:

• Need to use functions, DB features not in JPQL
• Have many inheritance relationships in your model, which complicates your query.

JPA2.0 added some new stuff that helps with all of these, see here.

Due to a Hibernate bug though, you cannot use TypedQuery in this case..

Second try: use a native query

Simply use EntityManager's createNativeQuery method. Be warned, however if you return multiple columns, the returned result will be of List<Object[]> and it seems that there is no easy way to change that. This all means that you still need to post-process the returned result and create your own POJOs.

In many forums, the recommended way to solve this is to use @SqlResultSetMapping, however that only works for entities not POJOs. (Somewhere it was mentioned that it also works for @MappedSuperclass, I've found this not to work with Hibernate 4.1.2).

If you are using EclipseLink, you can add a query hint to return the result as a Map instead of Object[], using the @ResultType annotation.