JPA Result Set Mapper for Native Query

Version 5

    step 1 : describe problem in JPA with Native Query 

     

    List<Object[]>  objectlist =-------------------- ").getResultList();

         Iterator<Object[]> iterator = customerList1.iterator();

                while (iterator.hasNext()) {

                    Object[] customer = iterator.next();

                    Integer id = (Integer) customer[0];

                    String firstName = (String) customer[1];

                    String lastName = (String) customer[2];

            }

     

    =====================================very pain(indexing--customer[1]) coding form in JPA =======================================

     

    step 2 : solution

     

    1. step 1 format                                                              ------pain format

    1. JDBC dbutils                                                       ------------- using only jdbc app / Not for JPA app

    3. JPA Criteria api: hibernate . setResultTransformer     --------only support JPA hibernate criteria query not for other way

    4. sqlresultmaping             -          JPA  1.0               ---------use for JPA but database table field map is very hard for lage size query

    5. @ConstructorResult(targetClass )   JPA  2.1        ---------big problem when declare the mapping of the fields in the constructor    Ex --: columns={ @ColumnResult(name="balance"),

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                                            Last Solution -----------------QLRM 1.5 --Framework

                                                          Query Language Result Mapper

     

       download qlrm framework : qlrm-1.5.1.jar


    http://mirrors.ibiblio.org/maven2/ch/simas/qlrm/qlrm/1.5.1/

    http://mvnrepository.com/artifact/ch.simas.qlrm/qlrm/1.3

       

      sample :No need iterator /looping : only this two line


       Query query = em.createNativeQuery(sql1);

       List<ClosingRecord> closelist = jpaResultMapper.list(query, ClosingRecord.class);

     

    step 4 : Remarks-----------QLRM


          1. same number of arguments as the result of the SQL

          2. result types must match // type matching

          3. maintin order

     

    step 4 : QLRM problem : maintin order in constructor


    solution : ViewModel /DTO generation


                ResultSet rs = stmt.executeQuery(sql1);

               classGenerator.generateFromResultSet(System.getProperty("user.dir"), null, "EmployeeNameTO", false, rs);

     

     

    step 5 : follow attached file example  and

    -------------------------------------------------------------------End --------------------------------------------------------


    step 6 : some study on this topics  :


    http://blog.jooq.org/2013/09/11/annotatiomania-2-1/

    http://docs.oracle.com/javaee/7/api/javax/persistence/ConstructorResult.html


    simasch/qlrm · GitHub


    qlrm/ConstructorResult.md at master · simasch/qlrm · GitHub


    http://onpersistence.blogspot.com/2010/07/eclipselink-jpa-native-constructor.html