Migrate JDBC ResultSet to JPA List object

Question

Hello I need to Migrate My DAO's Class from JDBC to JPA, but what i need is to dont need to change the rs.getInt("NAME_COLUMN") OR rs.getString("NAME_COLUMN")

the rest of the code PreparedStatement / while(rs.next()){....} its very easy to migrate... i only need to resultset variables...

JDBC CODE

PreparedStatement ps = connection.prepareStatement("SELECT f1, f2 FROM table");
ResultSet rs = ps.executeQuery();
while (rs.next()) {

    System.out.println(rs.getInt("f1"));
    System.out.println(rs.getString("f2"));
    // More than 800 of this... 
}

JPA CODE

EntityManager em = PersistenceUtil.getEntityManager();

Query q = em.createNativeQuery("SELECT f1, f2 FROM table");
List<Object[]> results = q.getResultList();

for (Object[] rs : results) {
     System.out.println(rs[0]); // need to be rs.getInt("f1")
     System.out.println(rs[1]); // need to be rs.getString("f2")
}

I have alot of ResultSet in my project more than 800

I need to a method/Class/something to migrate only the result sets variables (inside for), if I write in rs[0] I will take a lot of work to re-write all my resulsets in JPA.

Your have many requested tables ? Or you use often the same tables in your actual queries ? — davidxxx, Feb 10 '17 at 13:22
If you have a lot of this then why even bother with JPA? Also the fact that you use JPA in your application doesn't mean you have to use it everywhere? — M. Deinum, Feb 10 '17 at 13:24
yeah not in everywhere, in fact i have others projects too, this one will be the pilot — sealabr, Feb 10 '17 at 13:25
The biggest benefit of JPA is that you can abstract away the database and model things as Entities and have the framework generate queries for you. If you're going to just write native queries anyway then this doesn't seem to make much sense. — Mick Mnemonic, Feb 10 '17 at 13:37
our use case is clearly not the most suitable choice for JPA but if you have no choice, I have given you a way to simplify mapping with native queries. — davidxxx, Feb 10 '17 at 13:44
Could you maybe switch your ResultSet transformer to return something other than "Object[]" ? Hibernate can : http://stackoverflow.com/questions/2605385/using-sql-column-names-in-hibernate-createsqlquery-result — GPI, Feb 10 '17 at 15:10
lalokana answered Mar 7 '15 at 2:09 http://stackoverflow.com/questions/17202334/jpa-entity-manager-select-many-columns-and-get-result-list-custom-objects — sealabr, Feb 10 '17 at 15:58

score 1 · Accepted Answer · answered Feb 10 '17 at 16:21

here's the answer

    EntityManager em = PersistenceUtil.getEntityManager();

    Query q  = em.createNativeQuery("SELECT f1, f2 FROM table");
    q.setHint(QueryHints.RESULT_TYPE, ResultType.Map);
    List<Map> lm = q.getResultList();

        for (Map rs : lm) {

            System.out.println(rs.get("f1")); // add some function convert to Int
            System.out.println(rs.get("f2")); // add some function convert to string


        }

anyone knows any function to convert string and Int?

like: getInt or getString?

Migrate JDBC ResultSet to JPA List object

1 Answers1