Chapter 10. JPA Query
Prev | Part 2. Java Persistence API | Next

## Chapter 10. JPA Query

The `javax.persistence.Query` interface is the mechanism for issuing queries in JPA. The primary query language used is the Java Persistence Query Language, or `JPQL`. JPQL is syntactically very similar to SQL, but is object-oriented rather than table-oriented.

The API for executing JPQL queries will be discussed in Section 1, “ JPQL API ”, and a full language reference will be covered in Section 2, “ JPQL Language Reference ”.

## 1. JPQL API

### 1.1. Query Basics

    SELECT x FROM Magazine x

The preceding is a simple JPQL query for all `Magazine` entities.

    public Query createQuery(String jpql);

The `EntityManager.createQuery` method creates a `Query` instance from a given JPQL string.

    public List getResultList();

Invoking `Query.getResultList` executes the query and returns a `List` containing the matching objects. The following example executes our `Magazine` query above:

    EntityManager em = ...
    Query q = em.createQuery("SELECT x FROM Magazine x");
    List<Magazine> results = (List<Magazine>) q.getResultList();

A JPQL query has an internal namespace declared in the `from` clause of the query. Arbitrary identifiers are assigned to entities so that they can be referenced elsewhere in the query. In the query example above, the identifier `x` is assigned to the entity `Magazine` .

### Note

The `as` keyword can optionally be used when declaring identifiers in the `from` clause. `SELECT x FROM Magazine x` and `SELECT x FROM Magazine AS x` are synonymous.

Following the `select` clause of the query is the object or objects that the query returns. In the case of the query above, the query's result list will contain instances of the `Magazine` class.

### Note

When selecting entities, you can optionally use the keyword `object` . The clauses `select x` and `SELECT OBJECT(x)` are synonymous.

The optional `where` clause places criteria on matching results. For example:

    SELECT x FROM Magazine x WHERE x.title = 'JDJ'

Keywords in JPQL expressions are case-insensitive, but entity, identifier, and member names are not. For example, the expression above could also be expressed as:

    select x from Magazine x where x.title = 'JDJ'

But it could not be expressed as:

    SELECT x FROM Magazine x WHERE x.TITLE = 'JDJ'

As with the `select` clause, alias names in the `where` clause are resolved to the entity declared in the `from` clause. The query above could be described in English as "for all `Magazine` instances `x`, return a list of every `x` such that `x`'s `title` field is equal to 'JDJ'".

JPQL uses SQL-like syntax for query criteria. The `and` and `or` logical operators chain multiple criteria together:

    SELECT x FROM Magazine x WHERE x.title = 'JDJ' OR x.title = 'JavaPro'

The `=` operator tests for equality. `<>` tests for inequality. JPQL also supports the following arithmetic operators for numeric comparisons: `>, >=, <, <=`. For example:

    SELECT x FROM Magazine x WHERE x.price > 3.00 AND x.price <= 5.00

This query returns all magazines whose price is greater than 3.00 and less than or equal to 5.00.

    SELECT x FROM Magazine x WHERE x.price <> 3.00

This query returns all Magazines whose price is not equal to 3.00.

You can group expressions together using parentheses in order to specify how they are evaluated. This is similar to how parentheses are used in Java. For example:

    SELECT x FROM Magazine x WHERE (x.price > 3.00 AND x.price <= 5.00) OR x.price < 7.00

This expression would match magazines whose price is less than 7.00. Alternately:

    SELECT x FROM Magazine x WHERE x.price > 3.00 AND (x.price <= 5.00 OR x.price < 7.00)

This expression would match magazines whose price is 4.00, 5.00 or 6.00, but not 1.00, 2.00 or 3.00.

JPQL also includes the following conditionals:

- `[NOT] BETWEEN`: Shorthand for expressing that a value falls between two other values. The following two statements are synonymous:

      SELECT x FROM Magazine x WHERE x.price >= 3.00 AND x.price <= 5.00

      SELECT x FROM Magazine x WHERE x.price BETWEEN 3.00 AND 5.00

