The simplest EJB QL statement has no WHERE clause and only one abstract schema type. For example, you could define a query method to select all Customer beans:

SELECT OBJECT( c ) FROM Customer AS c

The FROM clause determines which entity bean types will be included in the select statement (i.e., provides the scope of the select). In this case, the FROM clause declares the type to be Customer, which is the abstract schema name of the Customer EJB. The AS c part of the clause assigns c as the identifier of the Customer EJB. This is similar to SQL, which allows an identifier to be associated with a table. Identifiers can be any length and follow the same rules that are applied to field names in the Java programming language. However, identifiers cannot be the same as existing <ejb-name> or <abstract-schema-name> values. In addition, identification variable names are not case-sensitive, so an identifier of customer would be in conflict with an abstract schema name of Customer. For example, the following statement is illegal because Customer is the abstract schema name of the Customer EJB:

SELECT OBJECT( customer ) FROM Customer AS customer

The AS operator is optional, but it is used in this book to help make the EJB QL statements more clear. The following two statements are equivalent:

SELECT OBJECT(c) FROM Customer AS c

SELECT OBJECT(c) FROM Customer c

The SELECT clause determines the type of any values that are returned. In this case, the statement returns the Customer entity bean, as indicated by the c identifier.

The OBJECT( ) operator is required when the SELECT type is a solitary identifier for an entity bean. The reason for this requirement is pretty vague (and in the author’s opinion, the specification would have been better off without it), but it is required whenever the SELECT type is an entity bean identifier. The OBJECT( ) operator is not used if the SELECT type is expressed using a path, which is discussed below.

Identifiers cannot be EJB QL reserved words. In EJB 2.0, the following words are reserved: SELECT, FROM, WHERE, DISTINCT, OBJECT, NULL, TRUE, FALSE, NOT, AND, OR, BETWEEN, LIKE, IN, AS, UNKNOWN, EMPTY, MEMBER, OF and IS. EJB 2.1 adds 10 new reserved words to this list, which include AVG, MAX, MIN, SUM, COUNT, ORDER, BY, ASC, DESC, and MOD. You shouldn’t use these reserved words with EJB 2.0 either, because the queries that use them as identifiers won’t be forward compatible with EJB 2.1. It’s a good practice to avoid all SQL reserved words, because you never know which ones will be used by future versions of EJB QL. You can find more information in the Appendix (“SQL99 and Vendor-Specific Keywords”) of SQL in a Nutshell by Kevin E. Kline with David Kline (O’Reilly).

EJB QL allows SELECT clauses to return any CMP or single CMR field. For example, we can define a simple select statement to return the last names of all of Titan’s customers:

SELECT c.lastName FROM Customer AS c

The SELECT clause uses a simple path to select the Customer EJB’s lastName field as the return type. EJB QL uses the CMP and CMR field names declared in <cmp-field> and <cmr-field> elements of the deployment descriptor. To navigate between fields, use the familiar Java dot (.) operator. The previous EJB QL statement is based on the Customer EJB’s deployment descriptor:

<enterprise-beans>
    <entity>
        <ejb-name>CustomerEJB</ejb-name>
        <home>com.titan.customer.CustomerHomeRemote</home>
        <remote>com.titan.customer.CustomerRemote</remote>
        <ejb-class>com.titan.customer.CustomerBean</ejb-class>
        <persistence-type>Container</persistence-type>
        <prim-key-class>java.lang.Integer</prim-key-class>
        <reentrant>False</reentrant>
        <abstract-schema-name>Customer</abstract-schema-name>
        <cmp-version>2.x</cmp-version>
        <cmp-field><field-name>id</field-name></cmp-field>
        <cmp-field><field-name>lastName</field-name></cmp-field>
        <cmp-field><field-name>firstName</field-name></cmp-field>

You can also use CMR field types in simple select statements. The following EJB QL statement selects all the CreditCard EJBs from all the Customer EJBs:

SELECT c.creditCard FROM Customer AS c

In this case, the EJB QL statement uses a path to navigate from the Customer EJBs to their creditCard relationship fields. The creditCard identifier is obtained from the <cmr-field> name used in the relationship element that describes the Customer-CreditCard relationship:

<enterprise-beans>
    <entity>
        <ejb-name>CustomerEJB</ejb-name>
        ...
        <abstract-schema-name>Customer</abstract-schema-name>
    </entity>
