Use Named Queries with Java Persistence API (JPA)

Posted by Serge Truth on August 30, 2011 at 10:56 AM

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://openjpa.apache.org/builds/1.0.2/apache-openjpa-1.0.2/docs/manual/jpa_overview_query.html#jpa_overview_query_named

http://www.oracle.com/technetwork/articles/javaee/jpa-137156.html

Topics: developer guidance, application security

Serge Truth

Written by Serge Truth

Serge is a Content Lead here at Security Innovation. He is an IT and Information Security professional, certified by the Committee on National Security Systems Instruction.