- `[NOT] LIKE`: Performs a string comparison with wildcard support. The special character '_' in the parameter means to match any single character, and the special character '%' means to match any sequence of characters. The following statement matches title fields "JDJ" and "JavaPro", but not "IT Insider":

      SELECT x FROM Magazine x WHERE x.title LIKE 'J%'

  The following statement matches the title field "JDJ" but not "JavaPro":

      SELECT x FROM Magazine x WHERE x.title LIKE 'J__'

- `[NOT] IN`: Specifies that the member must be equal to one element of the provided list. The following two statements are synonymous:

      SELECT x FROM Magazine x WHERE x.title IN ('JDJ', 'JavaPro', 'IT Insider')

      SELECT x FROM Magazine x WHERE x.title = 'JDJ' OR x.title = 'JavaPro' OR x.title = 'IT Insider'

- `IS [NOT] EMPTY`: Specifies that the collection field holds no elements. For example:

      SELECT x FROM Magazine x WHERE x.articles is empty

  This statement will return all magazines whose `articles` member contains no elements.

- `IS [NOT] NULL`: Specifies that the field is equal to null. For example:

      SELECT x FROM Magazine x WHERE x.publisher is null

  This statement will return all Magazine instances whose "publisher" field is set to `null`.

- `NOT`: Negates the contained expression. For example, the following two statements are synonymous:

      SELECT x FROM Magazine x WHERE NOT(x.price = 10.0)

      SELECT x FROM Magazine x WHERE x.price <> 10.0

### 1.2. Relation Traversal

Relations between objects can be traversed using Java-like syntax. For example, if the Magazine class has a field named "publisher" of type Company, that relation can be queried as follows:

    SELECT x FROM Magazine x WHERE x.publisher.name = 'Random House'

This query returns all `Magazine` instances whose `publisher` field is set to a `Company` instance whose name is "Random House".

Single-valued relation traversal implies that the relation is not null. In SQL terms, this is known as an inner join. If you want to also include relations that are null, you can specify:

    SELECT x FROM Magazine x WHERE x.publisher.name = 'Random House' or x.publisher is null

You can also traverse collection fields in queries, but you must declare each traversal in the `from` clause. Consider:

    SELECT x FROM Magazine x, IN(x.articles) y WHERE y.authorName = 'John Doe'

This query says that for each `Magazine` `x` , traverse the `articles` relation and check each `Article` `y`, and pass the filter if `y`'s `authorName` field is equal to "John Doe". In short, this query will return all magazines that have any articles written by John Doe.

### Note

The `IN()` syntax can also be expressed with the keywords `inner join`. The statements `SELECT x FROM Magazine x, IN(x.articles) y WHERE y.authorName = 'John Doe'` and `SELECT x FROM Magazine x inner join x.articles y WHERE y.authorName = 'John Doe'` are synonymous.

### 1.3. Embeddable Traversal

Similar to relation traversal, nested embeddable objects can be traversed using Java-like syntax. For example, if the `Company` class has a field named "address" of an embeddable type `Address`, and the `Address` has a field named "geocode" of an embeddable type `Geocode`, the `geocode` of a company's address can be queried as follows:

    SELECT c.address.geocode FROM Company c WHERE c.name = 'Random House'

### Note

The `geocode` returned by the above query will not be part of the state of any managed entity. Modifications to these embeddable instances are not allowed.

Traversal into embeddable's state field is also allowed as shown in the following query:

    SELECT c.address.geocode.latitude FROM Company c WHERE c.name = 'Random House'

Embeddable objects may contain single-valued or collection-valued relations. These relations can also be traversed using Java-like syntax. For example, if the Address has a relation field named "phoneLists" of an entity type `PhoneNumber`, the following query returns the `PhoneNumber` entities of the `Company` named 'Random House':

    SELECT p FROM Company c, IN(c.address.phoneLists) p WHERE c.name = 'Random House'