</enterprise-beans>
...
<relationships>
    <ejb-relation>
        <ejb-relation-name>Customer-CreditCard</ejb-relation-name>

        <ejb-relationship-role>
            <ejb-relationship-role-name>
                Customer-has-a-CreditCard
            </ejb-relationship-role-name>
            <multiplicity>One</multiplicity>
            <relationship-role-source>
                <ejb-name>CustomerEJB</ejb-name>
            </relationship-role-source>
         <cmr-field>
                               <cmr-field-name>creditCard</cmr-field-name>
                           </cmr-field>
        </ejb-relationship-role>
        <ejb-relationship-role>
        ...

Paths can be as long as required. It’s common to use paths that navigate over one or more CMR fields to end at either a CMR or CMP field. For example, the following EJB QL statement selects all the city CMP fields of all the Address EJBs in each Customer EJB:

SELECT c.homeAddress.city  FROM Customer AS c

In this case, the path uses the abstract schema name of the Customer EJB, the Customer EJB’s homeAddress CMR field, and the Address EJB’s city CMP field.

To illustrate more complex paths, we’ll need to expand the class diagram. Figure 8-2 shows that the CreditCard EJB is related to a CreditCompany EJB that has its own Address EJB.

Figure 8-2. Expanded class diagram for CreditCard EJB

Using these relationships, we can specify a more complex path that navigates from the Customer EJB to the CreditCompany EJB to the Address EJB. Here’s an EJB QL statement that selects the addresses of all the credit card companies used by Titan’s customers:

SELECT c.creditCard.creditCompany.address FROM Customer AS c

The EJB QL statement could also navigate all the way to the Address bean’s CMP fields. The following statement selects all the cities in which the credit card companies that distribute credit cards used by Titan’s customers are based:

SELECT c.creditCard.creditCompany.address.city FROM Customer AS c

Note that these EJB QL statements return address CMR fields or city CMP fields only for those credit card companies responsible for cards owned by Titan’s customers. The address information of any credit card companies whose cards are not currently used by Titan’s customers won’t be included in the results.

Paths cannot navigate beyond CMP fields. For example, imagine that the Address EJB uses a ZipCode class as its zip CMP field:

public class ZipCode implements java.io.Serializable {
    public int mainCode;
    public int codeSuffix;
    ...
}

You can’t navigate to one of the ZipCode class’s instance fields:

// this is illegal
SELECT c.homeAddress.zip.mainCode FROM Customer AS c

The paths used in SELECT clauses of EJB QL statements must always end with a single type. They may not end with a collection-based relationship field. For example, the following is not legal because reservations is a collection-based relationship field:

// this is illegal
SELECT c.reservations FROM Customer AS c

In fact, it’s illegal to navigate across a collection-based relationship field. The following EJB QL statement is also illegal, even though the path ends in a single-type relationship field:

// this is illegal
SELECT c.reservations.cruise FROM Customer AS c

If you think about it, this limitation makes sense. You can’t use a navigation operator (.) in Java to access elements of a java.util.Collection object. For example, if getReservations( ) returns a java.util.Collection type, this statement is illegal:

// this is illegal in the Java programming language
customer.getReservations( ).getCruise( );

Referencing the elements of a collection-based relationship field is possible, but it requires the use of an IN operator and an identification assignment in the FROM clause.

Many relationships between entity beans are collection-based, and being able to access and select beans from these relationships is important. We’ve seen that it is illegal to select elements directly from a collection-based relationship. To overcome this limitation, EJB QL introduces the IN operator, which allows an identifier to represent individual elements in a collection-based relationship field.

The following query uses the IN operator to select the elements from a collection-based relationship. It returns all the reservations of all the customers:

SELECT OBJECT( r ) 
FROM Customer AS c,  IN( c.reservations ) AS r

The IN operator assigns the individual elements in the reservations CMR field to the identifier r. Once we have an identifier to represent the individual elements of the collection, we can reference them directly and even select them in the EJB QL statement. We can also use the element identifier in path expressions. For example, the following statement selects every cruise for which Titan’s customers have made reservations:

SELECT r.cruise 
FROM Customer AS c, IN( c.reservations ) AS r

