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

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.

The easiest way is to use so projections. It can map query results directly to interfaces and is easier to implement than using SqlResultSetMapping.

An example is shown below:

@Repository public interface PeopleRepository extends JpaRepository<People, Long> { @Query(value = "SELECT p.name AS name, COUNT(dp.people_id) AS count " + "FROM people p INNER JOIN dream_people dp " + "ON p.id = dp.people_id " + "WHERE p.user_id = :userId " + "GROUP BY dp.people_id " + "ORDER BY p.name", nativeQuery = true) List<PeopleDTO> findByPeopleAndCountByUserId(@Param("userId") Long userId); @Query(value = "SELECT p.name AS name, COUNT(dp.people_id) AS count " + "FROM people p INNER JOIN dream_people dp " + "ON p.id = dp.people_id " + "WHERE p.user_id = :userId " + "GROUP BY dp.people_id " + "ORDER BY p.name", nativeQuery = true) Page<PeopleDTO> findByPeopleAndCountByUserId(@Param("userId") Long userId, Pageable pageable); } // Interface to which result is projected public interface PeopleDTO { String getName(); Long getCount(); } 

The fields from projected interface must match fields in this entity. Otherwise field mapping might break.

Also if you use SELECT table.column notation always define aliases matching names from entity as shown in example.

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.

Yes, with JPA 2.1 it's easy. You have very useful Annotations. They simplify your life.

First declare your native query, then your result set mapping (which defines the mapping of the data returned by the database to your POJOs). Write your POJO class to refer to (not included here for brevity). Last but not least: create a method in a DAO (for example) to call the query. This worked for me in a dropwizard (1.0.0) app.

First declare a native query in an entity class (@Entity):

@Entity @NamedNativeQuery ( name = "domain.io.MyClass.myQuery", query = "Select a.colA, a.colB from Table a", resultSetMapping = "mappinMyNativeQuery") // must be the same name as in the SqlResultSetMapping declaration 

Underneath you can add the resultset mapping declaration:

@SqlResultSetMapping( name = "mapppinNativeQuery", // same as resultSetMapping above in NativeQuery classes = { @ConstructorResult( targetClass = domain.io.MyMapping.class, columns = { @ColumnResult( name = "colA", type = Long.class), @ColumnResult( name = "colB", type = String.class) } ) } ) 

Later in a DAO you can refer to the query as

public List<domain.io.MyMapping> findAll() { return (namedQuery("domain.io.MyClass.myQuery").list()); } 

That's it.

If you use Spring-jpa, this is a supplement to the answers and this question. Please correct this if any flaws. I have mainly used three methods to achieve "mapping result Object[] to a pojo" based on what practical need I meet:

1. JPA built in method is enough.
2. JPA built in method is not enough, but a customized sql with its Entity are enough.

3. The former 2 failed, and I have to use a nativeQuery. Here are the examples. The pojo expected:

   public class Antistealingdto { private String secretKey; private Integer successRate; // GETTERs AND SETTERs public Antistealingdto(String secretKey, Integer successRate) { this.secretKey = secretKey; this.successRate = successRate; } } 

Method 1: Change the pojo into an interface:

public interface Antistealingdto { String getSecretKey(); Integer getSuccessRate(); } 

And repository:

interface AntiStealingRepository extends CrudRepository<Antistealing, Long> { Antistealingdto findById(Long id); } 

Method 2: Repository:

@Query("select new AntistealingDTO(secretKey, successRate) from Antistealing where ....") Antistealing whatevernamehere(conditions); 

Note: parameter sequence of POJO constructor must be identical in both POJO definition and sql.

Method 3: Use @SqlResultSetMapping and @NamedNativeQuery in Entity as the example in Edwin Dalorzo's answer.

The first two methods would call many in-the-middle handlers, like customized converters. For example, AntiStealing defines a secretKey, before it is persisted, a converter is inserted to encrypt it. This would result in the first 2 methods returning a converted back secretKey which is not what I want. While the method 3 would overcome the converter, and returned secretKey would be the same as it is stored (an encrypted one).

In hibernate you can use this code to easily map your native query.

private List < Map < String, Object >> getNativeQueryResultInMap() { String mapQueryStr = "SELECT * FROM AB_SERVICE three "; Query query = em.createNativeQuery(mapQueryStr); NativeQueryImpl nativeQuery = (NativeQueryImpl) query; nativeQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); List < Map < String, Object >> result = query.getResultList(); for (Map map: result) { System.out.println("after request ::: " + map); } return result;} 

First declare following annotations:

@Target(ElementType.TYPE) @Retention(RetentionPolicy.RUNTIME) public @interface NativeQueryResultEntity { } @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) public @interface NativeQueryResultColumn { int index(); } 

