Query Parameters in JPA

Query parameters enable you to define reusable queries. You can execute these queries with different parameter values to retrieve different results. Executing the same query multiple times with different parameter values is more efficient than using a new query string for each execution because it eliminates repeated query compilations.

Named Parameters (:name)

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

  public Country getCountryByName(EntityManagerjakarta.persistence.EntityManagerInterface used to interact with the persistence context. em, String name) {
    TypedQueryjakarta.persistence.TypedQueryInterface used to control the execution of typed queries.<Country> query = em.createQueryjakarta.persistence.EntityManager.createQuery(String,Class)Create an instance of   TypedQuery<X>   for executing a Jakarta Persistence query language statement.(
        "SELECT c FROM Country c WHERE c.name = :name", Country.class);
    return query.setParameterjakarta.persistence.TypedQuery.setParameter(String,Object)Bind an argument value to a named parameter.("name", name).getSingleResultjakarta.persistence.TypedQuery.getSingleResult()Execute a SELECT query that returns a single result.();
  }

The WHERE clause filters the query results to Country objects whose name field is equal to :name. The :name identifier is a parameter that serves as a placeholder for a value. Before executing the query, you must set a value for the parameter by using the setParameterjakarta.persistence.TypedQuery.setParameter(String,Object)Bind an argument value to a named parameter. method. The setParameterjakarta.persistence.TypedQuery.setParameter(String,Object)Bind an argument value to a named parameter. method supports method chaining by returning the TypedQueryjakarta.persistence.TypedQueryInterface used to control the execution of typed queries. instance on which it was invoked. This allows you to chain the call to getSingleResultjakarta.persistence.TypedQuery.getSingleResult()Execute a SELECT query that returns a single result. in the same expression.

Named parameters in a query string are identifiable by their format: a colon (:) followed by a valid JPQL identifier that serves as the parameter name. JPA does not provide an API to define parameters explicitly (except when using the Criteria API). Instead, parameters are defined implicitly when they appear in the query string. The parameter's type is inferred from the context. In the preceding example, comparing :name to a field of type String indicates that the type of :name is also String.

Queries can include multiple parameters, and each parameter can appear multiple times in the query string. A query can be executed only after you set values for all its parameters. The order in which you set the parameters does not matter.

Ordinal Parameters (?index)

In addition to named parameters, which use the :name format, JPQL also supports ordinal parameters, which use the ?index format. The following method is equivalent to the method above, but it uses an ordinal parameter instead of a named parameter:

  public Country getCountryByName(EntityManagerjakarta.persistence.EntityManagerInterface used to interact with the persistence context. em, String name) {
    TypedQueryjakarta.persistence.TypedQueryInterface used to control the execution of typed queries.<Country> query = em.createQueryjakarta.persistence.EntityManager.createQuery(String,Class)Create an instance of   TypedQuery<X>   for executing a Jakarta Persistence query language statement.(
        "SELECT c FROM Country c WHERE c.name = ?1", Country.class);
    return query.setParameterjakarta.persistence.TypedQuery.setParameter(int,Object)Bind an argument value to a positional parameter.(1, name).getSingleResultjakarta.persistence.TypedQuery.getSingleResult()Execute a SELECT query that returns a single result.();
  }

The format for an ordinal parameter is a question mark (?) followed by a positive integer. Other than the notation, named and ordinal parameters are identical.

Named parameters can improve the clarity of the query string, assuming you select meaningful names. For this reason, named parameters are preferred over ordinal parameters.

Criteria Query Parameters

In a JPA query built using the JPA Criteria API, parameters and other query elements are represented by objects (of type ParameterExpressionjakarta.persistence.criteria.ParameterExpressionType of criteria query parameter expressions. or its superinterface, Parameterjakarta.persistence.ParameterType for query parameter objects.) rather than by names or numbers.

For more details, see the Parameters in Criteria Queries section.

Parameters vs. Literals

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

  public Country getCountryByName(EntityManagerjakarta.persistence.EntityManagerInterface used to interact with the persistence context. em, String name) {
    TypedQueryjakarta.persistence.TypedQueryInterface used to control the execution of typed queries.<Country> query = em.createQueryjakarta.persistence.EntityManager.createQuery(String,Class)Create an instance of   TypedQuery<X>   for executing a Jakarta Persistence query language statement.(
        "SELECT c FROM Country c WHERE c.name = '" + name + "'",
        Country.class);
    return query.getSingleResultjakarta.persistence.TypedQuery.getSingleResult()Execute a SELECT query that returns a single result.();
  }

Instead of using a parameter for the name, this method embeds the name directly into the query as a String literal. Using literals instead of parameters has several drawbacks.

First, the query is not reusable. Different literal values result in different query strings, and each new query string requires its own compilation, which is inefficient. In contrast, when you use parameters, ObjectDB can identify queries with the same query string and use a cached, compiled query program if one is available. This is true even if a new TypedQueryjakarta.persistence.TypedQueryInterface used to control the execution of typed queries. instance is constructed for each execution.

Second, embedding strings in queries is unsafe and can expose the application to JPQL injection attacks. For example, suppose the name parameter is received as user input and then embedded in the query string as-is. A malicious user could provide JPQL expressions instead of a simple country name, altering the query and potentially compromising the system.

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

API Parameter Methods

Over half of the methods in the Queryjakarta.persistence.QueryInterface used to control query execution. and TypedQueryjakarta.persistence.TypedQueryInterface used to control the execution of typed queries. interfaces handle parameters. The Query interface defines 18 such methods, 9 of which are overridden in TypedQuery. This large number of methods is not typical for JPA, which is generally known for its lean and simple API.

There are nine methods for setting parameters in a query, which is an essential task when using query parameters. In addition, there are nine methods for extracting parameter values from a query. These get methods are used less commonly than the set methods.

Two set methods are demonstrated previously: 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 instead of three is that JPA also provides three separate methods for setting Date parameters and three for setting Calendar parameters.

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

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

query.setParameterjakarta.persistence.TypedQuery.setParameter(String,Date,TemporalType)Bind an instance of   Date   to a named parameter.("date", new java.util.Date(), TemporalTypejakarta.persistence.TemporalTypeType used to indicate a specific mapping of   Date   or   Calendar  ..DATEjakarta.persistence.TemporalType.DATEMap as  java.sql.Date );

Because TemporalTypejakarta.persistence.TemporalType.DATEMap as java.sql.Date .DATEjakarta.persistence.TemporalType.DATEMap as java.sql.Date represents only the date, the time part of the java.util.Date instance is discarded. This is useful for comparisons against a specific date when the time component should be ignored.

The get methods support different ways to extract parameters and their values from a query: by name (for named parameters), by position (for ordinal parameters), or by a Parameterjakarta.persistence.ParameterType for query parameter objects. object (for Criteria API queries). Each of these approaches can be used with or without specifying an expected type. There is also a method to extract all parameters as a Set (getParametersjakarta.persistence.Query.getParameters()Get the parameter objects corresponding to the declared parameters of the query.) and a method to check if a specified parameter has a value (isBoundjakarta.persistence.Query.isBound(Parameter)Return a boolean indicating whether a value has been bound to the parameter.). These methods are not required to execute queries and are likely used less often.