Table of Contents
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 ”.
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
.
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.
When selecting entities, you can optional 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 equals 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
Relations between objects can be traversed using Java-like syntax. For example, if the Magazine class has a field named "publisher" or 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.
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.
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'
Specifying the join fetch
declaration is
functionally equivalent to adding the fields to the Query's
FetchConfiguration
. See Section 6, “
Fetch Groups
”.
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 ending at length
characters past
startIndex
.
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
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
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 parameter 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.
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 the sql keywords OPTIMIZE FOR 2 ROWS and UPDATE to be emitted into the sql provided that a pessimistic LockManager is being used.
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(); ...
Invalid hints or hints which can not be processed by a particular database are ignored. Otherwise, invalid hints will result in an ArgumentException being thrown.
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 FOR UPDATE or USE AND KEEP UPDATE LOCKS in 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 UPDATE | sql without update;
reread version field at the end of transaction and check for no change. |
WRITE | sql with UPDATE |
sql without update;
force update version field at the end of transaction |
not specified | sql without update | sql without update |
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.
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.
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
.
The hint name "openjpa.hint.OracleSelectHint" can be used to specify a string value of an Oracle query hint that will inserted into sql for an Oracle database.See Section 15.1, “ Using Query Hints with Oracle ” for an example.
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")} ) ...
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.
Example 10.3. Delete by Query
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..
Example 10.4. 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();