JPQL - EJB 3 in Action - page 457

11.3. Native queries
Just what is native SQL? It's the SQL understood by the specific database server—Oracle,
MySQL, Derby, and so on—that you're using. Up to this point, you've been constructing
queries either in JPQL or via the criteria API, which are then converted into native SQL.
This extra layer gives you database-independence and also enables you to work in terms
of objects instead of the relational model. This extra layer of abstraction is a double-edged
sword in that you can't use any database-specific features. Native queries allow for the dir-
ect use of database-specific SQL without the extra translation layer.
To see how native SQL is beneficial, suppose you want to generate a hierarchical list of cat-
egories, each showing its subcategories; it's impossible to do that in JPQL because JPQL
doesn't support recursive joins, similar to databases like Oracle. This means you have to
use 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:
SELECT CATEGORY_ID, CATEGORY_NAME
FROM CATEGORY
START WITH parent_id = ?
CONNECT BY PRIOR category_id = category_id
Ideally, you should limit your use of native SQL to queries that you can't express using
JPQL (as in your Oracle database-specific SQL query). But for demonstration purposes, in
the 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 doesn't track wheth-
er 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 incon-
sistent/stale data if your JPA provider uses caching.