Use the WHERE clause to restrict query results using conditional expressions, including the use of literals, path expressions, named and positional parameters, logical operators, the following expressions (and their NOT options): BETWEEN, IN, LIKE, NULL, EMPTY, MEMBER [OF], EXISTS, ALL, ANY, SOME, and functional expressions.

Use the WHERE clause to restrict query results using conditional expressions, including the use of literals, path expressions, named and positional parameters, logical operators, the following expressions (and their NOT options): BETWEEN, IN, LIKE, NULL, EMPTY, MEMBER [OF], EXISTS, ALL, ANY, SOME, and functional expressions.
Prev	Chapter 8. Java Persistence Query Language	Next


	[EJB_3.0_PERSISTENCE] 4.5; 4.6.1; 4.6.3; 4.6.4; 4.6.4.1; 4.6.4.2; 4.6.6; 4.6.7; 4.6.8; 4.6.9; 4.6.10; 4.6.11; 4.6.12; 4.6.13; 4.6.14; 4.6.16; 4.6.16.1; 4.6.16.2; 4.6.16.3

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.

Examples:

SELECT c.status, avg(c.filledOrderCount), count(c)
FROM Customer c
GROUP BY c.status
HAVING c.status IN (1, 2)

SELECT c.country, COUNT(c)
FROM Customer c
GROUP BY c.country
HAVING COUNT(c.country) > 3

Literals

A string literal is enclosed in single quotes—for example: 'literal'. A string literal that includes a single quote is represented by two single quotes—for example: 'literal''s'. String literals in queries, like Java String literals, use unicode character encoding. The use of Java escape notation is NOT supported in query string literals.

Exact numeric literals support the use of Java integer literal syntax as well as SQL exact numeric literal syntax.

Approximate literals support the use Java floating point literal syntax as well as SQL approximate numeric literal syntax.

Enum literals support the use of Java enum literal syntax. The enum class name must be specified.

Appropriate suffixes may be used to indicate the specific type of a numeric literal in accordance with the Java Language Specification. Support for the use of hexadecimal and octal numeric literals is not required by this specification.

The boolean literals are TRUE and FALSE.

Although predefined reserved literals appear in upper case, they are case INSENSITIVE.

Path Expressions

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

Input Parameters

Either positional or named parameters may be used. Positional and named parameters MAY NOT be mixed in a single query.

Input parameters can ONLY be used in the WHERE clause or HAVING clause of a query.

Note that if an input parameter value is null, comparison operations or arithmetic operations involving the input parameter will return an unknown value.

Positional Parameters
The following rules apply to positional parameters:
- Input parameters are designated by the question mark (?) prefix followed by an integer. For example: ?1.
- Input parameters are numbered starting from 1.
  Note that the same parameter can be used MORE THAN ONCE in the query string and that the ordering of the use of parameters within the query string need not conform to the order of the positional parameters.
  The following query finds the orders for a product whose name is designated by an input parameter:
```
SELECT DISTINCT o
FROM Order o, IN(o.lineItems) l
WHERE l.product.name = ?1
											
```
  For this query, the input parameter must be of the type of the state-field name, i.e., a string.
