The Java Persistence query language (JPQL) is used to define searches
against persistent entities independent of the mechanism used to
store those entities. As such, JPQL is "portable", and not constrained to
any particular data store. The Java
Persistence query language is an extension of the Enterprise JavaBeans
query language, EJB QL, adding operations such
as bulk deletes and updates, join operations, aggregates, projections,
and subqueries. Furthermore, JPQL queries can be declared statically in
metadata, or can be dynamically built in code. This chapter provides the full
definition of the language.

Note

Much of this section is paraphrased or taken directly
from Chapter 4 of the JSR 220 specification.

10.2.1. JPQL Statement Types

A JPQL statement
may be either a SELECT statement, an UPDATE
statement, or a DELETE statement. This chapter refers to all
such statements as "queries". Where
it is important to distinguish among statement types, the specific
statement type is referenced. In BNF syntax, a query language statement
is defined as:

A select statement
must always have a SELECT
and a FROM clause. The square brackets []
indicate that the other clauses are optional.

10.2.1.2. JPQL Update and Delete Statements

Update and delete statements provide bulk operations over sets of entities.
In BNF syntax, these operations are defined as:

update_statement ::= update_clause [where_clause]

delete_statement ::= delete_clause [where_clause]

The update and delete clauses determine
the type of the entities to be updated or deleted.
The WHERE clause may
be used to restrict the scope of the update or delete operation. Update
and delete statements are described further in
Section 10.2.9, “JPQL Bulk Update and Delete”.

10.2.2. JPQL Abstract Schema Types and Query Domains

The Java Persistence query
language is a typed language, and every expression has a type. The
type of an expression is derived from the structure of the expression,
the abstract schema types of the identification variable declarations,
the types to which the persistent fields and relationships evaluate,
and the types of literals. The abstract schema type of an entity is
derived from the entity class and the metadata information provided by
Java language annotations or in the XML descriptor.

Informally, the abstract schema type of an entity can be characterized
as follows:

For every persistent field or get accessor method (for a persistent
property) of the entity class, there is a field ("state-field") whose
abstract schema type corresponds to that of the field or the result type
of the accessor method.

For every persistent relationship field or get accessor method (for a
persistent relationship property) of the entity class, there is a field
("association-field") whose type is the abstract schema type of the
related entity (or, if the relationship is a one-to-many or many-to-many,
a collection of such). Abstract schema types are specific to the query
language data model. The persistence provider is not required to implement
or otherwise materialize an abstract schema type. The domain of a query
consists of the abstract schema types of all entities that are defined
in the same persistence unit. The domain of a query may be restricted
by the navigability of the relationships of the entity on which it
is based. The association-fields of an entity's abstract schema type
determine navigability. Using the association-fields and their values,
a query can select related entities and use their abstract schema types
in the query.

10.2.2.1. JPQL Entity Naming

Entities are designated in query strings by their entity
names. The entity name is defined by the name element of the Entity
annotation (or the entity-name XML descriptor element), and defaults to
the unqualified name of the entity class. Entity names are scoped within
the persistence unit and must be unique within the persistence unit.

10.2.2.2. JPQL Schema Example

This example assumes that the application developer
provides several entity classes representing magazines, publishers,
authors, and articles.
The abstract schema
types for these entities are Magazine,
Publisher, Author,
and Article.

The entity Publisher has a
one-to-many relationships with Magazine.
There is also a one-to-many
relationship between Magazine and
Article. The entity Article
is related to Author in a one-to-one relationship.

Queries to select magazines can be defined by navigating over the
association-fields and state-fields defined by Magazine
and Author. A query to find all magazines that
have unpublished articles is as follows:

