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

## Chapter 11. 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.

| Important
This feature requires Kodo Enterprise Edition.

## 11.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 11.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, Kodo supports stored procedure invocations as SQL queries. Kodo 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
10.2. JPQL Language Reference | Home | 11.2. Retrieving Persistent Objects with SQL