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 .

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.

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:

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.

Similar to relation traversal, nested embeddable objects can be traversed using Java-like syntax. For example, if the Compony 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'

Traverse 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'

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'

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

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

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.

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.

...
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.

...
@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")
    }
)
...

...
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
...

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.

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();

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();

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.

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

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..

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();