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.

[Note]
  • [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.

  1. 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();
    								

  2. 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

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:

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

Arithmetic Functions

Datetime Functions

Professional hosting     Belorussian informational portal         Free SCWCD 1.4 Study Guide     Free SCDJWS 1.4 Study Guide     SCDJWS 1.4 Quiz     Free IBM Certified Associate Developer Study Guide     IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide     Free Mock Exam Engine