JPQL - EJB 3 in Action - page 458

As in JPQL, you can use both dynamic queries and named queries with SQL. You have to
remember the subtle differences between JPQL and SQL. JPQL returns an entity, or set,
of scalar values, but a SQL query returns database records. Therefore, a SQL query may
return more than entities, because you may join multiple tables in your SQL. Let's see how
to use native SQL with both dynamic and native queries.
11.3.1. Using dynamic queries with native SQL
You can use the createNativeQuery method of the EntityManager interface to
create a dynamic query using SQL as follows:
Query q = em.createNativeQuery("SELECT user_id, first_name, last_name "
+ " FROM users WHERE user_id IN (SELECT seller_id FROM "
+ "items GROUP BY seller_id HAVING COUNT(*) > 1)",
actionbazaar.persistence.User.class);
return q.getResultList();
In this statement the createNativeQuery method takes two parameters: the SQL
query and the entity class being returned. This will become an issue if the query returns
more than one entity class, which is why JPA allows a @SqlResultSetMapping to
be used with the createNativeQuery method instead of passing an entity class. A
@SqlResultSetMapping may be mapped to one or more entities.
For example, if you want to create a SqlResultSetMapping for the User entity and
use it in your native query, then you can use the @SqlResultSetMapping annotation
as follows:
@SqlResultSetMapping(name = "UserResults",
entities = @EntityResult(
entityClass = actionbazaar.persistence.User.class))
Then you can specify the mapping in the query as follows:
Query q = em.createNativeQuery("SELECT user_id, first_name, last_name "
+ " FROM users WHERE user_id IN (SELECT seller_id FROM "
+ "items GROUP BY seller_id HAVING COUNT(*) > 1)",
"UserResults");
return q.getResultList();