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      lt --------------- 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

User · Answer

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    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 lt domain io MyMapping gt  findAll             return  namedQuery  domain io MyClass myQuery   list             That s it

User · Answer

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  lt T gt  List lt T gt  map List lt Object   gt  objectArrayList  Class lt T gt  genericType            List lt T gt  ret   new ArrayList lt T gt             List lt Field gt  mappingFields   getNativeQueryResultColumnAnnotatedFields genericType           try               for  Object   objectArr   objectArrayList                    T t   genericType newInstance                    for  int i   0  i  lt  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  lt T gt  List lt Field gt  getNativeQueryResultColumnAnnotatedFields Class lt T gt  genericType            Field   fields   genericType getDeclaredFields            List lt Field gt  orderedFields   Arrays asList new Field fields length            for  int i   0  i  lt  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 lt ClassX gt  results   NativeQueryResultsMapper map q getResultList    ClassX class

User · Answer

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    JPA built in method is enough  JPA built in method is not enough  but a customized sql with its Entity are enough  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 lt Antistealing  Long gt        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

User · Answer

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 lt String gt  list   em createNativeQuery  select cast array to json array agg row to json a    as text  from myschema actors a                       getResultList     List lt ActorProxy gt  map   new ObjectMapper   readValue list get 0   new TypeReference lt List lt ActorProxy gt  gt           I am sure you can find same for other databases   Also FYI  JPA 2 0 native query results as map

User · Answer

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 lt TestInfo gt  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

User · Answer

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 lt Actors gt  list    List lt Actors gt   query getResultList

User · Answer

Unwrap procedure can be performed to assign results to non-entity which is Beans POJO   The procedure is as following   List lt JobDTO gt  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

User · Answer

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 lt People  Long gt          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 lt PeopleDTO gt  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 lt PeopleDTO gt  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

User · Answer

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    quot SELECT   FROM FT SEARCH   something    0  0  quot   List lt Object   gt  results   query getResultList    List lt Model gt  models   new ArrayList lt  gt     for  Object   result   results        var newQuery    quot SELECT   FROM  quot     String  result 0       models addAll entityManager createNativeQuery newQuery  Model class  getResultList          There are probably cleaner way to do this

User · Answer

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  lt -- it mide be need  public class EntityBuilder         public static PersonEntity buildPersonEntity PersonDTO person            return new PersonEntity person getId    person getAddress

User · Answer

All you need is a DTO with a constructor    public class User2DTO implements Serializable            pode ser email ou id do Google comecando com G ou F para Facebook        private String username       private String password       private String email       private String name       private Integer loginType       public User2DTO Object   fields            super            this username    String  fields 0           this name    String  fields 1           this email    String  fields 2           this password    String  fields 3           this loginType    Integer  fields 4           and call it    EntityManager em   repo getEntityManager            Query q   em createNativeQuery  SELECT u username  u name  u email   blabla  as passe  login type as loginType FROM users u            List lt Object   gt  objList   q getResultList            List lt User2DTO gt  ooBj   objList stream   map User2DTO  new  collect Collectors toList

User · Answer

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

User · Answer

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   lt  xml version  1 0  encoding  UTF-8   gt  lt 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  gt   lt persistence-unit name  jpa-mysql  transaction-type  RESOURCE LOCAL  gt       lt class gt org moonwave jpa model pojo Employee lt  class gt       lt properties gt           lt property name  javax persistence jdbc url  value  jdbc mysql   localhost 3306 employees    gt           lt property name  javax persistence jdbc user  value  user    gt           lt property name  javax persistence jdbc password  value         gt           lt property name  javax persistence jdbc driver  value  com mysql jdbc Driver    gt       lt  properties gt   lt  persistence-unit gt      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 lt Employee gt  list    List lt Employee gt   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

User · Answer

In hibernate you  can use this code to easily map your native query   private List  lt  Map  lt  String  Object  gt  gt  getNativeQueryResultInMap     String mapQueryStr    SELECT   FROM AB SERVICE three    Query query   em createNativeQuery mapQueryStr   NativeQueryImpl nativeQuery    NativeQueryImpl  query  nativeQuery setResultTransformer AliasToEntityMapResultTransformer INSTANCE   List  lt  Map  lt  String  Object  gt  gt  result   query getResultList    for  Map map  result        System out println  after request          map     return result

User · Answer

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 lt User2DTO gt  userList   q list

User · Answer

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

User · Answer

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 lt User2DTO gt  userList   q list

User · Answer

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  lt   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

User · Answer

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 lt Store gt  results    List lt Store gt   sqlQuery getResultList      In my case I had to use SQL parts defined in Strings somewhere else  so I could not just use NamedNativeQuery

User · Answer

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 lt Jedi gt  items    List lt Jedi gt   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 lt Jedi gt  query   em createNamedQuery  jedisQry   Jedi class   List lt Jedi gt  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  lt T gt  T map Class lt T gt  type  Object   tuple      List lt Class lt   gt  gt  tupleTypes   new ArrayList lt  gt        for Object field   tuple         tupleTypes add field getClass             try         Constructor lt T gt  ctor   type getConstructor tupleTypes toArray new Class lt   gt  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  lt T gt  List lt T gt  map Class lt T gt  type  List lt Object   gt  records      List lt T gt  result   new LinkedList lt  gt        for Object   record   records         result add map type  record            return result     public static  lt T gt  List lt T gt  getResultList Query query  Class lt T gt  type      SuppressWarnings  unchecked     List lt Object   gt  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 lt Jedi gt  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 lt Jedi gt  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    lt named-native-query name  GetAllJedi  result-set-mapping  JediMapping  gt       lt query gt SELECT name age FROM jedi table lt  query gt   lt  named-native-query gt    lt sql-result-set-mapping name  JediMapping  gt           lt constructor-result target-class  org answer model Jedi  gt               lt column name  name  class  java lang String   gt               lt column name  age  class  java lang Integer   gt           lt  constructor-result gt       lt  sql-result-set-mapping gt    And those are all the solutions I know  The last two are the ideal way if we can use JPA 2 1

User · Answer

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  lt   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

User · Answer

Using  quot Database View quot  like entity a k a immutable entity is super easy for this case  Normal entity  Entity  Table name    quot people quot   data class Person     Id   val id  Long   -1    val firstName  String    quot  quot     val lastName  String    null    View like entity  Entity  Immutable  Subselect  quot  quot  quot  select     p id      concat p first name       p last name  as full name from people p  quot  quot  quot   data class PersonMin     Id   val id  Long    val fullName  String     In any repository we can create query function method just like   Query value    quot select p from PersonMin p quot   fun findPeopleMinimum pageable  Pageable   Page lt PersonMin gt

User · Answer

Simple way to converting SQL query to POJO class collection    Query query   getCurrentSession   createSQLQuery sqlQuery  addEntity Actors class   List lt Actors gt  list    List lt Actors gt   query list    return list

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

Examples related to java

Examples related to jpa