This query navigates over the association-field authors of the
abstract schema type Magazine to find articles,
and uses the state-field
published of Article to select those
magazines that have at least one article that is published.
Although predefined reserved identifiers,
such as DISTINCT, FROM, AS,
JOIN, WHERE, and FALSE, appear in upper case
in this example, predefined reserved identifiers are case insensitive. The
SELECT clause of this example designates the return type of this query to
be of type Magazine. Because the same persistence unit defines the abstract
persistence schemas of the related entities, the developer can also
specify a query over articles that utilizes the abstract
schema type for
products, and hence the state-fields and association-fields of both the
abstract schema types Magazine and Author. For example, if the abstract
schema type Author has a state-field named firstName, a query over
articles can be specified using this state-field. Such a query might be
to find all magazines that have articles authored by someone with the
first name "John".

Because Magazine is related to Author by means of the relationships between
Magazine and Article and between
Article and Author, navigation using
the association-fields authors and product is used to express the
query. This query is specified by using the abstract schema name Magazine,
which designates the abstract schema type over which the query ranges. The
basis for the navigation is provided by the association-fields authors
and product of the abstract schema types Magazine
and Article respectively.

The FROM clause of
a query defines the domain of the query by declaring identification
variables. An identification variable is an identifier declared in the
FROM clause of a query. The domain of the query may be constrained by
path expressions. Identification variables designate instances of a
particular entity abstract schema type. The FROM clause can contain
multiple identification variable declarations separated by a comma (,).

10.2.3.1. JPQL FROM Identifiers

An identifier is a character sequence of unlimited
length. The character sequence must begin with a Java identifier
start character, and all other characters must be Java identifier
part characters. An identifier start character is any character for
which the method Character.isJavaIdentifierStart
returns true. This
includes the underscore (_) character and the dollar-sign ($)
character. An identifier-part character is any character for which
the method Character.isJavaIdentifierPart
returns true . The question-mark (?)
character is reserved for use by the Java Persistence query
language. The following are reserved identifiers:

SELECT

FROM

WHERE

UPDATE

DELETE

JOIN

OUTER

INNER

LEFT

GROUP

BY

HAVING

FETCH

DISTINCT

OBJECT

NULL

TRUE

FALSE

NOT

AND

OR

BETWEEN

LIKE

IN

AS

UNKNOWN

EMPTY

MEMBER

OF

IS

AVG

MAX

MIN

SUM

COUNT

ORDER

BY

ASC

DESC

MOD

UPPER

LOWER

TRIM

POSITION

CHARACTER_LENGTH

CHAR_LENGTH

BIT_LENGTH

CURRENT_TIME

CURRENT_DATE

CURRENT_TIMESTAMP

NEW

EXISTS

ALL

ANY

SOME

Reserved identifiers are
case insensitive. Reserved identifiers must not be used as identification
variables. It is recommended that other SQL reserved words not
be used as identification variables in queries, as they may be used as
reserved identifiers in future releases of the specification.

10.2.3.2. JPQL Identification Variables

An identification variable is a valid
identifier declared in the FROM clause of a query. All identification
variables must be declared in the FROM clause. Identification variables
cannot be declared in other clauses. An identification variable must not
be a reserved identifier or have the same name as any entity in the same
persistence unit: Identification variables are case insensitive. An
identification variable evaluates to a value of the type of the
expression used in declaring the variable. For example, consider the
previous query:

In the FROM clause
declaration mag.articlesart,
the identification variable art evaluates to
any Article value directly reachable from
Magazine. The association-field
articles is a collection of instances
of the abstract schema type Article
and the identification variable art
refers to an element of this
collection. The type of auth is the abstract
schema type of Author. An
identification variable ranges over the abstract schema type of an
entity. An identification variable designates an instance of an entity
abstract schema type or an element of a collection of entity abstract
schema type instances. Identification variables are existentially
quantified in a query. An identification variable always designates a
reference to a single value. It is declared in one of three ways: in a
range variable declaration, in a join clause, or in a collection member
declaration. The identification variable declarations are evaluated
from left to right in the FROM clause, and an identification variable
declaration can use the result of a preceding identification variable
declaration of the query string.

10.2.3.3. JPQL Range Declarations

The syntax for declaring an
identification variable as a range variable is similar to that of SQL;
optionally, it uses the AS keyword.

