Just what is native SQL? It’s the SQL understood by the specific database server— Oracle, MySQL, Derby, etc.—that you are using. This section provides what you need to start using native SQL with EJB 3 right now.
NOTE In EJB 2 CMP entity beans, almost every vendor supported their own way of using SQL to perform queries. The many limitations in EJBQL were the primary driver for the vendor-specific extension for native SQL in EJB 2. Although JPA standardizes use of native SQL queries you should think twice about using native SQL in your applications, unless you are very proficient in SQL and you are trying to take advantage of the proprietary features of your database. Also keep in mind that the use of native SQL will make your applications less portable, if you decide to change your underlying database.
Suppose you want to generate a hierarchical list of categories, each showing its subcategories; it’s impossible to do that in JPQL because JPQL does not support recursive joins, similar to databases like Oracle. This means you have to take advantage of native SQL.
Let’s assume you’re using an Oracle database and you want to retrieve all subcategories of a particular Category by using recursive joins in the form of a START with … connect by … clause as follows:
Ideally, you should limit your use of native SQL to queries that you cannot express using JPQL (as in our Oracle database-specific SQL query). However, for demonstration purposes, in our example in the next section, we’ve used a simple SQL statement that can be used with most relational databases.
NOTE A JPA provider just executes SQL statements as JDBC statements and does not track whether the SQL statement updated data related to any entities. You should avoid using SQL INSERT, UPDATE, and DELETE statements in a native query because your persistence provider will have no knowledge of such changes in the database and it may lead to inconsistent/stale data if your JPA provider uses caching.
As in JPQL, you can use both dynamic queries and named queries with SQL. You have to remember the subtle differences between JPQL and SQL. JPQL returns an entity, or set, of scalar values, but a SQL query returns database records. Therefore, a SQL query may return more than entities, because you may join multiple tables in your SQL. Let’s see how to use native SQL with both dynamic and native queries.
Using dynamic queries with native SQL
You can use the createNativeQuery method of the EntityManager interface to create a dynamic query using SQL as follows:
In this statement, the createNativeQuery method takes two parameters: the SQL query and the entity class being returned. This will become an issue if the query returns more than one entity class—which is why JPA allows a @SqlResultSet-Mapping to be used with the createNativeQuery method instead of passing an entity class. A @SqlResultSetMapping may be mapped to one or more entities.
For example, if we want to create a SqlResultSetMapping for the User entity and use in our native query, then we can use the @SqlResultSetMapping annotation as follows:
Then we can specify the mapping in the Query as follows:
This is useful when the SQL query returns more than one entity. The persistence provider will automatically determine the entities being returned based on the SqlResultSetMapping, instantiate the appropriate entities, and initialize those entities with values based on the O/R mapping metadata.
Once you create a query, it makes no difference whether you retrieve the results from a native SQL or a JPQL query.
Using a named native SQL query
Using a named native query is quite similar to using a named JPQL query. To use a named native query, you must first create it. You can use the @NamedNativeQuery annotation to define a named query:
Next, if our query returns more than one entity class, we must define SqlResult-SetMapping in the entity class using resultSetMapping as follows:
You can either use an entity class or a result set mapping with the @NamedNative-Query annotation. Suppose we want to convert the query that we used earlier to a named native query. The first step is to define the named native query in the User entity:
You can provide a vendor-specific hint using the queryHint element of the NamedNativeQuery. It is similar to the hints element for NamedQuery discussed in section 10.2.4.
NOTE There is no difference in executing a named native SQL query and a JPQL named query—except that a named parameter in native SQL query is not required by the JPA spec.
To illustrate how similar the execution of JPQL and native SQL queries is, let’s execute the named native query findUserWithMoreltems (which we defined earlier in a session bean method):
This statement first creates a query instance for the named native query find-UserWithMoreltems. Next, the required positional parameter is set. Finally, we return the result set.
JPA and database stored procedures
If you’re a big fan of SQL, you may be willing to exploit the power of database-stored procedures. Unfortunately, JPA doesn’t support stored procedures, and you have to depend on a proprietary feature of your persistence provider. However, you can use simple stored functions (without out parameters) with a native SQL query.
Well, it appears you made it through the jungle of native SQL queries relatively unscathed. We hope you can see that while it is possible to drop down into the bowels of SQL from JPA, we don’t recommend it. Embedding SQL into Java strings can be very time-consuming when it comes to debugging. And you lose all the benefits that JPQL affords you. However, if you really do need to go native, JPA will make it possible.
Summary
Queries are an important piece of the persistence puzzle, and in this topic we explored the various query capabilities provided by the EJB 3 Java Persistence API. JPA uses the following three methods to query entities:
■ EntityManager.find with the entity’s primary key
■ SQL queries native to the underlying database
You can either create ad hoc queries dynamically or use named queries that are stored with their associated entities. The EntityManager interface provides methods to create JPQL- or SQL-based queries, and the Query interface provides methods to execute a query. You can set parameters, pagination properties, and flush mode, and retrieve query results using methods in the Query interface. JPQL is the safest bet for writing queries, as it addresses the major limitations of EJBQL and is the only way to build applications that are portable across databases. Avoid using native SQL unless you have to use a vendor-specific feature.
This topic concludes part 3 of this topic. Part 4 assembles everything we’ve covered thus far, and allows you to apply these new skills in interesting, practical ways. We’ll also delve into packaging concerns and explore some EJB design patterns.