The following Query interface method used to define positional parameters:
```
public Query setParameter (int pos, Object value);
								
```
Specify positional parameters in your JPQL string using an integer prefixed by a question mark. You can then populate the Query object with positional parameter values via calls to the setParameter method above. The method returns the Query instance for optional method chaining.
The following code will substitute JDJ for the ?1 parameter and 5.0 for the ?2 parameter, then execute the query with those values:
```
EntityManager em = ...
Query q = em.createQuery("SELECT x FROM Magazine x WHERE x.title = ?1 and x.price > ?2");
q.setParameter(1, "JDJ").setParameter(2, 5.0);
List<Magazine> results = (List<Magazine>) q.getResultList();
								
```
Named Parameters
A named parameter is an identifier that is prefixed by the ":" symbol. It follows the rules for identifiers (must not be a reserved word, may include underscore (_) character and the dollar sign ($) character, may NOT include question mark (?). Named parameters are case SENSITIVE.
Example:
```
SELECT c
FROM Customer c
WHERE c.status = :stat
								
```
The following Query interface method used to define named parameters:
```
public Query setParameter(String name, Object value);
								
```
Named parameters are denoted by prefixing an arbitrary name with a colon in your JPQL string. You can then populate the Query object with parameter values using the method above. Like the positional parameter method, this method returns the Query instance for optional method chaining.
This code substitutes JDJ for the :titleParam parameter and 5.0 for the :priceParam parameter, then executes the query with those values:
```
EntityManager em = ...
Query q = em.createQuery("SELECT x FROM Magazine x WHERE x.title = :titleParam and x.price > :priceParam");
q.setParameter("titleParam", "JDJ").setParameter("priceParam", 5.0);
List<Magazine> results = (List<Magazine>) q.getResultList();
								
```

Logical operators

NOT
AND
OR

Between Expressions

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.

Example 1:

p.age BETWEEN 15 and 19

is equivalent to:

p.age >= 15 AND p.age <= 19

Example 2:

p.age NOT BETWEEN 15 and 19

is equivalent to:

p.age < 15 OR p.age > 19

In Expressions

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.

Example:

o.country IN ('UK', 'US', 'Belarus')

is true for Belarus and false for Russia, and is equivalent to the expression:

(o.country = 'UK') OR (o.country = 'US') OR (o.country = 'Belarus')

Example 2:

o.country NOT IN ('UK', 'US', 'Belarus')

is false for UK and true for Germany, and is equivalent to the expression:

NOT ((o.country = 'UK') OR (o.country = 'US') OR (o.country = 'Belarus'))

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.

Like Expressions

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

string_expression [NOT] LIKE pattern_value [ESCAPE escape_character]

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

Examples:

address.phone LIKE '12%3' is true for '123' and '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'

Null Comparison Expressions

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

{single_valued_path_expression | input_parameter} IS [NOT] NULL

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

Empty Collection Comparison Expressions

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

collection_valued_path_expression IS [NOT] EMPTY

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

Example:

SELECT o
FROM Order o
WHERE o.lineItems IS EMPTY

Collection Member Expressions

The syntax for the use of the comparison operator MEMBER OF (the use of the reserved word OF is optional in this expression) in an collection_member_expression is as follows:

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.

Exists Expressions

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

The syntax of an exists expression is:

exists_expression ::= [NOT] EXISTS (subquery)

Example:

SELECT DISTINCT emp
FROM Employee emp
WHERE EXISTS (
	SELECT spouseEmp
	FROM Employee spouseEmp
	WHERE spouseEmp = emp.spouse
)

The result of this query consists of all employees whose spouses are also employees.

All or Any Expressions

An ALL conditional expression is a predicate that is true if the comparison operation is true for all values in the result of the subquery or the result of the subquery is empty. An ALL conditional expression is false if the result of the comparison is false for AT LEAST ONE row, and is unknown if neither true nor false.

An ANY conditional expression is a predicate that is true if the comparison operation is true for some value in the result of the subquery. An ANY conditional expression is false if the result of the subquery is empty or if the comparison operation is false for EVERY VALUE in the result of the subquery, and is unknown if neither true nor false. The keyword SOME is synonymous with ANY.

The syntax of an ALL or ANY expression is specified as follows:

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

Example:

SELECT emp
FROM Employee emp
WHERE emp.salary > ALL (
	SELECT m.salary
	FROM Manager m
	WHERE m.department = emp.department
)

String Functions

CONCAT(string1, string2)
The CONCAT function returns a string that is a concatenation of its arguments.
```
SELECT x FROM Magazine x WHERE CONCAT(x.title, 's') = 'JDJs'
								
```
SUBSTRING(string, startIndex, length)
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.
```
SELECT x FROM Magazine x WHERE SUBSTRING(x.title, 1, 1) = 'J'
								
```
TRIM([LEADING | TRAILING | BOTH] [character FROM] 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 (LEADING | TRAILING | BOTH) is not provided, BOTH is assumed. The TRIM function returns the trimmed string.
```
SELECT x FROM Magazine x WHERE TRIM(BOTH 'J' FROM x.title) = 'D'
								
```

LOWER(string), UPPER(string)

The LOWER and UPPER functions convert a string to lower and upper case, respectively. They return a string.

SELECT x FROM Magazine x WHERE LOWER(x.title) = 'jdj'

SELECT x FROM Magazine x WHERE UPPER(x.title) = 'JAVAPRO'

LOCATE(searchString, candidateString [, startIndex])
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.
```
SELECT x FROM Magazine x WHERE LOCATE('D', x.title) = 2
								
```
LENGTH(string)
The LENGTH function returns the length of the string in characters as an integer.
```
SELECT x FROM Magazine x WHERE LENGTH(x.title) = 3
								
```

Arithmetic Functions

ABS(number)
Returns the absolute value of the argument.
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.
```
SELECT x FROM Magazine x WHERE ABS(x.price) >= 5.00
								
```
SQRT(number)
Returns the square root of the argument.
The SQRT function takes a numeric argument and returns a double.
```
SELECT x FROM Magazine x WHERE SQRT(x.price) >= 1.00
								
```
MOD(number, divisor)
Returns the modulo of number and divisor.
The MOD function takes two integer arguments and returns an integer.
```
SELECT x FROM Magazine x WHERE MOD(x.price, 10) = 0
								
```
SIZE
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.

Datetime Functions

CURRENT_DATE
The CURRENT_DATE function returns the value of current date on the database server.
CURRENT_TIME
The CURRENT_TIME function returns the value of current time on the database server.
CURRENT_TIMESTAMP
The CURRENT_TIMESTAMP function returns the value of current timestamp on the database server.

Prev	Up	Next
Develop queries that use Java Persistence Query Language syntax for defining the domain of a query using JOIN clauses, IN, and prefetching.	Home	Develop Java Persistence Query Language statements that update a set of entities using UPDATE/SET and DELETE FROM.