Section C.4. WHERE Clause

C.4. WHERE Clause

The WHERE clause of an EJB QL query is used to specify conditional logic that is used to select a subset of the beans references in the FROM clause. Any beans that you want to refer to in your conditional query logic must be declared in the FROM clause of the query.

The syntax of the WHERE clause mirrors the syntax of SQL WHERE clauses. The WHERE clause is made up of literals and variables composed into various conditional expressions. The variables used in a WHERE clause can be query variables declared in the FROM clause, path expressions derived from the query variables, or input parameters provided in the arguments to the corresponding finder or select method. The conditional expressions in the WHERE clause can be composed using arithmetic operators, comparison operators, or logical operators.

C.4.1. Literals

Literals are strings, numeric values, or Boolean values. String literals are enclosed in single quotes:

 SELECT OBJECT(p) FROM ProfileBean p WHERE p.name = 'Fred' 

If you want to include a single quote in a literal, use two single quotes:

 SELECT OBJECT(p) FROM ProfileBean p WHERE p.name = 'Fred''s profile' 

Numeric literals can be either exact integer values or approximate floating-point values. Integer values are specified using a sequence of digits with no decimal point:

 SELECT OBJECT(prof) FROM ProfileBean prof WHERE prof.id < 1000 

Floating-point values are specified using either scientific notation or a numeric value with a decimal point:

 SELECT OBJECT(p) FROM Person p WHERE p.salary >     +5e4 AND p.overhead < 1.32 

Floating-point values can be in the range supported by the Java double type.

Boolean literals are specified using true or false. These values are case-insensitive.

C.4.2. Variables

Variables used in expressions in a WHERE clause can be query variables declared in the FROM clause, input parameter variables that refer to arguments to the finder or select method, or path expressions based on query variables or input parameter variables.

C.4.2.1. Simple query variables

Query variables can be used directly in conditional expressions when it is necessary to directly compare abstract schema types. This makes sense only in comparison expressions in which the equality of two entity beans is being compared. For example:

 SELECT OBJECT(p) FROM Person p1, Person p2, Organization o1, Organization     o2 WHERE o1 = p1.employer AND o2 = p2.employer AND o1 = o2 AND ... 

In this case we're comparing the two Organization beans for equality. Equality between entity beans is based on the equality of their primary keys, (e.g., if the two primary keys are equal, then the two entities are considered equal).

C.4.2.2. Input parameter variables

Input variables refer to the arguments of the query's finder or select method. They are specified using a $ followed by the integer index (starting with 1) of the method argument. For example, if we are defining a query for a finder method declared with the following signature:

 abstract public Collection findEmployees(String companyName); 

We can refer to the string argument in the query using an input parameter variable:

 SELECT OBJECT(e) FROM Person e, Organization o     WHERE o.name = $1 AND e.employer = o 

If the input variable is an entity bean object with an abstract schema type, you can also use the input parameter variable in path expressions. For example, if we have a finder method on our Person bean:

 abstract public Collection findCoWorkers(Person p); 

a corresponding query might be:

 SELECT OBJECT(w) FROM Person w, Organization o     WHERE $1.employer = o AND w.employer = o 

Input parameter variables can be used only in comparison expressions that use the basic comparison operators and the MEMBER OF comparison operator, as described in "Comparison operators" later in this appendix.

C.4.2.3. Path expression variables

Path expressions are specified using either query variables or input parameter variables. A path expression is a variable followed by one or more dot-delimited CMP field or CMR field references. In one of our earlier examples, we used a path expression to refer to the name CMP field on the Organization bean:

 SELECT OBJECT(e) FROM Person e, Organization o      WHERE o.name = $1 AND e.employer = o 

Here, the path expression o.name uses the query variable Organization o declared in the FROM clause and then refers to the CMP field name on the variable.

References to cmr-fields on variables can be referenced further in multilevel path expressions. For example, a simplified version of the previous query is:

 SELECT OBJECT(e) FROM Person e WHERE p.employer.name = $1 

Here, we're referencing the CMR field employer on the Person bean, then further referencing the CMP field name on the resulting Organization. Referencing CMR fields in path expressions is possible only if the relationship is navigable from the bean at the root of the reference. So in this case, we're assuming that the employer CMR field is navigable from the Person bean, which allows us to reference it as an Organization and then further reference the name CMP field on the Organization.

Path expressions can evaluate to either single values or collection values. If, for example, a CMR field is one-to-many or many-to-many, a reference to it in a path expression is collection-valued. Path expressions that are collection-valued can be used only in comparison expressions using the IS EMPTY operator or the MEMBER OF operator (described in "Comparison operators" later in this appendix).

C.4.3. Functions

EJB QL also includes several built-in functions that can be applied to literals, variables, or values derived from other expressions. They are listed in Table C-1.

Table C-1. EJB QL built-in functions

Function	Description
CONCAT(<string1>,<string2>)	Concatenates the two strings and returns a string value.
SUBSTRING(<string>,<start>, <length>)	Extracts the specified substring from the first argument, and returns a string value or null if the specified substring can't be extracted (e.g., the start position is greater than the length of the string). The <start> argument is indexed from 1.
LOCATE(<string1>,<string2> [,<start>])	Finds the index of the location of <string1> in <string2>, optionally starting at the specified location in <string2>. The return value is an integer index, indexed from 1. If <string1> is not found in <string2>, a value of 0 is returned.
LENGTH(<string val>)	Returns the number of characters in the string value as an integer value.
ABS(<numeric val>)	Returns the absolute value of the given numeric value. The return type is the same as the type of the numeric expression used as the argument.
SQRT(<floating-point val>)	Returns the square root of the given floating-point value as a double.