Range variable declarations allow the developer
to designate a "root" for objects which may not be reachable by
navigation. In order to select values by comparing more than one instance
of an entity abstract schema type, more than one identification variable
ranging over the abstract schema type is needed in the FROM clause.

The following query returns magazines whose prices are greater than the
price of magazines published by "Adventure" publishers. This example illustrates the use of
two different identification variables in the FROM clause, both of the
abstract schema type Magazine. The SELECT clause of this query determines
that it is the magazines with prices greater than those of "Adventure" publisher's that are
returned.

10.2.3.4. JPQL Path Expressions

An identification variable followed by the
navigation operator (.) and a state-field or association-field is a
path expression. The type of the path expression is the type computed
as the result of navigation; that is, the type of the state-field
or association-field to which the expression navigates. Depending on
navigability, a path expression that leads to an association-field may
be further composed. Path expressions can be composed from other path
expressions if the original path expression evaluates to a single-valued
type (not a collection) corresponding to an association-field.
Path-expression navigability is composed using "inner join" semantics. That is,
if the value of a non-terminal association-field in the path expression is
null, the path is considered to have no value, and does not participate
in the determination of the result. The syntax for single-valued path
expressions and collection-valued path expressions is as follows:

A single_valued_association_field is designated by the
name of an association-field in a one-to-one or many-to-one
relationship. The type of a single_valued_association_field and thus a
single_valued_association_path_expression is the abstract schema type of
the related entity. A collection_valued_association_field is designated
by the name of an association-field in a one-to-many or a many-to-many
relationship. The type of a collection_valued_association_field is a
collection of values of the abstract schema type of the related entity. An
embedded_class_state _field is designated by the name of an entity-state
field that corresponds to an embedded class. Navigation to a related
entity results in a value of the related entity's abstract schema type.

The evaluation of a path expression terminating in a state-field results
in the abstract schema type corresponding to the Java type designated by
the state-field. It is syntactically illegal to compose a path expression
from a path expression that evaluates to a collection. For example, if
mag
designates
Magazine,
the path expression mag.articles.author is illegal since
navigation to authors results in a collection. This case should produce
an error when the query string is verified. To handle such a navigation,
an identification variable must be declared in the FROM clause to range
over the elements of the articles collection. Another path expression
must be used to navigate over each such element in the WHERE clause of
the query, as in the following query, which returns all authors that have
any articles in any magazines:

The association referenced by the right
side of the FETCH JOIN clause must be
an association that belongs to an
entity that is returned as a result of the query. It is not permitted
to specify an identification variable for the entities referenced by
the right side of the FETCH JOIN clause, and
hence references to the
implicitly fetched entities cannot appear elsewhere in the query. The
following query returns a set of magazines. As a side effect, the
associated articles for those magazines are also retrieved, even
though they are not part of the explicit query result. The persistent
fields or properties of the articles that are eagerly fetched are
fully initialized. The initialization of the relationship properties
of the articles that are retrieved is determined
by the metadata for the Article entity class.

A fetch join has the same join semantics as the corresponding inner or
outer join, except that the related objects specified on the right-hand
side of the join operation are not returned in the query result or
otherwise referenced in the query. Hence, for example, if magazine
id 1 has five articles, the above query returns five references to the
magazine 1 entity.

10.2.3.6. JPQL Collection Member Declarations

An identification variable declared
by a collection_member_declaration ranges over values of a collection
obtained by navigation using a path expression. Such a path expression
represents a navigation involving the association-fields of an entity
abstract schema type. Because a path expression can be based on another
path expression, the navigation can use the association-fields of related
entities. An identification variable of a collection member declaration
is declared using a special operator, the reserved
identifier IN. The
argument to the IN operator is a
collection-valued path expression. The
path expression evaluates to a collection type specified as a result of
navigation to a collection-valued association-field of an entity abstract
schema type. The syntax for declaring a collection member identification
variable is as follows:

In this example, articles is the name of
an association-field whose value
is a collection of instances of the abstract schema
type Article. The
identification variable art designates a member
of this collection, a
single Article abstract schema type instance.
In this example, mag is an
identification variable of the abstract schema type
Magazine.

10.2.3.7. JPQL Polymorphism

Java Persistence queries are automatically
polymorphic. The FROM clause of a query designates not only instances
of the specific entity classes to which the query explicitly refers but of
subclasses as well. The instances returned by a query include instances
of the subclasses that satisfy the query criteria.

10.2.4. JPQL WHERE Clause

The WHERE clause of a query consists of a conditional
expression used to select objects or values that satisfy the
expression. The WHERE clause restricts the result of a select statement
or the scope of an update or delete operation. A WHERE clause is defined
as follows:

where_clause ::= WHERE conditional_expression

The GROUP BY construct
enables the aggregation of values according to the properties of an entity
class. The HAVING construct enables conditions to be specified that
further restrict the query result as restrictions upon the groups. The
syntax of the HAVING clause is as follows:

The following sections describe the language
constructs that can be used in a conditional expression of the WHERE
clause or HAVING clause. State-fields that are mapped in serialized form
or as lobs may not be portably used in conditional expressions.

Note

The implementation is not
expected to perform such query operations involving such fields in memory
rather than in the database.

10.2.5.1. JPQL Literals

A string literal is enclosed in single quotes--for example:
'literal'. A string literal that includes a single quote is represented by
two single quotes--for example: 'literal''s'. String literals in queries,
like Java String literals, use unicode character encoding. The use of Java
escape notation is not supported in query string literals Exact numeric
literals support the use of Java integer literal syntax as well as SQL
exact numeric literal syntax. Approximate literals support the use Java
floating point literal syntax as well as SQL approximate numeric literal
syntax. Enum literals support the use of Java enum literal syntax. The
enum class name must be specified. Appropriate suffixes may be used
to indicate the specific type of a numeric literal in accordance with
the Java Language Specification. The boolean
literals are TRUE and FALSE.
Although predefined reserved literals appear in upper case, they are case insensitive.

10.2.5.2. JPQL Identification Variables

All identification variables used
in the WHERE or HAVING clause of a
SELECT or DELETE statement must
be declared in the FROM clause, as described in
Section 10.2.3.2, “JPQL Identification Variables”. The
identification variables used in the WHERE clause of
an UPDATE statement
must be declared in the UPDATE clause.
Identification variables are
existentially quantified in the WHERE and
HAVING clause. This means
that an identification variable represents a member of a collection
or an instance of an entity's abstract schema type. An identification
variable never designates a collection in its entirety.

10.2.5.3. JPQL Path Expressions

It is illegal to use
a collection_valued_path_expression within a WHERE or
HAVING clause as part of a conditional expression except in an
empty_collection_comparison_expression, in a collection_member_expression,
or as an argument to the SIZE operator.

10.2.5.4. JPQL Input Parameters

Either positional or named parameters may be
used. Positional and named parameters may not be mixed in a single
query. Input parameters can only be used in the WHERE
clause or HAVING clause of a query.

Note that if an input parameter value is null, comparison operations
or arithmetic operations involving the input parameter will return an
unknown value. See Section 10.2.10, “JPQL Null Values”.

10.2.5.4.1. JPQL Positional Parameters

The following rules apply to positional
parameters.

Input parameters are designated by the question mark (?) prefix followed
by an integer. For example: ?1.

Input parameters are numbered starting from 1. Note that the same
parameter can be used more than once in the query string and that the
ordering of the use of parameters within the query string need not
conform to the order of the positional parameters.

There must be at least one
element in the comma separated list that defines the set of values for
the IN expression. If the value of a state_field_path_expression in an
IN or NOT IN expression is
NULL or unknown, the value of the expression is unknown.

10.2.5.9. JPQL Like Expressions

The syntax for the use of the comparison operator
[NOT] LIKE in a
conditional expression is as follows:

string_expression [NOT] LIKE pattern_value [ESCAPE escape_character]

The string_expression must have a string value. The pattern_value is a
string literal or a string-valued input parameter in which an underscore
(_) stands for any single character, a percent (%) character stands
for any sequence of characters (including the empty sequence), and all
other characters stand for themselves. The optional escape_character is
a single-character string literal or a character-valued input parameter
(i.e., char or Character) and is used to escape the special meaning of
the underscore and percent characters in pattern_value. Examples are:

address.phone LIKE '12%3'

is true for '123' '12993' and false for '1234'

asentence.word LIKE 'l_se'

is true for 'lose' and false for 'loose'

aword.underscored LIKE '\_%' ESCAPE '\'

is true for '_foo' and false for 'bar'

address.phone NOT LIKE '12%3'

is false for '123'
and '12993' and true for '1234' If the value of the string_expression
or pattern_value is NULL or unknown, the value of the
LIKE expression
is unknown. If the escape_character is specified and is NULL, the value
of the LIKE expression is unknown.

10.2.5.10. JPQL Null Comparison Expressions

The syntax for the use of the
comparison operator IS NULL in a conditional expression is as follows:

{single_valued_path_expression | input_parameter } IS [NOT] NULL

A null comparison expression tests whether or not the single-valued path
expression or input parameter is a NULL value.

10.2.5.11. JPQL Empty Collection Comparison Expressions

The syntax
for the use of the comparison operator IS EMPTY in an
empty_collection_comparison_expression is as follows:

collection_valued_path_expression IS [NOT] EMPTY

This expression tests whether or not the collection designated
by the collection-valued path expression is empty (i.e, has no
elements).

For example, the following query will return all magazines that
don't have any articles at all:

SELECT mag FROM Magazine mag WHERE mag.articles IS EMPTY

If the value of the collection-valued path expression in an empty collection
comparison expression is unknown, the value of the empty comparison
expression is unknown.

10.2.5.12. JPQL Collection Member Expressions

The use of the comparison
collection_member_expression is as follows: syntax for the operator
MEMBER OF in an

This expression tests whether
the designated value is a member of the collection specified by the
collection-valued path expression. If the collection valued path
expression designates an empty collection, the value of the
MEMBER OF expression is FALSE and
the value of the NOT MEMBER OF expression is
TRUE. Otherwise, if the value of the collection-valued path expression
or single-valued association-field path expression in the collection
member expression is NULL or unknown, the value of the collection member
expression is unknown.

10.2.5.13. JPQL Exists Expressions

An EXISTS expression is a predicate that is
true only if the result of the subquery consists of one or more values
and that is false otherwise. The syntax of an exists expression is

The result of this query consists of all authors whose spouse is also an author.

10.2.5.14. JPQL All or Any Expressions

An ALL conditional expression is a predicate
that is true if the comparison operation is true for all values in the
result of the subquery or the result of the subquery is empty. An
ALL conditional expression is false
if the result of the comparison is false
for at least one row, and is unknown if neither true nor false. An
ANY conditional expression is a
predicate that is true if the comparison
operation is true for some value in the result of the subquery. An
ANY conditional expression is false if the
result of the subquery is empty
or if the comparison operation is false for every value in the result
of the subquery, and is unknown if neither true nor false. The keyword
SOME is synonymous with ANY.
The comparison operators used with ALL
or ANY conditional expressions are =, <, <=, >, >=, <>. The result of
the subquery must be like that of the other argument to the comparison
operator in type. See Section 10.2.11, “JPQL Equality and Comparison Semantics”.
The syntax of an ALL or ANY
expression is specified as follows:

all_or_any_expression ::= { ALL | ANY | SOME} (subquery)

The following example select the authors who make the highest salary for
their magazine:

Note that some contexts in which
a subquery can be used require that the subquery be a scalar subquery
(i.e., produce a single result). This is illustrated in the following
example involving a numeric comparison operation.

10.2.5.16. JPQL Functional Expressions