Then annotate your POJO as follows:

@NativeQueryResultEntity public class ClassX { @NativeQueryResultColumn(index=0) private String a; @NativeQueryResultColumn(index=1) private String b; } 

Then write annotation processor:

public class NativeQueryResultsMapper { private static Logger log = LoggerFactory.getLogger(NativeQueryResultsMapper.class); public static <T> List<T> map(List<Object[]> objectArrayList, Class<T> genericType) { List<T> ret = new ArrayList<T>(); List<Field> mappingFields = getNativeQueryResultColumnAnnotatedFields(genericType); try { for (Object[] objectArr : objectArrayList) { T t = genericType.newInstance(); for (int i = 0; i < objectArr.length; i++) { BeanUtils.setProperty(t, mappingFields.get(i).getName(), objectArr[i]); } ret.add(t); } } catch (InstantiationException ie) { log.debug("Cannot instantiate: ", ie); ret.clear(); } catch (IllegalAccessException iae) { log.debug("Illegal access: ", iae); ret.clear(); } catch (InvocationTargetException ite) { log.debug("Cannot invoke method: ", ite); ret.clear(); } return ret; } // Get ordered list of fields private static <T> List<Field> getNativeQueryResultColumnAnnotatedFields(Class<T> genericType) { Field[] fields = genericType.getDeclaredFields(); List<Field> orderedFields = Arrays.asList(new Field[fields.length]); for (int i = 0; i < fields.length; i++) { if (fields[i].isAnnotationPresent(NativeQueryResultColumn.class)) { NativeQueryResultColumn nqrc = fields[i].getAnnotation(NativeQueryResultColumn.class); orderedFields.set(nqrc.index(), fields[i]); } } return orderedFields; } } 

Use above framework as follows:

String sql = "select a,b from x order by a"; Query q = entityManager.createNativeQuery(sql); List<ClassX> results = NativeQueryResultsMapper.map(q.getResultList(), ClassX.class); 

Using Hibernate :

@Transactional(readOnly=true) public void accessUser() { EntityManager em = repo.getEntityManager(); org.hibernate.Session session = em.unwrap(org.hibernate.Session.class); org.hibernate.SQLQuery q = (org.hibernate.SQLQuery) session.createSQLQuery("SELECT u.username, u.name, u.email, 'blabla' as passe, login_type as loginType FROM users u").addScalar("username", StringType.INSTANCE).addScalar("name", StringType.INSTANCE).addScalar("email", StringType.INSTANCE).addScalar("passe", StringType.INSTANCE).addScalar("loginType", IntegerType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(User2DTO.class)); List<User2DTO> userList = q.list(); } 

We have resolved the issue using following way :

