JPA : How to convert a native query result set to POJO class collection

Question

I am using JPA in my project.

I came to a query in which I need to make join operation on five tables. So I created a native query which returns five fields.

Now I want to convert the result object to java POJO class which contains the same five Strings.

Is there any way in JPA to directly cast that result to POJO object list ??

I came to the following solution ..

@NamedNativeQueries({  
    @NamedNativeQuery(  
        name = "nativeSQL",  
        query = "SELECT * FROM Actors",  
        resultClass = db.Actor.class),  
    @NamedNativeQuery(  
        name = "nativeSQL2",  
        query = "SELECT COUNT(*) FROM Actors",  
        resultClass = XXXXX) // <--------------- problem  
})  

Now here in resultClass, do we need to provide a class which is actual JPA entity ? OR We can convert it to any JAVA POJO class which contains the same column names ?

Answer 1

I have found a couple of solutions to this.

Using Mapped Entities (JPA 2.0)

Using JPA 2.0 it is not possible to map a native query to a POJO, it can only be done with an entity.

For instance:

Query query = em.createNativeQuery("SELECT name,age FROM jedi_table", Jedi.class);
@SuppressWarnings("unchecked")
List<Jedi> items = (List<Jedi>) query.getResultList();

But in this case, Jedi, must be a mapped entity class.

An alternative to avoid the unchecked warning here, would be to use a named native query. So if we declare the native query in an entity

@NamedNativeQuery(
 name="jedisQry", 
 query = "SELECT name,age FROM jedis_table", 
 resultClass = Jedi.class)

Then, we can simply do:

TypedQuery<Jedi> query = em.createNamedQuery("jedisQry", Jedi.class);
List<Jedi> items = query.getResultList();

This is safer, but we are still restricted to use a mapped entity.

Manual Mapping

A solution I experimented a bit (before the arrival of JPA 2.1) was doing mapping against a POJO constructor using a bit of reflection.

public static <T> T map(Class<T> type, Object[] tuple){
   List<Class<?>> tupleTypes = new ArrayList<>();
   for(Object field : tuple){
      tupleTypes.add(field.getClass());
   }
   try {
      Constructor<T> ctor = type.getConstructor(tupleTypes.toArray(new Class<?>[tuple.length]));
      return ctor.newInstance(tuple);
   } catch (Exception e) {
      throw new RuntimeException(e);
   }
}

This method basically takes a tuple array (as returned by native queries) and maps it against a provided POJO class by looking for a constructor that has the same number of fields and of the same type.

Then we can use convenient methods like:

public static <T> List<T> map(Class<T> type, List<Object[]> records){
   List<T> result = new LinkedList<>();
   for(Object[] record : records){
      result.add(map(type, record));
   }
   return result;
}

public static <T> List<T> getResultList(Query query, Class<T> type){
  @SuppressWarnings("unchecked")
  List<Object[]> records = query.getResultList();
  return map(type, records);
}

And we can simply use this technique as follows:

Query query = em.createNativeQuery("SELECT name,age FROM jedis_table");
List<Jedi> jedis = getResultList(query, Jedi.class);

JPA 2.1 with @SqlResultSetMapping

With the arrival of JPA 2.1, we can use the @SqlResultSetMapping annotation to solve the problem.

We need to declare a result set mapping somewhere in a entity:

@SqlResultSetMapping(name="JediResult", classes = {
    @ConstructorResult(targetClass = Jedi.class, 
    columns = {@ColumnResult(name="name"), @ColumnResult(name="age")})
})

And then we simply do:

Query query = em.createNativeQuery("SELECT name,age FROM jedis_table", "JediResult");
@SuppressWarnings("unchecked")
List<Jedi> samples = query.getResultList();

Of course, in this case Jedi needs not to be an mapped entity. It can be a regular POJO.

Using XML Mapping

I am one of those that find adding all these @SqlResultSetMapping pretty invasive in my entities, and I particularly dislike the definition of named queries within entities, so alternatively I do all this in the META-INF/orm.xml file:

<named-native-query name="GetAllJedi" result-set-mapping="JediMapping">
    <query>SELECT name,age FROM jedi_table</query>
</named-native-query>

<sql-result-set-mapping name="JediMapping">
        <constructor-result target-class="org.answer.model.Jedi">
            <column name="name" class="java.lang.String"/>
            <column name="age" class="java.lang.Integer"/>
        </constructor-result>
    </sql-result-set-mapping>

And those are all the solutions I know. The last two are the ideal way if we can use JPA 2.1.