The identifiers assigned in the FROM clause are evaluated from left to right. Once you declare an identifier, you can use it in subsequent declarations in the FROM clause. The identifier c, which was declared first, was subsequently used in the IN operator to define the identifier r.

Identification chains can become very long. The following statement uses two IN operators to navigate two collection-based relationships and a single CMR relationship. While not necessarily useful, this statement demonstrates how a query can use IN operators across many relationships:

SELECT cbn.ship
FROM Customer AS c, IN ( c.reservations ) AS r, 
IN( r.cabins ) AS cbn

To put the examples in this section into action, see Exercise 8.1 in the Workbook.

The DISTINCT keyword ensures that the query does not return duplicates. It is especially valuable when applied to EJB QL statements used by multivalued find methods. Find methods in CMP have only one return type, java.util.Collection, which may include duplicates. For example, the following find method and its associated query will return duplicates:

// the find method declared in the remote or local home interface
public java.util.Collection  findAllCustomersWithReservations( ) 

// the EJB QL statement associated with the find method
SELECT OBJECT( cust ) FROM Reservation AS res, IN (res.customers) AS cust

If a customer has more than one reservation, there will be duplicate references to that Customer EJB in the result Collection. Using the DISTINCT keyword ensures that each Customer EJB is represented only once in the result:

SELECT DISTINCT OBJECT( cust ) FROM Reservation AS res, 
IN (res.customers) cust

The DISTINCT keyword can also be used with select methods. It works the same way for select methods that have a return type of Collection. If the select method’s return type is java.util.Set, the DISTINCT keyword has no effect; the Set object eliminates duplicates by definition.

You can use literal values to narrow the scope of the elements selected. This is accomplished through the WHERE clause, which behaves in much the same way as the WHERE clause in SQL.

For example, you can define an EJB QL statement that selects all the Customer EJBs that use a specific brand of credit card. The literal in this case is a String literal. Literal strings are enclosed by single quotes. Literal values that include a single quote, like the restaurant name “Wendy’s,” use two single quotes to escape the quote: 'Wendy''s‘. The following statement returns customers that use American Express:

SELECT OBJECT( c ) FROM Customer AS c 
WHERE c.creditCard.organization = 'American Express'

Path expressions in the WHERE clause are used in the same way as in the SELECT clause. When making comparisons with a literal, the path expression must evaluate to a CMP field; you can’t compare a CMR field with a literal.

In addition to literal strings, literals can be exact numeric values (long types) and approximate numeric values (double types). Exact numeric literal values are expressed using the Java integer literal syntax (321, -8932, +22). Approximate numeric literal values are expressed using Java floating point literal syntax in scientific (5E3, -8.932E5) or decimal (5.234, 38282.2) notation. For example, the following EJB QL statement selects all the ships that weigh 100,000.00 metric tons:

SELECT OBJECT( s ) 
FROM Ship AS s
WHERE s.tonnage = 100000.00

Boolean literal values use TRUE and FALSE. Here’s an EJB QL statement that selects all the customers who have good credit:

SELECT OBJECT( c ) FROM Customer AS c 
WHERE c.hasGoodCredit = TRUE

Query methods (find and select methods) that use EJB QL statements may specify method arguments. Input parameters allow those method arguments to be mapped to EJB QL statements and are used to narrow the scope of the query. For example, the ejbSelectByCity( ) method selects all the customers who reside in a particular city and state:

public abstract class CustomerBean implements javax.ejb.EntityBean {
    ...
    public abstract Collection ejbSelectByCity(String city,String state)
        throws FinderException;
    ...
}

The EJB QL statement for this method uses the city and state arguments as input parameters:

SELECT OBJECT( c ) FROM Customer AS c
WHERE c.homeAddress.state = ?2 
AND c.homeAddress.city = ?1

Input parameters use a ? prefix followed by the argument’s position, in order of the query method’s parameters. In this case, city is the first argument listed in the ejbSelectByCity( ) method and state is the second. When a query method declares one or more arguments, the associated EJB QL statement may use some or all of the arguments as input parameters.

Input parameters are not limited to simple CMP field types; they can also be EJB object references. For example, the following find method, findByShip( ), is declared in the Cruise bean’s local home interface:

public interface CruiseLocalHome extends javax.ejb.EJBLocalHome {
    ...
    public Collection findByShip( ShipLocal ship ) 
        throws FinderException;
}

