Query Parameters in JPA

Query parameters enable the definition of reusable queries. Such queries can be executed with different parameter values to retrieve different results. Running the same query multiple times with different parameter values (arguments) is more efficient than using a new query string for every query execution, because it eliminates the need for repeated query compilations.

This page covers the following topics:

Named Parameters (:name)

The following method retrieves a Country object from the database by its name:

  public Country getCountryByName(EntityManagerjavax.persistence.EntityManager - JPA InterfaceInterface used to interact with the persistence context. em, String name) {
    TypedQueryjavax.persistence.TypedQuery - JPA InterfaceInterface used to control the execution of typed queries.<Country> query = em.createQueryEntityManager.createQuery(qlString,resultClass) - JPA MethodCreate an instance of TypedQuery for executing a
 Java Persistence query language statement.(
        "SELECT c FROM Country c WHERE c.name = :name", Country.class);
    return query.setParameterTypedQuery.setParameter(name,value) - JPA MethodBind an argument value to a named parameter.("name", name).getSingleResultTypedQuery.getSingleResult() - JPA MethodExecute a SELECT query that returns a single result.();
  }

The WHERE clause reduces the query results to Country objects whose name field value is equal to :name, which is a parameter that serves as a placeholder for a real value. Before the query can be executed a parameter value has to be set using the setParameterTypedQuery.setParameter(name,value) - JPA MethodBind an argument value to a named parameter. method. The setParameterTypedQuery.setParameter(name,value) - JPA MethodBind an argument value to a named parameter. method supports method chaining (by returning the same TypedQueryjavax.persistence.TypedQuery - JPA InterfaceInterface used to control the execution of typed queries. instance on which it was invoked), so invocation of getSingleResultTypedQuery.getSingleResult() - JPA MethodExecute a SELECT query that returns a single result. can be chained to the same expression.

Named parameters can be easily identified in a query string by their special form, which is a colon (:) followed by a valid JPQL identifier that serves as the parameter name. JPA does not provide an API for defining the parameters explicitly (except when using criteria API), so query parameters are defined implicitly by appearing in the query string. The parameter type is inferred by the context. In the above example, a comparison of :name to a field whose type is String indicates that the type of :name itself is String.

Queries can include multiple parameters, and each parameter can occur multiple times in the query string. A query can be run only after setting values for all its parameters (no matter in which order).

Ordinal Parameters (?index)

In addition to named parameters, whose form is :name, JPQL also supports ordinal parameters, whose form is ?index. The following method is equivalent to the method above, except that an ordinal parameter replaces the named parameter:

  public Country getCountryByName(EntityManagerjavax.persistence.EntityManager - JPA InterfaceInterface used to interact with the persistence context. em, String name) {
    TypedQueryjavax.persistence.TypedQuery - JPA InterfaceInterface used to control the execution of typed queries.<Country> query = em.createQueryEntityManager.createQuery(qlString,resultClass) - JPA MethodCreate an instance of TypedQuery for executing a
 Java Persistence query language statement.(
        "SELECT c FROM Country c WHERE c.name = ?1", Country.class);
    return query.setParameterTypedQuery.setParameter(position,value) - JPA MethodBind an argument value to a positional parameter.(1, name).getSingleResultTypedQuery.getSingleResult() - JPA MethodExecute a SELECT query that returns a single result.();
  }

The form of ordinal parameters is a question mark (?) followed by a positive int number. Apart from the different notation, named parameters and ordinal parameters are identical.

Named parameters can provide added value to the clarity of the query string (assuming that meaningful names are selected). Therefore, they are preferred over ordinal parameters.

Criteria Query Parameters

In a JPA query that is built by using the JPA Criteria API - parameters (as other query elements) are represented by objects (of type ParameterExpressionjavax.persistence.criteria.ParameterExpression - JPA InterfaceType of criteria query parameter expressions. or its super interface Parameterjavax.persistence.Parameter - JPA InterfaceType for query parameter objects.) rather than by names or numbers.

See the Parameters in Criteria Queries section for more details.

Parameters vs. Literals

