Applies to
Java web-based applications using JPA.
Summary
Executing Named Queries using JPA.
Objectives
Use JPA named queries to perform database operations safely.
Scenarios
JPA named queries provide a data access and manipulation mechanism that closely ties the query content to the Java code defining the objects that the query is executing against. It also removes the actual query language from Java code, which is a common tactic and creates certain maintainability issues. A security related advantage to named queries is that dynamic data must be bound, which prevents SQL injection attacks. The code below shows how to both define and use named queries in JPA.
Solution Example
1. Define your entity class and use the @NamedQuery (or @NamedQueries with multiple @NamedQuery's) annotation to define each named query. The examples below are straightforward.
@Entity
@NamedQuery(
name="Product.findAllProductsByProductName",
queryString="SELECT pro FROM Product pro WHERE pro.name = :productName"
)
public class Product {
...
}
@Entity
@NamedQueries({
@NamedQuery(name="Product.findAllProducts", queryString="SELECT pro FROM Product pro"),
@NamedQuery(name="Product.findAllProductsByProductId", queryString="SELECT pro FROM Product pro WHERE pro.id = :productId"),
@NamedQuery(name="Product.findAllProductsByProductName", queryString="SELECT pro FROM Product pro WHERE pro.name = :productName"),
@NamedQuery(name="Product.findAllProductsByProductPrice", queryString="SELECT pro FROM Product pro WHERE pro.price between :minPrice and :maxPrice")
})
public class Product {
...
}
2. Use the defined named queries in code. If there is a need for dynamic data, use the parameter binding mechanism included in the query implementation.
--------------------------
Untyped results example
--------------------------
//get dynamic data
//request parameters should be validated before use - this is a simple example
String productName = request.getParameter("product_name");
//use entity manager to retrieve named query
Query queryProductsByName = em.createNamedQuery("Product.findAllProductsByProductName");
//set dynamic data for query
queryProductsByName.setParameter("productName", productName);
//execute query and get results
Collection products = queryProductsByName.getResultList();
--------------------------
Typed results example
--------------------------
//get dynamic data
//request parameters should be validated before use - this is a simple example
String productName = request.getParameter("product_name");
//use entity manager to retrieve named query
TypedQuery<Product> queryProductsByName = em.createNamedQuery("Product.findAllProductsByProductName", Product.class);
//set dynamic data for query
queryProductsByName.setParameter("productName", productName);
//execute query and get results
List<Product> products = queryProductsByName.getResultList();
--------------------------
Example without using dynamic data
--------------------------
//use entity manager to retrieve named query
TypedQuery<Product> queryProducts = em.createNamedQuery("Product.findAllProducts", Product.class);
//execute query and get results
List<Product> products = queryProducts.getResultList();
The examples above show how to use named queries within JPA to create an environment where the queries are defined closely with the data they are operating against and that are safely used by requiring bound parameters for dynamic data. The use of other types of queries works in an identical manner, such as delete, update, insert, etc.
See Also
Links to API references and other useful links.
http://download.oracle.com/docs/cd/B31017_01/web.1013/b28221/ent30qry001.htm
http://www.oracle.com/technetwork/articles/javaee/jpa-137156.html