The EJB QL statement associated with this method would use the ship argument to locate all the cruises scheduled for the specified Ship bean:

SELECT OBJECT( crs ) FROM Cruise AS crs 
WHERE crs.ship = ?1

When an EJB object is used as an input parameter, the container bases the comparison on the primary key of the EJB object. In this case, it searches through all the Cruise EJBs looking for references to a Ship EJB with the same primary key value as the one the Ship EJB passed to the query method.

The WHERE clause is composed of conditional expressions that reduce the scope of the query and limit the number of items selected. Several conditional and logical operators can be used in expressions; they are listed below in order of precedence:

EJB QL statements are declared in XML deployment descriptors. XML uses the greater than (>) and less than (<) characters as delimiters for tags; using these symbols in the EJB QL statements causes parsing errors unless CDATA sections are used. For example, the following EJB QL statement causes a parsing error, because the XML parser interprets the > symbol as an incorrectly placed XML tag delimiter:

<query>
    <query-method>
        <method-name>findWithPaymentGreaterThan</method-name>
        <method-params>
            <method-param>java.lang.Double</method-param>
        </method-params>
    </query-method>
    <ejb-ql>
        SELECT OBJECT( r ) FROM Reservation AS r
        WHERE r.amountPaid  > ?1
    </ejb-ql>
</query>

To avoid this problem, place the EJB QL statement in a CDATA section, which takes the form <![CDATA[ literal-text ]]>:

<query>
    <query-method>
        <method-name>findWithPaymentGreaterThan</method-name>
        <method-params>
            <method-param>java.lang.Double</method-param>
        </method-params>
    </query-method>
    <ejb-ql> 
        <![CDATA[
        SELECT OBJECT( r ) FROM Reservation AS r
        WHERE r.amountPaid  > 300.00
        ]]>
    </ejb-ql>
</query>

When an XML processor encounters a CDATA section, it doesn’t attempt to parse the contents enclosed by the CDATA section; instead, the parser treats the contents as literal text.^[25]

The arithmetic operators allow a query to perform arithmetic in the process of doing a comparison. Arithmetic operators can be used only in the WHERE clause, not in the SELECT clause.

The following EJB QL statement returns references to all the Reservation EJBs that will be charged a port tax of more than $300.00:

SELECT OBJECT( r ) FROM Reservation AS r
WHERE (r.amountPaid * .01)  > 300.00

The rules applied to arithmetic operations are the same as those used in the Java programming language, where numbers are widened or promoted in the process of performing a calculation. For example, multiplying a double and an int value requires that the int first be promoted to a double value. (The result will always be that of the widest type used in the calculation, so multiplying an int and a double results in a double value.)

String, boolean, and EJB object types cannot be used in arithmetic operations. For example, using the addition operator with two String values is considered an illegal operation. There is a special function for concatenating String values, covered later in the section titled “The WHERE Clause and Functional Expressions.”

Logical operators such as AND , OR , and NOT operate the same way in EJB QL as their corresponding logical operators in SQL.

Logical operators evaluate only Boolean expressions, so each operand (i.e., each side of the expression) must evaluate to true or false. Logical operators have the lowest precedence so that all the expressions can be evaluated before they are applied.

The AND and OR operators don’t behave like their Java language counterparts, && and ||. EJB QL does not specify whether the right-hand operands are evaluated conditionally. For example, the && operator in Java evaluates its right-hand operand only if the left-hand operand is true. Similarly, the || logical operator evaluates the right-hand operand only if the left-hand operand is false. We can’t make the same assumption for the AND and OR operators in EJB QL. Whether these operators evaluate right-hand operands depends on the native query language into which the statements are translated. It’s best to assume that both operands are evaluated on all logical operators.

NOT simply reverses the Boolean result of its operand; expressions that evaluate to the Boolean value of true become false, and vice versa.

Comparison operators, which use the symbols =, >, >=, <, <=, and <>, should be familiar to you. The following statement selects all the Ship EJBs whose tonnage CMP field is greater than or equal to 80,000 tons but less than or equal to 130,000 tons:

SELECT OBJECT( s ) FROM Ship AS s
WHERE s.tonnage >= 80000.00 AND s.tonnage <= 130000.00