Following is a third version of the same method, this time without parameters:

  public Country getCountryByName(EntityManagerjavax.persistence.EntityManager - JPA InterfaceInterface used to interact with the persistence context. em, String name) {
    TypedQueryjavax.persistence.TypedQuery - JPA InterfaceInterface used to control the execution of typed queries.<Country> query = em.createQueryEntityManager.createQuery(qlString,resultClass) - JPA MethodCreate an instance of TypedQuery for executing a
 Java Persistence query language statement.(
        "SELECT c FROM Country c WHERE c.name = '" + name + "'",
        Country.class);
    return query.getSingleResultTypedQuery.getSingleResult() - JPA MethodExecute a SELECT query that returns a single result.();
  }

Instead of using a parameter for the queried name, the new method embeds the name as a String literal. There are a few drawbacks to using literals rather than parameters in queries.

First of all, the query is not reusable. Different literal values lead to different query strings, and each query string requires its own query compilation, which is very inefficient. On the other hand, when using parameters, even if a new TypedQueryjavax.persistence.TypedQuery - JPA InterfaceInterface used to control the execution of typed queries. instance is constructed on every query execution, ObjectDB can identify repeating queries with the same query string and use a cached compiled query program, if available.

Secondly, embedding strings in queries is unsafe and can expose the application to JPQL injection attacks. Suppose that the name parameter is received as an input from the user and then embedded in the query string as is. Instead of a simple country name, a malicious user may provide JPQL expressions that change the query and may help in hacking the system.

In addition, parameters are more flexible and support elements that are unavailable as literals, such as entity objects.

API Parameter Methods

Over half of the methods in Queryjavax.persistence.Query - JPA InterfaceInterface used to control query execution.javax.jdo.Query - JDO InterfaceThe Query interface allows applications to obtain persistent instances, values, and aggregate data from the data store. and TypedQueryjavax.persistence.TypedQuery - JPA InterfaceInterface used to control the execution of typed queries. deal with parameter handling. The Query interface defines 18 such methods, 9 of which are overridden in TypedQuery. That large number of methods is not typical to JPA, which generally excels in its thin and simple API.

There are 9 methods for setting parameters in a query, which is essential whenever using query parameters. In addition, there are 9 methods for extracting parameter values from a query. These get-methods, which are new in JPA 2, are expected to be much less commonly used than the set methods.

Two set methods are demonstrated above - one for setting a named parameter and the other for setting an ordinal parameter. A third method is designated for setting a parameter in a Criteria API query. The reason for having nine set methods rather than just three is that JPA additionally provides three separate methods for setting Date, parameters as well as three separate methods for setting Calendar parameters.

Date and Calendar parameter values require special methods in order to specify what they represent, such as a pure date, a pure time or a combination of date and time, as explained in detail in the Date and Time (Temporal) Types section.

For example, the following invocation passes a Date object as a pure date (no time):

query.setParameterTypedQuery.setParameter(name,value,temporalType) - JPA MethodBind an instance of java.util.Date to a named parameter.("date", new java.util.Date(), TemporalTypejavax.persistence.TemporalType - JPA EnumType used to indicate a specific mapping of java.util.Date
 or java.util.Calendar..DATEjavax.persistence.TemporalType.DATE - JPA Enum ConstantMap as java.sql.Date);

Since TemporalTypejavax.persistence.TemporalType - JPA EnumType used to indicate a specific mapping of java.util.Date or java.util.Calendar..Datejavax.persistence.TemporalType.DATE - JPA Enum ConstantMap as java.sql.Date represents a pure date, the time part of the newly constructed java.util.Date instance is discarded. This is very useful in comparison against a specific date, when time should be ignored.

The get methods support different ways to extract parameters and their values from a query, including by name (for named parameter), by position (for ordinal parameters) by Parameter object (for Criteria API queries), each with or without an expected type. There is also a method for extracting all the parameters as a set (getParametersQuery.getParameters() - JPA MethodGet the parameter objects corresponding to the declared parameters of the query.), and a method for checking if a specified parameter has a value (isBoundQuery.isBound(param) - JPA MethodReturn a boolean indicating whether a value has been bound to the parameter.). These methods are not required for running queries and are likely to be less commonly used.

< Running Queries ^ Query API Named Queries >