The JPQL includes
the following built-in functions, which may be used in the WHERE
or HAVING clause of a query. If the
value of any argument to a functional expression
is null or unknown, the value of the functional expression is unknown.

The CONCAT function returns a string that is a concatenation of its
arguments. The second and third arguments of the
SUBSTRING function denote
the starting position and length of the substring to be returned. These
arguments are integers. The first position of a string is denoted by
1. The SUBSTRING function returns a string.
The TRIM function trims
the specified character from a string. If the character to be trimmed
is not specified, it is assumed to be space (or blank). The optional
trim_character is a single-character string literal or a character-valued
input parameter (i.e., char or Character). If a trim specification
is not provided, BOTH is assumed.
The TRIM function returns the trimmed
string. The LOWER and UPPER
functions convert a string to lower and upper
case, respectively. They return a string. The LOCATE function returns
the position of a given string within a string, starting the search at
a specified position. It returns the first position at which the string
was found as an integer. The first argument is the string to be located;
the second argument is the string to be searched; the optional third
argument is an integer that represents the string position at which
the search is started (by default, the beginning of the string to be
searched). The first position in a string is denoted by 1. If the string
is not found, 0 is returned. The LENGTH function
returns the length of the string in characters as an integer.

The ABS function takes a numeric
argument and returns a number (integer, float, or double) of the same
type as the argument to the function. The
SQRT function takes a numeric
argument and returns a double.

Note that not all databases support the use of a trim character
other than the space character; use of this argument may result in
queries that are not portable. Note that not all databases support
the use of the third argument to LOCATE;
use of this argument may result
in queries that are not portable.

The MOD function takes two integer
arguments and returns an integer. The
SIZE function returns an integer value,
the number of elements of the
collection. If the collection is empty, the
SIZE function evaluates to
zero. Numeric arguments to these functions may correspond to the numeric
Java object types as well as the primitive numeric types.

10.2.5.16.3. JPQL Datetime Functions

The datetime functions return the value of current date, time, and
timestamp on the database server.

10.2.6. JPQL GROUP BY, HAVING

The GROUP BY construct enables the aggregation of
values according to a set of properties. The HAVING construct enables
conditions to be specified that further restrict the query result. Such
conditions are restrictions upon the groups. The syntax of the
GROUP BY and HAVING clauses is as follows:

If a query contains both a WHERE clause
and a GROUP BY clause, the effect is that of first applying the where
clause, and then forming the groups and filtering them according to
the HAVING clause. The HAVING
clause causes those groups to be retained that satisfy the condition of
the HAVING clause. The requirements for
the SELECT clause when GROUP BY
is used follow those of SQL: namely, any item that appears in the
SELECT clause (other than as an argument
to an aggregate function) must also appear in the GROUP BY
clause. In forming the groups, null values are treated as the same for grouping
purposes. Grouping by an entity is permitted. In this case, the entity
must contain no serialized state fields or lob-valued state fields. The
HAVING clause must specify search conditions over the grouping items or
aggregate functions that apply to grouping items.

If there is no GROUP BY clause and the
HAVING clause is used, the
result is treated as a single group, and the select list can only
consist of aggregate functions.
When a query declares a HAVING clause, it must
always also declare a GROUP BY clause.

10.2.7. JPQL SELECT Clause

The SELECT clause denotes the query result. More than
one value may be returned from the SELECT clause of a query.
The SELECT clause may contain one or more of the following elements: a single
range variable or identification variable that ranges over an entity
abstract schema type, a single-valued path expression, an aggregate
select expression, a constructor expression. The SELECT clause has the
following syntax:

Note that the SELECT clause must be specified to return
only single-valued expressions. The query below is therefore not valid:

SELECT mag.authors FROM Magazine AS mag

The DISTINCT keyword is used to specify that duplicate
values must be eliminated from the query result. If DISTINCT is not
specified, duplicate values are not eliminated. Standalone identification
variables in the SELECT clause may optionally be qualified by the
OBJECT operator. The SELECT clause must not
use the OBJECT operator to qualify path expressions.