Only the = and <> (not equal) operators may be used on boolean and EJB object identifiers. In EJB 2.0, the greater-than and less-than symbols (>, >=, <, <=) can be used only on numeric values. In EJB 2.0, it’s illegal to use the greater-than or less-than symbols to compare two Strings. In EJB 2.1, the greater-than and less-than symbols can also be used with String values. However, the semantics of these operations are not defined by the EJB 2.1 specification. Is character case (upper or lower) important? Does leading and trailing whitespace matter? Issues like these affect the ordering of String values. In order for EJB QL to maintain its status as an abstraction of native query languages (e.g., SQL-92, JDOQL, OQL, etc.) it cannot dictate String ordering, because native query languages may have very different ordering rules. In fact, even different relational database vendors vary on the question of String ordering, which makes it all but impossible to standardize ordering even for SQL “compliant” databases.

Of course, this is all academic if you plan on using the same database well into the future. In such a case, the best thing to do is to examine the documentation for the database you are using to find out how it orders strings in comparisons. This tells you exactly how your EJB QL comparisons will work.

While it is legal to compare an exact numeric value (short, int, long) to an approximate numeric value (double, float), all other equality comparisons must compare the same types. You cannot, for example, compare a String value of 123 to the Integer literal 123. However, you can compare two String types for equality.

In EJB 2.1, you can compare numeric values for which the rules of numeric promotion apply. For example, a short may be compared to an int, an int to a long, etc. EJB 2.1 also states that primitives may be compared to primitive wrappers primitives—the rules of numeric promotion apply.

Where EJB 2.0 was very specific about String type comparisons, saying that they must match exactly, character-for-character, EJB 2.1 drops this requirement, making the evaluation of equality between String types more ambiguous. Again, this ambiguity arises from the differences between kinds of databases (relational versus object- oriented versus file), as well as differences between vendors of relational databases. Consult your vendor’s documentation to determine exactly how String equality comparisons are evaluated.

You can also compare EJB objects for equality, but these too must be of the same type. To be more specific, they must both be EJB object references to beans from the same deployment. As an example, the following method finds all the Reservation EJBs made by a specific Customer EJB:

public interface ReservationHomeLocal extends EJBLocalHome {
    public Collection findByCustomer(CustomerLocal customer) 
        throws FinderException;
    ...
}

The matching EJB QL statement uses the customer argument as a parameter:

SELECT OBJECT( r ) 
FROM Reservation r, IN ( r.customers ) AS cust
WHERE  cust = ?1

It’s not enough for the EJB object used in the comparison to implement the CustomerLocal interface; it must be of the same bean type as the Customer EJB used in the Reservation’s customers CMR field. In other words, they must be from the same deployment. Once it’s determined that the bean is the correct type, the actual comparison is performed on the beans’ primary keys. If they have the same primary key, they are considered equal.

You cannot use java.util.Date objects in equality comparisons. To compare dates, you must use the long millisecond value of the date, which means that the date must be persisted in a long CMP field, not a DateCMP field. The input value or literal must also be a long value. Use the java.util.Calandar class to obtain the long millisecond value of a Date object.

The BETWEEN clause is an inclusive operator specifying a range of values. In this example, we use it to select all ships weighing between 80,000 and 130,000 tons:

SELECT OBJECT( s ) FROM Ship AS s
WHERE s.tonnage BETWEEN 80000.00 AND 130000.00

The BETWEEN clause may be used only on numeric primitives (byte, short, int, long, double, float) and their corresponding java.lang.Number types (Byte, Short, Integer, etc.). It cannot be used on String, boolean, or EJB object references.

Using the NOT logical operator in conjunction with BETWEEN excludes the range specified. For example, the following EJB QL statement selects all the ships that weigh less than 80,000 tons or greater than 130,000 tons but excludes everything in between:

SELECT OBJECT( s ) FROM Ship AS s
WHERE s.tonnage NOT BETWEEN 80000.00 AND 130000.00

The net effect of this query is the same as if it had been executed with comparison symbols:

SELECT OBJECT( s ) FROM Ship AS s
WHERE s.tonnage < 80000.00 OR s.tonnage > 130000.00

The IN conditional operator used in the WHERE clause is not the same as the IN operator used in the FROM clause. In the WHERE clause, IN tests for membership in a list of literal values. For example, the following EJB QL statement uses the IN operator to select all the customers who reside in a specific set of states:

SELECT OBJECT( c ) FROM Customer AS c
WHERE c.homeAddress.state  IN ('FL', 'TX', 'MI', 'WI', 'MN')

Applying the NOT operator to this expression reverses the selection, excluding all customers who reside in the list of states:

SELECT OBJECT( c ) FROM Customer AS c
WHERE c.homeAddress.state NOT IN ('FL', 'TX', 'MI', 'WI', 'MN')

If the field tested is null, the value of the expression is “unknown”, which means it cannot be predicted.

In EJB 2.0, the IN operator is limited to evaluating string values. In EJB 2.1, this operator can be used with operands that evaluate to either string or numeric values. For example, the following EJB QL statement uses the IN operator to select all cabins on deck levels 1, 3, 5, and 7:

SELECT OBJECT( cab ) FROM Cabin AS cab
WHERE cab.deckLevel IN (1,3,5,7)

EJB 2.1 also allows you to use the IN operator with input parameters; EJB 2.0 does not. For example, the following select method returns all the customers who live is the designated states:

public Collection ejbSelectCustomersByStates(String state1, String state2, String state3)

The EJB QL assigned to this select method would use the input parameters with the IN operator, as shown in the following listing:

SELECT OBJECT( c ) FROM Customer AS c
WHERE c.homeAddress.state IN ( ?1, ?2, ?3, 'WI', 'MN')

In this case, the input parameters (?1, ?2, and ?3) are combined with string literals ('WI' and 'MN') to show that mixing literal and input parameters is allowed, providing they are “like” types.

The IS NULL comparison operator allows you to test whether a path expression is null. For example, the following EJB QL statement selects all the customers who do not have home addresses:

SELECT OBJECT( c ) FROM Customer AS c
WHERE c.homeAddress IS NULL

Using the NOT logical operator, we can reverse the results of this query, selecting all the customers who do have home addresses:

SELECT OBJECT( c ) FROM Customer AS c
WHERE c.homeAddress IS NOT NULL

In EJB 2.0, null fields in comparison operations (e.g., IN and BETWEEN) can cause bizarre side effects. In most cases, evaluating a null field in a comparison operation (other than IS NULL) produces an UNKNOWN result. Unknown evaluations throw the entire EJB QL result set into question. One way to avoid this situation is to require that fields used in the expressions have values. This requires careful programming. To ensure that an entity bean field is never null, you must initialize the field when the entity is created. For primitive values, this not a problem; they have default values, so they cannot be null. Other fields, such as single CMR fields and object-based CMP fields such as String, must be initialized in the ejbCreate( ) and ejbPostCreate( ) methods.

In EJB 2.1, path expressions are composed using “inner join” semantics. If an entity has a null CMR field, any query that uses that field as part of a path expression eliminates that entity from consideration. For example, if the Customer EJB representing “John Smith” has a null value for its address CMR field, then the “John Smith” Customer EJB won’t be included in the result set for the following query:

SELECT OBJECT( c ) FROM Customer AS c
WHERE c.homeAddress.state = 'TX'
AND c.lastName = 'Smith' AND c.firstName = 'John'

This seems obvious at first, but stating it explicitly helps eliminate much of the ambiguity associated with null CMR fields. In EJB 2.0, it was unclear what would happen, which is why it was recommended that all CMR fields have values. This is not necessary in EJB 2.1.

In EJB 2.1, the NULL comparison operator can also be used to test input parameters. In this case, NULL is usually combined with the NOT operator to ensure that an input parameter is not a null value. For example, the query used in conjunction with the ejbSelectByCity( ) method can be modified to test for null input parameters.

public abstract class CustomerBean implements javax.ejb.EntityBean {
    ...
    public abstract Collection ejbSelectByCity(String city, String state)
        throws FinderException;
    ...
}

The EJB QL statement for this method first checks that the city and state input parameters are not null, and then uses them in comparison operations.

SELECT OBJECT( c ) FROM Customer AS c
WHERE ?1 IS NOT NULL AND ?2 IS NOT NULL
AND c.homeAddress.state = ?2 
AND c.homeAddress.city = ?1

In this case, if either of the input parameters are null values, the query returns an empty Collection, avoiding the possibility of UNKNOWN results from null input parameters.

