Field's value of native query in JPA

Question

Dear members,

How to get value of some fields in a native query (JPA)?

For example I want to get name and age of customer table:

Query q = em.createNativeQuery("SELECT name,age FROM customer WHERE id=...");

Note: I don't want to map results to entities. I just want to get the value of the field.

Thanks

Answer 1

Depends on the JPA implementation. Hibernate does it different than castor, for example. Here's a example how it would work in Hibernate:

Query query = session.createSQLQuery(
"select s.stock_code from stock s where s.stock_code = :stockCode")
.setParameter("stockCode", "7277");
List result = query.list();

I don't think that it is possible to get a plane value such as an integer... but this one should come very close to it.

Answer 2

A native query with multiple select expressions returns an Object[] (or List<Object[]>). From the specification:

3.6.1 Query Interface

...

The elements of the result of a Java Persistence query whose SELECT clause consists of more than one select expression are of type Object[]. If the SELECT clause consists of only one select expression, the elements of the query result are of type Object. When native SQL queries are used, the SQL result set mapping (see section 3.6.6), determines how many items (entities, scalar values, etc.) are returned. If multiple items are returned, the elements of the query result are of type Object[]. If only a single item is returned as a result of the SQL result set mapping or if a result class is specified, the elements of the query result are of type Object.

So, to get the name and age in your example, you'd have to do something like this:

Query q = em.createNativeQuery("SELECT name,age FROM customer WHERE id = ?1");
q.setParameter(1, customerId);
Object[] result = (Object[])q.getSingleResult();
String name = result[0];
int age = result[1];

References

• JPA 1.0 specification
  • Section 3.6.1 "Query Interface"
  • Section 3.6.6 "SQL Queries"