### 1.4. Fetch Joins

JPQL queries may specify one or more `join fetch` declarations, which allow the query to specify which fields in the returned instances will be pre-fetched.

    SELECT x FROM Magazine x join fetch x.articles WHERE x.title = 'JDJ'

The query above returns `Magazine` instances and guarantees that the `articles` field will already be fetched in the returned instances.

Multiple fields may be specified in separate `join fetch` declarations:

    SELECT x FROM Magazine x join fetch x.articles join fetch x.authors WHERE x.title = 'JDJ'

Notice that in the above query, both `articles` and `authors` are relation property in `Magazine`. JPQL syntax does not allow range variable declared for paths on the right-hand side of `join fetch`. Therefore, if `Article` entity has a relation property of `publishers`, it is not possible to specify a query that returns `Magazine` instances and pre-fetch the `articles` and the `publishers`. The following query will result in syntax error:

    SELECT x FROM Magazine x join fetch x.articles a join fetch a.publishers p WHERE x.title = 'JDJ'

### Note

Specifying the `join fetch` declaration is functionally equivalent to adding the fields to the Query's `FetchConfiguration`. See Section 7, “ Fetch Groups ”.

### 1.5. JPQL Functions

As well as supporting direct field and relation comparisons, JPQL supports a pre-defined set of functions that you can apply.

- `CONCAT(string1, string2)`: Concatenates two string fields or literals. For example:

      SELECT x FROM Magazine x WHERE CONCAT(x.title, 's') = 'JDJs'

- `SUBSTRING(string, startIndex, [length])`: Returns the part of the `string` argument starting at `startIndex` (1-based) and optionally ending at `length` characters past `startIndex`. If the `length` argument is not specified, the substring from the `startIndex` to the end of the `string` is returned.

      SELECT x FROM Magazine x WHERE SUBSTRING(x.title, 1, 1) = 'J'

- `TRIM([LEADING | TRAILING | BOTH] [character FROM] string`: Trims the specified character from either the beginning ( `LEADING` ) end ( `TRAILING`) or both ( `BOTH` ) of the string argument. If no trim character is specified, the space character will be trimmed.

      SELECT x FROM Magazine x WHERE TRIM(BOTH 'J' FROM x.title) = 'D'

- `LOWER(string)`: Returns the lower-case of the specified string argument.

      SELECT x FROM Magazine x WHERE LOWER(x.title) = 'jdj'

- `UPPER(string)`: Returns the upper-case of the specified string argument.

      SELECT x FROM Magazine x WHERE UPPER(x.title) = 'JAVAPRO'

- `LENGTH(string)`: Returns the number of characters in the specified string argument.

      SELECT x FROM Magazine x WHERE LENGTH(x.title) = 3

- `LOCATE(searchString, candidateString [, startIndex])`: Returns the first index of `searchString` in `candidateString`. Positions are 1-based. If the string is not found, returns 0.

      SELECT x FROM Magazine x WHERE LOCATE('D', x.title) = 2

- `ABS(number)`: Returns the absolute value of the argument.

      SELECT x FROM Magazine x WHERE ABS(x.price) >= 5.00

- `SQRT(number)`: Returns the square root of the argument.

      SELECT x FROM Magazine x WHERE SQRT(x.price) >= 1.00

- `MOD(number, divisor)`: Returns the modulo of `number` and `divisor`.

      SELECT x FROM Magazine x WHERE MOD(x.price, 10) = 0

- `INDEX(identification_variable)`: Returns an integer value corresponding to the position of its argument in an ordered list. The INDEX function can only be applied to identification variables denoting types for which an order column has been specified.

  In the following example, `studentWaitlist` is a list of students for which an order column has been specified, the query returns the name of the first student on the waiting list of the course named 'Calculus':

      SELECT w.name FROM Course c JOIN c.studentWaitlist w WHERE c.name = ‘Calculus’ AND INDEX(w) = 0