In EJB 2.1, if the results of a query include a null CMR or CMP field, the results must include null values. For example, the following query selects the Address EJBs customers with the last name “Smith”:

SELECT c.address FROM Customer AS c
WHERE c.lastName = 'Smith'

If the Customer EJB representing “John Smith” has a null value for its address CMR field, the previous query returns a Collection that includes a null value—the null represents the address CMR field of “John Smith”—in addition to a bunch of Address EJB references. EJB 2.0 was not clear on whether null values were returned or not, but EJB 2.1 says they are. You can eliminate null values by including the NOT NULL operator in the query, as shown here:

SELECT c.address.city FROM Customer AS c
WHERE c.address.city NOT NULL AND c.address.state = 'FL'

The IS EMPTY operator allows the query to test whether a collection-based relationship is empty. Remember from Chapter 7 that a collection-based relationship will never be null. If a collection-based relationship field has no elements, it returns an empty Collection or Set.

Testing whether a collection-based relationship is empty has the same purpose as testing whether a single CMR field or CMP field is null: it can be used to limit the scope of the query and items selected. For example, the following query selects all the cruises that have not booked any reservations:

SELECT OBJECT( crs ) FROM Cruise AS crs
WHERE crs.reservations IS EMPTY

The NOT operator reverses the result of IS EMPTY. The following query selects all the cruises that have at least one reservation:

SELECT OBJECT( crs ) FROM Cruise AS crs
WHERE crs.reservations IS NOT EMPTY

It is illegal to use IS EMPTY against collection-based relationships that have been assigned an identifier in the FROM clause:

                  // illegal query
SELECT OBJECT( r ) 
FROM Reservation AS r, IN( r.customers ) AS c
WHERE 
r.customers IS NOT EMPTY AND
c.address.city = 'Boston'

While this query appears to be good insurance against UNKNOWN results, it’s not. It’s illegal because the IS EMPTY operator cannot be used on a collection-based relationship identified in an IN operator in the FROM clause. Because the relationship is specified in the IN clause, only those Reservation EJBs that have a nonempty customers field will be included in the query; any Reservation EJB that has an empty CMR field is already excluded because its customers elements cannot be assigned the c identifier.

The MEMBER OF operator is a powerful tool for determining whether an EJB object is a member of a specific collection-based relationship. The following query determines whether a particular Customer (specified by the input parameter) is a member of any of the Reservation-Customer relationships:

SELECT OBJECT( crs ) 
FROM Cruise AS crs, IN (crs.reservations) AS res, Customer AS cust
WHERE  
cust = ?1 
  AND
cust MEMBER OF res.customers

Applying the NOT operator to MEMBER OF has the reverse effect, selecting all the cruises on which the specified customer does not have a reservation:

SELECT OBJECT( crs ) 
FROM Cruise AS crs, IN (crs.reservations) AS res, Customer AS cust
WHERE  
cust = ?1 
  AND
cust NOT MEMBER OF res.customers

Checking whether an EJB object is a member of an empty collection always returns false.

The LIKE comparison operator allows the query to select String type CMP fields that match a specified pattern. For example, the following EJB QL statement selects all the customers with hyphenated names, like “Monson-Haefel” and “Berners-Lee”:

SELECT OBJECT( c ) FROM Customer AS c
WHERE c.lastName LIKE '%-%'

You can use two special characters when establishing a comparison pattern: % (percent) stands for any sequence of characters, and _ (underscore) stands for any single character. You can use these characters at any location within a string pattern. If a % or _ actually occurs in the string, you can escape it with the character. The NOT logical operator reverses the evaluation so that matching patterns are excluded. The following examples show how the LIKE clause evaluates String type CMP fields:

The LIKE operator cannot be used with input parameters. This is an important point that is confusing to many new EJB developers. The LIKE operator compares a String type CMP field to a String literal. As it is currently defined, it cannot be used in a comparison with an input parameter, because an input parameter is, by definition, unknown until the method is invoked. The comparison pattern must be known at deployment time in order to generate the native query code.

In the previous edition of this book, I complained about the limited support for functions in EJB QL. EJB 2.1 has started to address this problem by adding five new aggregate functions for the SELECT clause as well as the MOD function for the WHERE clause.

SELECT OBJECT( c ) 
FROM Customer AS c
WHERE 
LENGTH(c.lastName) > 6
  AND
