WHERE Clause (The Java EE 5 Tutorial)

The Java EE 5 Tutorial

`WHERE` Clause

The WHERE clause specifies a conditional expression that limits the values returned by the query. The query returns all corresponding values in the data store for which the conditional expression is TRUE. Although usually specified, the WHERE clause is optional. If the WHERE clause is omitted, then the query returns all values. The high-level syntax for the WHERE clause follows:

where_clause ::= WHERE conditional_expression

Literals

There are four kinds of literals: string, numeric, Boolean, and enum.

String Literals

A string literal is enclosed in single quotes:

’Duke’

If a string literal contains a single quote, you indicate the quote by using two single quotes:

’Duke’’s’

Like a Java String, a string literal in the query language uses the Unicode character encoding.

Numeric Literals

There are two types of numeric literals: exact and approximate.

An exact numeric literal is a numeric value without a decimal point, such as 65,– 233, and +12. Using the Java integer syntax, exact numeric literals support numbers in the range of a Java long.

An approximate numeric literal is a numeric value in scientific notation, such as 57.,– 85.7, and +2.1. Using the syntax of the Java floating-point literal, approximate numeric literals support numbers in the range of a Java double.

Boolean Literals

A Boolean literal is either TRUE or FALSE. These keywords are not case-sensitive.

Enum Literals

The Java Persistence Query Language supports the use of enum literals using the Java enum literal syntax. The enum class name must be specified as fully qualified class name.

SELECT e
 FROM Employee e
 WHERE e.status = com.xyz.EmployeeStatus.FULL_TIME

Input Parameters

An input parameter can be either a named parameter or a positional parameter.

A named input parameter is designated by a colon (:) followed by a string. For example, :name.

A positional input parameter is designated by a question mark (?) followed by an integer. For example, the first input parameter is ?1, the second is ?2, and so forth.

The following rules apply to input parameters:

They can be used only in a WHERE or HAVING clause.
Positional parameters must be numbered, starting with the integer 1.
Named parameters and positional parameters may not be mixed in a single query.
Named parameters are case-sensitive.

Conditional Expressions

A WHERE clause consists of a conditional expression, which is evaluated from left to right within a precedence level. You can change the order of evaluation by using parentheses.

Operators and Their Precedence

Table 27–2 lists the query language operators in order of decreasing precedence.

Table 27–2 Query Language Order Precedence


Type	Precedence Order
Navigation	`.` (a period)
Arithmetic	`+ –` (unary) `* /` (multiplication and division) `+ –` (addition and subtraction)
Comparison	`=` `>` `>=` `<` `<=` `<>` (not equal) `[NOT] BETWEEN` `[NOT] LIKE` `[NOT] IN` `IS [NOT] NULL` `IS [NOT] EMPTY` `[NOT] MEMBER OF`
Logical	`NOT` `AND` `OR`

`BETWEEN` Expressions

A BETWEEN expression determines whether an arithmetic expression falls within a range of values.

These two expressions are equivalent:

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

The following two expressions are also equivalent:

p.age NOT BETWEEN 15 AND 19
 p.age < 15 OR p.age > 19

If an arithmetic expression has a NULL value, then the value of the BETWEEN expression is unknown.

`IN` Expressions

An IN expression determines whether or not a string belongs to a set of string literals, or whether a number belongs to a set of number values.

The path expression must have a string or numeric value. If the path expression has a NULL value, then the value of the IN expression is unknown.

In the following example, if the country is UK the expression is TRUE. If the country is Peru it is FALSE.

o.country IN (’UK’, ’US’, ’France’)

You may also use input parameters:

o.country IN (’UK’, ’US’, ’France’, :country)

`LIKE` Expressions

A LIKE expression determines whether a wildcard pattern matches a string.

The path expression must have a string or numeric value. If this value is NULL, then the value of the LIKE expression is unknown. The pattern value is a string literal that can contain wildcard characters. The underscore (_) wildcard character represents any single character. The percent (%) wildcard character represents zero or more characters. The ESCAPE clause specifies an escape character for the wildcard characters in the pattern value. Table 27–3 shows some sample LIKE expressions.

Table 27–3 LIKE Expression Examples


Expression	TRUE	FALSE
`address.phone LIKE ’12%3’`	`’123’` `’12993’`	`’1234’`
`asentence.word LIKE ’l_se’`	`’lose’`	`’loose’`
`aword.underscored LIKE ’\_%’ ESCAPE ’\’`	`’_foo’`	`’bar’`
`address.phone NOT LIKE ’12%3’`	`’1234’`	`’123’` `’12993’`

`NULL` Comparison Expressions

A NULL comparison expression tests whether a single-valued path expression or an input parameter has a NULL value. Usually, the NULL comparison expression is used to test whether or not a single-valued relationship has been set.

SELECT t
 FROM Team t
 WHERE t.league IS NULL

This query selects all teams where the league relationship is not set. Please note, the following query is not equivalent:

SELECT t
 FROM Team t
 WHERE t.league = NULL

The comparison with NULL using the equals operator (=) always returns an unknown value, even if the relationship is not set. The second query will always return an empty result.

Empty Collection Comparison Expressions

The IS [NOT] EMPTY comparison expression tests whether a collection-valued path expression has no elements. In other words, it tests whether or not a collection-valued relationship has been set.

If the collection-valued path expression is NULL, then the empty collection comparison expression has a NULL value.

Here is an example that finds all orders that do not have any line items:

SELECT o
FROM Order o
WHERE o.lineItems IS EMPTY

Collection Member Expressions

The [NOT] MEMBER [OF] collection member expression determines whether a value is a member of a collection. The value and the collection members must have the same type.

If either the collection-valued or single-valued path expression is unknown, then the collection member expression is unknown. If the collection-valued path expression designates an empty collection, then the collection member expression is FALSE.

The OF keyword is optional.

The following example tests whether a line item is part of an order:

SELECT o
 FROM Order o
 WHERE :lineItem MEMBER OF o.lineItems

Subqueries

Subqueries may be used in the WHERE or HAVING clause of a query. Subqueries must be surrounded by parentheses.

The following example find all customers who have placed more than 10 orders:

SELECT c
FROM Customer c
WHERE (SELECT COUNT(o) FROM c.orders o) > 10

`EXISTS` Expressions

The [NOT] EXISTS expression is used with a subquery, and is true only if the result of the subquery consists of one or more values and is false otherwise.

The following example finds all employees whose spouse is also an employee:

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

`ALL` and `ANY` Expressions

The ALL expression is used with a subquery, and is true if all the values returned by the subquery are true, or if the subquery is empty.

The ANY expression is used with a subquery, and is true if some of the values returned by the subquery are true. An ANY expression is false if the subquery result is empty, or if all the values returned are false. The SOME keyword is synonymous with ANY.

The ALL and ANY expressions are used with the =, <, <=, >, >=, <> comparison operators.

The following example finds all employees whose salary is higher than the salary of the managers in the employee’s department:

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

Functional Expressions

The query language includes several string and arithmetic functions which may be used in the WHERE or HAVING clause of a query. The functions are listed in the following tables. In Table 27–4, the start and length arguments are of type int. They designate positions in the String argument. The first position in a string is designated by 1. In Table 27–5, the number argument can be either an int, a float, or a double.

Table 27–4 String Expressions


Function Syntax	Return Type
`CONCAT(String, String)`	`String`
`LENGTH(String)`	`int`
`LOCATE(String, String [, start])`	`int`
`SUBSTRING(String, start, length)`	`String`
`TRIM([[LEADING\|TRAILING\|BOTH] char) FROM] (String)`	`String`
`LOWER(String)`	`String`
`UPPER(String)`	`String`

The CONCAT function concatenates two strings into one string.

The LENGTH function returns the length of a string in characters as an integer.

The LOCATE function returns the position of a given string within a string. 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 starting string position. By default, LOCATE starts at the beginning of the string. The starting position of a string is 1. If the string cannot be located, LOCATE returns 0.

The SUBSTRING function returns a string that is a substring of the first argument based on the starting position and length.

The TRIM function trims the specified character from the beginning and/or end of a string. If no character is specified, TRIM removes spaces or blanks from the string. If the optional LEADING specification is used, TRIM removes only the leading characters from the string. If the optional TRAILING specification is used, TRIM removes only the trailing characters from the string. The default is BOTH, which removes the leading and trailing characters from the string.

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

Table 27–5 Arithmetic Expressions


Function Syntax	Return Type
`ABS(number)`	`int`, `float`, or `double`
`MOD(int, int)`	`int`
`SQRT(double)`	`double`
`SIZE(Collection)`	`int`

The ABS function takes a numeric expression and returns a number of the same type as the argument.

The MOD function returns the remainder of the first argument divided by the second.

The SQRT function returns the square root of a number.

The SIZE function returns an integer of the number of elements in the given collection.

NULL Values

If the target of a reference is not in the persistent store, then the target is NULL. For conditional expressions containing NULL, the query language uses the semantics defined by SQL92. Briefly, these semantics are as follows:

If a comparison or arithmetic operation has an unknown value, it yields a NULL value.
Two NULL values are not equal. Comparing two NULL values yields an unknown value.
The IS NULL test converts a NULL persistent field or a single-valued relationship field to TRUE. The IS NOT NULL test converts them to FALSE.
Boolean operators and conditional tests use the three-valued logic defined by Table 27–6 and Table 27–7. (In these tables, T stands for TRUE, F for FALSE, and U for unknown.)

Table 27–6 AND Operator Logic


AND	T	F	U
T	T	F	U
F	F	F	F
U	U	F	U

Table 27–7 OR Operator Logic


OR	T	F	U
T	T	T	T
F	T	F	U
U	T	U	U

Equality Semantics

In the query language, only values of the same type can be compared. However, this rule has one exception: Exact and approximate numeric values can be compared. In such a comparison, the required type conversion adheres to the rules of Java numeric promotion.

The query language treats compared values as if they were Java types and not as if they represented types in the underlying data store. For example, if a persistent field could be either an integer or a NULL, then it must be designated as an Integer object and not as an int primitive. This designation is required because a Java object can be NULL but a primitive cannot.

Two strings are equal only if they contain the same sequence of characters. Trailing blanks are significant; for example, the strings ’abc’ and ’abc ’ are not equal.

Two entities of the same abstract schema type are equal only if their primary keys have the same value. Table 27–8 shows the operator logic of a negation, and Table 27–9 shows the truth values of conditional tests.

Table 27–8 NOT Operator Logic


NOT Value	Value
T	F
F	T
U	U

Table 27–9 Conditional Test


Conditional Test	T	F	U
Expression `IS TRUE`	T	F	F
Expression `IS FALSE`	F	T	F
Expression is unknown	F	F	T

WHERE Clause