EJB QL is expressed in terms of the abstract persistence schema of an entity bean: its abstract schema name, CMP fields, and CMR fields. EJB QL uses the abstract schema names to identify beans, the CMP fields to specify values, and the CMR fields to navigate across relationships.
To discuss EJB QL, we will make use of the relationships among the Customer, Address, CreditCard, Cruise, Ship, Reservation, and Cabin EJBs defined in Chapter 7. Figure 8-1 is a class diagram that shows the direction and cardinality (multiplicity) of the relationships among these beans.
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.
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
.
The OBJECT( )
operator is used for single
identifiers in the select statement and not for path expressions.
While this convention makes little sense, it is required by the EJB
specifications. As a rule of thumb, if the select type is a solitary
identifier of an entity bean, it must be wrapped in an
OBJECT( )
operator. If the select type is a path
expression, OBJECT( )
is not necessary.
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
String
s. 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 Date
CMP 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.deckLevelIN
(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.stateIN
(?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.customersIS 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:
phone.number LIKE '617%
' True for “617-322-4151” |
False for “415-222-3523” |
cabin.name LIKE 'Suite _100
' True for “Suite A100” |
False for “Suite A233” |
phone.number NOT LIKE '608%
' True for “415-222-3523” |
False for “608-233-8484” |
someField.underscored LIKE '\_%
' True for “_xyz” |
False for “abc” |
someField.percentage LIKE '\%%
' True for “% XYZ” |
False for “ABC” |
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.
EJB QL has four functional expressions that allow for
simple String
manipulation and three functional
expressions for basic numeric operations. The
String
functions are:
CONCAT(String1, String2)
Returns the String
that results from concatenating
String1
and String2
.
LENGTH(String)
Returns an int
indicating the length of the string.
LOCATE(String1, String2 [, start])
Returns an int
indicating the position at which
String1
is found within
String2
. If it’s present,
start
indicates the character position in
String2
at which the search should start. Support
for the start
parameter is optional; some
containers will support it, it while others will not.
Don’t use it if you want to ensure the query is
portable.
SUBSTRING(String1, start, length)
Returns the String
consisting of length characters
taken from String1
, starting at the position given
by start
.
The start
and length
parameters
indicate positions in a String
as integer values.
You can use these expressions in the WHERE
clause
to refine the scope of the items selected. Here’s
how the LOCATE
and LENGTH
functions might be used:
SELECT OBJECT( c ) FROM Customer AS c WHERELENGTH(c.lastName)
> 6 ANDLOCATE( c.lastName, 'Monson')
> -1
This statement selects all the customers with
Monson
somewhere in their last name, but specifies
that the name must be longer than six characters. Therefore,
“Monson-Haefel” and
“Monson-Ares” evaluate to
true
, but
“Monson” returns
false
because it has only six characters.
The arithmetic functions in EJB QL may be applied to primitive as well as corresponding primitive wrapper types:
Aggregate functions are used with queries
that return a collection of values. They are fairly simple to
understand and can be handy, especially the COUNT( )
function. It’s important to understand
that aggregate functions can only be used with select methods, not
find methods. The find methods may only return EJB object references
(local or remote).
This function returns the number of items in the
query’s final result set. The return type is a
long
or java.util.Long
,
depending on whether it is the return type of the query method. For
example, the following query provides a count of all the customers
who live in Wisconsin:
SELECT COUNT( c ) FROM Customers AS c WHERE c.address.state = 'WI'
The COUNT( )
function can be used with an
identifier, in which case it always counting entities, or with path
expressions, in which case it counts either CMR fields or CMP fields.
For example, the following statement provides a count of all the Zip
codes that start with the characters
“554”:
SELECT COUNT(c.address.zip) FROM Customers AS c WHERE c.address.zip LIKE '554%'
In some cases, queries that count a path expression have a
corresponding query that can be used to count an identifier. For
example, the result of the following query, which counts Customers
instead of the zip
CMP field, is equivalent to the
previous query:
SELECT COUNT( c ) FROM Customers AS c WHERE c.address.zip LIKE '554%'
These functions can be used to find the largest or smallest value from a collection of any type of CMP field. They cannot be used with identifiers or paths that terminate in a CMR field. The result type will be the type of CMP field that is being evaluated. For example, the following query returns the highest price paid for a reservation:
SELECT MAX( r.amountPaid ) FROM Reservation AS r
The MAX( )
and MIN( )
functions
can be applied to any valid CMP value, including primitive types,
Strings, and even serializable objects. The result of applying the
MAX( )
and MIN( )
functions to
serializable objects is not specified, because there is no standard
way to determine which serializable object is greater or lesser than
another.
The result of applying the MAX( )
and
MIN( )
functions to a
String
CMP field depends on the underlying data
store. This has to do with the inherent problems associated with
String
type comparisons.
The AVG( )
and SUM( )
functions can only be applied to path expressions that terminate in a
numeric primitive field (byte
,
long
, float
, etc.) or one their
corresponding numeric wrappers (Byte, Long
,
Float
, etc.). The result of a query that uses the
SUM( )
function has the same type as the numeric
type it’s evaluating. The result type of the
AVG( )
function is a double
or
java.util.Double
, depending on whether it is used
in the return type of the select method.
For example, the following query uses the SUM( )
function to get the total amount paid by all customers for a specific
Cruise (specified by input parameter):
SELECT SUM( r.amountPaid) FROM Cruise c, IN( c.reservations) AS r WHERE c = ?1
The DISTINCT
operator can be used with any of the
aggregate functions to eliminate duplicate values. The following
query uses the DISTINCT
operator to count the
number of different Zip codes that match the
pattern:
SELECT DISTINCT COUNT(c.address.zip) FROM Customers AS c WHERE c.address.zip LIKE '554%'
The DISTINCT
operator first eliminates duplicate
Zip codes; if 100 customers live in the same area with the same Zip
code, their Zip code is only counted once. After the duplicates have
been eliminated, the COUNT( )
function counts the
number of items left.
Any CMP field with a null
value is automatically
eliminated from the result set operated on by the aggregate
functions. The COUNT( )
function also ignores CMP
values with null values. The aggregate functions AVG( )
, SUM( )
, MAX( )
,
and MIN( )
return null
when
evaluating an empty collection. For example, the following query
attempts to obtain the average price paid by customers for a specific
Cruise:
SELECT AVG( r.amountPaid) FROM Cruise As c, IN( c.reservations ) AS r WHERE c = ?1
If the Cruise specified by the input parameter has no reservations,
the collection on which the AVG( )
function
operates is empty (there are no reservations and therefore no amounts
paid). In this case, the select method returns
null
if it specified a
java.lang.Double
or
java.lang.Float
return type. If, however, it
returns the select method specified primitive type return value
(e.g., double or float), a
javax.ejb.ObjectNotFoundException
will be thrown.
The COUNT( )
function returns 0
(zero) when the argument it evaluates is an empty collection. If the
following query is evaluated on a Cruise with no reservations, the
result is 0
(zero) because the argument is an
empty collection:
SELECT COUNT( r ) FROM Cruise AS c, IN( c.reservations ) AS r WHERE c = ?1
To deploy these examples in an EJB container, see Exercise 8.2 in the Workbook.
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.
[25] To learn more about XML and the use of CDATA sections, see XML in a Nutshell by Elliotte Rusty Harold and W. Scott Means (O’Reilly).