 //Add actual table name here in Query final String sqlQuery = "Select a.* from ACTORS a" // add your entity manager here Query query = entityManager.createNativeQuery(sqlQuery,Actors.class); //List contains the mapped entity data. List<Actors> list = (List<Actors>) query.getResultList(); 

Since others have already mentioned all the possible solutions, I am sharing my workaround solution.

In my situation with Postgres 9.4, while working with Jackson,

//Convert it to named native query. List<String> list = em.createNativeQuery("select cast(array_to_json(array_agg(row_to_json(a))) as text) from myschema.actors a") .getResultList(); List<ActorProxy> map = new ObjectMapper().readValue(list.get(0), new TypeReference<List<ActorProxy>>() {}); 

I am sure you can find same for other databases.

Also FYI, JPA 2.0 native query results as map

Old style using ResultSet

@Transactional(readOnly=true) public void accessUser() { EntityManager em = this.getEntityManager(); org.hibernate.Session session = em.unwrap(org.hibernate.Session.class); session.doWork(new Work() { @Override public void execute(Connection con) throws SQLException { try (PreparedStatement stmt = con.prepareStatement( "SELECT u.username, u.name, u.email, 'blabla' as passe, login_type as loginType FROM users u")) { ResultSet rs = stmt.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 1; i <= rsmd.getColumnCount(); i++) { System.out.print(rsmd.getColumnName(i) + " (" + rsmd.getColumnTypeName(i) + ") / "); } System.out.println(""); while (rs.next()) { System.out.println("Found username " + rs.getString("USERNAME") + " name " + rs.getString("NAME") + " email " + rs.getString("EMAIL") + " passe " + rs.getString("PASSE") + " email " + rs.getInt("LOGIN_TYPE")); } } } }); } 

If you want to map the custom query result directly to an entity without writing any code to map, try this way. In my experience, it is the most convenient way to do, but the downside is to lose the benefit of hibernate ddl-auto:

1. Disable hibernate validation by removing the hibernate.ddl-auto. If not doing this, hibernate can complain about missing table in database.

2. Create a pojo with @Entity for the custom result set without table mapping, something like:

   @Getter @Setter @Entity public class MyCustomeResult implements Serializable { @Id private Long id; @Column(name = "name") private String name; } 

3. In repository, use the entity to map directly from query.getResultList()

   public List<MyCustomeResult> findByExampleCustomQuery(Long test) { String sql = "select id, name from examples where id =:test"; Query query = entityManager.createNativeQuery(sql, MyCustomeResult.class); return query.setParameter("test", test).getResultList(); } 

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.

//PackageCustomerResponse Class /** * @author : azad * @mailto : [email protected] * @created : 10/17/2023, Tuesday **/ public interface PackageCustomerResponse { Long getCustomerId(); String getCustomerName(); Long getPackageId(); String getPackageName(); LocalDate getStartDate(); } //Repository @Repository public interface CustomerRepository extends JpaRepository<Customer, Long> { @Query(nativeQuery = true, value="SELECT c.customer_id as customerId, c.customer_name as customerName, pa.package_id as packageId, " + "pa.package_name as packageName, pa.start_date as startDate FROM customers c "+ "INNER JOIN customer_packages p ON p.customer_id = c.customer_id "+ "INNER JOIN packages pa ON pa.package_id = p.package_id") List<PackageCustomerResponse> findPackageCustomer(); } 

See example below for using a POJO as pseudo entity to retrieve result from native query without using complex SqlResultSetMapping. Just need two annotations, a bare @Enity and a dummy @Id in your POJO. @Id can be used on any field of your choice, an @Id field can have duplicate keys but not null values.

Since @Enity does not map to any physical table, so this POJO is called a pseudo entity.

Environment: eclipselink 2.5.0-RC1, jpa-2.1.0, mysql-connector-java-5.1.14

You can download complete maven project here

Native query is based on mysql sample employees db http://dev.mysql.com/doc/employee/en/employees-installation.html

persistence.xml

<?xml version="1.0" encoding="UTF-8"?><persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.1" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"> <persistence-unit name="jpa-mysql" transaction-type="RESOURCE_LOCAL"> <class>org.moonwave.jpa.model.pojo.Employee</class> <properties> <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/employees" /> <property name="javax.persistence.jdbc.user" value="user" /> <property name="javax.persistence.jdbc.password" value="***" /> <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" /> </properties> </persistence-unit> 

Employee.java

