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
Statement, and executing that
to obtain a
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
interface introduced in Chapter 10, JPA Query.
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
instance that will return instances of the specified class.
The second method uses a
to determine the type of object or objects to return.
The example below shows these methods in action.
Example 11.1. Creating a SQL Query
EntityManager em = ...; Query query = em.createNativeQuery ("SELECT * FROM MAG", Magazine.class); processMagazines (query.getResultList ());