Defining a WHERE Clause

Defining a WHERE Clause

As with SQL, an EJB QL WHERE clause defines a conditional expression that is used to filter the results of a query. A simple example of a WHERE clause is

 SELECT OBJECT(auction) FROM EnglishAuction auction,    IN(auction.bids) aBid WHERE aBid.amountField > 500.0 

This modified query now limits the auctions it returns to those that have received a bid for an amount greater than $500. Identification variables , such as aBid in this case, can be referenced in a WHERE clause but they cannot be declared there.

A WHERE clause supports a number of literal types, such as the floating-point value 500.0 used in this example. You can use string, integer, floating-point, and Boolean literals in a WHERE clause. String literals must be enclosed in single quotes (you can use a double quote to represent a single quote within a literal). An integer literal can be any value legal for the Java long primitive. A floating-point literal can be any legal double value and can be expressed in either standard or scientific notation (for example, 123.45 or 1.2345e2). The Boolean literals are TRUE and FALSE (case doesn't matter).

Input Parameters

Many finder and select methods accept parameters that define the criteria for their results. A finder or select method parameter can be referenced in the WHERE clause of an EJB QL query using a ? followed by an integer number. For example, the bid threshold in the example could be replaced by an input parameter using

 SELECT OBJECT(auction) FROM EnglishAuction auction,    IN(auction.bids) aBid WHERE aBid.amountField > ?1 

The number that follows the ? indicates the parameter number. Unlike typical Java indexing, parameter numbers in EJB QL start with 1. A query doesn't have to use all the input parameters that are available to it, but it's definitely an error to reference a parameter number that's higher than the number of parameters declared by the finder or select method.

The type of a parameter in a query is determined by the corresponding finder or select method declaration. You're not limited to simple numeric types for parameters. Finder and select methods can accept more complex parameters, such as component interface references, that you can use within your queries as well. The one restriction on using input parameters is that you can reference them only in conditional expressions that involve single-valued path expressions.

Expressions and Operators

The expression associated with a WHERE clause is known as its conditional expression. Complex conditional expressions can be built using the operators defined by the language. Expressions can include logical operators, relational operators, arithmetic operators, Boolean literals, and path expressions that evaluate to Boolean results. One of the few restrictions is that you can't compare instances of dependent value classes as part of a conditional expression. Operators are evaluated based on the order of precedence shown in Table 8.1 (starting with the highest precedence).

Table 8.1. Precedence of Conditional Expression Operators

Operator	Description
.	Navigation operator
+ , -	Unary sign operators
* , /	Multiplication and division
+ , -	Addition and subtraction
= , > , >= , < , <= , <>	Relational operators
NOT	Logical NOT
AND	Logical AND
OR	Logical OR

You can use parentheses within a query to group operators when the precedence rules won't produce the intended results. Of the relational operators shown, only = and <> apply to String or Boolean operands.

BETWEEN

You can use the BETWEEN comparison operator within an arithmetic expression. This operator compares an expression result to a lower and an upper limit using the following syntax:

  expression  [NOT] BETWEEN  lowerLimit  AND  upperLimit  

Note

Square brackets ( [] ) are used throughout this chapter to identify optional elements of a query.

As an example, you could look for auctions with a starting bid within a certain range using

 SELECT OBJECT(auction) FROM EnglishAuction auction    WHERE auction.startingBidField BETWEEN 100 AND 500 

The lower and upper limits in a BETWEEN expression don't have to be literals. The limits can be parameters or any arithmetic expression that evaluates to a result of the same type as the expression being tested . If an expression used with a BETWEEN operator evaluates to null , the value of the expression is unknown. You'll see more about what this means a little later.

IN

When used in a WHERE clause, the IN operator serves a purpose other than what you use if for in the FROM clause. The IN comparison operator allows you to form a conditional expression that is evaluated based on whether a string is found within a specified set of string literals. This operator is used only with single-valued path expressions that evaluate to the String value of a CMP field. This operator can also be used as NOT IN to return a negated result. As an example, the following conditional looks for bidders located in a certain region of the United States:

 SELECT OBJECT(b) FROM Bidder b WHERE b.shippingAddress.stateField    IN ('GA', 'FL', 'AL', 'TN') 

When using the IN operator, you must include at least one string literal in the list being used for the comparison. If the single-valued path expression evaluates to null , the result of an expression using IN is unknown.

LIKE

The LIKE operator allows you to build a conditional that looks for strings that either match or don't match a pattern you specify. The syntax for the LIKE operator is

  single_valued_path_expression  [NOT] LIKE  pattern  [ESCAPE  escape-character  ] 

The pattern used with the LIKE operator must be a string literal, so the operator can be used only with a single-valued path expression that evaluates to a String . The pattern can include an underscore ( _ ) to represent any single character in a given position or a percent sign ( % ) to represent any sequence of characters starting in a certain position. If you need to search for underscores or percent signs, you must precede them with an escape character within the pattern and then include the ESCAPE identifier to identify the escape character you're using. The following examples illustrate the uses of LIKE :

• WHERE auction.statusField LIKE 'C%' is true for all auctions that are either Closed or Cancelled .

• WHERE auction.statusField NOT LIKE 'C%' is true for all auctions that are either Pending or Open .

• WHERE bidder.shippingAddress.stateField LIKE 'A_' is true for all bidders who live in Alabama, Alaska, Arkansas, or Arizona.

• WHERE auction.nameField LIKE '\_%' ESCAPE '\' is true for all auctions with a name that starts with an underscore.

If the single-valued expression evaluates to null , the result of a LIKE expression is unknown.

MEMBER OF

You can use MEMBER OF to test whether an identification variable, an input parameter, or the object returned by a single-valued navigation is contained in the collection returned by a collection-valued path expression. A single-valued navigation is defined by navigating an identification variable to a single-valued CMR field. For example, the following query returns all bidders who have submitted a winning bid using MEMBER OF with the single-valued navigation auction.winningBid :

 SELECT OBJECT(b) FROM EnglishAuction auction, Bidder b    WHERE auction.winningBid MEMBER OF b.myBids 

MEMBER OF always returns a Boolean result. If the collection-valued path expression with which it's used is empty, FALSE is returned. You also can use NOT MEMBER OF to build conditional expressions. The OF part of the operator is optional and is assumed if you leave it out of an expression.

Null Values and Empty Collections

As you've seen from a few of the cautions about undefined query results, you must be careful about null results from path expressions. If a path expression encounters a null value anywhere along its navigation, the expression evaluates to null . EJB QL provides a way to test for null results from a single-valued path expression using the IS NULL and IS NOT NULL comparison operators. You can apply these operators to a CMP field or single-valued CMR field to get a Boolean TRUE or FALSE result based on whether the field is null . For example, you could locate only the auctions with a valid winning bid using

 SELECT OBJECT(auction) FROM EnglishAuction auction    WHERE auction.winningBid IS NOT NULL 

The preceding operator descriptions pointed out that applying certain operators to a null value (which can include null input parameter values) produces an unknown result. This is true for any arithmetic or comparison operator. For example, the sum of a number and an unknown value is unknown. Asking if an unknown value is less than some number can't produce a known result either. Unlike arithmetic and comparison operators, the Boolean operators are defined to produce known results when working with unknown values in some cases. The AND operator produces an unknown result when applied to two unknown values or an unknown value and a TRUE , but it produces a FALSE if applied to an unknown value and a FALSE . This is because a FALSE and anything produces a FALSE with the AND operator. Similarly, applying OR to a TRUE and an unknown value produces a TRUE , but any other use of OR with an unknown produces an unknown result. Applying the unary NOT operator to an unknown value produces an unknown result as well. If you apply an equality test to an unknown value, the result of the conditional is always FALSE . This is because unknown isn't TRUE and it isn't FALSE . So a test for either will report FALSE as its result.

Caution

EJB QL considers an empty string and a null string to be two different things. However, not all data stores do the same. You can't rely on consistent results when you define queries that could involve the comparison of an empty string to null .

Besides checking for null values, you also must be able to recognize empty collections. The IS EMPTY and IS NOT EMPTY comparison operators allow you to test for empty collection results when you're working with a collection-values path expression. For example, you might want to look for auctions that have at least one bid using

 SELECT OBJECT(auction) FROM EnglishAuction auction    WHERE auction.bids IS NOT EMPTY 

This query is much more straightforward than the following one that was presented earlier to find the same set of auctions:

 SELECT OBJECT(auction) FROM EnglishAuction auction, IN(auction.bids) aBid 

Adding a WHERE clause to this original query to test auction.bids for an empty collection would be invalid. This is because the FROM clause filters out empty collections automatically as written. You can't make an empty collection comparison using a collection-valued path expression that is used to declare an identification variable in the FROM clause.

Note

You've now seen the only three places you can use a collection-valued path expression: an identification variable declaration using IN , an empty collection comparison expression using IS [NOT] EMPTY , and a collection member expression using [NOT] MEMBER [OF] .