Google Git
Sign in
apache / openjpa-site / 36cfd139db917058976908071948336fbc1ff605 / . / content / builds / 1.0.4 / apache-openjpa-1.0.4 / docs / manual / jpa_langref.html
blob: f65fbd3b84349251210e12458179eac3262fdb43 [file] [log] [blame]
<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>2.&nbsp; JPQL Language Reference</title><link rel="stylesheet" href="css/docbook.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.72.0"><link rel="start" href="manual.html" title="Apache OpenJPA User's Guide"><link rel="up" href="jpa_overview_query.html" title="Chapter&nbsp;10.&nbsp; JPA Query"><link rel="prev" href="jpa_overview_query.html" title="Chapter&nbsp;10.&nbsp; JPA Query"><link rel="next" href="jpa_overview_sqlquery.html" title="Chapter&nbsp;11.&nbsp; SQL Queries"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">2.&nbsp;
JPQL Language Reference
</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="jpa_overview_query.html">Prev</a>&nbsp;</td><th width="60%" align="center">Chapter&nbsp;10.&nbsp;
JPA Query
</th><td width="20%" align="right">&nbsp;<a accesskey="n" href="jpa_overview_sqlquery.html">Next</a></td></tr></table><hr></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="jpa_langref"></a>2.&nbsp;
JPQL Language Reference
</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_stmnttypes">2.1.
JPQL Statement Types
</a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_select">2.1.1.
JPQL Select Statement
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_bulk">2.1.2.
JPQL Update and Delete Statements
</a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_schematypes">2.2.
JPQL Abstract Schema Types and Query Domains
</a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_schemanaming">2.2.1.
JPQL Entity Naming
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_schemaexample">2.2.2.
JPQL Schema Example
</a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_fromclause">2.3.
JPQL FROM Clause and Navigational Declarations
</a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_from_identifiers">2.3.1.
JPQL FROM Identifiers
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_from_vars">2.3.2.
JPQL Identification Variables
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_range">2.3.3.
JPQL Range Declarations
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_path">2.3.4.
JPQL Path Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_Joins">2.3.5.
JPQL Joins
</a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_inner_joins">2.3.5.1.
JPQL Inner Joins (Relationship Joins)
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_outer_joins">2.3.5.2.
JPQL Outer Joins
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_fetch_joins">2.3.5.3.
JPQL Fetch Joins
</a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_collection_dec">2.3.6.
JPQL Collection Member Declarations
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_polymorph">2.3.7.
JPQL Polymorphism
</a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_where">2.4.
JPQL WHERE Clause
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_cond">2.5.
JPQL Conditional Expressions
</a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_lit">2.5.1.
JPQL Literals
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_idvar">2.5.2.
JPQL Identification Variables
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_path_exp">2.5.3.
JPQL Path Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_input_params">2.5.4.
JPQL Input Parameters
</a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_pos_params">2.5.4.1.
JPQL Positional Parameters
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_named_params">2.5.4.2.
JPQL Named Parameters
</a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_cond_comp">2.5.5.
JPQL Conditional Expression Composition
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_operators">2.5.6.
JPQL Operators and Operator Precedence
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_between">2.5.7.
JPQL Between Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_in">2.5.8.
JPQL In Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_like">2.5.9.
JPQL Like Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_null">2.5.10.
JPQL Null Comparison Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_empty_comp">2.5.11.
JPQL Empty Collection Comparison Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_collection_member">2.5.12.
JPQL Collection Member Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_exists">2.5.13.
JPQL Exists Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_all_any">2.5.14.
JPQL All or Any Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_subqueries">2.5.15.
JPQL Subqueries
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_functional">2.5.16.
JPQL Functional Expressions
</a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_string_fun">2.5.16.1.
JPQL String Functions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_arithmetic">2.5.16.2.
JPQL Arithmetic Functions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_datetime">2.5.16.3.
JPQL Datetime Functions
</a></span></dt></dl></dd></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_group">2.6.
JPQL GROUP BY, HAVING
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_select_clause">2.7.
JPQL SELECT Clause
</a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_resulttype">2.7.1.
JPQL Result Type of the SELECT Clause
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_constructor">2.7.2.
JPQL Constructor Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_null_select">2.7.3.
JPQL Null Values in the Query Result
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_aggregates">2.7.4.
JPQL Aggregate Functions
</a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_agg_examples">2.7.4.1.
JPQL Aggregate Examples
</a></span></dt></dl></dd></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_orderby">2.8.
JPQL ORDER BY Clause
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_bulk_ops">2.9.
JPQL Bulk Update and Delete
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_null_values">2.10.
JPQL Null Values
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_equality">2.11.
JPQL Equality and Comparison Semantics
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_bnf">2.12.
JPQL BNF
</a></span></dt></dl></div><p>
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, <code class="literal">EJB QL</code>, 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.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Much of this section is paraphrased or taken directly from Chapter 4 of the
JSR 220 specification.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_stmnttypes"></a>2.1.&nbsp;
JPQL Statement Types
</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_select">2.1.1.
JPQL Select Statement
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_bulk">2.1.2.
JPQL Update and Delete Statements
</a></span></dt></dl></div><p>
A JPQL statement may be either a <code class="literal">SELECT</code> statement, an
<code class="literal">UPDATE</code> statement, or a <code class="literal">DELETE</code> 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:
</p><div class="itemizedlist"><ul type="disc"><li><p>
QL_statement ::= select_statement | update_statement | delete_statement
</p></li></ul></div><p>
The complete BNF for JPQL is defined in <a href="jpa_langref.html#jpa_langref_bnf" title="2.12.&nbsp; JPQL BNF">Section&nbsp;2.12, &#8220;
JPQL BNF
&#8221;</a>.
Any JPQL statement may be constructed dynamically or may be statically defined
in a metadata annotation or XML descriptor element. All statement types may
have parameters, as discussed in <a href="jpa_langref.html#jpa_langref_input_params" title="2.5.4.&nbsp; JPQL Input Parameters">Section&nbsp;2.5.4, &#8220;
JPQL Input Parameters
&#8221;</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_select"></a>2.1.1.&nbsp;
JPQL Select Statement
</h4></div></div></div><p>
A select statement is a string which consists of the following clauses:
</p><div class="itemizedlist"><ul type="disc"><li><p>
a <code class="literal">SELECT</code> clause, which determines the type of the objects
or values to be selected.
</p></li><li><p>
a <code class="literal">FROM</code> clause, which provides declarations that designate the
domain to which the expressions specified in the other clauses of the query
apply.
</p></li><li><p>
an optional <code class="literal">WHERE</code> clause, which may be used to restrict the
results that are returned by the query.
</p></li><li><p>
an optional <code class="literal">GROUP BY</code> clause, which allows query results to be
aggregated in terms of groups.
</p></li><li><p>
an optional <code class="literal">HAVING</code> clause, which allows filtering over
aggregated groups.
</p></li><li><p>
an optional <code class="literal">ORDER BY</code> clause, which may be used to order the
results that are returned by the query.
</p></li></ul></div><p>
In BNF syntax, a select statement is defined as:
</p><div class="itemizedlist"><ul type="disc"><li><p>
select_statement ::= select_clause from_clause [where_clause] [groupby_clause]
[having_clause] [orderby_clause]
</p></li></ul></div><p>
A select statement must always have a <code class="literal">SELECT</code> and a
<code class="literal">FROM</code> clause. The square brackets [] indicate that the other
clauses are optional.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_bulk"></a>2.1.2.&nbsp;
JPQL Update and Delete Statements
</h4></div></div></div><p>
Update and delete statements provide bulk operations over sets of entities. In
BNF syntax, these operations are defined as:
</p><div class="itemizedlist"><ul type="disc"><li><p>
update_statement ::= update_clause [where_clause]
</p></li><li><p>
delete_statement ::= delete_clause [where_clause]
</p></li></ul></div><p>
The update and delete clauses determine the type of the entities to be updated
or deleted. The <code class="literal">WHERE</code> clause may be used to restrict the
scope of the update or delete operation. Update and delete statements are
described further in <a href="jpa_langref.html#jpa_langref_bulk_ops" title="2.9.&nbsp; JPQL Bulk Update and Delete">Section&nbsp;2.9, &#8220;
JPQL Bulk Update and Delete
&#8221;</a>.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_schematypes"></a>2.2.&nbsp;
JPQL Abstract Schema Types and Query Domains
</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_schemanaming">2.2.1.
JPQL Entity Naming
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_schemaexample">2.2.2.
JPQL Schema Example
</a></span></dt></dl></div><p>
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.
</p><p>
Informally, the abstract schema type of an entity can be characterized as
follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
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.
</p></li><li><p>
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.
</p></li></ul></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_schemanaming"></a>2.2.1.&nbsp;
JPQL Entity Naming
</h4></div></div></div><p>
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.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_schemaexample"></a>2.2.2.&nbsp;
JPQL Schema Example
</h4></div></div></div><p>
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 <code class="literal">Magazine</code>, <code class="literal">
Publisher</code>, <code class="literal">Author</code>, and <code class="literal">Article</code>.
</p><p>
Several Entities with Abstract Persistence Schemas Defined in the Same
Persistence Unit. The entity <code class="literal">Publisher</code> has a one-to-many
relationships with <code class="literal">Magazine</code>. There is also a one-to-many
relationship between <code class="literal">Magazine</code> and <code class="literal">Article</code>
. The entity <code class="literal">Article</code> is related to <code class="literal">Author</code>
in a one-to-one relationship.
</p><p>
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:
</p><pre class="programlisting">
SELECT DISTINCT mag FROM Magazine AS mag JOIN mag.articles AS art WHERE art.published = FALSE
</pre><p>
This query navigates over the association-field authors of the
abstract schema type <code class="literal">Magazine</code> to find articles, and uses the
state-field <code class="literal">published</code> of <code class="literal">Article</code> to select
those magazines that have at least one article that is published. Although
predefined reserved identifiers, such as <code class="literal">DISTINCT</code>, <code class="literal">
FROM</code>, <code class="literal">AS</code>, <code class="literal">JOIN</code>, <code class="literal">
WHERE</code>, and <code class="literal">FALSE</code> appear in upper case in this
example, predefined reserved identifiers are case insensitive. The <code class="literal">
SELECT</code> 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 <code class="literal">articles</code> 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".
</p><pre class="programlisting">
SELECT DISTINCT mag FROM Magazine mag JOIN mag.articles art JOIN art.author auth WHERE auth.firstName = 'John'
</pre><p>
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.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_fromclause"></a>2.3.&nbsp;
JPQL FROM Clause and Navigational Declarations
</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_from_identifiers">2.3.1.
JPQL FROM Identifiers
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_from_vars">2.3.2.
JPQL Identification Variables
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_range">2.3.3.
JPQL Range Declarations
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_path">2.3.4.
JPQL Path Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_Joins">2.3.5.
JPQL Joins
</a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_inner_joins">2.3.5.1.
JPQL Inner Joins (Relationship Joins)
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_outer_joins">2.3.5.2.
JPQL Outer Joins
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_fetch_joins">2.3.5.3.
JPQL Fetch Joins
</a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_collection_dec">2.3.6.
JPQL Collection Member Declarations
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_polymorph">2.3.7.
JPQL Polymorphism
</a></span></dt></dl></div><p>
The <code class="literal">FROM</code> clause of a query defines the domain of the query by
declaring identification variables. An identification variable is an identifier
declared in the <code class="literal">FROM</code> 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 <code class="literal">FROM
</code> clause can contain multiple identification variable declarations
separated by a comma (,).
</p><div class="itemizedlist"><ul type="disc"><li><p>
from_clause ::= FROM identification_variable_declaration {,
{identification_variable_declaration | collection_member_declaration}}*
</p></li><li><p>
identification_variable_declaration ::= range_variable_declaration { join |
fetch_join }*
</p></li><li><p>
range_variable_declaration ::= abstract_schema_name [AS] identification_variable
</p></li><li><p>
join ::= join_spec join_association_path_expression [AS] identification_variable
</p></li><li><p>
fetch_join ::= join_spec FETCH join_association_path_expression
</p></li><li><p>
join_association_path_expression ::= join_collection_valued_path_expression |
join_single_valued_association_path_expression
</p></li><li><p>
join_spec ::= [ LEFT [OUTER] | INNER ] JOIN
</p></li><li><p>
collection_member_declaration ::= IN (collection_valued_path_expression) [AS]
identification_variable
</p></li></ul></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_from_identifiers"></a>2.3.1.&nbsp;
JPQL FROM Identifiers
</h4></div></div></div><p>
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 <code class="methodname">
Character.isJavaIdentifierStart</code> returns <code class="literal">true</code>.
This includes the underscore (_) character and the dollar sign ($) character. An
identifier part character is any character for which the method <code class="methodname">
Character.isJavaIdentifierPart</code> returns <code class="literal">true</code>.
The question mark (?) character is reserved for use by the Java Persistence
query language. The following are reserved identifiers:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">SELECT</code>
</p></li><li><p>
<code class="literal">FROM</code>
</p></li><li><p>
<code class="literal">WHERE</code>
</p></li><li><p>
<code class="literal">UPDATE</code>
</p></li><li><p>
<code class="literal">DELETE</code>
</p></li><li><p>
<code class="literal">JOIN</code>
</p></li><li><p>
<code class="literal">OUTER</code>
</p></li><li><p>
<code class="literal">INNER</code>
</p></li><li><p>
<code class="literal">LEFT</code>
</p></li><li><p>
<code class="literal">GROUP</code>
</p></li><li><p>
<code class="literal">BY</code>
</p></li><li><p>
<code class="literal">HAVING</code>
</p></li><li><p>
<code class="literal">FETCH</code>
</p></li><li><p>
<code class="literal">DISTINCT</code>
</p></li><li><p>
<code class="literal">OBJECT</code>
</p></li><li><p>
<code class="literal">NULL</code>
</p></li><li><p>
<code class="literal">TRUE</code>
</p></li><li><p>
<code class="literal">FALSE</code>
</p></li><li><p>
<code class="literal">NOT</code>
</p></li><li><p>
<code class="literal">AND</code>
</p></li><li><p>
<code class="literal">OR</code>
</p></li><li><p>
<code class="literal">BETWEEN</code>
</p></li><li><p>
<code class="literal">LIKE</code>
</p></li><li><p>
<code class="literal">IN</code>
</p></li><li><p>
<code class="literal">AS</code>
</p></li><li><p>
<code class="literal">UNKNOWN</code>
</p></li><li><p>
<code class="literal">EMPTY</code>
</p></li><li><p>
<code class="literal">MEMBER</code>
</p></li><li><p>
<code class="literal">OF</code>
</p></li><li><p>
<code class="literal">IS</code>
</p></li><li><p>
<code class="literal">AVG</code>
</p></li><li><p>
<code class="literal">MAX</code>
</p></li><li><p>
<code class="literal">MIN</code>
</p></li><li><p>
<code class="literal">SUM</code>
</p></li><li><p>
<code class="literal">COUNT</code>
</p></li><li><p>
<code class="literal">ORDER</code>
</p></li><li><p>
<code class="literal">BY</code>
</p></li><li><p>
<code class="literal">ASC</code>
</p></li><li><p>
<code class="literal">DESC</code>
</p></li><li><p>
<code class="literal">MOD</code>
</p></li><li><p>
<code class="literal">UPPER</code>
</p></li><li><p>
<code class="literal">LOWER</code>
</p></li><li><p>
<code class="literal">TRIM</code>
</p></li><li><p>
<code class="literal">POSITION</code>
</p></li><li><p>
<code class="literal">CHARACTER_LENGTH</code>
</p></li><li><p>
<code class="literal">CHAR_LENGTH</code>
</p></li><li><p>
<code class="literal">BIT_LENGTH</code>
</p></li><li><p>
<code class="literal">CURRENT_TIME</code>
</p></li><li><p>
<code class="literal">CURRENT_DATE</code>
</p></li><li><p>
<code class="literal">CURRENT_TIMESTAMP</code>
</p></li><li><p>
<code class="literal">NEW</code>
</p></li><li><p>
<code class="literal">EXISTS</code>
</p></li><li><p>
<code class="literal">ALL</code>
</p></li><li><p>
<code class="literal">ANY</code>
</p></li><li><p>
<code class="literal">SOME</code>
</p></li></ul></div><p>
Reserved identifiers are case insensitive. Reserved identifiers must not be
used as identification variables. It is recommended that other SQL reserved
words also not be as identification variables in queries because they may be
used as reserved identifiers in future releases of the specification.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_from_vars"></a>2.3.2.&nbsp;
JPQL Identification Variables
</h4></div></div></div><p>
An identification variable is a valid identifier declared in the <code class="literal">FROM
</code> clause of a query. All identification variables must be declared in
the <code class="literal">FROM</code> 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: </p><pre class="programlisting">SELECT DISTINCT mag FROM Magazine mag JOIN mag.articles art JOIN art.author auth WHERE auth.firstName = 'John'
</pre><p> In the <code class="literal">FROM</code> clause declaration <code class="literal">
mag.articles</code><code class="literal">art</code>, the identification variable
<code class="literal">art</code> evaluates to any <code class="literal">Article</code> value
directly reachable from <code class="literal">Magazine</code>. The association-field
<code class="literal">articles</code> is a collection of instances of the abstract schema
type <code class="literal">Article</code> and the identification variable <code class="literal">art
</code> refers to an element of this collection. The type of <code class="literal">auth
</code> is the abstract schema type of <code class="literal">Author</code>. 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 <code class="literal">FROM</code>
clause, and an identification variable declaration can use the result of a
preceding identification variable declaration of the query string.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_range"></a>2.3.3.&nbsp;
JPQL Range Declarations
</h4></div></div></div><p>
The syntax for declaring an identification variable as a range variable is
similar to that of SQL; optionally, it uses the AS keyword.
</p><div class="itemizedlist"><ul type="disc"><li><p>
range_variable_declaration ::= abstract_schema_name [AS]
identification_variable
</p></li></ul></div><p>
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 <code class="literal">FROM</code> clause.
</p><p>
The following query returns magazines whose price is greater than the price of
magazines published by "Adventure" publishers. This example illustrates the use
of two different identification variables in the <code class="literal">FROM</code> clause,
both of the abstract schema type Magazine. The <code class="literal">SELECT</code> clause
of this query determines that it is the magazines with prices greater than those
of "Adventure" publisher's that are returned.
</p><pre class="programlisting">
SELECT DISTINCT mag1 FROM Magazine mag1, Magazine mag2
WHERE mag1.price &gt; mag2.price AND mag2.publisher.name = 'Adventure'
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_path"></a>2.3.4.&nbsp;
JPQL Path Expressions
</h4></div></div></div><p>
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 a 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 a 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:
</p><div class="itemizedlist"><ul type="disc"><li><p>
single_valued_path_expression ::= state_field_path_expression |
single_valued_association_path_expression
</p></li><li><p>
state_field_path_expression ::= {identification_variable |
single_valued_association_path_expression}.state_field
</p></li><li><p>
single_valued_association_path_expression ::=
identification_variable.{single_valued_association_field.}*single_valued_association_field
</p></li><li><p>
collection_valued_path_expression ::=
identification_variable.{single_valued_association_field.}*collection_valued_association_field
</p></li><li><p>
state_field ::= {embedded_class_state_field.}*simple_state_field
</p></li></ul></div><p>
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.
</p><p>
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 <code class="literal">mag
</code> designates <code class="literal">Magazine</code>, the path expression <code class="literal">
mag.articles.author</code> 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 <code class="literal">FROM</code> clause to range over the elements of the
<code class="literal">articles</code> collection. Another path expression must be used to
navigate over each such element in the <code class="literal">WHERE</code> clause of the
query, as in the following query which returns all authors that have any
articles in any magazines: </p><pre class="programlisting">SELECT DISTINCT art.author FROM Magazine AS mag, IN(mag.articles) art
</pre><p>
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_Joins"></a>2.3.5.&nbsp;
JPQL Joins
</h4></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_inner_joins">2.3.5.1.
JPQL Inner Joins (Relationship Joins)
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_outer_joins">2.3.5.2.
JPQL Outer Joins
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_fetch_joins">2.3.5.3.
JPQL Fetch Joins
</a></span></dt></dl></div><p>
An inner join may be implicitly specified by the use of a cartesian product in
the <code class="literal">FROM</code> clause and a join condition in the <code class="literal">WHERE
</code> clause.
</p><p>
The syntax for explicit join operations is as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
join ::= join_spec join_association_path_expression [AS] identification_variable
</p></li><li><p>
fetch_join ::= join_spec FETCH join_association_path_expression
</p></li><li><p>
join_association_path_expression ::= join_collection_valued_path_expression |
join_single_valued_association_path_expression
</p></li><li><p>
join_spec ::= [ LEFT [OUTER] | INNER ] JOIN
</p></li></ul></div><p>
The following inner and outer join operation types are supported.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_inner_joins"></a>2.3.5.1.&nbsp;
JPQL Inner Joins (Relationship Joins)
</h5></div></div></div><p>
The syntax for the inner join operation is </p><pre class="programlisting">
[ INNER ] JOIN join_association_path_expression [AS] identification_variable
</pre><p> For example, the query below joins over the relationship
between publishers and magazines. This type of join typically equates to a join
over a foreign key relationship in the database.
</p><pre class="programlisting">
SELECT pub FROM Publisher pub JOIN pub.magazines mag WHERE pub.revenue &gt; 1000000
</pre><p>
The keyword <code class="literal">INNER</code> may optionally be used:
</p><pre class="programlisting">
SELECT pub FROM Publisher pub INNER JOIN pub.magazines mag WHERE pub.revenue &gt; 1000000
</pre><p>
This is equivalent to the following query using the earlier
<code class="literal">IN</code> construct. It selects those publishers with revenue of
over 1 million for which at least one magazine exists:
</p><pre class="programlisting">
SELECT OBJECT(pub) FROM Publisher pub, IN(pub.magazines) mag WHERE pub.revenue &gt; 1000000
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_outer_joins"></a>2.3.5.2.&nbsp;
JPQL Outer Joins
</h5></div></div></div><p>
<code class="literal">LEFT JOIN</code> and <code class="literal">LEFT OUTER JOIN</code> are
synonymous. They enable the retrieval of a set of entities where matching values
in the join condition may be absent. The syntax for a left outer join is:
</p><pre class="programlisting">LEFT [OUTER] JOIN join_association_path_expression [AS] identification_variable
</pre><p>
</p><p>
For example: </p><pre class="programlisting">SELECT pub FROM Publisher pub LEFT JOIN pub.magazines mag WHERE pub.revenue &gt; 1000000
</pre><p> The keyword <code class="literal">OUTER</code> may optionally be used:
</p><pre class="programlisting">SELECT pub FROM Publisher pub LEFT OUTER JOIN pub.magazines mags WHERE pub.revenue &gt; 1000000
</pre><p> An important use case for <code class="literal">LEFT JOIN</code> is in
enabling the prefetching of related data items as a side effect of a query. This
is accomplished by specifying the <code class="literal">LEFT JOIN</code> as a <code class="literal">
FETCH JOIN</code>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_fetch_joins"></a>2.3.5.3.&nbsp;
JPQL Fetch Joins
</h5></div></div></div><p>
A <code class="literal">FETCH JOIN</code> enables the fetching of an association as a side
effect of the execution of a query. A <code class="literal">FETCH JOIN</code> is specified
over an entity and its related entities. The syntax for a fetch join is
</p><div class="itemizedlist"><ul type="disc"><li><p>fetch_join ::= [ LEFT [OUTER] | INNER ] JOIN
FETCH join_association_path_expression
</p></li></ul></div><p>
</p><p>
The association referenced by the right side of the <code class="literal">FETCH JOIN
</code> 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
<code class="literal">FETCH JOIN</code> 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 <code class="literal">articles</code> that are retrieved is determined
by the metadata for the <code class="literal">Article</code> entity class.
</p><pre class="programlisting">SELECT mag FROM Magazine mag LEFT JOIN FETCH mag.articles WHERE mag.id = 1
</pre><p>
</p><p>
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.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_collection_dec"></a>2.3.6.&nbsp;
JPQL Collection Member Declarations
</h4></div></div></div><p>
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 <code class="literal">IN</code>
. The argument to the <code class="literal">IN</code> 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:
</p><p>
</p><div class="itemizedlist"><ul type="disc"><li><p>collection_member_declaration ::= IN
(collection_valued_path_expression) [AS] identification_variable
</p></li></ul></div><p>
</p><p>
For example, the query </p><pre class="programlisting">SELECT DISTINCT mag FROM Magazine mag
JOIN mag.articles art
JOIN art.author auth
WHERE auth.lastName = 'Grisham'</pre><p> may equivalently be
expressed as follows, using the <code class="literal">IN</code> operator: </p><pre class="programlisting">SELECT DISTINCT mag FROM Magazine mag,
IN(mag.articles) art
WHERE art.author.lastName = 'Grisham'</pre><p> In this example,
<code class="literal">articles</code> is the name of an association-field whose value is a
collection of instances of the abstract schema type <code class="literal">Article</code>.
The identification variable <code class="literal">art</code> designates a member of this
collection, a single <code class="literal">Article</code> abstract schema type instance.
In this example, <code class="literal">mag</code> is an identification variable of the
abstract schema type <code class="literal">Magazine</code>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_polymorph"></a>2.3.7.&nbsp;
JPQL Polymorphism
</h4></div></div></div><p>
Java Persistence queries are automatically polymorphic. The <code class="literal">FROM
</code> clause of a query designates not only instances of the specific
entity classes to which explicitly refers but of subclasses as well. The
instances returned by a query include instances of the subclasses that satisfy
the query criteria.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_where"></a>2.4.&nbsp;
JPQL WHERE Clause
</h3></div></div></div><p>
The <code class="literal">WHERE</code> clause of a query consists of a conditional
expression used to select objects or values that satisfy the expression. The
<code class="literal">WHERE</code> clause restricts the result of a select statement or
the scope of an update or delete operation. A <code class="literal">WHERE</code> clause is
defined as follows: </p><div class="itemizedlist"><ul type="disc"><li><p>where_clause ::= WHERE
conditional_expression
</p></li></ul></div><p>
</p><p>
The <code class="literal">GROUP BY</code> construct enables the aggregation of values
according to the properties of an entity class. The <code class="literal">HAVING</code>
construct enables conditions to be specified that further restrict the query
result as restrictions upon the groups. The syntax of the <code class="literal">HAVING
</code> clause is as follows: </p><div class="itemizedlist"><ul type="disc"><li><p>having_clause
::= HAVING conditional_expression
</p></li></ul></div><p>
</p><p>
The <code class="literal">GROUP BY</code> and <code class="literal">HAVING</code> constructs are
further discussed in <a href="jpa_langref.html#jpa_langref_group" title="2.6.&nbsp; JPQL GROUP BY, HAVING">Section&nbsp;2.6, &#8220;
JPQL GROUP BY, HAVING
&#8221;</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_cond"></a>2.5.&nbsp;
JPQL Conditional Expressions
</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_lit">2.5.1.
JPQL Literals
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_idvar">2.5.2.
JPQL Identification Variables
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_path_exp">2.5.3.
JPQL Path Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_input_params">2.5.4.
JPQL Input Parameters
</a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_pos_params">2.5.4.1.
JPQL Positional Parameters
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_named_params">2.5.4.2.
JPQL Named Parameters
</a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_cond_comp">2.5.5.
JPQL Conditional Expression Composition
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_operators">2.5.6.
JPQL Operators and Operator Precedence
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_between">2.5.7.
JPQL Between Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_in">2.5.8.
JPQL In Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_like">2.5.9.
JPQL Like Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_null">2.5.10.
JPQL Null Comparison Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_empty_comp">2.5.11.
JPQL Empty Collection Comparison Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_collection_member">2.5.12.
JPQL Collection Member Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_exists">2.5.13.
JPQL Exists Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_all_any">2.5.14.
JPQL All or Any Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_subqueries">2.5.15.
JPQL Subqueries
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_functional">2.5.16.
JPQL Functional Expressions
</a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_string_fun">2.5.16.1.
JPQL String Functions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_arithmetic">2.5.16.2.
JPQL Arithmetic Functions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_datetime">2.5.16.3.
JPQL Datetime Functions
</a></span></dt></dl></dd></dl></div><p>
The following sections describe the language constructs that can be used in a
conditional expression of the <code class="literal">WHERE</code> clause or <code class="literal">
HAVING</code> clause. State-fields that are mapped in serialized form or as
lobs may not be portably used in conditional expressions. </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> The
implementation is not expected to perform such query operations involving such
fields in memory rather than in the database.
</p></div><p>
</p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_lit"></a>2.5.1.&nbsp;
JPQL Literals
</h4></div></div></div><p>
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 <code class="literal">TRUE</code> and
<code class="literal">FALSE</code>. Although predefined reserved literals appear in upper
case, they are case insensitive.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_idvar"></a>2.5.2.&nbsp;
JPQL Identification Variables
</h4></div></div></div><p>
All identification variables used in the <code class="literal">WHERE</code> or <code class="literal">
HAVING</code> clause of a <code class="literal">SELECT</code> or <code class="literal">DELETE
</code> statement must be declared in the <code class="literal">FROM</code> clause, as
described in <a href="jpa_langref.html#jpa_langref_from_vars" title="2.3.2.&nbsp; JPQL Identification Variables">Section&nbsp;2.3.2, &#8220;
JPQL Identification Variables
&#8221;</a>. The identification
variables used in the <code class="literal">WHERE</code> clause of an <code class="literal">UPDATE
</code> statement must be declared in the <code class="literal">UPDATE</code> clause.
Identification variables are existentially quantified in the <code class="literal">WHERE
</code> and <code class="literal">HAVING</code> 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.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_path_exp"></a>2.5.3.&nbsp;
JPQL Path Expressions
</h4></div></div></div><p>
It is illegal to use a collection_valued_path_expression within a <code class="literal">
WHERE</code> or <code class="literal">HAVING</code> 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 <code class="literal">SIZE</code>
operator.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_input_params"></a>2.5.4.&nbsp;
JPQL Input Parameters
</h4></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_pos_params">2.5.4.1.
JPQL Positional Parameters
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_named_params">2.5.4.2.
JPQL Named Parameters
</a></span></dt></dl></div><p>
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 <code class="literal">WHERE</code> clause or <code class="literal">HAVING</code> clause of a
query.
</p><p>
Note that if an input parameter value is null, comparison operations or
arithmetic operations involving the input parameter will return an unknown
value. See <a href="jpa_langref.html#jpa_langref_null_values" title="2.10.&nbsp; JPQL Null Values">Section&nbsp;2.10, &#8220;
JPQL Null Values
&#8221;</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_pos_params"></a>2.5.4.1.&nbsp;
JPQL Positional Parameters
</h5></div></div></div><p>
The following rules apply to positional parameters. </p><div class="itemizedlist"><ul type="disc"><li><p> Input parameters are designated by the question mark (?) prefix followed
by an integer. For example: ?1.
</p></li><li><p>
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.
</p></li></ul></div><p>
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_named_params"></a>2.5.4.2.&nbsp;
JPQL Named Parameters
</h5></div></div></div><p>
A named parameter is an identifier that is prefixed by the ":" symbol. It
follows the rules for identifiers defined in
<a href="jpa_langref.html#jpa_langref_from_identifiers" title="2.3.1.&nbsp; JPQL FROM Identifiers">Section&nbsp;2.3.1, &#8220;
JPQL FROM Identifiers
&#8221;</a>. Named parameters are case
sensitive.
</p><p>
Example: </p><pre class="programlisting">SELECT pub FROM Publisher pub WHERE pub.revenue &gt; :rev
</pre><p>
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_cond_comp"></a>2.5.5.&nbsp;
JPQL Conditional Expression Composition
</h4></div></div></div><p>
Conditional expressions are composed of other conditional expressions,
comparison operations, logical operations, path expressions that evaluate to
boolean values, boolean literals, and boolean input parameters. Arithmetic
expressions can be used in comparison expressions. Arithmetic expressions are
composed of other arithmetic expressions, arithmetic operations, path
expressions that evaluate to numeric values, numeric literals, and numeric input
parameters. Arithmetic operations use numeric promotion. Standard bracketing ()
for ordering expression evaluation is supported. Conditional expressions are
defined as follows:
</p><p>
</p><div class="itemizedlist"><ul type="disc"><li><p>conditional_expression ::= conditional_term |
conditional_expression OR conditional_term
</p></li><li><p>
conditional_term ::= conditional_factor | conditional_term AND
conditional_factor
</p></li><li><p>
conditional_factor ::= [ NOT ] conditional_primary
</p></li><li><p>
conditional_primary ::= simple_cond_expression | (conditional_expression)
</p></li><li><p>
simple_cond_expression ::= comparison_expression | between_expression |
like_expression | in_expression | null_comparison_expression |
empty_collection_comparison_expression | collection_member_expression |
exists_expression
</p></li></ul></div><p>
</p><p>
Aggregate functions can only be used in conditional expressions in a <code class="literal">
HAVING</code> clause. See <a href="jpa_langref.html#jpa_langref_group" title="2.6.&nbsp; JPQL GROUP BY, HAVING">Section&nbsp;2.6, &#8220;
JPQL GROUP BY, HAVING
&#8221;</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_operators"></a>2.5.6.&nbsp;
JPQL Operators and Operator Precedence
</h4></div></div></div><p>
The operators are listed below in order of decreasing precedence. </p><div class="itemizedlist"><ul type="disc"><li><p> Navigation operator (.)
</p></li><li><p>
Arithmetic operators: +, - unary *, / multiplication and division +, - addition
and subtraction
</p></li><li><p>
Comparison operators: =, &gt;, &gt;=, &lt;, &lt;=, &lt;&gt; (not equal), [
<code class="literal">NOT</code> ] <code class="literal">BETWEEN</code>, [ <code class="literal">NOT</code> ]
<code class="literal">LIKE</code>, [ <code class="literal">NOT</code> ] <code class="literal">IN</code>,
<code class="literal">IS</code> [ <code class="literal">NOT</code> ] <code class="literal">NULL</code>,
<code class="literal">IS</code> [ <code class="literal">NOT</code> ] <code class="literal">EMPTY</code>, [
<code class="literal">NOT</code> ] <code class="literal">MEMBER</code> [ <code class="literal">OF</code> ]
</p></li><li><p>
Logical operators: <code class="literal">NOT</code><code class="literal">AND</code><code class="literal">OR
</code>
</p></li></ul></div><p>
The following sections describe other operators used in specific expressions.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_between"></a>2.5.7.&nbsp;
JPQL Between Expressions
</h4></div></div></div><p>
The syntax for the use of the comparison operator [ <code class="literal">NOT</code> ]
<code class="literal">BETWEEN</code> in a conditional expression is as follows:
</p><p>
arithmetic_expression [NOT] BETWEEN arithmetic_expression AND
arithmetic_expression | string_expression [NOT] BETWEEN string_expression AND
string_expression | datetime_expression [NOT] BETWEEN datetime_expression AND
datetime_expression
</p><p>
The BETWEEN expression </p><pre class="programlisting">x BETWEEN y AND z</pre><p> is
semantically equivalent to: </p><pre class="programlisting">y &lt;= x AND x &lt;= z
</pre><p> The rules for unknown and <code class="literal">NULL</code> values in
comparison operations apply. See <a href="jpa_langref.html#jpa_langref_null_values" title="2.10.&nbsp; JPQL Null Values">Section&nbsp;2.10, &#8220;
JPQL Null Values
&#8221;</a>
. Examples are: </p><pre class="programlisting">p.age BETWEEN 15 and 19</pre><p> is
equivalent to </p><pre class="programlisting">p.age &gt;= 15 AND p.age &lt;= 19</pre><p>
</p><p>
</p><pre class="programlisting">p.age NOT BETWEEN 15 and 19</pre><p> is equivalent to
</p><pre class="programlisting">p.age &lt; 15 OR p.age &gt; 19</pre><p>
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_in"></a>2.5.8.&nbsp;
JPQL In Expressions
</h4></div></div></div><p>
The syntax for the use of the comparison operator [ <code class="literal">NOT</code> ]
<code class="literal">IN</code> in a conditional expression is as follows:
</p><p>
</p><div class="itemizedlist"><ul type="disc"><li><p>in_expression ::= state_field_path_expression
[NOT] IN ( in_item {, in_item}* | subquery)
</p></li><li><p>
in_item ::= literal | input_parameter
</p></li></ul></div><p>
</p><p>
The state_field_path_expression must have a string, numeric, or enum value. The
literal and/or input_parameter values must be like the same abstract schema type
of the state_field_path_expression in type. (See
<a href="jpa_langref.html#jpa_langref_equality" title="2.11.&nbsp; JPQL Equality and Comparison Semantics">Section&nbsp;2.11, &#8220;
JPQL Equality and Comparison Semantics
&#8221;</a> ).
</p><p>
The results of the subquery must be like the same abstract schema type of the
state_field_path_expression in type. Subqueries are discussed in
<a href="jpa_langref.html#jpa_langref_subqueries" title="2.5.15.&nbsp; JPQL Subqueries">Section&nbsp;2.5.15, &#8220;
JPQL Subqueries
&#8221;</a>. Examples are: </p><pre class="programlisting">o.country IN ('UK', 'US', 'France')
</pre><p> is true for UK and false for Peru, and is equivalent to the
expression: </p><pre class="programlisting">(o.country = 'UK') OR (o.country = 'US') OR (o.country = ' France')
</pre><p> In the following expression: </p><pre class="programlisting">o.country NOT IN ('UK', 'US', 'France')
</pre><p> is false for UK and true for Peru, and is equivalent to the
expression: </p><pre class="programlisting">NOT ((o.country = 'UK') OR (o.country = 'US') OR (o.country = 'France'))
</pre><p> There must be at least one element in the comma separated list
that defines the set of values for the <code class="literal">IN</code> expression. If the
value of a state_field_path_expression in an <code class="literal">IN</code> or <code class="literal">
NOT IN</code> expression is <code class="literal">NULL</code> or unknown, the value of
the expression is unknown.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_like"></a>2.5.9.&nbsp;
JPQL Like Expressions
</h4></div></div></div><p>
The syntax for the use of the comparison operator [ <code class="literal">NOT</code> ]
<code class="literal">LIKE</code> in a conditional expression is as follows:
</p><p>
string_expression [NOT] LIKE pattern_value [ESCAPE escape_character]
</p><p>
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:
</p><p>
</p><div class="itemizedlist"><ul type="disc"><li><pre class="programlisting">address.phone LIKE '12%3'
</pre><p> is true for '123' '12993' and false for '1234'
</p></li><li><p>
</p><pre class="programlisting">asentence.word LIKE 'l_se'</pre><p> is true for 'lose'
and false for 'loose'
</p></li><li><p>
</p><pre class="programlisting">aword.underscored LIKE '\_%' ESCAPE '\'</pre><p> is true
for '_foo' and false for 'bar'
</p></li><li><p>
</p><pre class="programlisting">address.phone NOT LIKE '12%3'</pre><p> is false for
'123' and '12993' and true for '1234'. If the value of the string_expression or
pattern_value is <code class="literal">NULL</code> or unknown, the value of the <code class="literal">
LIKE</code> expression is unknown. If the escape_character is specified and
is <code class="literal">NULL</code>, the value of the <code class="literal">LIKE</code> expression
is unknown.
</p></li></ul></div><p>
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_null"></a>2.5.10.&nbsp;
JPQL Null Comparison Expressions
</h4></div></div></div><p>
The syntax for the use of the comparison operator <code class="literal">IS NULL</code> in
a conditional expression is as follows:
</p><p>
{single_valued_path_expression | input_parameter } IS [NOT] NULL
</p><p>
A null comparison expression tests whether or not the single-valued path
expression or input parameter is a <code class="literal">NULL</code> value.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_empty_comp"></a>2.5.11.&nbsp;
JPQL Empty Collection Comparison Expressions
</h4></div></div></div><p>
The syntax for the use of the comparison operator <code class="literal">IS EMPTY</code> in
an empty_collection_comparison_expression is as follows:
</p><p>
collection_valued_path_expression IS [NOT] EMPTY
</p><p>
This expression tests whether or not the collection designated by the
collection-valued path expression is empty (i.e, has no elements).
</p><p>
For example, the following query will return all magazines that don't have any
articles at all: </p><pre class="programlisting">SELECT mag FROM Magazine mag WHERE mag.articles IS EMPTY
</pre><p> 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.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_collection_member"></a>2.5.12.&nbsp;
JPQL Collection Member Expressions
</h4></div></div></div><p>
The use of the comparison collection_member_expression is as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
collection_member_expression ::= entity_expression [NOT] MEMBER [OF]
collection_valued_path_expression
</p></li><li><p>
entity_expression ::= single_valued_association_path_expression |
simple_entity_expression
</p></li><li><p>
simple_entity_expression ::= identification_variable | input_parameter
</p></li></ul></div><p>
</p><p>
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 <code class="literal">
MEMBER OF</code> expression is <code class="literal">FALSE</code> and the value of the
<code class="literal">NOT MEMBER OF</code> expression is <code class="literal">TRUE</code>.
Otherwise, if the value of the collection-valued path expression or
single-valued association-field path expression in the collection member
expression is <code class="literal">NULL</code> or unknown, the value of the collection
member expression is unknown.
</p><p>
The use of the reserved word OF is optional in this expression.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_exists"></a>2.5.13.&nbsp;
JPQL Exists Expressions
</h4></div></div></div><p>
An <code class="literal">EXISTS</code> 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 </p><div class="itemizedlist"><ul type="disc"><li><p>
exists_expression ::= [NOT] EXISTS (subquery)
</p></li></ul></div><p>
</p><p>
Example: </p><pre class="programlisting">SELECT DISTINCT auth FROM Author auth
WHERE EXISTS
(SELECT spouseAuthor FROM Author spouseAuthor WHERE spouseAuthor = auth.spouse)
</pre><p> The result of this query consists of all authors whose spouse
is also an author.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_all_any"></a>2.5.14.&nbsp;
JPQL All or Any Expressions
</h4></div></div></div><p>
An <code class="literal">ALL</code> 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 <code class="literal">ALL</code> 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 <code class="literal">ANY</code>
conditional expression is a predicate that is true if the comparison operation
is true for some value in the result of the subquery. An <code class="literal">ANY</code>
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 <code class="literal">SOME</code> is
synonymous with <code class="literal">ANY</code>. The comparison operators used with
<code class="literal">ALL</code> or <code class="literal">ANY</code> conditional expressions are =,
&lt;, &lt;=, &gt;, &gt;=, &lt;&gt;. The result of the subquery must be like that
of the other argument to the comparison operator in type. See
<a href="jpa_langref.html#jpa_langref_equality" title="2.11.&nbsp; JPQL Equality and Comparison Semantics">Section&nbsp;2.11, &#8220;
JPQL Equality and Comparison Semantics
&#8221;</a>. The syntax of an <code class="literal">ALL
</code> or <code class="literal">ANY</code> expression is specified as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>all_or_any_expression ::= { ALL | ANY | SOME}
(subquery)
</p></li></ul></div><p>
</p><p>
The following example select the authors who make the highest salary for their
magazine: </p><pre class="programlisting">SELECT auth FROM Author auth
WHERE auth.salary &gt;= ALL(SELECT a.salary FROM Author a WHERE a.magazine = auth.magazine)
</pre><p>
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_subqueries"></a>2.5.15.&nbsp;
JPQL Subqueries
</h4></div></div></div><p>
Subqueries may be used in the <code class="literal">WHERE</code> or <code class="literal">HAVING
</code> clause. The syntax for subqueries is as follows: </p><div class="itemizedlist"><ul type="disc"><li><p>subquery ::= simple_select_clause subquery_from_clause
[where_clause] [groupby_clause] [having_clause]
</p></li></ul></div><p>
</p><p>
Subqueries are restricted to the <code class="literal">WHERE</code> and <code class="literal">HAVING
</code> clauses in this release. Support for subqueries in the <code class="literal">FROM
</code> clause will be considered in a later release of the specification.
</p><div class="itemizedlist"><ul type="disc"><li><p>simple_select_clause ::= SELECT [DISTINCT]
simple_select_expression
</p></li><li><p>
subquery_from_clause ::= FROM subselect_identification_variable_declaration {,
subselect_identification_variable_declaration}*
</p></li><li><p>
subselect_identification_variable_declaration ::=
identification_variable_declaration | association_path_expression [AS]
identification_variable | collection_member_declaration
</p></li><li><p>
simple_select_expression ::= single_valued_path_expression |
aggregate_expression | identification_variable
</p></li></ul></div><p>
</p><p>
Examples: </p><pre class="programlisting">SELECT DISTINCT auth FROM Author auth
WHERE EXISTS (SELECT spouseAuth FROM Author spouseAuth WHERE spouseAuth = auth.spouse)
</pre><pre class="programlisting">SELECT mag FROM Magazine mag
WHERE (SELECT COUNT(art) FROM mag.articles art) &gt; 10</pre><p>
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.
</p><pre class="programlisting">SELECT goodPublisher FROM Publisher goodPublisher
WHERE goodPublisher.revenue &lt; (SELECT AVG(p.revenue) FROM Publisher p)
</pre><p>
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_functional"></a>2.5.16.&nbsp;
JPQL Functional Expressions
</h4></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_string_fun">2.5.16.1.
JPQL String Functions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_arithmetic">2.5.16.2.
JPQL Arithmetic Functions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_datetime">2.5.16.3.
JPQL Datetime Functions
</a></span></dt></dl></div><p>
The JPQL includes the following built-in functions, which may be used in the
<code class="literal">WHERE</code> or <code class="literal">HAVING</code> 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.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_string_fun"></a>2.5.16.1.&nbsp;
JPQL String Functions
</h5></div></div></div><p>
</p><div class="itemizedlist"><ul type="disc"><li><p>functions_returning_strings ::=
CONCAT(string_primar y, string_primary) | SUBSTRING(string_primar y,
simple_arithmetic_expression, simple_arithmetic_expression) |
TRIM([[trim_specification] [trim_character] FROM] string_primary) |
LOWER(string_primar y) | UPPER(string_primar y)
</p></li><li><p>
trim_specification ::= LEADING | TRAILING | BOTH
</p></li><li><p>
functions_returning_numerics ::= LENGTH(string_primar y) | LOCATE(string_primar
y, string_primar y[, simple_arithmetic_expression])
</p></li></ul></div><p>
</p><p>
The <code class="literal">CONCAT</code> function returns a string that is a concatenation
of its arguments. The second and third arguments of the <code class="literal">SUBSTRING
</code> 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 <code class="literal">SUBSTRING</code> function returns a string. The
<code class="literal">TRIM</code> 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, <code class="literal">BOTH</code> is assumed. The <code class="literal">
TRIM</code> function returns the trimmed string. The <code class="literal">LOWER</code>
and <code class="literal">UPPER</code> functions convert a string to lower and upper case,
respectively. They return a string. The <code class="literal">LOCATE</code> 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 <code class="literal">LENGTH
</code> function returns the length of the string in characters as an
integer.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_arithmetic"></a>2.5.16.2.&nbsp;
JPQL Arithmetic Functions
</h5></div></div></div><p>
</p><div class="itemizedlist"><ul type="disc"><li><p>functions_returning_numerics ::=
ABS(simple_arithmetic_expression) | SQRT(simple_arithmetic_expression) |
MOD(simple_arithmetic_expression, simple_arithmetic_expression) |
SIZE(collection_valued_path_expression)
</p></li></ul></div><p>
</p><p>
The <code class="literal">ABS</code> function takes a numeric argument and returns a
number (integer, float, or double) of the same type as the argument to the
function. The <code class="literal">SQRT</code> function takes a numeric argument and
returns a double.
</p><p>
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
<code class="literal">LOCATE</code>; use of this argument may result in queries that are
not portable.
</p><p>
The <code class="literal">MOD</code> function takes two integer arguments and returns an
integer. The <code class="literal">SIZE</code> function returns an integer value, the
number of elements of the collection. If the collection is empty, the <code class="literal">
SIZE</code> function evaluates to zero. Numeric arguments to these functions
may correspond to the numeric Java object types as well as the primitive numeric
types.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_datetime"></a>2.5.16.3.&nbsp;
JPQL Datetime Functions
</h5></div></div></div><p>
functions_returning_datetime:= CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP
</p><p>
The datetime functions return the value of current date, time, and timestamp on
the database server.
</p></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_group"></a>2.6.&nbsp;
JPQL GROUP BY, HAVING
</h3></div></div></div><p>
The <code class="literal">GROUP BY</code> construct enables the aggregation of values
according to a set of properties. The <code class="literal">HAVING</code> construct
enables conditions to be specified that further restrict the query result. Such
conditions are restrictions upon the groups. The syntax of the <code class="literal">GROUP
BY</code> and <code class="literal">HAVING</code> clauses is as follows:
</p><p>
</p><div class="itemizedlist"><ul type="disc"><li><p>groupby_clause ::= GROUP BY groupby_item {,
groupby_item}*
</p></li><li><p>
groupby_item ::= single_valued_path_expression | identification_variable
</p></li><li><p>
having_clause ::= HAVING conditional_expression
</p></li></ul></div><p>
</p><p>
If a query contains both a <code class="literal">WHERE</code> clause and a <code class="literal">GROUP
BY</code> clause, the effect is that of first applying the where clause, and
then forming the groups and filtering them according to the <code class="literal">HAVING
</code> clause. The <code class="literal">HAVING</code> clause causes those groups to
be retained that satisfy the condition of the <code class="literal">HAVING</code> clause.
The requirements for the <code class="literal">SELECT</code> clause when <code class="literal">GROUP
BY</code> is used follow those of SQL: namely, any item that appears in the
<code class="literal">SELECT</code> clause (other than as an argument to an aggregate
function) must also appear in the <code class="literal">GROUP BY</code> 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 <code class="literal">HAVING</code> clause
must specify search conditions over the grouping items or aggregate functions
that apply to grouping items.
</p><p>
If there is no <code class="literal">GROUP BY</code> clause and the <code class="literal">HAVING
</code> 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
<code class="literal">HAVING</code> clause, it must always also declare a <code class="literal">GROUP
BY</code> clause.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_select_clause"></a>2.7.&nbsp;
JPQL SELECT Clause
</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_resulttype">2.7.1.
JPQL Result Type of the SELECT Clause
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_constructor">2.7.2.
JPQL Constructor Expressions
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_null_select">2.7.3.
JPQL Null Values in the Query Result
</a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_aggregates">2.7.4.
JPQL Aggregate Functions
</a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_agg_examples">2.7.4.1.
JPQL Aggregate Examples
</a></span></dt></dl></dd></dl></div><p>
The <code class="literal">SELECT</code> clause denotes the query result. More than one
value may be returned from the <code class="literal">SELECT</code> clause of a query. The
<code class="literal">SELECT</code> 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 <code class="literal">SELECT</code>
clause has the following syntax:
</p><p>
</p><div class="itemizedlist"><ul type="disc"><li><p>select_clause ::= SELECT [DISTINCT]
select_expression {, select_expression}*
</p></li><li><p>
select_expression ::= single_valued_path_expression | aggregate_expression |
identification_variable | OBJECT(identification_variable) |
constructor_expression
</p></li><li><p>
constructor_expression ::= NEW constructor_name ( constructor_item {,
constructor_item}*)
</p></li><li><p>
constructor_item ::= single_valued_path_expression | aggregate_expression
</p></li><li><p>
aggregate_expression ::= { AVG | MAX | MIN | SUM } ([DISTINCT]
state_field_path_expression) | COUNT ([DISTINCT] identification_variable |
state_field_path_expression | single_valued_association_path_expression)
</p></li></ul></div><p>
</p><p>
For example: </p><pre class="programlisting">SELECT pub.id, pub.revenue
FROM Publisher pub JOIN pub.magazines mag WHERE mag.price &gt; 5.00
</pre><p>
</p><p>
Note that the <code class="literal">SELECT</code> clause must be specified to return only
single-valued expressions. The query below is therefore not valid:
</p><pre class="programlisting">SELECT mag.authors FROM Magazine AS mag</pre><p> The
<code class="literal">DISTINCT</code> keyword is used to specify that duplicate values
must be eliminated from the query result. If <code class="literal">DISTINCT</code> is not
specified, duplicate values are not eliminated. Standalone identification
variables in the <code class="literal">SELECT</code> clause may optionally be qualified by
the <code class="literal">OBJECT</code> operator. The <code class="literal">SELECT</code> clause
must not use the OBJECT operator to qualify path expressions.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_resulttype"></a>2.7.1.&nbsp;
JPQL Result Type of the SELECT Clause
</h4></div></div></div><p>
The type of the query result specified by the <code class="literal">SELECT</code> 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 <code class="literal">SELECT</code> clause is defined by
the the result types of the select_expressions contained in it. When multiple
select_expressions are used in the <code class="literal">SELECT</code> 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 <code class="literal">SELECT</code>
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: </p><div class="itemizedlist"><ul type="disc"><li><p> 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.
</p></li><li><p>
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.
</p></li><li><p>
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.
</p></li><li><p>
The result type of aggregate_expression is defined in section
<a href="jpa_langref.html#jpa_langref_aggregates" title="2.7.4.&nbsp; JPQL Aggregate Functions">Section&nbsp;2.7.4, &#8220;
JPQL Aggregate Functions
&#8221;</a>.
</p></li><li><p>
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.
</p></li></ul></div><p>
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_constructor"></a>2.7.2.&nbsp;
JPQL Constructor Expressions
</h4></div></div></div><p>
in the <code class="literal">SELECT</code> Clause A constructor may be used in the
<code class="literal">SELECT</code> 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.
</p><p>
If an entity class name is specified in the <code class="literal">SELECT NEW</code>
clause, the resulting entity instances are in the new state. </p><pre class="programlisting">SELECT NEW com.company.PublisherInfo(pub.id, pub.revenue, mag.price)
FROM Publisher pub JOIN pub.magazines mag WHERE mag.price &gt; 5.00
</pre><p>
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_null_select"></a>2.7.3.&nbsp;
JPQL Null Values in the Query Result
</h4></div></div></div><p>
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 <code class="literal">IS NOT NULL</code> 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 <code class="literal">NULL
</code> values in the query result. A query that returns such a state-field
type as a result type must not return a null value.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_aggregates"></a>2.7.4.&nbsp;
JPQL Aggregate Functions
</h4></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_agg_examples">2.7.4.1.
JPQL Aggregate Examples
</a></span></dt></dl></div><p>
in the <code class="literal">SELECT</code> 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 <code class="literal">SELECT</code> clause of a query:
<code class="literal">AVG</code>, <code class="literal">COUNT</code>, <code class="literal">MAX</code>,
<code class="literal">MIN</code>, <code class="literal">SUM</code>. For all aggregate functions
except <code class="literal">COUNT</code>, the path expression that is the argument to
the aggregate function must terminate in a state-field. The path expression
argument to <code class="literal">COUNT</code> may terminate in either a state-field or a
association-field, or the argument to <code class="literal">COUNT</code> may be an
identification variable. Arguments to the functions <code class="literal">SUM</code> and
<code class="literal">AVG</code> must be numeric. Arguments to the functions <code class="literal">MAX
</code> and <code class="literal">MIN</code> 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: </p><div class="itemizedlist"><ul type="disc"><li><p><code class="literal">COUNT</code> returns
Long.
</p></li><li><p>
<code class="literal">MAX</code>, <code class="literal">MIN</code> return the type of the
state-field to which they are applied.
</p></li><li><p>
<code class="literal">AVG</code> returns Double.
</p></li><li><p>
<code class="literal">SUM</code> 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 <code class="literal">SUM
</code>, <code class="literal">AVG</code>, <code class="literal">MAX</code>, or <code class="literal">MIN
</code> is used, and there are no values to which the aggregate function can
be applied, the result of the aggregate function is <code class="literal">NULL</code>. If
<code class="literal">COUNT</code> is used, and there are no values to which <code class="literal">
COUNT</code> can be applied, the result of the aggregate function is 0.
</p></li></ul></div><p>
</p><p>
The argument to an aggregate function may be preceded by the keyword <code class="literal">
DISTINCT</code> to specify that duplicate values are to be eliminated before
the aggregate function is applied.
It is legal to specify <code class="literal">DISTINCT</code> with <code class="literal">MAX</code>
or <code class="literal">MIN</code>, but it does not affect the result.
Null values are eliminated before the
aggregate function is applied, regardless of whether the keyword <code class="literal">
DISTINCT</code> is specified.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_agg_examples"></a>2.7.4.1.&nbsp;
JPQL Aggregate Examples
</h5></div></div></div><p>
Examples The following query returns the average price of all magazines:
</p><pre class="programlisting">SELECT AVG(mag.price) FROM Magazine mag</pre><p> The
following query returns the sum of all the prices from all the
magazines published by 'Larry': </p><pre class="programlisting">SELECT SUM(mag.price) FROM Publisher pub JOIN pub.magazines mag pub.firstName = 'Larry'
</pre><p> The following query returns the total number of magazines:
</p><pre class="programlisting">SELECT COUNT(mag) FROM Magazine mag</pre><p>
</p></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_orderby"></a>2.8.&nbsp;
JPQL ORDER BY Clause
</h3></div></div></div><p>
The <code class="literal">ORDER BY</code> clause allows the objects or values that are
returned by the query to be ordered. The syntax of the <code class="literal">ORDER BY
</code> clause is
</p><p>
</p><div class="itemizedlist"><ul type="disc"><li><p>orderby_clause ::= ORDER BY orderby_item {,
orderby_item}*
</p></li><li><p>
orderby_item ::= state_field_path_expression [ASC | DESC]
</p></li></ul></div><p>
</p><p>
When the <code class="literal">ORDER BY</code> clause is used in a query, each element of
the <code class="literal">SELECT</code> clause of the query must be one of the following:
an identification variable x, optionally denoted as <code class="literal">OBJECT(x)</code>
, a single_valued_association_path_expression, or a state_field_path_expression.
For example: </p><pre class="programlisting">
SELECT pub FROM Publisher pub ORDER BY pub.revenue, pub.name
</pre><p> 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 <code class="literal">ASC</code>
specifies that ascending ordering be used; the keyword <code class="literal">DESC</code>
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 <code class="literal">ORDER BY</code> clause is used.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_bulk_ops"></a>2.9.&nbsp;
JPQL Bulk Update and Delete
</h3></div></div></div><p>
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 <code class="literal">FROM</code> or <code class="literal">UPDATE
</code> clause. The syntax of these operations is as follows:
</p><p>
</p><div class="itemizedlist"><ul type="disc"><li><p>update_statement ::= update_clause [where_clause]
</p></li><li><p>
update_clause ::= UPDATE abstract_schema_name [[AS] identification_variable] SET
update_item {, update_item}*
</p></li><li><p>
update_item ::= [identification_variable.]{state_field |
single_valued_association_field} = new_value
</p></li><li><p>
new_value ::= simple_arithmetic_expression | string_primary | datetime_primary |
boolean_primary | enum_primary simple_entity_expression | NULL
</p></li><li><p>
delete_statement ::= delete_clause [where_clause]
</p></li><li><p>
delete_clause ::= DELETE FROM abstract_schema_name [[AS]
identification_variable]
</p></li></ul></div><p>
</p><p>
The syntax of the <code class="literal">WHERE</code> clause is described in
<a href="jpa_langref.html#jpa_langref_where" title="2.4.&nbsp; JPQL WHERE Clause">Section&nbsp;2.4, &#8220;
JPQL WHERE Clause
&#8221;</a>. 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).
</p><p>
Examples: </p><pre class="programlisting">DELETE FROM Publisher pub WHERE pub.revenue &gt; 1000000.0
</pre><pre class="programlisting">DELETE FROM Publisher pub WHERE pub.revenue = 0 AND pub.magazines IS EMPTY
</pre><pre class="programlisting">UPDATE Publisher pub SET pub.status = 'outstanding'
WHERE pub.revenue &lt; 1000000 AND 20 &gt; (SELECT COUNT(mag) FROM pub.magazines mag)
</pre><p>
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_null_values"></a>2.10.&nbsp;
JPQL Null Values
</h3></div></div></div><p>
When the target of a reference does not exist in the database, its value is
regarded as <code class="literal">NULL</code>. SQL 92 <code class="literal">NULL</code> semantics
defines the evaluation of conditional expressions containing <code class="literal">NULL
</code> values. The following is a brief description of these semantics:
</p><p>
</p><div class="itemizedlist"><ul type="disc"><li><p> Comparison or arithmetic operations with a
<code class="literal">NULL</code> value always yield an unknown value.
</p></li><li><p>
Two <code class="literal">NULL</code> values are not considered to be equal, the
comparison yields an unknown value.
</p></li><li><p>
Comparison or arithmetic operations with an unknown value always yield an
unknown value.
</p></li><li><p>
The <code class="literal">IS NULL</code> and <code class="literal">IS NOT NULL</code> operators
convert a <code class="literal">NULL</code> state-field or single-valued association-field
value into the respective <code class="literal">TRUE</code> or <code class="literal">FALSE</code>
value.
</p></li></ul></div><p>
</p><p>
Note: The JPQL defines the empty string, "", as a string with 0 length, which is
not equal to a <code class="literal">NULL</code> value. However, <code class="literal">NULL</code>
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 <code class="literal">NULL
</code> value.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_equality"></a>2.11.&nbsp;
JPQL Equality and Comparison Semantics
</h3></div></div></div><p>
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.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_bnf"></a>2.12.&nbsp;
JPQL BNF
</h3></div></div></div><p>
The following is the BNF for the Java Persistence query language, from section
4.14 of the JSR 220 specification.
</p><div class="itemizedlist"><ul type="disc"><li><p>
QL_statement ::= select_statement | update_statement | delete_statement
</p></li><li><p>
select_statement ::= select_clause from_clause [where_clause] [groupby_clause]
[having_clause] [orderby_clause]
</p></li><li><p>
update_statement ::= update_clause [where_clause]
</p></li><li><p>
delete_statement ::= delete_clause [where_clause]
</p></li><li><p>
from_clause ::= <code class="literal">FROM</code> identification_variable_declaration {,
{identification_variable_declaration | collection_member_declaration}}*
</p></li><li><p>
identification_variable_declaration ::= range_variable_declaration { join |
fetch_join }*
</p></li><li><p>
range_variable_declaration ::= abstract_schema_name [ <code class="literal">AS</code> ]
identification_variable
</p></li><li><p>
join ::= join_spec join_association_path_expression [ <code class="literal">AS</code> ]
identification_variable
</p></li><li><p>
fetch_join ::= join_spec <code class="literal">FETCH</code>
join_association_path_expression
</p></li><li><p>
association_path_expression ::= collection_valued_path_expression |
single_valued_association_path_expression
</p></li><li><p>
join_spec ::= [ <code class="literal">LEFT</code> [ <code class="literal">OUTER</code> ]| <code class="literal">
INNER</code> ] <code class="literal">JOIN</code>
</p></li><li><p>
join_association_path_expression ::= join_collection_valued_path_expression |
join_single_valued_association_path_expression
</p></li><li><p>
join_collection_valued_path_expression ::=
identification_variable.collection_valued_association_field
</p></li><li><p>
join_single_valued_association_path_expression ::=
identification_variable.single_valued_association_field
</p></li><li><p>
collection_member_declaration ::= <code class="literal">IN</code>
(collection_valued_path_expression) [ <code class="literal">AS</code> ]
identification_variable
</p></li><li><p>
single_valued_path_expression ::= state_field_path_expression |
single_valued_association_path_expression
</p></li><li><p>
state_field_path_expression ::= {identification_variable |
single_valued_association_path_expression}.state_field
</p></li><li><p>
single_valued_association_path_expression ::=
identification_variable.{single_valued_association_field.}*
single_valued_association_field
</p></li><li><p>
collection_valued_path_expression ::=
identification_variable.{single_valued_association_field.}*collection_valued_association_field
</p></li><li><p>
state_field ::= {embedded_class_state_field.}*simple_state_field
</p></li><li><p>
update_clause ::= <code class="literal">UPDATE</code> abstract_schema_name [[ <code class="literal">AS
</code> ] identification_variable] <code class="literal">SET</code> update_item {,
update_item}*
</p></li><li><p>
update_item ::= [identification_variable.]{state_field |
single_valued_association_field}= new_value
</p></li><li><p>
new_value ::= simple_arithmetic_expression | string_primary | datetime_primary |
boolean_primary | enum_primary simple_entity_expression | <code class="literal">NULL
</code>
</p></li><li><p>
delete_clause ::= <code class="literal">DELETE</code><code class="literal">FROM</code>
abstract_schema_name [[ <code class="literal">AS</code> ] identification_variable]
</p></li><li><p>
select_clause ::= <code class="literal">SELECT</code> [ <code class="literal">DISTINCT</code> ]
select_expression {, select_expression}*
</p></li><li><p>
select_expression ::= single_valued_path_expression | aggregate_expression |
identification_variable | <code class="literal">OBJECT</code> (identification_variable)|
constructor_expression
</p></li><li><p>
constructor_expression ::= <code class="literal">NEW</code> constructor_name(
constructor_item {, constructor_item}*)
</p></li><li><p>
constructor_item ::= single_valued_path_expression | aggregate_expression
</p></li><li><p>
aggregate_expression ::= { <code class="literal">AVG</code> | <code class="literal">MAX</code> |
<code class="literal">MIN</code> | <code class="literal">SUM</code> }([ <code class="literal">DISTINCT</code>
] state_field_path_expression) | <code class="literal">COUNT</code> ([ <code class="literal">DISTINCT
</code> ] identification_variable | state_field_path_expression |
single_valued_association_path_expression)
</p></li><li><p>
where_clause ::= <code class="literal">WHERE</code> conditional_expression
</p></li><li><p>
groupby_clause ::= <code class="literal">GROUP</code><code class="literal">BY</code> groupby_item {,
groupby_item}*
</p></li><li><p>
groupby_item ::= single_valued_path_expression | identification_variable
</p></li><li><p>
having_clause ::= <code class="literal">HAVING</code> conditional_expression
</p></li><li><p>
orderby_clause ::= <code class="literal">ORDER</code><code class="literal">BY</code> orderby_item {,
orderby_item}*
</p></li><li><p>
orderby_item ::= state_field_path_expression [ <code class="literal">ASC</code> |
<code class="literal">DESC</code> ]
</p></li><li><p>
subquery ::= simple_select_clause subquery_from_clause [where_clause]
[groupby_clause] [having_clause]
</p></li><li><p>
subquery_from_clause ::= <code class="literal">FROM</code>
subselect_identification_variable_declaration {,
subselect_identification_variable_declaration}*
</p></li><li><p>
subselect_identification_variable_declaration ::=
identification_variable_declaration | association_path_expression [ <code class="literal">AS
</code> ] identification_variable | collection_member_declaration
</p></li><li><p>
simple_select_clause ::= <code class="literal">SELECT</code> [ <code class="literal">DISTINCT</code>
] simple_select_expression
</p></li><li><p>
simple_select_expression ::= single_valued_path_expression |
aggregate_expression | identification_variable
</p></li><li><p>
conditional_expression ::= conditional_term | conditional_expression <code class="literal">
OR</code> conditional_term
</p></li><li><p>
conditional_term ::= conditional_factor | conditional_term <code class="literal">AND
</code> conditional_factor
</p></li><li><p>
conditional_factor ::= [ <code class="literal">NOT</code> ] conditional_primary
</p></li><li><p>
conditional_primary ::= simple_cond_expression |(conditional_expression)
</p></li><li><p>
simple_cond_expression ::= comparison_expression | between_expression |
like_expression | in_expression | null_comparison_expression |
empty_collection_comparison_expression | collection_member_expression |
exists_expression
</p></li><li><p>
between_expression ::= arithmetic_expression [ <code class="literal">NOT</code> ]
<code class="literal">BETWEEN</code> arithmetic_expression <code class="literal">AND</code>
arithmetic_expression | string_expression [ <code class="literal">NOT</code> ] <code class="literal">
BETWEEN</code> string_expression <code class="literal">AND</code> string_expression |
datetime_expression [ <code class="literal">NOT</code> ] <code class="literal">BETWEEN</code>
datetime_expression <code class="literal">AND</code> datetime_expression
</p></li><li><p>
in_expression ::= state_field_path_expression [ <code class="literal">NOT</code> ]
<code class="literal">IN</code> ( in_item {, in_item}* | subquery)
</p></li><li><p>
in_item ::= literal | input_parameter
</p></li><li><p>
like_expression ::= string_expression [ <code class="literal">NOT</code> ] <code class="literal">LIKE
</code> pattern_value [ <code class="literal">ESCAPE</code> escape_character]
</p></li><li><p>
null_comparison_expression ::= {single_valued_path_expression | input_parameter}
<code class="literal">IS</code> [ <code class="literal">NOT</code> ] <code class="literal">NULL</code>
</p></li><li><p>
empty_collection_comparison_expression ::= collection_valued_path_expression
<code class="literal">IS</code> [ <code class="literal">NOT</code> ] <code class="literal">EMPTY</code>
</p></li><li><p>
collection_member_expression ::= entity_expression [ <code class="literal">NOT</code> ]
<code class="literal">MEMBER</code> [ <code class="literal">OF</code> ]
collection_valued_path_expression
</p></li><li><p>
exists_expression ::= [ <code class="literal">NOT</code> ] <code class="literal">EXISTS</code>
(subquery)
</p></li><li><p>
all_or_any_expression ::= { <code class="literal">ALL</code> | <code class="literal">ANY</code> |
<code class="literal">SOME</code> }(subquery)
</p></li><li><p>
comparison_expression ::=
string_expressioncomparison_operator{string_expression|all_or_any_expression}|
boolean_expression {=|&lt;&gt;} {boolean_expression | all_or_any_expression} |
enum_expression {=|&lt;&gt;} {enum_expression | all_or_any_expression} |
datetime_expression comparison_operator {datetime_expression |
all_or_any_expression} | entity_expression {= |&lt;&gt; } {entity_expression |
all_or_any_expression} | arithmetic_expression comparison_operator
{arithmetic_expression | all_or_any_expression}
</p></li><li><p>
comparison_operator ::== |&gt; |&gt;= |&lt; |&lt;= |&lt;&gt;
</p></li><li><p>
arithmetic_expression ::= simple_arithmetic_expression |(subquery)
</p></li><li><p>
simple_arithmetic_expression ::= arithmetic_term | simple_arithmetic_expression
{+ |- } arithmetic_term
</p></li><li><p>
arithmetic_term ::= arithmetic_factor | arithmetic_term {* |/ }
arithmetic_factor
</p></li><li><p>
arithmetic_factor ::= [{+ |-}] arithmetic_primary
</p></li><li><p>
arithmetic_primary ::= state_field_path_expression | numeric_literal |
(simple_arithmetic_expression) | input_parameter | functions_returning_numerics
| aggregate_expression
</p></li><li><p>
string_expression ::= string_primary |(subquery)
</p></li><li><p>
string_primary ::= state_field_path_expression | string_literal |
input_parameter | functions_returning_strings | aggregate_expression
</p></li><li><p>
datetime_expression ::= datetime_primary |(subquery)
</p></li><li><p>
datetime_primary ::= state_field_path_expression | input_parameter |
functions_returning_datetime | aggregate_expression
</p></li><li><p>
boolean_expression ::= boolean_primary |(subquery)
</p></li><li><p>
boolean_primary ::= state_field_path_expression | boolean_literal |
input_parameter |
</p></li><li><p>
enum_expression ::= enum_primary |(subquery)
</p></li><li><p>
enum_primary ::= state_field_path_expression | enum_literal | input_parameter |
</p></li><li><p>
entity_expression ::= single_valued_association_path_expression |
simple_entity_expression
</p></li><li><p>
simple_entity_expression ::= identification_variable | input_parameter
</p></li><li><p>
functions_returning_numerics ::= <code class="literal">LENGTH</code> (string_primary)|
<code class="literal">LOCATE</code> (string_primary,string_primary [,
simple_arithmetic_expression]) | <code class="literal">ABS</code>
(simple_arithmetic_expression) | <code class="literal">SQRT</code>
(simple_arithmetic_expression) | <code class="literal">MOD</code>
(simple_arithmetic_expression, simple_arithmetic_expression) | <code class="literal">SIZE
</code> (collection_valued_path_expression)
</p></li><li><p>
functions_returning_datetime ::= <code class="literal">CURRENT_DATE</code> | <code class="literal">
CURRENT_TIME</code> | <code class="literal">CURRENT_TIMESTAMP</code>
</p></li><li><p>
functions_returning_strings ::= <code class="literal">CONCAT</code> (string_primary,
string_primary) | <code class="literal">SUBSTRING</code> (string_primary,
simple_arithmetic_expression,simple_arithmetic_expression)| <code class="literal">TRIM
</code> ([[trim_specification] [trim_character] <code class="literal">FROM</code> ]
string_primary) | <code class="literal">LOWER</code> (string_primary) | <code class="literal">UPPER
</code> (string_primary)
</p></li><li><p>
trim_specification ::= <code class="literal">LEADING</code> | <code class="literal">TRAILING</code>
| <code class="literal">BOTH</code>
</p></li></ul></div></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="jpa_overview_query.html">Prev</a>&nbsp;</td><td width="20%" align="center"><a accesskey="u" href="jpa_overview_query.html">Up</a></td><td width="40%" align="right">&nbsp;<a accesskey="n" href="jpa_overview_sqlquery.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter&nbsp;10.&nbsp;
JPA Query
&nbsp;</td><td width="20%" align="center"><a accesskey="h" href="manual.html">Home</a></td><td width="40%" align="right" valign="top">&nbsp;Chapter&nbsp;11.&nbsp;
SQL Queries
</td></tr></table></div></body></html>