 | CONTENTS |  |
Chapter 8. EJB 2.0 CMP: EJB QL
Find methods have been a part of the Enterprise JavaBeans specification since EJB 1.0. These methods are defined on the entity bean's local and remote home interfaces and are used for locating entity beans. All entity beans must have a findByPrimaryKey() method, which takes the primary key of the entity bean as an argument and returns a reference to an entity bean. For example, the Cruise EJB defines the standard primary key find method in its home interface as follows:
public CruiseHomeLocal extends javax.ejb.EJBLocalHome
{
public Integer create(String name,ShipLocal ship);
public CruiseLocal findByPrimaryKey(Integer key);
}
In addition to the mandatory findByPrimaryKey() method, entity bean developers may define as many custom find methods as they like. For example, the Cruise EJB might define a method, such as findByName(), for locating a Cruise with a specific name:
public CruiseHomeLocal extends javax.ejb.EJBLocalHome
{
public Integer create(String name,ShipLocal ship)
throws CreateException;
public CruiseLocal findByPrimaryKey(Integer key)
throws FinderException;
public CruiseLocal findByName(String cruiseName)
throws FinderException;
}
The option of defining custom find methods is nothing new, but until EJB 2.0 there was no standard way of defining how the find methods should work. The behavior of the findByPrimaryKey() method is obvious: find the entity bean with the same primary key. However, the behavior of custom find methods is not always obvious, so additional information is needed to tell the container how these methods should behave. EJB 1.1 didn't provide any standard mechanism for declaring the behavior of custom find methods, so vendors came up with their own query languages and methods. Consequently, the custom methods generally were not portable, and guesswork was required on the part of the deployer to determine how to properly execute queries against them. EJB 2.0 introduced the Enterprise JavaBeans Query Language (EJB QL)—a standard query language for declaring the behavior of custom find methods—and the new select methods. Select methods are similar to find methods, but they are more flexible and are visible to the bean class only. Find and select methods are collectively referred to as query methods in EJB 2.0.
EJB QL is a declarative query language that is similar to the Structured Query Language (SQL) used in relational databases, but it is tailored to work with the abstract persistence schema of entity beans in EJB 2.0.
EJB QL queries are defined in terms of the abstract persistence schema of entity beans and not the underlying data store, so they are portable across databases and data schemas. When an entity bean's abstract bean class is deployed by the container, the EJB QL statements are typically examined and translated into data access code optimized for that container's data store. At runtime, query methods defined in EJB QL usually execute in the native language of the underlying data store. For example, a container that uses a relational database for persistence might translate EJB QL statements into standard SQL 92, while an object-database container might translate the same EJB QL statements into an object query language.
EJB QL makes it possible for bean developers to describe the behavior of query methods in an abstract fashion, making queries portable across databases and EJB vendors. The EJB QL language is easy for developers to learn, yet precise enough to be interpreted into native database code. It is a fairly rich and flexible query language that empowers developers at development time, while executing in fast native code at runtime. However, EJB QL is not a silver bullet and is not without its problems, as we'll see later in this chapter.
8.1 Declaring EJB QL
EJB QL statements are declared in <query> elements in an entity bean's deployment descriptor. In the following listing, you can see that the findByName() method defined in the Cruise bean's local home interface has its own query element and EJB QL statement:
<ejb-jar>
<enterprise-beans>
<entity>
<ejb-name>CruiseEJB</ejb-name>
...
<reentrant>False</reentrant>
<abstract-schema-name>Cruise</abstract-schema-name>
<cmp-version>2.x</cmp-version>
<cmp-field>
<field-name>name</field-name>
</cmp-field>
<primkey-field>id</primkey-field>
<query>
<query-method>
<method-name>findByName</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>
SELECT OBJECT(c) FROM Cruise c WHERE c.name = ?1
</ejb-ql>
</query>
</entity>
</enterprise-beans>
</ejb-jar>
The <query> element contains two primary elements. The <query-method> element identifies the find method of the remote and/or local home interfaces, and the <ejb-ql> element declares the EJB QL statement. The <query> element binds the EJB QL statement to the proper find method. Don't worry too much about the EJB QL statement just yet; we'll cover that in detail starting in the next section.
Every entity bean that will be referenced in an EJB QL statement must have a special designator called an abstract schema name, which is declared by the <abstract-schema-name> element. The <abstract-schema-name> elements must have unique names; no two entity beans may have the same abstract schema name. In the entity element that describes the Cruise EJB, the abstract schema name is declared as Cruise. The <ejb-ql> element contains an EJB QL statement that uses this identifier in its FROM clause.
In Chapter 7, you learned that the abstract persistence schema of an entity bean is defined by its <cmp-field> and <cmr-field> elements. The abstract schema name is also an important part of the abstract persistence schema. EJB QL statements are always expressed in terms of the abstract persistence schemas of entity beans. EJB QL uses the abstract schema names to identify entity bean types, the container-managed persistence (CMP) fields to identify specific entity bean data, and the container-managed relationship (CMR) fields to create paths for navigating between entity beans.
8.2 The Query Methods
There are two main types of query methods: find methods and select methods. These are discussed in the following sections.
8.2.1 Find Methods
Find methods are invoked by EJB clients (applications or beans) in order to locate and obtain the remote or local EJB object references to specific entity beans. For example, you might call the findByPrimaryKey() method on the Customer EJB's home interface to obtain a reference to a specific Customer bean.
Find methods are always declared in the local and remote home interfaces of an entity bean. As you have already learned, every home interface must define a findByPrimaryKey() method; this is a type of single-entity find method. Specifying a single remote or local return type for a find method indicates that the method locates only one bean. findByPrimaryKey() obviously returns only one remote reference, because there is a one-to-one relationship between a primary key's value and an entity. Other single-entity find methods can also be declared. For example, in the following code segment the Customer EJB declares several single-entity find methods, each of which supports a different query:
public interface CustomerHomeRemote extends javax.ejb.EJBHome {
public CustomerRemote findByPrimaryKey(Integer primaryKey)
throws javax.ejb.FinderException, java.rmi.RemoteException;
public CustomerRemote findByName(String lastName, String firstName)
throws javax.ejb.FinderException, java.rmi.RemoteException;
public CustomerRemote findBySSN(String socialSecurityNumber)
throws javax.ejb.FinderException, java.rmi.RemoteException;
}
Bean developers can also define multi-entity find methods, which return a collection of EJB objects. The following listing shows a couple of multi-entity find methods:
public interface CustomerHomeLocal extends javax.ejb.EJBLocalHome {
public CustomerLocal findByPrimaryKey(Integer primaryKey)
throws javax.ejb.FinderException;
public Collection findByCity(String city,String state)
throws javax.ejb.FinderException;
public Collection findByGoodCredit()
throws javax.ejb.FinderException;
}
To return several references from a find method, you must use a java.util.Collection type.[1] A find method that uses this return type may have duplicates. To avoid duplicates, you can use the keyword DISTINCT in the EJB QL statement associated with the find method. This technique is explained in more detail in Section 8.3.4 later in this chapter. Multi-entity finds return an empty Collection if no matching beans are found.
Enterprise JavaBeans specifies that all query methods (find or select) must be declared as throwing the javax.ejb.FinderException. Find methods that return a single remote reference throw a FinderException if an application error occurs and a javax.ejb.ObjectNotFoundException if a matching bean cannot be found. The ObjectNotFoundException is a subtype of FinderException and is thrown only by single-entity find methods.
Every find method declared in the local or remote home interface of a CMP 2.0 entity bean must have a matching query declaration in the bean's deployment descriptor. The following snippet from the Customer EJB's deployment descriptor shows declarations of two find methods, findByName() and findByGoodCredit(), from the earlier examples:
<query>
<query-method>
<method-name>findByName</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>
SELECT OBJECT(c) FROM Customer c
WHERE c.lastName = ?1 AND c.firstName = ?2
</ejb-ql>
</query>
<query>
<query-method>
<method-name>findByGoodCredit</method-name>
<method-params/>
</query-method>
<ejb-ql>
SELECT OBJECT(c) FROM Customer c
WHERE c.hasGoodCredit = TRUE
</ejb-ql>
</query>
The query elements in the deployment descriptor allow the bean developer to associate EJB QL statements with specific find methods. When the bean is deployed, the container attempts to match the find method declared in each of the query elements with find methods in the entity bean's home interfaces. This is done by matching the values of the <method-name> and <method-params> elements with method names and parameter types (ordering is important) in the home interfaces.
When two find methods in the local and remote home interfaces have the same method name and parameters, the query declaration will apply to both of the methods. The container will return the proper type for each query method: the remote home will return one or more remote EJB objects, and the local home will return one or more local EJB objects. This allows you to define the behavior of both the local and remote home find methods using a single <query> element, which is convenient if you want local clients to have access to the same find methods as remote clients.
The <ejb-ql> element specifies the EJB QL statement for a specific find method. You may have noticed that EJB QL statements can use input parameters (e.g., ?1, ?2, ... ?n), which are mapped to the <method-param> of the find method, as well as literals (e.g., TRUE). The use of input parameters and literals will be discussed in more detail throughout this chapter.
With the exception of findByPrimaryKey() methods, all single-entity and multi-entity find methods must be declared in <query> elements in the deployment descriptor. Query declarations for findByPrimaryKey() methods are not necessary and, in fact, are forbidden. It's obvious what this method should do, and you may not try to change its behavior.
8.2.2 Select Methods
Select methods are similar to find methods, but they are more versatile and can be used only internally, by the bean class. In other words, select methods are private query methods; they are not exposed to an entity bean's clients through the home interfaces.
Another difference between the select and find methods is the transaction context under which they execute. The select method executes in the transaction context of the business or callback method that is using it, while the find methods execute according to their own transaction attributes, as specified by the bean provider.
Select methods are declared as abstract methods using the naming convention ejbSelect<METHOD-NAME>. The following code shows four select methods declared in the AddressBean class:
public class AddressBean implements javax.ejb.EntityBean {
...
public abstract String ejbSelectMostPopularCity()
throws FinderException;
public abstract Set ejbSelectZipCodes(String state)
throws FinderException;
public abstract Collection ejbSelectAll()
throws FinderException;
public abstract CustomerLocal ejbSelectCustomer(AddressLocal addr)
throws FinderException;
...
Select methods can return the values of CMP fields. The ejbSelectMostPopularCity() method, for example, returns a single String value, the name of the city referenced by the most Address EJBs.
To return several references from a select method, you must declare the return type to be either a java.util.Collection or java.util.Set.[2] A select method that uses a Set return type will not have duplicate values, while a Collection return type may have duplicates. Multi-entity selects return an empty Collection or Set if no matching beans are found. The ejbSelectZipCodes() method returns a java.util.Set of String values: a unique collection of all the Zip Codes declared for the Address EJBs for a specific state.
Like find methods, select methods can declare zero or more arguments, which are used to limit the scope of the query. The ejbSelectZipCodes() and ejbSelectCustomer() methods both declare arguments used to limit the scope of the results. These arguments are used as input parameters in the EJB QL statements assigned to the select methods.
Select methods can return local or remote EJB objects. For single-entity select methods, the type is determined by the return type of the ejbSelect() method. The ejbSelectCustomer() method, for example, returns a local EJB object, the CustomerLocal. This method could easily have been defined to return a remote EJB object by changing the return type to the Customer bean's remote interface (CustomerRemote). Multi-entity select methods, which return a collection of EJB objects, return local EJB objects by default. However, the bean provider can override this default behavior using a special element, the <result-type-mapping> element, in the select method's <query> element.
The following portion of an XML deployment descriptor declares two of the select methods from the above example. Notice that they are exactly the same as the find method declarations. Find and select methods are declared in the same part of the deployment descriptor, within a <query> element inside an <entity> element:
<query>
<query-method>
<method-name>ejbSelectZipCodes</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>
SELECT a.homeAddress.zip FROM Address AS a
WHERE a.homeAddress.state = ?1
</ejb-ql>
</query>
<query>
<query-method>
<method-name>ejbSelectAll</method-name>
<method-params/>
</query-method>
<result-type-mapping>Remote</result-type-mapping>
<ejb-ql>
SELECT OBJECT(a) FROM Address AS a
</ejb-ql>
</query>
The name given in each <method-name> element must match one of the ejbSelect<METHOD-NAME>() methods defined in the bean class. This is different from find methods of CMP 2.0 beans, which do not have a corresponding ejbFind method in the bean class. For find methods, we use the method name in the local or remote home interface. Select methods, on the other hand, are not declared in the local or remote home interface, so we use the ejbSelect() method name in the bean class.
Select methods can return local or remote EJB objects. The default is to return local EJB object types for both single-entity and multi-entity select methods. However, the bean provider can override this default behavior using a special element, <result-type-mapping>, in the select method's <query> element. The value of <result-type-mapping> can be either Remote or Local. A value of Local indicates that the select method should return local EJB objects; Remote indicates remote EJB objects. If the <result-type-mapping> element is not declared, the default is Local. For single-entity select, the actual return type of the ejbSelect() method must match the <result-type-mapping>. For example, if a single-entity ejbSelect() method returns an EJBObject type, the <result-type-mapping> must be Remote. In the previous example, the <result-type-mapping> in the <query> element for the ejbSelectAll() method is declared as Remote, which means the query should return remote EJB object types (i.e., remote references to the Address EJB).[3]
Select methods are not limited to the context of any specific entity bean. They can be used to query across all the entity beans declared in the same deployment descriptor. Select methods may be used by the bean class from its ejbHome() methods, from any business methods, or from the ejbLoad() and ejbStore() methods. In most cases, select methods will be called from ejbHome() or from business methods in the bean class. The ejbHome(), ejbLoad(), and ejbStore() methods are covered in more detail in Chapter 11.
The most important thing to remember about select methods is that while they can do anything find methods can and more, they can be used only by the entity bean class that declares them, not by the entity bean's clients.
8.3 EJB QL Examples
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.
Figure 8-1. Titan Cruises class diagram
8.3.1 Simple Queries
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 values that will be returned. In this case, it's 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.
8.3.2 Simple Queries with Paths
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, as follows:
SELECT c.lastName FROM Customer AS c
The SELECT clause uses a simple path to select the Customer EJB's lastName CMP 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. This navigation leverages the same syntax as the Java programming language, specifically the dot (.) navigation operator. For example, the previous EJB QL statement is based on the Customer EJB's deployment descriptor:
<ejb-jar>
<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. For example, the following EJB QL statement selects all the CreditCard EJBs from all the Customer EJBs:
SELECT c.creditCard FROM Customer 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 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. Using paths in EJB QL is similar to navigating through object references in the Java language.
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. The following EJB QL statement selects all the addresses of all the credit card companies:
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. For example, 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;
...
}
It would be illegal to attempt to navigate to one of the ZipCode class's instance fields:
// this is illegal SELECT c.homeAddress.zip.mainCode FROM Customer AS c
CMP fields cannot be further decomposed and navigated by paths. All CMP fields are considered opaque.
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 the CMR field 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:
SELECT c.reservations.cruise FROM Customer AS c
If you think about it, this limitation makes sense. You cannot use a navigation operator (.) in Java to access elements of a java.util.Collection object either. For example, you cannot do the following (assume getReservations() returns a java.util.Collection type):
// this is illegal in the Java programming language customer.getReservations().getCruise();
Referencing the elements of a collection-based relationship field is possible in EJB QL, but it requires the use of an IN operator and an identification assignment in the FROM clause, which are discussed next.
8.3.3 The IN Operator
Many relationships between entity beans are collection-based, and being able to access and select 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 EJB QL statement will select 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. Notice that the identifier c, which was declared first, was subsequently used in the IN operator to define the identifier r.
|
Identification chains, in which subsequent identifications depend on previous identifications, can become very long. The following EJB QL 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
Please
refer to Workbook Exercise 8.1, Simple EJB QL Statements. This workbook is
available free, in PDF format, at
http://www.oreilly.com/catalog/entjbeans3/workbooks.
8.3.4 Using DISTINCT
The DISTINCT keyword ensures that the query does not return duplicates. This is especially valuable when applied to EJB QL statements used by find methods. Find methods in CMP 2.0 have only one return type, java.util.Collection, which may return duplicates. However, if the keyword DISTINCT is used, the query results of the find method will not contain 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 res, IN (res.customers) cust
The previous find method and associated EJB QL statement will return all Customer EJBs with reservations, but if a Customer EJB has more than one reservation, there will be duplicate references to that Customer EJB in the result Collection. Using the DISTINCT keyword will ensure that each Customer EJB is represented only once in the result Collection:
SELECT DISTINCT OBJECT( cust ) FROM Reservation 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 (no duplicates) result will be returned whether the DISTINCT keyword is used or not.
The Set type is explicitly defined as having no duplicates. Using the Set return type in combination with the DISTINCT keyword is redundant, but it is not illegal.
8.3.5 The WHERE Clause and Literals
You can also use literal values in EJB QL 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 could define an EJB QL statement to select 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 the American Express credit card:
SELECT OBJECT( c ) FROM Customer AS c WHERE c.creditCard.organization = 'American Express'
Path expressions are always used in the WHERE clause in the same way they're used 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
8.3.6 The WHERE Clause and Input Parameters
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 is designed to select all the customers that 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 would use 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 interface:
public interface CruiseLocal extends javax.ejb.EJBLocalObject {
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.
8.3.7 The WHERE Clause and Operator Precedence
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. The operators at the top of the list have the highest precedence and are evaluated first:
-
Navigation operator (.)
-
Arithmetic operators:
- +, - unary
- *, / multiplication and division
- +, - addition and subtraction
-
Comparison operators:
- =, >, >=, <, <=, <> (not equal)
- LIKE, BETWEEN, IN, IS NULL, IS EMPTY, MEMBER OF
-
Logical operators:
- NOT, AND, OR
8.3.8 The WHERE Clause and CDATA Sections
EJB QL statements are declared in XML deployment descriptors. XML uses the greater than (>) and less than (<) characters as delimiters for tags, so using these symbols in the EJB QL statements will cause 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 r
WHERE r.amountPaid > ?1
</ejb-ql>
</query>
To avoid this problem, you must place the EJB QL statement in a CDATA section:
<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 r
WHERE r.amountPaid > 300.00
]]>
</ejb-ql>
</query>
The CDATA section takes the form <![CDATA[ literal-text ]]>. 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.[4]
8.3.9 The WHERE Clause and Arithmetic Operators
The arithmetic operators allow a query to perform arithmetic in the process of doing a comparison. In EJB QL, 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 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, which is covered later in Section 8.3.19.
8.3.10 The WHERE Clause and Logical Operators
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 may not, however,behave like their Java language counterparts && and ||. EJB QL does not specify whether the righthand operands are evaluated conditionally. For example, the && operator in Java evaluates its righthand operand only if the lefthand operand is true. Similarly, the || logical operator evaluates the righthand operand only if the lefthand operand is false. We can't make the same assumption for the AND and OR operators in EJB QL. Whether these operators evaluate righthand 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.
8.3.11 The WHERE Clause and Comparison Symbols
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 s WHERE s.tonnage >= 80000.00 AND s.tonnage <= 130000.00
Only the = and <> (not equal) operators may be used on String, boolean, and EJB object references. The greater-than and less-than symbols (>, >=, <, <=) can be used only on numeric values. It would be illegal, for example, to use the greater-than or less-than symbols to compare two Strings. There is no mechanism to compare Strings in this way in EJB QL.
8.3.12 The WHERE Clause and Equality Semantics
While it's 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.
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 EJBLocalObject {
public Collection findByCustomer(CustomerLocal customer)
throws FinderException;
...
}
The matching EJB QL statement uses the customer argument as an input parameter:
SELECT OBJECT( r ) FROM Reservation r, IN ( r.customers ) 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 and not a java.util.Date CMP field. The input value or literal must also be a long value.
8.3.13 The WHERE Clause and BETWEEN
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 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 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 comparative symbols, like this:
SELECT OBJECT( s ) FROM Ship s WHERE s.tonnage < 80000.00 OR s.tonnage > 130000.00
8.3.14 The WHERE Clause and IN
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 string values and can be used only with operands that evaluate to string 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 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 c
WHERE c.homeAddress.city 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.
8.3.15 The WHERE Clause and IS NULL
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 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 c WHERE c.homeAddress IS NOT NULL
When null fields appear in comparison operations such as IN and BETWEEN, they can have side effects. In most cases, evaluating a null field in a comparison operation (other than IS NULL) produces in an UNKNOWN result. Unknown evaluations throw the entire EJB QL result set into question; since we cannot predict the outcome of the EJB QL statement, it is unreliable. 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.
8.3.16 The WHERE Clause and IS EMPTY
The IS EMPTY operator allows the query to test if 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 will return 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 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 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 r, IN( r.customers ) 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. In fact, it's an illegal EJB QL statement, 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 will be excluded because its customers elements cannot be assigned the c identifier.
8.3.17 The WHERE Clause and MEMBER OF
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 crs, IN (crs.reservations) res, Customer 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 crs, IN (crs.reservations) res, Customer 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.
8.3.18 The WHERE Clause and LIKE
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 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 % and _ 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 would evaluate 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. Only the input parameter's type is known at deployment time. How would you compare a CMP field, whose value varies, with an arbitrary input parameter using a comparison pattern? It's just not possible. The comparison pattern is composed of special comparison characters (% and _ ) as well as regular characters. The comparison pattern must be known at deployment time in order to match it against the variable CMP fields at runtime.
8.3.19 The WHERE Clause and Functional Expressions
EJB QL has six functional expressions that allow for simple String manipulation and two 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.
- 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 help refine the scope of the items selected. Here is an example of how the LOCATE and LENGTH functions might be used:
SELECT OBJECT( c ) FROM Customer c WHERE LENGTH(c.lastName) > 6 AND LOCATE( c.lastName, 'Monson') > -1
This EJB QL statement selects all the customers with Monson somewhere in their last name, but specifies that the name must be longer than 6 characters. Therefore, "Monson-Haefel" and "Monson-Ares" evaluate to true, but "Monson" returns false because it has only 6 characters.
The arithmetic functions in EJB QL are:
- ABS(number)
-
Returns the absolute value of a number (int, float, or double)
- SQRT(double)
-
Returns the square root of a double
Please
refer to Workbook Exercise 8.2, Complex EJB QL Statements. This workbook is
available free, in PDF format, at
http://www.oreilly.com/catalog/entjbeans3/workbooks.
8.4 Problems with EJB QL
EJB QL is a powerful new tool that promises to improve performance, flexibility, and portability of entity beans in container-managed persistence, but it has some design flaws and omissions.
8.4.1 The OBJECT( ) Operator
The use of the OBJECT() operator is unnecessary and cumbersome and provides little or no value to the bean developer. It's trivial for EJB vendors to determine when an abstract schema type is the return value, so the OBJECT() operator provides little real value during query translation. In addition, the OBJECT() operator is applied haphazardly. It's required when the return type is an abstract schema identifier, but not when a path expression of the SELECT clause ends in a CMR field. Both return an EJB object reference, so the use of OBJECT() in one scenario and not the other is illogical and confusing.
When questioned about this, Sun replied that several vendors had requested the use of the OBJECT() operator because it will be included in the next major release of the SQL programming language. EJB QL was designed to be similar to SQL because SQL is the query language that is most familiar to developers, but this doesn't mean it should include functions and operations that have no real meaning in Enterprise JavaBeans.
8.4.2 The Missing ORDER BY Clause
Soon after you begin using EJB QL, you will probably realize that it's missing a major component, the ORDER BY clause. Requesting ordered lists is extremely important in any query language, and most major query languages, including SQL and object query languages, support this function. The ORDER BY clause has a couple of big advantages:
-
It provides a clear mechanism for the bean developer to communicate her intentions to the EJB QL interpreter. The ORDER BY clause is unambiguous; it states exactly how a collection should be ordered (by which attributes, ascending or descending, etc.). Given that EJB QL's purpose is to clearly describe the behavior of the find and select operations in a portable fashion, ORDER BY is clearly a significant omission.
-
In most cases, it would allow EJB QL interpreters used by EJB vendors to choose an ordering mechanism that is optimized for a particular database. Allowing the resource to perform the ordering is more efficient than having the container do it after the data is retrieved.[5] However, even if the application server vendor chooses to have the container do the ordering, the ORDER BY clause still provides the EJB vendor with a clear indication of how to order the collection. It's up to the vendor to choose how to support the ORDER BY clause. For databases and other resources that support it, ordering can be delegated to the resource. For those resources that don't support ordering, it can be performed by container. Without an ORDER BY clause, the deployer has to manipulate collections manually or force the container's collection implementations to do the ordering. These two options are untenable in real-world applications in which performance is critical.
According to Sun, the ORDER BY clause was not included in this version of the specification because of problems dealing with the mismatch in ordering behavior between the Java language and databases. The example they gave had to do with string values. The semantics of ordering strings in a database may be different than those of the Java language. For example, Java orders String types according to character sequence and case (uppercase vs. lowercase). Different databases may or may not consider case while ordering, or they may not consider leading or trailing whitespace. In light of these possible differences, it seems as though Sun has a reasonable argument, but only for limiting the portability of ORDER BY, not for eliminating its use all together. EJB developers can live with less than perfect portability of the ORDER BY clause, but they cannot live without the ORDER BY clause altogether.
Another argument against using the ORDER BY clause is that it necessitates the use of java.util.List as a return type. Although the List type is supposed to be used for ordered lists, it also allows developers to place items in a specific location in the list, which in EJB would mean a specific location in the database. This is nearly impossible to support, and so appears to be a reasonable argument against using the ORDER BY clause. However, this reasoning is flawed, because there is nothing preventing EJB from using the simple Collection type for ordered queries. The understanding would be that the items are ordered, but only as long as the collection is not modified (i.e., elements are not added or removed after it is obtained). Another option is to require that EJB QL statements that use the ORDER BY clause return a java.util.Enumeration type. This seems perfectly reasonable, since the Collection received by a select or find operation shouldn't be manipulated anyway.
While Sun has not defined EJB QL as supporting the ORDER BY clause, some EJB servers (such as BEA's WebLogic) are expected to support it anyway using nonstandard extensions. This support is both welcome and problematic, because nonstandard extensions to EJB QL can result in nonportable enterprise beans.
8.4.3 Lack of Support for Date
EJB QL doesn't provide native support for the java.util.Date class. This is not acceptable. The java.util.Date class should be supported as a natural type in EJB QL. It should be possible, for example, to do comparisons with Date CMP fields and literal and input parameters using comparison operators (=, >, >=, <, <=, <>). It should also be possible to introduce common date functions so that comparisons can be done at different levels, such as comparing the day of the week (DOW()) or month (MONTH()), etc. Of course, including the Date as a supported type in EJB QL is not trivial and problems with interpretation of dates and locales would need to be considered, but the failure to address Date as a supported type is a significant omission.
8.4.4 Limited Functional Expressions
While the functional expressions provided by EJB QL will be valuable to developers, many other functions should also have been included. For example, COUNT() is used often in real-world applications, but it is not currently supported in EJB QL. Other functions that would be useful include (but are not limited to) CAST() (useful for comparing different types), MAX() and MIN(), SUM(), and UPPER(). In addition, if support for java.util.Date was included in EJB QL, other date functions, such as DOW(), MONTH(), etc., could be added.
[1] In CMP 2.0, java.util.Collection is the only collection type supported for multi-entity find methods. EJB 1.1 CMP and EJB 2.0 BMP also support java.util.Enumeration.
[2] Other collection types, such as java.util.List and java.util.Map, may be added in future versions.
[3] This is illustrative. As a developer, it is unlikely (although possible) that you would define a remote interface for the Address EJB, because it is too fine-grained for use by remote clients.
[4] 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).
[5] It was suggested that EJB vendors could provide ordering mechanically, by having the collection sorted after it's obtained. This is a rather ridiculous expectation, since it would require collections to be fully manifested after the query completes, eliminating the advantages of lazy loading.
| CONTENTS | |