Chapter 12. SQL Queries
Prev | Part 2. Java Persistence API | Next

## Chapter 12. SQL Queries

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.

## 1. Creating SQL Queries

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 12.1. Creating a SQL Query

    EntityManager em = ...;
    Query query = em.createNativeQuery("SELECT * FROM MAG", Magazine.class);
    processMagazines(query.getResultList());

### Note

In addition to SELECT statements, OpenJPA supports stored procedure invocations as SQL queries. OpenJPA will assume any SQL that does not begin with the `SELECT` keyword (ignoring case) is a stored procedure call, and invoke it as such at the JDBC level.

Prev | Up | Next
4. Generation of Canonical MetaModel classes | Home | 2. Retrieving Persistent Objects with SQL