Table of Contents
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 Statement 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 through the
javax.persistence.Query 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.
The 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 Query instance
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
action.
Example 11.1. Creating a SQL Query
EntityManager em = ...;
Query query = em.createNativeQuery("SELECT * FROM MAG", Magazine.class);
processMagazines(query.getResultList());