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.
This feature requires Kodo Enterprise Edition.
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 ());