- `CURRENT_DATE`: Returns the current date.

- `CURRENT_TIME`: Returns the current time.

- `CURRENT_TIMESTAMP`: Returns the current timestamp.

### 1.6. Polymorphic Queries

All JPQL queries are polymorphic, which means the `from` clause of a query includes not only instances of the specific entity class to which it refers, but all subclasses of that class as well. The instances returned by a query include instances of the subclasses that satisfy the query conditions. For example, the following query may return instances of `Magazine` , as well as `Tabloid` and `Digest` instances, where `Tabloid` and `Digest` are `Magazine` subclasses.

    SELECT x FROM Magazine x WHERE x.price < 5

Non-polymorphic queries or queries whose polymorphism is restricted can be specified using entity type expressions (see Section 2.6.4, “ Entity Type Expressions ” ) in the `WHERE` clause to restrict the domain of the query. For example, the following query returns instances of `Digest`:

    SELECT x FROM Magazine WHERE TYPE(x) = Digest

### 1.7. Query Parameters

JPQL provides support for parameterized queries. Either named parameters or positional parameters may be specified in the query string. Parameters allow you to re-use query templates where only the input parameters vary. A single query can declare either named parameters or positional parameters, but is not allowed to declare both named and positional parameters.

    public Query setParameter (int pos, Object value);

Specify positional parameters in your JPQL string using an integer prefixed by a question mark. You can then populate the `Query` object with positional parameter values via calls to the `setParameter` method above. The method returns the `Query` instance for optional method chaining.

    EntityManager em = ...
    Query q = em.createQuery("SELECT x FROM Magazine x WHERE x.title = ?1 and x.price > ?2");
    q.setParameter(1, "JDJ").setParameter(2, 5.0);
    List<Magazine> results = (List<Magazine>) q.getResultList();

This code will substitute `JDJ` for the `?1` parameter and `5.0` for the `?2` parameter, then execute the query with those values.

    public Query setParameter(String name, Object value);

Named parameters are denoted by prefixing an arbitrary name with a colon in your JPQL string. You can then populate the `Query` object with parameter values using the method above. Like the positional parameter method, this method returns the `Query` instance for optional method chaining.

    EntityManager em = ...
    Query q = em.createQuery("SELECT x FROM Magazine x WHERE x.title = :titleParam and x.price > :priceParam");
    q.setParameter("titleParam", "JDJ").setParameter("priceParam", 5.0);
    List<Magazine> results = (List<Magazine>) q.getResultList();

This code substitutes `JDJ` for the `:titleParam` parameter and `5.0` for the `:priceParam` parameter, then executes the query with those values.

All input parameters must be single-valued, except in IN expressions (see Section 2.5.9, “ JPQL In Expressions ”), which support the use of collection-valued input parameters.

### 1.8. Query Hints

JPQL provides support for hints which are name/value pairs used to control locking and optimization keywords in SQL. The following example shows how to use the JPA hint API to set the `ReadLockMode` and `ResultCount` in the OpenJPA fetch plan. This will result in a database-specific SQL keyword (usually FOR UPDATE) to be emitted into the SQL provided that a pessimistic LockManager is being used. Additionally, if a DB2 database is being used, the OPTIMIZE FOR 2 ROWS clause will also be emitted.

Example 10.1. Query Hints

    ...
    Query q = em.createQuery("select m from Magazine m where ... ");
    q.setHint("openjpa.hint.OptimizeResultCount", new Integer(2));
    q.setHint("openjpa.FetchPlan.ReadLockMode","WRITE");
    List r = q.getResultList();
    ...

Hints which can not be processed by a particular database or are unknown to OpenJPA are ignored. Hints known to OpenJPA but supplied with an incompatible value will result in an `IllegalArgumentException` being thrown.

#### 1.8.1. Locking Hints