LOCATE( c.lastName, 'Monson') > -1

SELECT  COUNT( c ) 
FROM Customers AS c
WHERE c.address.state = 'WI'

SELECT  COUNT(c.address.zip)
FROM Customers AS c
WHERE c.address.zip LIKE '554%'

SELECT COUNT( c )
FROM Customers AS c
WHERE c.address.zip LIKE '554%'

SELECT MAX( r.amountPaid )
FROM Reservation AS r

SELECT SUM( r.amountPaid)
FROM Cruise c, IN( c.reservations) AS r
WHERE  c = ?1

SELECT DISTINCT COUNT(c.address.zip)
FROM Customers AS c
WHERE c.address.zip LIKE '554%'

SELECT AVG( r.amountPaid)
FROM Cruise As c, IN( c.reservations ) AS r
WHERE c = ?1

SELECT COUNT( r )
FROM Cruise AS c, IN( c.reservations ) AS r
WHERE c = ?1

The ORDER BY clause allows you to specify the order of the entities in the collection returned by a query. EJB 2.0 didn’t include an ORDER BY clause, and as a result you never knew what order the results would be in. The semantics of the ORDER BY clause are basically the same as in SQL. For example, we can construct a simple query that uses the ORDER BY clause to return an alphabetical list of all of Titan’s Customers:

SELECT OBJECT( c )
FROM Customers AS c
ORDER BY c.lastName

This might return a Collection of Customer EJBs in the following order (assume their last and first names are printed to output):

Aares, John
Astro, Linda
Brooks, Hank
.
.
Xerces, Karen
Zastro, William

You can use the ORDER BY clause with or without the WHERE clause. For example, we can refine the previous query by listing only those customers who reside in Boston, MA:

SELECT OBJECT( c )
FROM Customers AS c
WHERE c.address.city = 'Boston' AND c.address.state = 'MA'
ORDER BY c.lastName

The default order of an item listed in the ORDER BY clause is always ascending, which means that the lesser values are listed first and the greatest values last. You can explicitly specify the order as ascending or descending by using the key words ASC or DESC. The default is ASC. Here’s a statement that lists customers in reverse (descending) order:

SELECT OBJECT( c )
FROM Customers AS c
ORDER BY c.lastName DESC

The results of this query are:

Zastro, William
Xerces, Karen
.
.
Brooks, Hank
Astro, Linda
Aares, John

You can specify multiple order-by items. For example, you can sort customers by lastName in ascending order and firstName in descending order:

SELECT OBJECT( c )
FROM Customers AS c
ORDER BY c.lastName ASC, c.firstName DESC

If you have five Customer EJBs with the lastName equal to “Brooks”, this query sorts the results as follows:

Brooks, William 
Brooks, Henry 
Brooks, Hank
Brooks, Ben
Brooks, Andy

Although the fields used in the ORDER BY clause must be CMP fields, the value selected can be an entity identifier, a CMR field, or a CMP field. For example, the following query returns an ordered list of all Zip codes:

SELECT addr.zip
FROM Address AS addr
ORDER BY addr.zip

The following query returns all the Address EJBs for customers named “Smith”, ordered by their Zip code.

SELECT c.address
FOR Customer AS c
WHERE c.lastName = 'Smith'
ORDER BY c.address.zip

You must be careful which CMP fields you use in the ORDER BY clause. If the query selects a collection of entities, than the ORDER BY clause can only be used with CMP fields of the entity type that is selected. The following query is illegal, because the CMP field used in the ORDER BY clause is not a field of the entity type selected:

// Illegal EJB QL
SELECT OBJECT( c )
FROM Customer AS c
ORDER BY c.address.city

Because the city CMP field is not a direct CMP field of the Customer EJB, you cannot use it in the ORDER BY clause.

A similar restriction applies to CMP results. The CMP field used in the ORDER BY clause must be the same as the CMP field identified in the SELECT clause. The following query is illegal, because the CMP that identified in the SELECT clause is not the same as the one used in the ORDER BY clause:

SELECT c.address.city
FROM Customer AS c
ORDER BY c.address.state

In the above query, we wanted a list of all the cities ordered by their state. Unfortunately, this is illegal. You can’t order by the state CMP field if you are selecting the city CMP field.