JPA Native Queries

The time when executing native SQL meant mishandles connections, heaps of finally blocks and prepared statements is finally over. With the introduction of the Java Persistence API (JPA 1.0) executing plain SQL queries is now as easy as it should be. JPA native queries have the following properties:

• A native query is assumed to return either a single scalar value, like:
  view plainprint?

  1. Query query = em.createNativeQuery(
  2. "select id from users where username = ?");
  3. query.setParameter(1, "lt");
  4. BigDecimal val = (BigDecimal) query.getSingleResult();

  Query query = em.createNativeQuery(
  "select id from users where username = ?");
  query.setParameter(1, "lt");
  BigDecimal val = (BigDecimal) query.getSingleResult();
  

  or return all persistent values as specified in a entity class:
  
  view plainprint?

  1. Query query = em.createNativeQuery(
  2. "select * from users where username = ?", User.class);
  3. query.setParameter(1, "lt");
  4. User user = (User) query.getSingleResult();

  Query query = em.createNativeQuery(
  "select * from users where username = ?", User.class);
  query.setParameter(1, "lt");
  User user = (User) query.getSingleResult();
  

  setting the result class to: User.class ensures that the returned columns are mapped to the fields of this class (either through defaulting or via explicit @Column mappings). All non-transient fields in the result class must be returned from the query (null values are allowed).
  


• Native queries can return lists of entities/scalars by issuing query.getResultList(). Database updates/deletes can be performed via the query.executeUpdate() command.


• Like JPQL, native queries can be named for easy reuse:
  
  view plainprint?

  1. @NamedNativeQuery(name = "User.findLike",
  2. resultClass = User.class, query = "select * "
  3. + "from users where username like concat(1,'%')")

  @NamedNativeQuery(name = "User.findLike",
  resultClass = User.class, query = "select * "
  + "from users where username like concat(1,'%')")
  

• which then can be used like:
  
  view plainprint?

  1. Query query = em.createNamedQuery("User.findLike");
  2. query.setParameter(1, "lt");
  3. List users = query.getResultList();

  Query query = em.createNamedQuery("User.findLike");
  query.setParameter(1, "lt");
  List users = query.getResultList();
  

  Only positional parameters are portable in native queries in the JPA 1.0 Specification (Section 3.6.6). Using named parameters in native queries is undefined.

Advanced Query Mapping

Native queries are often used when a simple entity bean is not enough requiring advanced mapping of the query result columns. Such mappings are done with the @SqlResultSetMapping annotation. Use it when:

1. The query does not return all the mapped columns in a entity bean.
2. The result columns of the query are named differently from the entity mapping (common when using the SQL as keyword)
   
3. You are returning a mixture of scalar and entity columns or multiple entities from several tables (common when issuing joins).

As an example consider the following mapping:

view plainprint?

1. @SqlResultSetMapping(name="PMapping",
2. entities={
3. @EntityResult(entityClass=Project.class,
4. fields={
5. @FieldResult(name="name",column="p_name"),
6. @FieldResult(name="description",column="p_desc")
7. })}
8. columns={ @ColumnResult(name="user_n") }
9. )

@SqlResultSetMapping(name="PMapping",
entities={
   @EntityResult(entityClass=Project.class,
      fields={
        @FieldResult(name="name",column="p_name"),
        @FieldResult(name="description",column="p_desc")
})}
columns={ @ColumnResult(name="user_n") }
)

which maps the following query:

view plainprint?

1. select u.name as u_name, p.name as p_name,
2. p.description as p_desc from users u, project p
3. where u.id = p.owner_id;

select u.name as u_name, p.name as p_name,
p.description as p_desc from users u, project p
where u.id = p.owner_id;

The @EntityResult annotation is used to map columns in the result set to fields of target entities. Thus the query must return the columns p_name and p_desc which will be mapped to the fields name and description. The @ColumnResult annotation is used to specify scalar results, using its name attribute to singling out column names that are going to be mapped as primitives. Notice how the query mapping is itself named, facilitating easy reuse, since a entity manager can reference it directly when creating a query:

view plainprint?

1. Query query = em.createNativeQuery("select..", "PMapping");

Query query = em.createNativeQuery("select..", "PMapping");

alternatively set it directly on a named native query:

view plainprint?

1. @NamedNativeQuery(name = "User.findLike",
2. resultSetMapping = "PMapping", query = "select ..")

@NamedNativeQuery(name = "User.findLike",
resultSetMapping = "PMapping", query = "select ..")

Since this query returns multi-typed values, executing it requires some tweaking:

view plainprint?

1. Query query = em.createNativeQuery("select..", "PMapping");
2. List results = query.getResultList();
3. Object[] resultSet1 = results.get(0);
4. // get objects from result set
5. Project project = (Project) resultSet1[0];
6. String username = (String) resultSet1[1];

Query query = em.createNativeQuery("select..", "PMapping");
List results = query.getResultList();
Object[] resultSet1 = results.get(0);
// get objects from result set
Project project = (Project) resultSet1[0];
String username = (String) resultSet1[1];

So to handle a multi-typed result you simple cast each array entry to the type denoted in the mapping. For complex column types such as CLOBs, cast to the appropriate JDBC type.

REFER: http://blog.randompage.org/2006/06/jpa-native-queries.html