package org.moonwave.jpa.model.pojo; @Entity public class Employee { @Id protected Long empNo; protected String firstName; protected String lastName; protected String title; public Long getEmpNo() { return empNo; } public void setEmpNo(Long empNo) { this.empNo = empNo; } public String getFirstName() { return firstName; } public void setFirstName(String firstName) { this.firstName = firstName; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String toString() { StringBuilder sb = new StringBuilder(); sb.append("empNo: ").append(empNo); sb.append(", firstName: ").append(firstName); sb.append(", lastName: ").append(lastName); sb.append(", title: ").append(title); return sb.toString(); } } 

EmployeeNativeQuery.java

public class EmployeeNativeQuery { private EntityManager em; private EntityManagerFactory emf; public void setUp() throws Exception { emf=Persistence.createEntityManagerFactory("jpa-mysql"); em=emf.createEntityManager(); } public void tearDown()throws Exception { em.close(); emf.close(); } @SuppressWarnings("unchecked") public void query() { Query query = em.createNativeQuery("select e.emp_no as empNo, e.first_name as firstName, e.last_name as lastName," + "t.title from employees e join titles t on e.emp_no = t.emp_no", Employee.class); query.setMaxResults(30); List<Employee> list = (List<Employee>) query.getResultList(); int i = 0; for (Object emp : list) { System.out.println(++i + ": " + emp.toString()); } } public static void main( String[] args ) { EmployeeNativeQuery test = new EmployeeNativeQuery(); try { test.setUp(); test.query(); test.tearDown(); } catch (Exception e) { System.out.println(e); } } } 

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. } 

Using Hibernate :

@Transactional(readOnly=true) public void accessUser() { EntityManager em = repo.getEntityManager(); org.hibernate.Session session = em.unwrap(org.hibernate.Session.class); org.hibernate.SQLQuery q = (org.hibernate.SQLQuery) session.createSQLQuery("SELECT u.username, u.name, u.email, 'blabla' as passe, login_type as loginType FROM users u") .addScalar("username", StringType.INSTANCE).addScalar("name", StringType.INSTANCE) .addScalar("email", StringType.INSTANCE).addScalar("passe", StringType.INSTANCE) .addScalar("loginType", IntegerType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(User2DTO.class)); List<User2DTO> userList = q.list(); } 

I tried a lot of things as mentioned in the above answers. The SQLmapper was very confusing as to where to put it. Non managed POJOs only were a problem. I was trying various ways and one easy way I got it worked was as usual. I am using hibernate-jpa-2.1.

List<TestInfo> testInfoList = factory.createNativeQuery(QueryConstants.RUNNING_TEST_INFO_QUERY) .getResultList(); 

The only thing to take care was that POJO has same member variable names as that of the query ( all in lowercase). And apparently I didn't even need to tell the target class along with query as we do with TypedQueries in JPQL.

TestInfo.class

@Setter @Getter @NoArgsConstructor @ToString public class TestInfo { private String emailid; private Long testcount; public TestInfo(String emailId, Long testCount) { super(); this.emailid = emailId; this.testcount = testCount; } } 

Using "Database View" like entity a.k.a immutable entity is super easy for this case.

Normal entity

@Entity @Table(name = "people") data class Person( @Id val id: Long = -1, val firstName: String = "", val lastName: String? = null ) 

View like entity

@Entity @Immutable @Subselect(""" select p.id, concat(p.first_name, ' ', p.last_name) as full_name from people p """) data class PersonMin( @Id val id: Long, val fullName: String, ) 

In any repository we can create query function/method just like:

@Query(value = "select p from PersonMin p") fun findPeopleMinimum(pageable: Pageable): Page<PersonMin> 

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

Simple way to converting SQL query to POJO class collection ,

Query query = getCurrentSession().createSQLQuery(sqlQuery).addEntity(Actors.class); List<Actors> list = (List<Actors>) query.list(); return list; 

If the query is not too complicated you can do something like this. In my case i needed to use a H2 FT_Search result query to make another query.

var ftSearchQuery = "SELECT * FROM FT_SEARCH(\'something\', 0, 0)"; List<Object[]> results = query.getResultList(); List<Model> models = new ArrayList<>(); for (Object[] result : results) { var newQuery = "SELECT * FROM " + (String) result[0]; models.addAll(entityManager.createNativeQuery(newQuery, Model.class).getResultList()); } 

There are probably cleaner way to do this.