EJBQL is a powerful query language, but there are times when it is not enough. Maybe you're migrating a JDBC application to EJB on a strict deadline, and you don't have time to translate your existing SQL selects to EJBQL. Or maybe a certain query requires database-specific SQL your EJB 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 EJB applications. However, EJB also supports executing SQL queries through the javax.persistence.Query interface introduced in Chapter 10, EJB Query. Using a EJB 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.