To avoid deadlock and optimistic update exceptions among multiple updaters, use a pessimistic LockManager, specified in the persistence unit definition, and use a hint name of "openjpa.FetchPlan.ReadLockMode" on queries for entities that must be locked for serialization. The value of `ReadLockMode` can be either "READ" or "WRITE". This results in a database-specific locking keyword (usually FOR UPDATE) to be emitted into the SQL.

Using a `ReadLockMode` hint with JPA optimistic locking (i.e. specifying LockManager = "version") will result in the entity version field either being reread at end of transaction in the case of a value of "READ" or the version field updated at end of transaction in the case of "WRITE". You must define a version field in the entity mapping when using a version LockManager and using ReadLockMode.

Table 10.1. Interaction of ReadLockMode hint and LockManager

ReadLockMode | LockManager=pessimistic | LockManager=version
--- | --- | ---
READ | SQL with FOR UPDATE | SQL without FOR UPDATE; reread version field at the end of transaction and check for no change.
WRITE | SQL with FOR UPDATE | SQL without FOR UPDATE; force update version field at the end of transaction
not specified | SQL without FOR UPDATE | SQL without FOR UPDATE

#### 1.8.2. Lock Timeout Hint

To specify a lock timeout hint in milliseconds to those databases that support it, specify a hint name of "openjpa.LockTimeout" or "javax.persistence.lock.timeout" with an integer value greater than zero, or zero for no timeout which is the default behavior.

#### 1.8.3. Query Timeout Hint

To specify a query timeout hint in milliseconds to those database drivers that support it, specify a hint name of "javax.persistence.query.timeout" with an integer value greater than zero, or zero for no timeout which is the default behavior.

#### 1.8.4. Result Set Size Hint

To specify a result set size hint to those databases that support it, specify a hint name of "openjpa.hint.OptimizeResultCount" with an integer value greater than zero. This causes the SQL keyword OPTIMIZE FOR to be generated.

#### 1.8.5. Isolation Level Hint

To specify an isolation level, specify a hint name of "openjpa.FetchPlan.Isolation". The value will be used to specify isolation level using the SQL WITH <isolation> clause for those databases that support it. This hint only works in conjunction with the ReadLockMode hint.

#### 1.8.6. Other Fetchplan Hints

Any property of an OpenJPA FetchPlan can be changed using a hint by using a name of the form "openjpa.FetchPlan."<property name>. Valid property names include: `MaxFetchDepth`, `FetchBatchSize`, `LockTimeOut`, `EagerFetchMode`, `SubclassFetchMode` and `Isolation`.

#### 1.8.7. Database-Specific Hints

The hint names "openjpa.hint.MySQLSelectHint" and "openjpa.hint.OracleSelectHint" can be used to specify a string value of a query hint that will be inserted into SQL for MySQL and Oracle databases. See Section 19.1, “ Using Query Hints with MySQL ” and Section 21.1, “ Using Query Hints with Oracle ” for examples.

#### 1.8.8. Named Query Hints

Hints can also be included as part of a NamedQuery definition.

Example 10.2. Named Query using Hints

    ...
    @NamedQuery(name="magsOverPrice",
        query="SELECT x FROM Magazine x WHERE x.price > ?1",
        hints={
            @QueryHint(name="openjpa.hint.OptimizeResultCount", value="2"),
            @QueryHint(name="openjpa.FetchPlan.ReadLockMode", value="WRITE")
        }
    )
    ...

#### 1.8.9. Handling of Multiple Similar Query Hints

When similar hints in different prefix scopes are specified in a query, the following prefix precedence order is used to select the effective hint:

- javax.persistence.*
- openjpa.FetchPlan.*
- openjpa.jdbc.*
- openjpa.*

Example 10.3. Setting Multiple Similar Query Hints

    ...
    Query q = em.createQuery(.....);
    q.setHint("openjpa.FetchPlan.LockTimeout", 1000);
    q.setHint("javax.persistence.lock.timeout", 2000);
    q.setHint("openjpa.LockTimeout", 3000);
    // Lock time out of 2000 ms is in effect for query q
    ...

