JPA : How to convert a native query result set to

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

Not sure if this fits here, but I had similar question and found following simple solution/example for me:

private EntityManager entityManager;
...
    final String sql = " SELECT * FROM STORE "; // select from the table STORE
    final Query sqlQuery = entityManager.createNativeQuery(sql, Store.class);

    @SuppressWarnings("unchecked")
    List<Store> results = (List<Store>) sqlQuery.getResultList();

In my case I had to use SQL parts defined in Strings somewhere else, so I could not just use NamedNativeQuery.

Answer 2

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.

Answer 3

Unwrap procedure can be performed to assign results to non-entity(which is Beans/POJO). The procedure is as following.

List<JobDTO> dtoList = entityManager.createNativeQuery(sql)
        .setParameter("userId", userId)
        .unwrap(org.hibernate.Query.class).setResultTransformer(Transformers.aliasToBean(JobDTO.class)).list();

The usage is for JPA-Hibernate implementation.

Answer 4

Use DTO Design Pattern. It was used in EJB 2.0. Entity was container managed. DTO Design Pattern is used to solve this problem. But, it might be use now, when the application is developed Server Side and Client Side separately.DTO is used when Server side doesn't want to pass/return Entity with annotation to Client Side.

DTO Example :

PersonEntity.java

@Entity
public class PersonEntity {
    @Id
    private String id;
    private String address;

    public PersonEntity(){

    }
    public PersonEntity(String id, String address) {
        this.id = id;
        this.address = address;
    }
    //getter and setter

}

PersonDTO.java

public class PersonDTO {
    private String id;
    private String address;

    public PersonDTO() {
    }
    public PersonDTO(String id, String address) {
        this.id = id;
        this.address = address;
    }

    //getter and setter 
}

DTOBuilder.java

public class DTOBuilder() {
    public static PersonDTO buildPersonDTO(PersonEntity person) {
        return new PersonDTO(person.getId(). person.getAddress());
    }
}

EntityBuilder.java <-- it mide be need

public class EntityBuilder() {
    public static PersonEntity buildPersonEntity(PersonDTO person) {
        return new PersonEntity(person.getId(). person.getAddress());
    }
}

Answer 5

JPA provides an SqlResultSetMapping that allows you to map whatever returns from your native query into an Entity or a custom class.

EDIT JPA 1.0 does not allow mapping to non-entity classes. Only in JPA 2.1 a ConstructorResult has been added to map return values a java class.

Also, for OP's problem with getting count it should be enough to define a result set mapping with a single ColumnResult

Answer 6

if you are using Spring, you can use org.springframework.jdbc.core.RowMapper

Here is an example:

public List query(String objectType, String namedQuery)
{
  String rowMapper = objectType + "RowMapper";
  // then by reflection you can instantiate and use. The RowMapper classes need to follow the naming specific convention to follow such implementation.
}