Java Reference
In-Depth Information
@NamedNativeQuery(
name = "findUserWithMoreItems",
query = "SELECT user_id , first_name , last_name,
birth_date
FROM users
WHERE user_id IN
(SELECT seller_id
FROM items
GROUP BY seller_id
HAVING COUNT(*) > ?)",
resultSetMapping = "UserResults")
You can provide a vendor-specific hint using the queryHint element of the Named-
Native-Query . It's similar to the hints element for NamedQuery discussed in sec-
tion 10.3.2 .
Note
There's no difference in executing a named native SQL query and a JPQL named query,
except that a named parameter in a native SQL query isn't required by the JPA spec.
To illustrate how similar the execution of JPQL and native SQL queries is, you'll execute
the named native query findUserWithMoreItems (which you defined earlier in a ses-
sion bean method):
return em.createNamedQuery("findUserWithMoreItems")
.setParameter(1, 5)
.getResultList();
This statement first creates a query instance for the named native query findUser-
WithMoreItems . Next, you set the required positional parameter. Finally, you return the
result set.
11.3.3. Using stored procedures
Java EE 7 introduced support for invoking stored procedures. A stored procedure is essen-
tially a script that executes inside of the database. The language used to write the scripts
is usually database-specific—Oracle has PL/SQL, SQL Server uses Transact-SQL, Post-
greSQL supports its own pgSQL as well as pl/perl and pl/PHP, and so on. Some databases
Search WWH ::




Custom Search