10.2.7.1. JPQL Result Type of the SELECT Clause

The type of the query result
specified by the SELECT clause of a query is an entity abstract schema
type, a state-field type, the result of an aggregate function, the result
of a construction operation, or some sequence of these. The result
type of the SELECT clause is defined by the the result types of the
select_expressions contained in it. When multiple select_expressions are
used in the SELECT clause, the result of the query is of type Object[],
and the elements in this result correspond in order to the order of
their specification in the SELECT clause and in type to the result
types of each of the select_expressions. The type of the result of a
select_expression is as follows:

A single_valued_path_expression that is a state_field_path_expression
results in an object of the same type as the corresponding state field
of the entity. If the state field of the entity is a primitive type,
the corresponding object type is returned.

single_valued_path_expression that is a
single_valued_association_path_expression results in an entity object
of the type of the relationship field or the subtype of the relationship
field of the entity object as determined by the object/relational mapping.

The result type of an identification_variable is the type of the
entity to which that identification variable corresponds or a subtype
as determined by the object/relational mapping.

The result type of a constructor_expression is the type of the class
for which the constructor is defined. The types of the arguments to the
constructor are defined by the above rules.

10.2.7.2. JPQL Constructor Expressions

in the SELECT Clause A constructor may
be used in the SELECT list to return one or more Java instances. The
specified class is not required to be an entity or to be mapped to the
database. The constructor name must be fully qualified.

If an entity class name is specified in the SELECT NEW clause,
the resulting entity instances are in the new state.

10.2.7.3. JPQL Null Values in the Query Result

If the result of a query corresponds
to a association-field or state-field whose value is null, that null
value is returned in the result of the query method.
The IS NOT NULL
construct can be used to eliminate such null values from the result set
of the query. Note, however, that state-field types defined in terms of
Java numeric primitive types cannot produce NULL
values in the query
result. A query that returns such a state-field type as a result type
must not return a null value.

10.2.7.4. JPQL Aggregate Functions

in the SELECT Clause The result of a query may
be the result of an aggregate function applied to a path expression. The
following aggregate functions can be used in the SELECT clause of a query:
AVG, COUNT, MAX,
MIN, SUM.
For all aggregate functions except COUNT,
the path expression that is the argument to the aggregate function must
terminate in a state-field. The path expression argument to COUNT may
terminate in either a state-field or a association-field, or the argument
to COUNT may be an identification variable. Arguments to the functions
SUM and AVG must be numeric. Arguments
to the functions MAX and MIN must
correspond to orderable state-field types (i.e., numeric types, string
types, character types, or date types). The Java type that is contained
in the result of a query using an aggregate function is as follows:

COUNT returns Long.

MAX, MIN return the type of the state-field to
which they are applied.

AVG returns Double.

SUM returns Long when
applied to state-fields of integral types (other than BigInteger); Double
when applied to state-fields of floating point types; BigInteger when
applied to state-fields of type BigInteger; and BigDecimal when applied
to state-fields of type BigDecimal. If SUM, AVG,
MAX, or MIN is used,
and there are no values to which the aggregate function can be applied,
the result of the aggregate function is NULL.
If COUNT is used, and
there are no values to which COUNT can be applied, the result of the
aggregate function is 0.

The argument to an aggregate function may be preceded by the keyword
DISTINCT to specify that duplicate values are to be eliminated before
the aggregate function is applied. Null values are eliminated before
the aggregate function is applied, regardless of whether the keyword
DISTINCT is specified.

10.2.7.4.1. JPQL Aggregate Examples

Examples The following query returns the average price of all magazines:

SELECT AVG(mag.price) FROM Magazine mag

The following query returns the sum total cost of all the prices from
all the magazines published by 'Larry':

10.2.8. JPQL ORDER BY Clause

The ORDER BY clause allows the objects or values
that are returned by the query to be ordered.
The syntax of the ORDER BY clause is

