How to get all the element from JDBC query

Question

HI,

I have query like this

final Query contractQuery = cgnDao.getEntityManager().
            createNativeQuery("SELECT k.phrase, ak.type FROM key k INNER JOIN adkey ak USING (key_id) WHERE pck.pub_id =" + pid +" AND pck.c_id =" + campId );

How can i get each and every element from the query?

Where phrase is an String and type is enum

in Java

Thanks

Answer 1

You can use value of method to gets enum instances from result set

enum A {}
A.valueOf()

Answer 2

See both this link on the JBoss JPA docs and this link on java2s.

In short, you have a Query on which you can call getResultList(), which returns a List you can iterate over. Have a look at the JPA javadoc.

Also, I'd recommend using PreparedStatements, something like

String sqlQuery = "select * from tbl_spaceship where owner = ?";
Query q = entityManager.createNativeQuery(sqlQuery, SpaceShip.class);
q.setParameter( 1, "Han" );
q.getResultList();

The advantage is that the JPA provider will take care of escaping the input values. Not necessary at this particular use-case but good habits never hurt.

Answer 3

First of all, you shouldn't use String concatenation but positional parameters (only positional parameter binding may be portably used for native queries). Second, for a native query returning scalar values, the result would be a List of Oject[]. So the result might look like this:

String sql = "SELECT k.phrase, ak.type " + 
             "FROM key k INNER JOIN adkey ak USING (key_id) " +
             "WHERE pck.pub_id = ?1 AND pck.c_id = ?2";
Query q = em.createNativeQuery(sql);
q.setParameter(1, pubId);
q.setParameter(2, cId);
List<Object[]> results = q.getResultList();

References

• JPA 1.0 specification
  • Section 3.6.3 "Named Parameters"
  • Section 3.6.6 "SQL Queries"
  • Section 4.6.4 "Input Parameters"