JPQL is a powerful query language, but there are times when it is not enough. Maybe you're migrating a JDBC application to JPA on a strict deadline, and you don't have time to translate your existing SQL selects to JPQL. Or maybe a certain query requires database-specific SQL your JPA implementation doesn't support. Or maybe your DBA has spent hours crafting the perfect select statement for a query in your application's critical path. Whatever the reason, SQL queries can remain an essential part of an application.
You are probably familiar with executing SQL queries by obtaining a
java.sql.Connection, using the JDBC APIs to create a
Statement, and executing that
ResultSet. And of course, you are free to
continue using this low-level approach to SQL execution in your JPA
applications. However, JPA also supports executing SQL queries through the
javax.persistence.Query interface introduced in
. Using a JPA SQL query, you can
retrieve either persistent objects or projections of column values. The
following sections detail each use.
EntityManager has two factory methods suitable for
creating SQL queries:
public Query createNativeQuery(String sqlString, Class resultClass); public Query createNativeQuery(String sqlString, String resultSetMapping);
The first method is used to create a new
that will return instances of the specified class.
The second method uses a
SqlResultSetMapping to determine the
type of object or objects to return. The example below shows these methods in
Example 11.1. Creating a SQL Query
EntityManager em = ...; Query query = em.createNativeQuery("SELECT * FROM MAG", Magazine.class); processMagazines(query.getResultList());