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.

Named Parameters (:name)

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

  public Country getCountryByName(EntityManagerjavax.persistence.EntityManagerJPA interfaceInterface used to interact with the persistence context.See JavaDoc Reference Page... em, String name) {
    TypedQueryjavax.persistence.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page...<Country> query = em.createQuerycreateQuery(qlString, resultClass)EntityManager's methodCreate an instance of TypedQuery for executing a
 Java Persistence query language statement.See JavaDoc Reference Page...(
        "SELECT c FROM Country c WHERE c.name = :name", Country.class);
    return query.setParametersetParameter(name, value)TypedQuery's methodBind an argument to a named parameter.See JavaDoc Reference Page...("name", name).getSingleResultgetSingleResult()TypedQuery's methodExecute a SELECT query that returns a single result.See JavaDoc Reference Page...();
  } 

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 setParametersetParameter(name, value)TypedQuery's methodBind an argument to a named parameter.See JavaDoc Reference Page... method. The setParametersetParameter(name, value)TypedQuery's methodBind an argument to a named parameter.See JavaDoc Reference Page... method supports method chaining (by returning the same TypedQueryjavax.persistence.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page... instance on which it was invoked), so invocation of getSingleResultgetSingleResult()TypedQuery's methodExecute a SELECT query that returns a single result.See JavaDoc Reference Page... 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.EntityManagerJPA interfaceInterface used to interact with the persistence context.See JavaDoc Reference Page... em, String name) {
    TypedQueryjavax.persistence.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page...<Country> query = em.createQuerycreateQuery(qlString, resultClass)EntityManager's methodCreate an instance of TypedQuery for executing a
 Java Persistence query language statement.See JavaDoc Reference Page...(
        "SELECT c FROM Country c WHERE c.name = ?1", Country.class);
    return query.setParametersetParameter(position, value)TypedQuery's methodBind an argument to a positional parameter.See JavaDoc Reference Page...(1, name).getSingleResultgetSingleResult()TypedQuery's methodExecute a SELECT query that returns a single result.See JavaDoc Reference Page...();
  } 

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.ParameterExpressionJPA interfaceType of criteria query parameter expressions.See JavaDoc Reference Page... or its super interface Parameterjavax.persistence.ParameterJPA interfaceType for query parameter objects.See JavaDoc Reference Page...) 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.EntityManagerJPA interfaceInterface used to interact with the persistence context.See JavaDoc Reference Page... em, String name) {
    TypedQueryjavax.persistence.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page...<Country> query = em.createQuerycreateQuery(qlString, resultClass)EntityManager's methodCreate an instance of TypedQuery for executing a
 Java Persistence query language statement.See JavaDoc Reference Page...(
        "SELECT c FROM Country c WHERE c.name = '" + name + "'",
        Country.class);
    return query.getSingleResultgetSingleResult()TypedQuery's methodExecute a SELECT query that returns a single result.See JavaDoc Reference Page...();
  } 

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.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page... 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.QueryJPA interfaceInterface used to control query execution.See JavaDoc Reference Page... and TypedQueryjavax.persistence.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page... 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.setParametersetParameter(name, value, temporalType)TypedQuery's methodBind an instance of java.util.Date to a named parameter.See JavaDoc Reference Page...("date", new java.util.Date(), TemporalTypejavax.persistence.TemporalTypeJPA enumType used to indicate a specific mapping of java.util.Date 
 or java.util.Calendar.See JavaDoc Reference Page....DATETemporalType.DATEenum constantMap as java.sql.DateSee JavaDoc Reference Page...);

Since TemporalTypejavax.persistence.TemporalTypeJPA enumType used to indicate a specific mapping of java.util.Date or java.util.Calendar.See JavaDoc Reference Page....DateTemporalType.DATEenum constantMap as java.sql.DateSee JavaDoc Reference Page... 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 (getParametersgetParameters()Query's methodGet the parameter objects corresponding to the declared parameters of the query.See JavaDoc Reference Page...), and a method for checking if a specified parameter has a value (isBoundisBound(param)Query's methodReturn a boolean indicating whether a value has been bound to the parameter.See JavaDoc Reference Page...). These methods are not required for running queries and are likely to be less commonly used.