C.4.4. Conditional Expressions

Conditional expressions are composed of a sequence of one or more other expressions, joined together by logical AND or OR operators. A single expression is made up of literals and/or variables joined by operators to result in a Boolean value. Conditional expressions follow the usual evaluation/precedence rules: they are evaluated left to right, taking into account operator precedence rules (as discussed next) and grouping of expressions using parentheses.

The operators available in EJB QL for creating conditional expressions fall into three categories: logical operators , arithmetic operators , and comparison operators .

C.4.4.1. Logical operators

The logical operators in EJB QL are NOT, AND, and OR, in precedence order, with the usual semantics: NOT is the unary negation operator, AND is the binary intersection operator, and OR is the binary union operator. These can be applied to any expressions that evaluate to a Boolean value.

C.4.4.2. Arithmetic operators

Arithmetic operators are applied to numeric variables and literals. They are listed in Table C-2.

Table C-2. Arithmetic operators

Operator	Description
+<numeric expression>	Unary positive operator. Specifies a positive literal value or a variable with an unchanged sign.
-<numeric expression>	Unary negation operator. Specifies a negative literal or a variable whose sign is negated.
<numeric exp> * <numeric exp>	Binary multiplication.
<numeric exp> / <numeric exp>	Binary division.
<numeric exp> + <numeric exp>	Binary addition.
<numeric exp> - <numeric exp>	Binary subtraction.

C.4.4.3. Comparison operators

The comparison operators are used to perform various value and range comparisons on variables and are listed in Table C-3. Some comparison operators can be applied only to variables of a certain typethese restrictions are mentioned in the description where applicable.

Table C-3. Comparison operators

Operators	Description	Examples
<exp> = <exp> <exp> <> <exp>	Equality and inequality operators, respectively. The two operands to these operators must be of compatible types: two Strings, two entity beans with the same abstract schema type, or two compatible numeric values.	...FROM Person p WHERE p.age = 65 ...FROM Employee p WHERE p.dept <> 'Finance' ...FROM Employee p WHERE p.title = 'Director' ...FROM Person p1, Person p2 WHERE p1 <> p2 AND... ...FROM Organization a, Person p, IN(p.affiliations) b WHERE a = b...
<exp> > <exp> <exp> >= <exp> <exp> < <exp> <exp> <= <exp>	Greater-than, greater-than-or-equal, less-than, less-than-or-equal operators, respectively. These operators can be applied only to numeric and String values, not to entity beans.	...FROM Item b WHERE b.size > 4 ...FROM Item a, Item b WHERE b.name > a.name ...FROM Item b WHERE b.size <= 4 ...FROM Item a, Item b WHERE b.name >= a.name
<arith exp1> [NOT] BETWEEN <arith-exp2> AND <arith-exp3>	Checks whether a given numeric value is between two other numeric values. The NOT operator checks that the first numeric value is not within the range. If any arithmetic expression used in a BETWEEN operator is null, the entire expression evaluates to NULL (boolean "unknown").	...FROM Person p WHERE p.age BETWEEN 18 AND 65 ...FROM Person p, Rate r WHERE p.salary NOT BETWEEN r.base AND 1.05 * r.base
<string-exp> [NOT] IN (str-literal [,str-literal]*)	Checks whether a given string value is contained (or not contained) in a list of string literals. The string values are matched exactly. If the string value to compare to the set is null, the expression evaluates to NULL (boolean "unknown").	...WHERE p.name IN ('John','Mary') ...WHERE p.dept NOT IN ('Finance', 'Human Resource', 'Corporate')
<string-exp> [NOT] LIKE <pattern>[ESCAPE <esc-char>]	Compares a string value to a pattern. The pattern consists of a sequence of characters, where _ is a wildcard that stands for any single character, and % stands for any sequence of characters. If you want to use the literal _ and % characters in the pattern, escape them with a character and specify the escape character with the optional ESCAPE clause. If the string to compare is null, the value of the LIKE expression is NULL (boolean "unknown").	...WHERE o.title LIKE '%Professor%' ...WHERE p.state_prov NOT LIKE '%Province' ...WHERE p.version LIKE 'v\_1._' ESCAPE '\'
<exp> IS [NOT] NULL	Checks if the value of the expression is null. The expression must be single-valued and not evaluate to a collection.	...FROM Person o WHERE o.employer IS NOT NULL ...FROM Publication p WHERE p.title IS NULL
<collection-exp> IS [NOT] EMPTY	Checks whether a given collection-valued expression is empty. The collection expression must evaluate to a collection of entity beans.	...FROM Person p WHERE p.profiles IS NOT EMPTY ...FROM Order o WHERE o.items IS EMPTY
<exp> [NOT] MEMBER OF <collection-exp>	Checks whether a given single value is contained within a given collection. The value being checked for membership must be a singular value (not a collection), and the collection expression must evaluate to a collection of entity beans.	...FROM Person p, Organization o WHERE p MEMBER OF o.employees ...FROM Item i, Warehouse w WHERE i NOT MEMBER OF w.backorders