The Java Persistence Query Language (JPQL) is used to define searches against
persistent entities independent of the mechanism used to store those entities.
As such, JPQL is "portable", and not constrained to any particular data store.
The Java Persistence query language is an extension of the Enterprise JavaBeans
query language, EJB QL
, adding operations such as bulk
deletes and updates, join operations, aggregates, projections, and subqueries.
Furthermore, JPQL queries can be declared statically in metadata, or can be
dynamically built in code. This chapter provides the full definition of the
language.
Much of this section is paraphrased or taken directly from Chapter 4 of the JSR 220 specification.
A JPQL statement may be either a SELECT
statement, an
UPDATE
statement, or a DELETE
statement.
This chapter refers to all such statements as "queries". Where it is important
to distinguish among statement types, the specific statement type is referenced.
In BNF syntax, a query language statement is defined as:
QL_statement ::= select_statement | update_statement | delete_statement
The complete BNF for JPQL is defined in Section 2.12, “ JPQL BNF ”. 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 Section 2.5.4, “ JPQL Input Parameters ”.
A select statement is a string which consists of the following clauses:
a SELECT
clause, which determines the type of the objects
or values to be selected.
a FROM
clause, which provides declarations that designate the
domain to which the expressions specified in the other clauses of the query
apply.
an optional WHERE
clause, which may be used to restrict the
results that are returned by the query.
an optional GROUP BY
clause, which allows query results to be
aggregated in terms of groups.
an optional HAVING
clause, which allows filtering over
aggregated groups.
an optional ORDER BY
clause, which may be used to order the
results that are returned by the query.
In BNF syntax, a select statement is defined as:
select_statement ::= select_clause from_clause [where_clause] [groupby_clause] [having_clause] [orderby_clause]
A select statement must always have a SELECT
and a
FROM
clause. The square brackets [] indicate that the other
clauses are optional.
Update and delete statements provide bulk operations over sets of entities. In BNF syntax, these operations are defined as:
update_statement ::= update_clause [where_clause]
delete_statement ::= delete_clause [where_clause]
The update and delete clauses determine the type of the entities to be updated
or deleted. The WHERE
clause may be used to restrict the
scope of the update or delete operation. Update and delete statements are
described further in Section 2.9, “
JPQL Bulk Update and Delete
”.
The Java Persistence query language is a typed language, and every expression has a type. The type of an expression is derived from the structure of the expression, the abstract schema types of the identification variable declarations, the types to which the persistent fields and relationships evaluate, and the types of literals. The abstract schema type of an entity is derived from the entity class and the metadata information provided by Java language annotations or in the XML descriptor.
Informally, the abstract schema type of an entity can be characterized as follows:
For every persistent field or get accessor method (for a persistent property) of the entity class, there is a field ("state-field") whose abstract schema type corresponds to that of the field or the result type of the accessor method.
For every persistent relationship field or get accessor method (for a persistent relationship property) of the entity class, there is a field ("association-field") whose type is the abstract schema type of the related entity (or, if the relationship is a one-to-many or many-to-many, a collection of such). Abstract schema types are specific to the query language data model. The persistence provider is not required to implement or otherwise materialize an abstract schema type. The domain of a query consists of the abstract schema types of all entities that are defined in the same persistence unit. The domain of a query may be restricted by the navigability of the relationships of the entity on which it is based. The association-fields of an entity's abstract schema type determine navigability. Using the association-fields and their values, a query can select related entities and use their abstract schema types in the query.
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.
This example assumes that the application developer provides several entity
classes, representing magazines, publishers, authors, and articles. The abstract
schema types for these entities are Magazine
,
Publisher
, Author
, and Article
.
Several Entities with Abstract Persistence Schemas Defined in the Same
Persistence Unit. The entity Publisher
has a one-to-many
relationships with Magazine
. There is also a one-to-many
relationship between Magazine
and Article
. The entity Article
is related to Author
in a one-to-one relationship.
Queries to select magazines can be defined by navigating over the association-fields and state-fields defined by Magazine and Author. A query to find all magazines that have unpublished articles is as follows:
SELECT DISTINCT mag FROM Magazine AS mag JOIN mag.articles AS art WHERE art.published = FALSE
This query navigates over the association-field authors of the
abstract schema type Magazine
to find articles, and uses the
state-field published
of Article
to select
those magazines that have at least one article that is published. Although
predefined reserved identifiers, such as DISTINCT
,
FROM
, AS
, JOIN
,
WHERE
, and FALSE
appear in upper case in this
example, predefined reserved identifiers are case insensitive. The
SELECT
clause of this example designates the return type of this
query to be of type Magazine. Because the same persistence unit defines the
abstract persistence schemas of the related entities, the developer can also
specify a query over articles
that utilizes the abstract
schema type for products, and hence the state-fields and association-fields of
both the abstract schema types Magazine and Author. For example, if the
abstract schema type Author has a state-field named firstName, a query over
articles can be specified using this state-field. Such a query might be to
find all magazines that have articles authored by someone with the first name
"John".
SELECT DISTINCT mag FROM Magazine mag JOIN mag.articles art JOIN art.author auth WHERE auth.firstName = 'John'
Because Magazine is related to Author by means of the relationships between Magazine and Article and between Article and Author, navigation using the association-fields authors and product is used to express the query. This query is specified by using the abstract schema name Magazine, which designates the abstract schema type over which the query ranges. The basis for the navigation is provided by the association-fields authors and product of the abstract schema types Magazine and Article respectively.
The FROM
clause of a query defines the domain of the query by
declaring identification variables. An identification variable is an identifier
declared in the FROM
clause of a query. The domain of the
query may be constrained by path expressions. Identification variables designate
instances of a particular entity abstract schema type. The FROM
clause can contain multiple identification variable declarations
separated by a comma (,).
from_clause ::= FROM identification_variable_declaration {, {identification_variable_declaration | collection_member_declaration}}*
identification_variable_declaration ::= range_variable_declaration { join | fetch_join }*
range_variable_declaration ::= abstract_schema_name [AS] identification_variable
join ::= join_spec join_association_path_expression [AS] identification_variable
fetch_join ::= join_spec FETCH join_association_path_expression
join_association_path_expression ::= join_collection_valued_path_expression | join_single_valued_association_path_expression
join_spec ::= [ LEFT [OUTER] | INNER ] JOIN
collection_member_declaration ::= IN (collection_valued_path_expression) [AS] identification_variable
An identifier is a character sequence of unlimited length. The character
sequence must begin with a Java identifier start character, and all other
characters must be Java identifier part characters. An identifier start
character is any character for which the method
Character.isJavaIdentifierStart
returns true
.
This includes the underscore (_) character and the dollar sign ($) character. An
identifier part character is any character for which the method
Character.isJavaIdentifierPart
returns true
.
The question mark (?) character is reserved for use by the Java Persistence
query language. The following are reserved identifiers:
SELECT
FROM
WHERE
UPDATE
DELETE
JOIN
OUTER
INNER
LEFT
GROUP
BY
HAVING
FETCH
DISTINCT
OBJECT
NULL
TRUE
FALSE
NOT
AND
OR
BETWEEN
LIKE
IN
AS
UNKNOWN
EMPTY
MEMBER
OF
IS
AVG
MAX
MIN
SUM
COUNT
ORDER
BY
ASC
DESC
MOD
UPPER
LOWER
TRIM
POSITION
CHARACTER_LENGTH
CHAR_LENGTH
BIT_LENGTH
CURRENT_TIME
CURRENT_DATE
CURRENT_TIMESTAMP
NEW
EXISTS
ALL
ANY
SOME
Reserved identifiers are case insensitive. Reserved identifiers must not be used as identification variables. It is recommended that other SQL reserved words also not be as identification variables in queries because they may be used as reserved identifiers in future releases of the specification.
An identification variable is a valid identifier declared in the FROM
clause of a query. All identification variables must be declared in
the FROM
clause. Identification variables cannot be declared
in other clauses. An identification variable must not be a reserved identifier
or have the same name as any entity in the same persistence unit: Identification
variables are case insensitive. An identification variable evaluates to a value
of the type of the expression used in declaring the variable. For example,
consider the previous query:
SELECT DISTINCT mag FROM Magazine mag JOIN mag.articles art JOIN art.author auth WHERE auth.firstName = 'John'
In the FROM
clause declaration
mag.articles
art
, the identification variable
art
evaluates to any Article
value
directly reachable from Magazine
. The association-field
articles
is a collection of instances of the abstract schema
type Article
and the identification variable art
refers to an element of this collection. The type of auth
is the abstract schema type of Author
. An
identification variable ranges over the abstract schema type of an entity. An
identification variable designates an instance of an entity abstract schema type
or an element of a collection of entity abstract schema type instances.
Identification variables are existentially quantified in a query. An
identification variable always designates a reference to a single value. It is
declared in one of three ways: in a range variable declaration, in a join
clause, or in a collection member declaration. The identification variable
declarations are evaluated from left to right in the FROM
clause, and an identification variable declaration can use the result of a
preceding identification variable declaration of the query string.
The syntax for declaring an identification variable as a range variable is similar to that of SQL; optionally, it uses the AS keyword.
range_variable_declaration ::= abstract_schema_name [AS] identification_variable
Range variable declarations allow the developer to designate a "root" for
objects which may not be reachable by navigation. In order to select values by
comparing more than one instance of an entity abstract schema type, more than
one identification variable ranging over the abstract schema type is needed in
the FROM
clause.
The following query returns magazines whose price is greater than the price of
magazines published by "Adventure" publishers. This example illustrates the use
of two different identification variables in the FROM
clause,
both of the abstract schema type Magazine. The SELECT
clause
of this query determines that it is the magazines with prices greater than those
of "Adventure" publisher's that are returned.
SELECT DISTINCT mag1 FROM Magazine mag1, Magazine mag2 WHERE mag1.price > mag2.price AND mag2.publisher.name = 'Adventure'
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:
single_valued_path_expression ::= state_field_path_expression | single_valued_association_path_expression
state_field_path_expression ::= {identification_variable | single_valued_association_path_expression}.state_field
single_valued_association_path_expression ::= identification_variable.{single_valued_association_field.}*single_valued_association_field
collection_valued_path_expression ::= identification_variable.{single_valued_association_field.}*collection_valued_association_field
state_field ::= {embedded_class_state_field.}*simple_state_field
A single_valued_association_field is designated by the name of an association-field in a one-to-one or many-to-one relationship. The type of a single_valued_association_field and thus a single_valued_association_path_expression is the abstract schema type of the related entity. A collection_valued_association_field is designated by the name of an association-field in a one-to-many or a many-to-many relationship. The type of a collection_valued_association_field is a collection of values of the abstract schema type of the related entity. An embedded_class_state _field is designated by the name of an entity state field that corresponds to an embedded class. Navigation to a related entity results in a value of the related entity's abstract schema type.
The evaluation of a path expression terminating in a state-field results in the
abstract schema type corresponding to the Java type designated by the
state-field. It is syntactically illegal to compose a path expression from a
path expression that evaluates to a collection. For example, if mag
designates Magazine
, the path expression
mag.articles.author
is illegal since navigation to authors results in
a collection. This case should produce an error when the query string is
verified. To handle such a navigation, an identification variable must be
declared in the FROM
clause to range over the elements of the
articles
collection. Another path expression must be used to
navigate over each such element in the WHERE
clause of the
query, as in the following query which returns all authors that have any
articles in any magazines:
SELECT DISTINCT art.author FROM Magazine AS mag, IN(mag.articles) art
An inner join may be implicitly specified by the use of a cartesian product in
the FROM
clause and a join condition in the WHERE
clause.
The syntax for explicit join operations is as follows:
join ::= join_spec join_association_path_expression [AS] identification_variable
fetch_join ::= join_spec FETCH join_association_path_expression
join_association_path_expression ::= join_collection_valued_path_expression | join_single_valued_association_path_expression
join_spec ::= [ LEFT [OUTER] | INNER ] JOIN
The following inner and outer join operation types are supported.
The syntax for the inner join operation is
[ INNER ] JOIN join_association_path_expression [AS] identification_variable
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.
SELECT pub FROM Publisher pub JOIN pub.magazines mag WHERE pub.revenue > 1000000
The keyword INNER
may optionally be used:
SELECT pub FROM Publisher pub INNER JOIN pub.magazines mag WHERE pub.revenue > 1000000
This is equivalent to the following query using the earlier
IN
construct. It selects those publishers with revenue of
over 1 million for which at least one magazine exists:
SELECT OBJECT(pub) FROM Publisher pub, IN(pub.magazines) mag WHERE pub.revenue > 1000000
LEFT JOIN
and LEFT OUTER JOIN
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:
LEFT [OUTER] JOIN join_association_path_expression [AS] identification_variable
For example:
SELECT pub FROM Publisher pub LEFT JOIN pub.magazines mag WHERE pub.revenue > 1000000
The keyword OUTER
may optionally be used:
SELECT pub FROM Publisher pub LEFT OUTER JOIN pub.magazines mags WHERE pub.revenue > 1000000
An important use case for LEFT JOIN
is in
enabling the prefetching of related data items as a side effect of a query. This
is accomplished by specifying the LEFT JOIN
as a
FETCH JOIN
.
A FETCH JOIN
enables the fetching of an association as a side
effect of the execution of a query. A FETCH JOIN
is specified
over an entity and its related entities. The syntax for a fetch join is
fetch_join ::= [ LEFT [OUTER] | INNER ] JOIN FETCH join_association_path_expression
The association referenced by the right side of the FETCH JOIN
clause must be an association that belongs to an entity that is
returned as a result of the query. It is not permitted to specify an
identification variable for the entities referenced by the right side of the
FETCH JOIN
clause, and hence references to the implicitly
fetched entities cannot appear elsewhere in the query. The following query
returns a set of magazines. As a side effect, the associated articles for those
magazines are also retrieved, even though they are not part of the explicit
query result. The persistent fields or properties of the articles that are
eagerly fetched are fully initialized. The initialization of the relationship
properties of the articles
that are retrieved is determined
by the metadata for the Article
entity class.
SELECT mag FROM Magazine mag LEFT JOIN FETCH mag.articles WHERE mag.id = 1
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.
An identification variable declared by a collection_member_declaration ranges
over values of a collection obtained by navigation using a path expression. Such
a path expression represents a navigation involving the association-fields of an
entity abstract schema type. Because a path expression can be based on another
path expression, the navigation can use the association-fields of related
entities. An identification variable of a collection member declaration is
declared using a special operator, the reserved identifier IN
. The argument to the IN
operator is a collection-valued path
expression. The path expression evaluates to a collection type specified as a
result of navigation to a collection-valued association-field of an entity
abstract schema type. The syntax for declaring a collection member
identification variable is as follows:
collection_member_declaration ::= IN (collection_valued_path_expression) [AS] identification_variable
For example, the query
SELECT DISTINCT mag FROM Magazine mag JOIN mag.articles art JOIN art.author auth WHERE auth.lastName = 'Grisham'
may equivalently be
expressed as follows, using the IN
operator:
SELECT DISTINCT mag FROM Magazine mag, IN(mag.articles) art WHERE art.author.lastName = 'Grisham'
In this example,
articles
is the name of an association-field whose value is a
collection of instances of the abstract schema type Article
.
The identification variable art
designates a member of this
collection, a single Article
abstract schema type instance.
In this example, mag
is an identification variable of the
abstract schema type Magazine
.
Java Persistence queries are automatically polymorphic. The FROM
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.
The WHERE
clause of a query consists of a conditional
expression used to select objects or values that satisfy the expression. The
WHERE
clause restricts the result of a select statement or
the scope of an update or delete operation. A WHERE
clause is
defined as follows:
where_clause ::= WHERE conditional_expression
The GROUP BY
construct enables the aggregation of values
according to the properties of an entity class. The HAVING
construct enables conditions to be specified that further restrict the query
result as restrictions upon the groups. The syntax of the HAVING
clause is as follows:
having_clause ::= HAVING conditional_expression
The GROUP BY
and HAVING
constructs are
further discussed in Section 2.6, “
JPQL GROUP BY, HAVING
”.
The following sections describe the language constructs that can be used in a
conditional expression of the WHERE
clause or
HAVING
clause. State-fields that are mapped in serialized form or as
lobs may not be portably used in conditional expressions.
The implementation is not expected to perform such query operations involving such fields in memory rather than in the database.
A string literal is enclosed in single quotes--for example: 'literal'. A string
literal that includes a single quote is represented by two single quotes--for
example: 'literal''s'. String literals in queries, like Java String literals,
use unicode character encoding. The use of Java escape notation is not supported
in query string literals Exact numeric literals support the use of Java integer
literal syntax as well as SQL exact numeric literal syntax. Approximate literals
support the use Java floating point literal syntax as well as SQL approximate
numeric literal syntax. Enum literals support the use of Java enum literal
syntax. The enum class name must be specified. Appropriate suffixes may be used
to indicate the specific type of a numeric literal in accordance with the Java
Language Specification. The boolean literals are TRUE
and
FALSE
. Although predefined reserved literals appear in upper
case, they are case insensitive.
All identification variables used in the WHERE
or
HAVING
clause of a SELECT
or DELETE
statement must be declared in the FROM
clause, as
described in Section 2.3.2, “
JPQL Identification Variables
”. The identification
variables used in the WHERE
clause of an UPDATE
statement must be declared in the UPDATE
clause.
Identification variables are existentially quantified in the WHERE
and HAVING
clause. This means that an
identification variable represents a member of a collection or an instance of an
entity's abstract schema type. An identification variable never designates a
collection in its entirety.
It is illegal to use a collection_valued_path_expression within a
WHERE
or HAVING
clause as part of a conditional
expression except in an empty_collection_comparison_expression, in a
collection_member_expression, or as an argument to the SIZE
operator.
Either positional or named parameters may be used. Positional and named
parameters may not be mixed in a single query. Input parameters can only be used
in the WHERE
clause or HAVING
clause of a
query.
Note that if an input parameter value is null, comparison operations or arithmetic operations involving the input parameter will return an unknown value. See Section 2.10, “ JPQL Null Values ”.
The following rules apply to positional parameters.
Input parameters are designated by the question mark (?) prefix followed by an integer. For example: ?1.
Input parameters are numbered starting from 1. Note that the same parameter can be used more than once in the query string and that the ordering of the use of parameters within the query string need not conform to the order of the positional parameters.
A named parameter is an identifier that is prefixed by the ":" symbol. It follows the rules for identifiers defined in Section 2.3.1, “ JPQL FROM Identifiers ”. Named parameters are case sensitive.
Example:
SELECT pub FROM Publisher pub WHERE pub.revenue > :rev
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:
conditional_expression ::= conditional_term | conditional_expression OR conditional_term
conditional_term ::= conditional_factor | conditional_term AND conditional_factor
conditional_factor ::= [ NOT ] conditional_primary
conditional_primary ::= simple_cond_expression | (conditional_expression)
simple_cond_expression ::= comparison_expression | between_expression | like_expression | in_expression | null_comparison_expression | empty_collection_comparison_expression | collection_member_expression | exists_expression
Aggregate functions can only be used in conditional expressions in a
HAVING
clause. See Section 2.6, “
JPQL GROUP BY, HAVING
”.
The operators are listed below in order of decreasing precedence.
Navigation operator (.)
Arithmetic operators: +, - unary *, / multiplication and division +, - addition and subtraction
Comparison operators: =, >, >=, <, <=, <> (not equal), [
NOT
] BETWEEN
, [ NOT
]
LIKE
, [ NOT
] IN
,
IS
[ NOT
] NULL
,
IS
[ NOT
] EMPTY
, [
NOT
] MEMBER
[ OF
]
Logical operators: NOT
AND
OR
The following sections describe other operators used in specific expressions.
The syntax for the use of the comparison operator [ NOT
]
BETWEEN
in a conditional expression is as follows:
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
The BETWEEN expression
x BETWEEN y AND z
is semantically equivalent to:
y <= x AND x <= z
The rules for unknown and NULL
values in
comparison operations apply. See Section 2.10, “
JPQL Null Values
”
. Examples are:
p.age BETWEEN 15 and 19
is equivalent to
p.age >= 15 AND p.age <= 19
p.age NOT BETWEEN 15 and 19
is equivalent to
p.age < 15 OR p.age > 19
The syntax for the use of the comparison operator [ NOT
]
IN
in a conditional expression is as follows:
in_expression ::= state_field_path_expression [NOT] IN ( in_item {, in_item}* | subquery)
in_item ::= literal | input_parameter
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 Section 2.11, “ JPQL Equality and Comparison Semantics ” ).
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 Section 2.5.15, “ JPQL Subqueries ”. Examples are:
o.country IN ('UK', 'US', 'France')
is true for UK and false for Peru, and is equivalent to the expression:
(o.country = 'UK') OR (o.country = 'US') OR (o.country = ' France')
In the following expression:
o.country NOT IN ('UK', 'US', 'France')
is false for UK and true for Peru, and is equivalent to the expression:
NOT ((o.country = 'UK') OR (o.country = 'US') OR (o.country = 'France'))
There must be at least one element in the comma separated list
that defines the set of values for the IN
expression. If the
value of a state_field_path_expression in an IN
or
NOT IN
expression is NULL
or unknown, the value of
the expression is unknown.
The syntax for the use of the comparison operator [ NOT
]
LIKE
in a conditional expression is as follows:
string_expression [NOT] LIKE pattern_value [ESCAPE escape_character]
The string_expression must have a string value. The pattern_value is a string literal or a string-valued input parameter in which an underscore (_) stands for any single character, a percent (%) character stands for any sequence of characters (including the empty sequence), and all other characters stand for themselves. The optional escape_character is a single-character string literal or a character-valued input parameter (i.e., char or Character) and is used to escape the special meaning of the underscore and percent characters in pattern_value. Examples are:
address.phone LIKE '12%3'
is true for '123' '12993' and false for '1234'
asentence.word LIKE 'l_se'
is true for 'lose' and false for 'loose'
aword.underscored LIKE '\_%' ESCAPE '\'
is true for '_foo' and false for 'bar'
address.phone NOT LIKE '12%3'
is false for
'123' and '12993' and true for '1234'. If the value of the string_expression or
pattern_value is NULL
or unknown, the value of the
LIKE
expression is unknown. If the escape_character is specified and
is NULL
, the value of the LIKE
expression
is unknown.
The syntax for the use of the comparison operator IS NULL
in
a conditional expression is as follows:
{single_valued_path_expression | input_parameter } IS [NOT] NULL
A null comparison expression tests whether or not the single-valued path
expression or input parameter is a NULL
value.
The syntax for the use of the comparison operator IS EMPTY
in
an empty_collection_comparison_expression is as follows:
collection_valued_path_expression IS [NOT] EMPTY
This expression tests whether or not the collection designated by the collection-valued path expression is empty (i.e, has no elements).
For example, the following query will return all magazines that don't have any articles at all:
SELECT mag FROM Magazine mag WHERE mag.articles IS EMPTY
If the value of the collection-valued path expression in an empty collection comparison expression is unknown, the value of the empty comparison expression is unknown.
The use of the comparison collection_member_expression is as follows:
collection_member_expression ::= entity_expression [NOT] MEMBER [OF] collection_valued_path_expression
entity_expression ::= single_valued_association_path_expression | simple_entity_expression
simple_entity_expression ::= identification_variable | input_parameter
This expression tests whether the designated value is a member of the collection
specified by the collection-valued path expression. If the collection valued
path expression designates an empty collection, the value of the
MEMBER OF
expression is FALSE
and the value of the
NOT MEMBER OF
expression is TRUE
.
Otherwise, if the value of the collection-valued path expression or
single-valued association-field path expression in the collection member
expression is NULL
or unknown, the value of the collection
member expression is unknown.
The use of the reserved word OF is optional in this expression.
An EXISTS
expression is a predicate that is true only if the
result of the subquery consists of one or more values and that is false
otherwise. The syntax of an exists expression is
exists_expression ::= [NOT] EXISTS (subquery)
Example:
SELECT DISTINCT auth FROM Author auth WHERE EXISTS (SELECT spouseAuthor FROM Author spouseAuthor WHERE spouseAuthor = auth.spouse)
The result of this query consists of all authors whose spouse is also an author.
An ALL
conditional expression is a predicate that is true if
the comparison operation is true for all values in the result of the subquery or
the result of the subquery is empty. An ALL
conditional
expression is false if the result of the comparison is false for at least one
row, and is unknown if neither true nor false. An ANY
conditional expression is a predicate that is true if the comparison operation
is true for some value in the result of the subquery. An ANY
conditional expression is false if the result of the subquery is empty or if the
comparison operation is false for every value in the result of the subquery, and
is unknown if neither true nor false. The keyword SOME
is
synonymous with ANY
. The comparison operators used with
ALL
or ANY
conditional expressions are =,
<, <=, >, >=, <>. The result of the subquery must be like that
of the other argument to the comparison operator in type. See
Section 2.11, “
JPQL Equality and Comparison Semantics
”. The syntax of an ALL
or ANY
expression is specified as follows:
all_or_any_expression ::= { ALL | ANY | SOME} (subquery)
The following example select the authors who make the highest salary for their magazine:
SELECT auth FROM Author auth WHERE auth.salary >= ALL(SELECT a.salary FROM Author a WHERE a.magazine = auth.magazine)
Subqueries may be used in the WHERE
or HAVING
clause. The syntax for subqueries is as follows:
subquery ::= simple_select_clause subquery_from_clause [where_clause] [groupby_clause] [having_clause]
Subqueries are restricted to the WHERE
and HAVING
clauses in this release. Support for subqueries in the FROM
clause will be considered in a later release of the specification.
simple_select_clause ::= SELECT [DISTINCT] simple_select_expression
subquery_from_clause ::= FROM subselect_identification_variable_declaration {, subselect_identification_variable_declaration}*
subselect_identification_variable_declaration ::= identification_variable_declaration | association_path_expression [AS] identification_variable | collection_member_declaration
simple_select_expression ::= single_valued_path_expression | aggregate_expression | identification_variable
Examples:
SELECT DISTINCT auth FROM Author auth WHERE EXISTS (SELECT spouseAuth FROM Author spouseAuth WHERE spouseAuth = auth.spouse)
SELECT mag FROM Magazine mag WHERE (SELECT COUNT(art) FROM mag.articles art) > 10
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.
SELECT goodPublisher FROM Publisher goodPublisher WHERE goodPublisher.revenue < (SELECT AVG(p.revenue) FROM Publisher p)
The JPQL includes the following built-in functions, which may be used in the
WHERE
or HAVING
clause of a query. If the
value of any argument to a functional expression is null or unknown, the value
of the functional expression is unknown.
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)
trim_specification ::= LEADING | TRAILING | BOTH
functions_returning_numerics ::= LENGTH(string_primar y) | LOCATE(string_primar y, string_primar y[, simple_arithmetic_expression])
The CONCAT
function returns a string that is a concatenation
of its arguments. The second and third arguments of the SUBSTRING
function denote the starting position and length of the substring to
be returned. These arguments are integers. The first position of a string is
denoted by 1. The SUBSTRING
function returns a string. The
TRIM
function trims the specified character from a string. If
the character to be trimmed is not specified, it is assumed to be space (or
blank). The optional trim_character is a single-character string literal or a
character-valued input parameter (i.e., char or Character). If a trim
specification is not provided, BOTH
is assumed. The
TRIM
function returns the trimmed string. The LOWER
and UPPER
functions convert a string to lower and upper case,
respectively. They return a string. The LOCATE
function
returns the position of a given string within a string, starting the search at a
specified position. It returns the first position at which the string was found
as an integer. The first argument is the string to be located; the second
argument is the string to be searched; the optional third argument is an integer
that represents the string position at which the search is started (by default,
the beginning of the string to be searched). The first position in a string is
denoted by 1. If the string is not found, 0 is returned. The LENGTH
function returns the length of the string in characters as an
integer.
functions_returning_numerics ::= ABS(simple_arithmetic_expression) | SQRT(simple_arithmetic_expression) | MOD(simple_arithmetic_expression, simple_arithmetic_expression) | SIZE(collection_valued_path_expression)
The ABS
function takes a numeric argument and returns a
number (integer, float, or double) of the same type as the argument to the
function. The SQRT
function takes a numeric argument and
returns a double.
Note that not all databases support the use of a trim character other than the
space character; use of this argument may result in queries that are not
portable. Note that not all databases support the use of the third argument to
LOCATE
; use of this argument may result in queries that are
not portable.
The MOD
function takes two integer arguments and returns an
integer. The SIZE
function returns an integer value, the
number of elements of the collection. If the collection is empty, the
SIZE
function evaluates to zero. Numeric arguments to these functions
may correspond to the numeric Java object types as well as the primitive numeric
types.
The GROUP BY
construct enables the aggregation of values
according to a set of properties. The HAVING
construct
enables conditions to be specified that further restrict the query result. Such
conditions are restrictions upon the groups. The syntax of the GROUP
BY
and HAVING
clauses is as follows:
groupby_clause ::= GROUP BY groupby_item {, groupby_item}*
groupby_item ::= single_valued_path_expression | identification_variable
having_clause ::= HAVING conditional_expression
If a query contains both a WHERE
clause and a GROUP
BY
clause, the effect is that of first applying the where clause, and
then forming the groups and filtering them according to the HAVING
clause. The HAVING
clause causes those groups to
be retained that satisfy the condition of the HAVING
clause.
The requirements for the SELECT
clause when GROUP
BY
is used follow those of SQL: namely, any item that appears in the
SELECT
clause (other than as an argument to an aggregate
function) must also appear in the GROUP BY
clause. In forming
the groups, null values are treated as the same for grouping purposes. Grouping
by an entity is permitted. In this case, the entity must contain no serialized
state fields or lob-valued state fields. The HAVING
clause
must specify search conditions over the grouping items or aggregate functions
that apply to grouping items.
If there is no GROUP BY
clause and the HAVING
clause is used, the result is treated as a single group, and the
select list can only consist of aggregate functions. When a query declares a
HAVING
clause, it must always also declare a GROUP
BY
clause.
The SELECT
clause denotes the query result. More than one
value may be returned from the SELECT
clause of a query. The
SELECT
clause may contain one or more of the following
elements: a single range variable or identification variable that ranges over an
entity abstract schema type, a single-valued path expression, an aggregate
select expression, a constructor expression. The SELECT
clause has the following syntax:
select_clause ::= SELECT [DISTINCT] select_expression {, select_expression}*
select_expression ::= single_valued_path_expression | aggregate_expression | identification_variable | OBJECT(identification_variable) | constructor_expression
constructor_expression ::= NEW constructor_name ( constructor_item {, constructor_item}*)
constructor_item ::= single_valued_path_expression | aggregate_expression
aggregate_expression ::= { AVG | MAX | MIN | SUM } ([DISTINCT] state_field_path_expression) | COUNT ([DISTINCT] identification_variable | state_field_path_expression | single_valued_association_path_expression)
For example:
SELECT pub.id, pub.revenue FROM Publisher pub JOIN pub.magazines mag WHERE mag.price > 5.00
Note that the SELECT
clause must be specified to return only
single-valued expressions. The query below is therefore not valid:
SELECT mag.authors FROM Magazine AS mag
The
DISTINCT
keyword is used to specify that duplicate values
must be eliminated from the query result. If DISTINCT
is not
specified, duplicate values are not eliminated. Standalone identification
variables in the SELECT
clause may optionally be qualified by
the OBJECT
operator. The SELECT
clause
must not use the OBJECT operator to qualify path expressions.
The type of the query result specified by the SELECT
clause
of a query is an entity abstract schema type, a state-field type, the result of
an aggregate function, the result of a construction operation, or some sequence
of these. The result type of the SELECT
clause is defined by
the the result types of the select_expressions contained in it. When multiple
select_expressions are used in the SELECT
clause, the result
of the query is of type Object[], and the elements in this result correspond in
order to the order of their specification in the SELECT
clause and in type to the result types of each of the select_expressions. The
type of the result of a select_expression is as follows:
A single_valued_path_expression that is a state_field_path_expression results in an object of the same type as the corresponding state field of the entity. If the state field of the entity is a primitive type, the corresponding object type is returned.
single_valued_path_expression that is a single_valued_association_path_expression results in an entity object of the type of the relationship field or the subtype of the relationship field of the entity object as determined by the object/relational mapping.
The result type of an identification_variable is the type of the entity to which that identification variable corresponds or a subtype as determined by the object/relational mapping.
The result type of aggregate_expression is defined in section Section 2.7.4, “ JPQL Aggregate Functions ”.
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.
in the SELECT
Clause A constructor may be used in the
SELECT
list to return one or more Java instances. The
specified class is not required to be an entity or to be mapped to the database.
The constructor name must be fully qualified.
If an entity class name is specified in the SELECT NEW
clause, the resulting entity instances are in the new state.
SELECT NEW com.company.PublisherInfo(pub.id, pub.revenue, mag.price) FROM Publisher pub JOIN pub.magazines mag WHERE mag.price > 5.00
If the result of a query corresponds to a association-field or state-field whose
value is null, that null value is returned in the result of the query method.
The IS NOT NULL
construct can be used to eliminate such null
values from the result set of the query. Note, however, that state-field types
defined in terms of Java numeric primitive types cannot produce NULL
values in the query result. A query that returns such a state-field
type as a result type must not return a null value.
in the SELECT
Clause The result of a query may be the result
of an aggregate function applied to a path expression. The following aggregate
functions can be used in the SELECT
clause of a query:
AVG
, COUNT
, MAX
,
MIN
, SUM
. For all aggregate functions
except COUNT
, the path expression that is the argument to
the aggregate function must terminate in a state-field. The path expression
argument to COUNT
may terminate in either a state-field or a
association-field, or the argument to COUNT
may be an
identification variable. Arguments to the functions SUM
and
AVG
must be numeric. Arguments to the functions MAX
and MIN
must correspond to orderable state-field
types (i.e., numeric types, string types, character types, or date types). The
Java type that is contained in the result of a query using an aggregate function
is as follows:
COUNT
returns
Long.
MAX
, MIN
return the type of the
state-field to which they are applied.
AVG
returns Double.
SUM
returns Long when applied to state-fields of integral
types (other than BigInteger); Double when applied to state-fields of floating
point types; BigInteger when applied to state-fields of type BigInteger; and
BigDecimal when applied to state-fields of type BigDecimal. If SUM
, AVG
, MAX
, or MIN
is used, and there are no values to which the aggregate function can
be applied, the result of the aggregate function is NULL
. If
COUNT
is used, and there are no values to which
COUNT
can be applied, the result of the aggregate function is 0.
The argument to an aggregate function may be preceded by the keyword
DISTINCT
to specify that duplicate values are to be eliminated before
the aggregate function is applied.
It is legal to specify DISTINCT
with MAX
or MIN
, but it does not affect the result.
Null values are eliminated before the
aggregate function is applied, regardless of whether the keyword
DISTINCT
is specified.
Examples The following query returns the average price of all magazines:
SELECT AVG(mag.price) FROM Magazine mag
The following query returns the sum of all the prices from all the magazines published by 'Larry':
SELECT SUM(mag.price) FROM Publisher pub JOIN pub.magazines mag pub.firstName = 'Larry'
The following query returns the total number of magazines:
SELECT COUNT(mag) FROM Magazine mag
The ORDER BY
clause allows the objects or values that are
returned by the query to be ordered. The syntax of the ORDER BY
clause is
orderby_clause ::= ORDER BY orderby_item {, orderby_item}*
orderby_item ::= state_field_path_expression [ASC | DESC]
When the ORDER BY
clause is used in a query, each element of
the SELECT
clause of the query must be one of the following:
an identification variable x, optionally denoted as OBJECT(x)
, a single_valued_association_path_expression, or a state_field_path_expression.
For example:
SELECT pub FROM Publisher pub ORDER BY pub.revenue, pub.name
If more than one orderby_item is specified, the left-to-right
sequence of the orderby_item elements determines the precedence, whereby the
leftmost orderby_item has highest precedence. The keyword ASC
specifies that ascending ordering be used; the keyword DESC
specifies that descending ordering be used. Ascending ordering is the default.
SQL rules for the ordering of null values apply: that is, all null values must
appear before all non-null values in the ordering or all null values must appear
after all non-null values in the ordering, but it is not specified which. The
ordering of the query result is preserved in the result of the query method if
the ORDER BY
clause is used.
Operations Bulk update and delete operations apply to entities of a single
entity class (together with its subclasses, if any). Only one entity abstract
schema type may be specified in the FROM
or UPDATE
clause. The syntax of these operations is as follows:
update_statement ::= update_clause [where_clause]
update_clause ::= UPDATE abstract_schema_name [[AS] identification_variable] SET update_item {, update_item}*
update_item ::= [identification_variable.]{state_field | single_valued_association_field} = new_value
new_value ::= simple_arithmetic_expression | string_primary | datetime_primary | boolean_primary | enum_primary simple_entity_expression | NULL
delete_statement ::= delete_clause [where_clause]
delete_clause ::= DELETE FROM abstract_schema_name [[AS] identification_variable]
The syntax of the WHERE
clause is described in
Section 2.4, “
JPQL WHERE Clause
”. A delete operation only applies to
entities of the specified class and its subclasses. It does not cascade to
related entities. The new_value specified for an update operation must be
compatible in type with the state-field to which it is assigned. Bulk update
maps directly to a database update operation, bypassing optimistic locking
checks. Portable applications must manually update the value of the version
column, if desired, and/or manually validate the value of the version column.
The persistence context is not synchronized with the result of the bulk update
or delete. Caution should be used when executing bulk update or delete
operations because they may result in inconsistencies between the database and
the entities in the active persistence context. In general, bulk update and
delete operations should only be performed within a separate transaction or at
the beginning of a transaction (before entities have been accessed whose state
might be affected by such operations).
Examples:
DELETE FROM Publisher pub WHERE pub.revenue > 1000000.0
DELETE FROM Publisher pub WHERE pub.revenue = 0 AND pub.magazines IS EMPTY
UPDATE Publisher pub SET pub.status = 'outstanding' WHERE pub.revenue < 1000000 AND 20 > (SELECT COUNT(mag) FROM pub.magazines mag)
When the target of a reference does not exist in the database, its value is
regarded as NULL
. SQL 92 NULL
semantics
defines the evaluation of conditional expressions containing NULL
values. The following is a brief description of these semantics:
Comparison or arithmetic operations with a
NULL
value always yield an unknown value.
Two NULL
values are not considered to be equal, the
comparison yields an unknown value.
Comparison or arithmetic operations with an unknown value always yield an unknown value.
The IS NULL
and IS NOT NULL
operators
convert a NULL
state-field or single-valued association-field
value into the respective TRUE
or FALSE
value.
Note: The JPQL defines the empty string, "", as a string with 0 length, which is
not equal to a NULL
value. However, NULL
values and empty strings may not always be distinguished when queries are mapped
to some databases. Application developers should therefore not rely on the
semantics of query comparisons involving the empty string and NULL
value.
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.
The following is the BNF for the Java Persistence query language, from section 4.14 of the JSR 220 specification.
QL_statement ::= select_statement | update_statement | delete_statement
select_statement ::= select_clause from_clause [where_clause] [groupby_clause] [having_clause] [orderby_clause]
update_statement ::= update_clause [where_clause]
delete_statement ::= delete_clause [where_clause]
from_clause ::= FROM
identification_variable_declaration {,
{identification_variable_declaration | collection_member_declaration}}*
identification_variable_declaration ::= range_variable_declaration { join | fetch_join }*
range_variable_declaration ::= abstract_schema_name [ AS
]
identification_variable
join ::= join_spec join_association_path_expression [ AS
]
identification_variable
fetch_join ::= join_spec FETCH
join_association_path_expression
association_path_expression ::= collection_valued_path_expression | single_valued_association_path_expression
join_spec ::= [ LEFT
[ OUTER
]|
INNER
] JOIN
join_association_path_expression ::= join_collection_valued_path_expression | join_single_valued_association_path_expression
join_collection_valued_path_expression ::= identification_variable.collection_valued_association_field
join_single_valued_association_path_expression ::= identification_variable.single_valued_association_field
collection_member_declaration ::= IN
(collection_valued_path_expression) [ AS
]
identification_variable
single_valued_path_expression ::= state_field_path_expression | single_valued_association_path_expression
state_field_path_expression ::= {identification_variable | single_valued_association_path_expression}.state_field
single_valued_association_path_expression ::= identification_variable.{single_valued_association_field.}* single_valued_association_field
collection_valued_path_expression ::= identification_variable.{single_valued_association_field.}*collection_valued_association_field
state_field ::= {embedded_class_state_field.}*simple_state_field
update_clause ::= UPDATE
abstract_schema_name [[ AS
] identification_variable] SET
update_item {,
update_item}*
update_item ::= [identification_variable.]{state_field | single_valued_association_field}= new_value
new_value ::= simple_arithmetic_expression | string_primary | datetime_primary |
boolean_primary | enum_primary simple_entity_expression | NULL
delete_clause ::= DELETE
FROM
abstract_schema_name [[ AS
] identification_variable]
select_clause ::= SELECT
[ DISTINCT
]
select_expression {, select_expression}*
select_expression ::= single_valued_path_expression | aggregate_expression |
identification_variable | OBJECT
(identification_variable)|
constructor_expression
constructor_expression ::= NEW
constructor_name(
constructor_item {, constructor_item}*)
constructor_item ::= single_valued_path_expression | aggregate_expression
aggregate_expression ::= { AVG
| MAX
|
MIN
| SUM
}([ DISTINCT
] state_field_path_expression) | COUNT
([ DISTINCT
] identification_variable | state_field_path_expression |
single_valued_association_path_expression)
where_clause ::= WHERE
conditional_expression
groupby_clause ::= GROUP
BY
groupby_item {,
groupby_item}*
groupby_item ::= single_valued_path_expression | identification_variable
having_clause ::= HAVING
conditional_expression
orderby_clause ::= ORDER
BY
orderby_item {,
orderby_item}*
orderby_item ::= state_field_path_expression [ ASC
|
DESC
]
subquery ::= simple_select_clause subquery_from_clause [where_clause] [groupby_clause] [having_clause]
subquery_from_clause ::= FROM
subselect_identification_variable_declaration {,
subselect_identification_variable_declaration}*
subselect_identification_variable_declaration ::=
identification_variable_declaration | association_path_expression [ AS
] identification_variable | collection_member_declaration
simple_select_clause ::= SELECT
[ DISTINCT
] simple_select_expression
simple_select_expression ::= single_valued_path_expression | aggregate_expression | identification_variable
conditional_expression ::= conditional_term | conditional_expression
OR
conditional_term
conditional_term ::= conditional_factor | conditional_term AND
conditional_factor
conditional_factor ::= [ NOT
] conditional_primary
conditional_primary ::= simple_cond_expression |(conditional_expression)
simple_cond_expression ::= comparison_expression | between_expression | like_expression | in_expression | null_comparison_expression | empty_collection_comparison_expression | collection_member_expression | exists_expression
between_expression ::= 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
in_expression ::= state_field_path_expression [ NOT
]
IN
( in_item {, in_item}* | subquery)
in_item ::= literal | input_parameter
like_expression ::= string_expression [ NOT
] LIKE
pattern_value [ ESCAPE
escape_character]
null_comparison_expression ::= {single_valued_path_expression | input_parameter}
IS
[ NOT
] NULL
empty_collection_comparison_expression ::= collection_valued_path_expression
IS
[ NOT
] EMPTY
collection_member_expression ::= entity_expression [ NOT
]
MEMBER
[ OF
]
collection_valued_path_expression
exists_expression ::= [ NOT
] EXISTS
(subquery)
all_or_any_expression ::= { ALL
| ANY
|
SOME
}(subquery)
comparison_expression ::= string_expressioncomparison_operator{string_expression|all_or_any_expression}| boolean_expression {=|<>} {boolean_expression | all_or_any_expression} | enum_expression {=|<>} {enum_expression | all_or_any_expression} | datetime_expression comparison_operator {datetime_expression | all_or_any_expression} | entity_expression {= |<> } {entity_expression | all_or_any_expression} | arithmetic_expression comparison_operator {arithmetic_expression | all_or_any_expression}
comparison_operator ::== |> |>= |< |<= |<>
arithmetic_expression ::= simple_arithmetic_expression |(subquery)
simple_arithmetic_expression ::= arithmetic_term | simple_arithmetic_expression {+ |- } arithmetic_term
arithmetic_term ::= arithmetic_factor | arithmetic_term {* |/ } arithmetic_factor
arithmetic_factor ::= [{+ |-}] arithmetic_primary
arithmetic_primary ::= state_field_path_expression | numeric_literal | (simple_arithmetic_expression) | input_parameter | functions_returning_numerics | aggregate_expression
string_expression ::= string_primary |(subquery)
string_primary ::= state_field_path_expression | string_literal | input_parameter | functions_returning_strings | aggregate_expression
datetime_expression ::= datetime_primary |(subquery)
datetime_primary ::= state_field_path_expression | input_parameter | functions_returning_datetime | aggregate_expression
boolean_expression ::= boolean_primary |(subquery)
boolean_primary ::= state_field_path_expression | boolean_literal | input_parameter |
enum_expression ::= enum_primary |(subquery)
enum_primary ::= state_field_path_expression | enum_literal | input_parameter |
entity_expression ::= single_valued_association_path_expression | simple_entity_expression
simple_entity_expression ::= identification_variable | input_parameter
functions_returning_numerics ::= LENGTH
(string_primary)|
LOCATE
(string_primary,string_primary [,
simple_arithmetic_expression]) | ABS
(simple_arithmetic_expression) | SQRT
(simple_arithmetic_expression) | MOD
(simple_arithmetic_expression, simple_arithmetic_expression) | SIZE
(collection_valued_path_expression)
functions_returning_datetime ::= CURRENT_DATE
|
CURRENT_TIME
| CURRENT_TIMESTAMP
functions_returning_strings ::= CONCAT
(string_primary,
string_primary) | SUBSTRING
(string_primary,
simple_arithmetic_expression,simple_arithmetic_expression)| TRIM
([[trim_specification] [trim_character] FROM
]
string_primary) | LOWER
(string_primary) | UPPER
(string_primary)
trim_specification ::= LEADING
| TRAILING
| BOTH