orderby_clause ::= ORDER BY orderby_item {, orderby_item}*

orderby_item ::= state_field_path_expression [ASC | DESC]

It is legal to specify DISTINCT with MAX
or MIN, but it does not affect the result.

When the ORDER BY clause is used in a query, each element of the
SELECT clause of the query must be one of the following: an
identification variable x, optionally denoted as OBJECT(x),
a single_valued_association_path_expression,
or a state_field_path_expression. For example:

If more
than one orderby_item is specified, the left-to-right sequence of the
orderby_item elements determines the precedence, whereby the leftmost
orderby_item has highest precedence.
The keyword ASC specifies that
ascending ordering be used; the keyword
DESC specifies that descending
ordering be used. Ascending ordering is the default. SQL rules for the
ordering of null values apply: that is, all null values must appear before
all non-null values in the ordering or all null values must appear after
all non-null values in the ordering, but it is not specified which. The
ordering of the query result is preserved in the result of the query
method if the ORDER BY clause is used.

10.2.9. JPQL Bulk Update and Delete

Operations Bulk update and delete operations
apply to entities of a single entity class (together with its subclasses,
if any). Only one entity abstract schema type may be specified in the
FROM or UPDATE clause.
The syntax of these operations is as follows:

The syntax of
the WHERE clause is
described in Section 10.2.4, “JPQL WHERE Clause”.
A delete operation only
applies to entities of the specified class and its subclasses. It does
not cascade to related entities. The new_value specified for an update
operation must be compatible in type with the state-field to which it
is assigned. Bulk update maps directly to a database update operation,
bypassing optimistic locking checks. Portable applications must manually
update the value of the version column, if desired, and/or manually
validate the value of the version column. The persistence context is
not synchronized with the result of the bulk update or delete. Caution
should be used when executing bulk update or delete operations because
they may result in inconsistencies between the database and the entities
in the active persistence context. In general, bulk update and delete
operations should only be performed within a separate transaction or
at the beginning of a transaction (before entities have been accessed
whose state might be affected by such operations).

Examples:

DELETE FROM Publisher pub WHERE pub.revenue > 1000000.0

DELETE FROM Publisher pub WHERE pub.revenue = 0 AND pub.magazines IS EMPTY

10.2.10. JPQL Null Values

When the target of a reference does not exist in
the database, its value is regarded as NULL.
SQL 92 NULL semantics
defines the evaluation of conditional expressions containing NULL
values. The following is a brief description of these semantics:

Comparison or arithmetic operations with a NULL value always yield
an unknown value.

Two NULL values are not considered to be equal,
the comparison yields an unknown value.

Comparison or arithmetic
operations with an unknown value always yield an unknown value.

The IS NULL and IS NOT NULL
operators convert a NULL state-field or single-valued
association-field value into the respective
TRUE or FALSE value.

Note: The JPQL defines the empty string, "",
as a string with 0 length, which is not equal to a NULL value. However,
NULL values and empty strings may not always be distinguished when
queries are mapped to some databases. Application developers should
therefore not rely on the semantics of query comparisons involving the
empty string and NULL value.

10.2.11. JPQL Equality and Comparison Semantics

Only the values of like types are
permitted to be compared. A type is like another type if they correspond
to the same Java language type, or if one is a primitive Java language
type and the other is the wrappered Java class type equivalent (e.g.,
int and Integer are like types in this sense). There is one exception to
this rule: it is valid to compare numeric values for which the rules of
numeric promotion apply. Conditional expressions attempting to compare
non-like type values are disallowed except for this numeric case. Note
that the arithmetic operators and comparison operators are permitted to
be applied to state-fields and input parameters of the wrappered Java
class equivalents to the primitive numeric Java types. Two entities of
the same abstract schema type are equal if and only if they have the
same primary key value. Only equality/inequality comparisons over enums
are required to be supported.

10.2.12. JPQL BNF

The following is the BNF for the Java Persistence query language,
from section 4.14 of the JSR 220 specification.