### 1.9. Ordering

JPQL queries may optionally contain an `order by` clause which specifies one or more fields to order by when returning query results. You may follow the `order by field` clause with the `asc` or `desc` keywords, which indicate that ordering should be ascending or descending, respectively. If the direction is omitted, ordering is ascending by default.

    SELECT x FROM Magazine x order by x.title asc, x.price desc

The query above returns `Magazine` instances sorted by their title in ascending order. In cases where the titles of two or more magazines are the same, those instances will be sorted by price in descending order.

### 1.10. Aggregates

JPQL queries can select aggregate data as well as objects. JPQL includes the `min`, `max`, `avg`, and `count` aggregates. These functions can be used for reporting and summary queries.

The following query will return the average of all the prices of all the magazines:

    EntityManager em = ...
    Query q = em.createQuery("SELECT AVG(x.price) FROM Magazine x");
    Number result = (Number) q.getSingleResult();

The following query will return the highest price of all the magazines titled "JDJ":

    EntityManager em = ...
    Query q = em.createQuery("SELECT MAX(x.price) FROM Magazine x WHERE x.title = 'JDJ'");
    Number result = (Number) q.getSingleResult();

### 1.11. Named Queries

Query templates can be statically declared using the `NamedQuery` and `NamedQueries` annotations. For example:

    @Entity
    @NamedQueries({
        @NamedQuery(name="magsOverPrice",
            query="SELECT x FROM Magazine x WHERE x.price > ?1"),
        @NamedQuery(name="magsByTitle",
            query="SELECT x FROM Magazine x WHERE x.title = :titleParam")
    })
    public class Magazine {
        ...
    }

These declarations will define two named queries called `magsOverPrice` and `magsByTitle`.

    public Query createNamedQuery(String name);

You retrieve named queries with the above `EntityManager` method. For example:

    EntityManager em = ...
    Query q = em.createNamedQuery("magsOverPrice");
    q.setParameter(1, 5.0f);
    List<Magazine> results = (List<Magazine>) q.getResultList();

    EntityManager em = ...
    Query q = em.createNamedQuery("magsByTitle");
    q.setParameter("titleParam", "JDJ");
    List<Magazine> results = (List<Magazine>) q.getResultList();

### 1.12. Delete By Query

Queries are useful not only for finding objects, but for efficiently deleting them as well. For example, you might delete all records created before a certain date. Rather than bring these objects into memory and delete them individually, JPA allows you to perform a single bulk delete based on JPQL criteria.

Delete by query uses the same JPQL syntax as normal queries, with one exception: begin your query string with the `delete` keyword instead of the `select` keyword. To then execute the delete, you call the following `Query` method:

    public int executeUpdate();

This method returns the number of objects deleted. The following example deletes all subscriptions whose expiration date has passed.

Example 10.4. Delete by Query

    Query q = em.createQuery("DELETE FROM Subscription s WHERE s.subscriptionDate < :today");
    q.setParameter("today", new Date());
    int deleted = q.executeUpdate();

### 1.13. Update By Query

Similar to bulk deletes, it is sometimes necessary to perform updates against a large number of queries in a single operation, without having to bring all the instances down to the client. Rather than bring these objects into memory and modifying them individually, JPA allows you to perform a single bulk update based on JPQL criteria.

Update by query uses the same JPQL syntax as normal queries, except that the query string begins with the `update` keyword instead of `select`. To execute the update, you call the following `Query` method:

    public int executeUpdate();

This method returns the number of objects updated. The following example updates all subscriptions whose expiration date has passed to have the "paid" field set to true..

Example 10.5. Update by Query

    Query q = em.createQuery("UPDATE Subscription s SET s.paid = :paid WHERE s.subscriptionDate < :today");
    q.setParameter("today", new Date());
    q.setParameter("paid", true);
    int updated = q.executeUpdate();

Prev | Up | Next
2. The EntityTransaction Interface | Home | 2. JPQL Language Reference