ONJava.com    
 Published on ONJava.com (http://www.onjava.com/)
 See this if you're having trouble printing code examples


Learning EJB QL

by Jeelani B. Shaik
09/19/2001

Why an EJB Query Language?
The specification for Enterprise Java Beans 1.1 doesn't offer a standard way to define queries for finder methods in entity beans with container-managed persistence (CMP). So EJB container providers have defined their own query syntax for the finder methods of entity beans. For example, BEA's WebLogic Application Server v. 5.1 defines a query language called WLQL.

Because of this, developers have to redefine queries for the finder methods whenever an application is moved from one vendor's application server to another's. Obviously, this makes applications built using CMP less portable.

What's more, EJB 1.1 offered no standard way to define a query for an entity bean with CMP to navigate with other entity beans in a variety of code contexts, relationships, or associations. There was no proper mechanism to define queries to navigate from one entity bean to its dependent classes and the member variables of those dependent classes.

EJB 2.0 deals with these shortcomings by defining the EJB Query Language. EJB QL is based on the SQL-92 specification for defining various finder and select methods of entity beans with CMP. The EJB QL query string consists of three clauses: SELECT, FROM, and WHERE. Among other things, EJB QL offers a standard way to define relationships between entity beans and dependent classes by introducing abstract schema types and relationships in the deployment descriptor. EJB QL also defines queries for navigation using abstract schema names and relationships.

What is EJB QL?

The following syntax is the basic format of the EJB QL query:

EJB QL ::=  select_clause from_clause  [ where_clause]

The SELECT clause was an optional clause in the earlier EJB 2.0 proposed final draft (released on 23rd October, 2000). In the final draft released on 24th April 2001, the SELECT clause is made mandatory.

The EJB QL must always contain SELECT and FROM clauses. The WHERE clause is optional (see sidebar). The FROM clause provides declarations for the identification variables based on abstract schema name, for navigating through the schema. The SELECT clause uses these identification variables to define the return type of the query, and the WHERE clause defines the conditional query.

The query for EJB QL is defined in the deployment descriptor using the <query> tag as shown below:

<query>
	<query-method>
		<method-name></method-name>
<method-params> 
<method-param></method-param>
		</method-params>	
	</query-method>
<result-type-mapping></result-type-mapping>
	<ejb-ql></ejb-ql>
</query>

You specify the name of the finder or select method in <method-name> and the parameters in <method-param>. The <result-type-mapping> indicates the return type, and can contain either Local (the default) or Remote values. The query string is in the <ejb-ql> tag.

I will give a brief description of the finder and select methods since these methods use EJB QL to define the queries. EJB 2.0 defines the finder and select methods for entity beans. The select method is a new addition to the specification.

Finder Methods: Finder methods get either a single or a collection of entity bean instances from the persistence store through a relational database. These methods define the home interface(s) of an entity bean. Hence, they are exposed to the client. A home interface can either be a Remote Home interface, EJBHome, or a Local Home interface, EJBLocalHome. The return type of the finder method defined in the remote home interface is either the entity bean's remote interface or a collection of objects implementing the entity bean's remote interface. The return type of the finder method defined in the local home interface is either the entity bean's local interface or a collection of objects implementing the entity bean's local interface. For example:

// Remote Home Interface
public interface OrderHome extends javax.ejb.EJBHome {
    ...
  public Order findByPrimaryKey(int orderId) throws FinderException,RemoteException;
  public Order findByBiggestOrder() throws FinderException,RemoteException;
public java.util.Collection findAllOrders(String supplierName) throws FinderException,RemoteException;
}
// Local Home Interface
public interface OrderHome extends javax.ejb.EJBLocalHome {
    ...
  public Order findByPrimaryKey(int orderId) throws FinderException;
  public Order findBiggestOrder() throws FinderException;
public java.util.Collection findAllOrders(String supplierName) throws FinderException;
}

Comment on this articleShare your experiences with this new database-driven component architecture for Java.
Post your comments

Select Methods: Select methods exist in the entity bean as a special type of query method. They are not declared in the home interface; therefore, they are not exposed to the client. The finder methods are not useful to access cmp-field, or any remote interface instance defined in cmr-field. Using select methods, an entity bean returns an instance of cmp-field type, or the remote interfaces represented by the cmr-field.

Select methods are usually two types.

Example:

public abstract class OrderBean implements javax.ejb.EntityBean {
...
public abstract java.util.Collection ejbSelectAllOrderedProducts(Date date)
throws FinderException;
...
public abstract java.util.Collection ejbSelectAllOrderedProductsInEntity(Date date)
throws FinderException;
}

The ejbSelect<METHOD> is not associated with a particular instance of the entity bean. So in the above example, the ejbSelectAllOrderProducts returns a collection of all products associated with all orders. Therefore, the ejbSelect<METHOD>InEntity is specific to the entity instance executed. The above ejbSelectAllOrderProductsInEntity returns all of the products associated with that instance of OrderBean.

Here the Order example explains each clause in detail. The relationship between OrderEJB, LineItemEJB, ProductEJB and AddressEJB are shown in the following diagram:

Diagram.

The relationships, as defined in the deployment descriptor, are shown below:

<relationships>
<!--
ONE-TO-MANY: Order LineItem
-->

<ejb-relation>
  <ejb-relation-name>Order-LineItem</ejb-relation-name>
  <ejb-relationship-role>
    <ejb-relationship-role-name>
    order-has-lineitems
    </ejb-relationship-role-name>
    <multiplicity>One</multiplicity>
    <relationship-role-source>
      <ejb-name>OrderEJB</ejb-name>
    </relationship-role-source>
    <cmr-field>
      <cmr-field-name>lineItems</cmr-field-name>
      <cmr-field-type>java.util.Collection
      </cmr-field-type>
    </cmr-field>
  </ejb-relationship-role>

  <ejb-relationship-role>
    <ejb-relationship-role-name>lineitem-belongsto-order
    </ejb-relationship-role-name>
    <multiplicity>Many</multiplicity>
    <cascade-delete/>
    <relationship-role-source>
      <ejb-name>LineItemEJB</ejb-name>
    </relationship-role-source>
    <cmr-field>
      <cmr-field-name>order</cmr-field-name>
    </cmr-field>
  </ejb-relationship-role>
</ejb-relation>
<!--
ONE-TO-MANY unidirectional relationship:
Product is not aware of its relationship with LineItem
-->
<ejb-relation>
  <ejb-relation-name>Product-LineItem</ejb-relation-name>
  <ejb-relationship-role>
    <ejb-relationship-role-name>product-has-lineitems</ejb-relationship-role-name>
    <multiplicity>One</multiplicity>
    <relationship-role-source>
      <ejb-name>ProductEJB</ejb-name>
    </relationship-role-source>
    <!-- since Product does not know about LineItem there is no cmr field in Product for accessing Lineitem -->
  </ejb-relationship-role>
  <ejb-relationship-role>
    <ejb-relationship-role-name>lineitem-for-product</ejb-relationship-role-name>
    <multiplicity>Many</multiplicity>
    <relationship-role-source>
      <ejb-name>LineItemEJB</ejb-name>
    </relationship-role-source>
    <cmr-field>
      <cmr-field-name>product</cmr-field-name>
    </cmr-field>
  </ejb-relationship-role>
</ejb-relation>
</relationships>

FROM Clause

The FROM clause defines the domain of the query by declaring the identification variables. Identification variables cannot be declared in the SELECT and WHERE clauses. Instead, the SELECT and WHERE clauses can use only those identification variables defined in the FROM clause. You can define multiple identification variables in the FROM clause.

An identification variable's name should not be the same as abstract-schema-name or ejb-name, which is reserved for future use. Also, an identification variable must not be a reserved identifier. The reserved identifiers are:

SELECT, FROM, WHERE, DISTINCT, OBJECT, NULL, TRUE, FALSE, NOT, AND, OR, BETWEEN, LIKE, IN, AS, UNKNOWN, EMPTY, MEMBER, OF and IS.

Any valid identifier may be used as an identification variable, though there are few restrictions (see sidebar). Identification variables are case insensitive.

The identification variable declaration consists of either a range variable declaration or a collection member declaration. Next, we will look at a simple query string which uses both the range and collection variable declarations from the FROM clause.

For example, to select all orders containing Floppy Drive products, the query is:

SELECT OBJECT(o) FROM Order o, IN (o.lineItems) li
WHERE li.product.product_type='Floppy Drive'

Here the FROM clause declares the identifier "o" as a range variable and "li" as a collection member variable. A range variable declares the abstract schema type, which uses the reserved identifier, AS.

A declared collection member variable uses the following:

The range variable "o" designates the abstract schema type, Order. Similarly, the identification variables: "li" is the abstract schema type, LineItem, and li.product is the abstract schema type, Product. The expression li.product.product_type in the WHERE clause is a java.lang.String type. Since all clauses are evaluated from left to right in EJB QL, the identification variable "li" utilizes the results of the navigation on "o".

The identifier OBJECT in the SELECT clause is required, because the OBJECT operator must qualify all stand-alone identification variables in the SELECT clause.

You may also declare a range variable using the optional identifier, AS. Therefore, the FROM clause in the above query becomes:

FROM Order AS o, IN (o.lineItems) li

Also, you may define more than one range variable in the FROM clause. For example:

FROM Order AS o, IN (o.lineItems) li, Product p

WHERE Clause

The WHERE clause defines conditional expressions to select objects or values that satisfy the expression.

For example, a WHERE clause follows:

Where_clause ::= WHERE conditional_expression

All of the identification variables in a WHERE clause in EJB QL must be declared from a FROM clause. You may also pass input parameters for the finder and select methods. The input parameters are only in the WHERE clause of a query.

Input parameters are designated by a question mark (?) prefix, followed by a one-based index of the parameter in the method declaration (i.e., ?1, ?2).

public abstract class OrderBean implements javax.ejb.EntityBean {
...
//method-a
public abstract java.util.Collection ejbSelectLineItems(int quantity)
throws FinderException;
...
//method-b
public abstract java.util.Collection ejbSelectAllProducts(String product_type, double price)
throws FinderException;
}

For example, the select method query for selecting Line Items (method-a) is:

SELECT OBJECT (o) FROM Order AS o IN (o.lineItems) li
WHERE li.quantity = ?1

Similarly, the select method query for choosing all products based on name and price is:

SELECT OBJECT (o) FROM Order AS o IN(o.lineItems) li
WHERE li.product.product_type=?1 AND li.product.price=?2

The query for method-a is in the following deployment descriptor:

<query>
  <description>
Method to find order specified no of lineItems</description>
  <query-method>
    <method-name>ejbSelectLineItems</method-name>
    <method-params>
      <method-param>int</method-param>
    </method-params>  
  </query-method>
<result-type-mapping>Local</result-type-mapping>
  <ejb-ql>
SELECT OBJECT (o) FROM Order AS o IN (o.lineItems) li
WHERE li.quantity = ?1
</ejb-ql>
</query>

In the same way, you may define the WHERE clause with input parameters.

The number of distinct input parameters in an EJB QL query must be the same as the number of input parameters for the finder and select methods. It is not required to use all of the input parameters for the finder or select methods in a query, though.

You can also pass an input parameter that corresponds to a particular EJBObject or EJBLocalObject. Containers map these input parameters to the abstract-schema-type values.

Next, I will show the various comparison operators available for use with the WHERE clause. In addition to the navigation operator (.) in the queries above, EJB QL supports the fundamental arithmetic operators (i.e., unary, multiplication and division, addition and subtraction), comparison operators (i.e., =, >, >=, <,<=, <>) and logical operators (i.e., NOT,OR, AND).

You can also use the comparison operators BETWEEN or NOT BETWEEN in a query. For example, the query to select all Line Items with a quanitity between 100 and 200 is shown here:

SELECT  OBJECT (li) FROM lineItems AS li
WHERE li.quantity BETWEEN 100 and 200

Using comparison operators >= and <=, the WHERE clause is now in the following expression:

li.quantity >= 100 AND li.quantity <= 200

The following query selects all Line Items with a quantity less than 100 or more than 200:

SELECT  OBJECT (li) FROM lineItems AS li
WHERE li.quantity NOT BETWEEN 100 and 200
			(or)
SELECT  OBJECT (li) FROM lineItems AS li
WHERE li.quantity < 100 AND li.quantity > 200

EJB QL also supports the IN and LIKE expressions. For example, to select the address(es) of an office in various cities, the query expression of the WHERE clause is:

address.city IN ('San Jose', 'New York', 'Florida')

The expression results are true for Florida and false for Texas.

Usage of NOT IN is just the opposite of the above:

address.city NOT IN ('San Jose', 'New York', 'Florida')

Here, the expression results are true for Texas and false for Florida.

An IN expression must contain at least one string-literal in the comma-separated string literal list. You use the comparison operator [NOT] LIKE in the WHERE clause to select a particular value.

The syntax of the LIKE expression is below:

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

The single_valued_path_expression must result in a String value. You use any string literal in the pattern-value. An underscore (_) represents any single character and a percent (%) any sequence of characters, including an empty sequence. The escape-character is a single character string literal, and is for escaping the special meaning of underscore and percent characters in pattern-value.

For example, the following query selects all employees whose names start with CHRIS:

SELECT OBJECT (emp) FROM employee as emp
WHERE emp.name LIKE 'CHRIS%'

And to select all employees whose names don't start with CHRIS, use the following:

SELECT OBJECT (emp) FROM employee as emp
WHERE emp.name NOT LIKE 'CHRIS%'

If the value of emp.name in the above expression is NULL, then the value of the expression is unknown. To avoid this, check whether a single_valued_path_expression is NULL by using the IS NULL operator. The following expression returns true when an employee's name is a NULL value.

emp.name IS NULL

EJB QL also provides the IS EMPTY comparison operator to check whether a collection object of a query string return type contains empty values. EJB QL provides the MEMBER [OF] identifier to check whether the value of a single_valued_path expression is a member of collection designated by the collection_valued_path expression.

The only identifiers that use a collection valued path expression are the comparison expressions: IS [NOT] EMPTY and [NOT] MEMBER [OF]. Note that in EJB QL queries, any comparison or arithmetic operations with a NULL value or an unknown value always yields an unknown value. Path expressions with NULL values during evaluation return NULL values.

The SELECT clause

The SELECT clause denotes the result of the query. The SELECT clause syntax is below, in BNF format:

SELECT [DISTINCT] {single_valued_path_expression | OBJECT (identification_variable)

DISTINCT works in the same way as SQL in selecting unique values from the query result.

You may also restrict the return type to contain only unique values by declaring java.util.Set as the return type for the finder or select methods. Since java.util.Set doesn't allow duplicate values, whenever the return type is java.util.Set, the container internally applies DISTINCT to the query. Therefore, it is not required to explicitly use the DISTINCT identifier in the query string. But when the return type is a java.util.Collection, then it requires an explicit DISTINCT identifier in the query expression to get unique values.

The SELECT clause determines the type of values returned by a query. For example, to get all orders, the query is:

SELECT OBJECT(o) FROM order o

Here, the order is the abstract-schema-name for OrderEJB.

Similarly, to get all products that are associated with a line item:

SELECT li.product FROM Order As o, IN(o.lineItems) li

These line items are the cmr-field name of LineItemsEJB.

Now, we see the following query, to get all line items related to an Order:

SELECT o.lineItems FROM Order As o

Looking carefully, the above query doesn't work, because the return type of the query is not a single-valued expression. The earlier query to get all products associated with line items works fine. This is because the relationship between line item and product is one-to-one, whereas the relationship between order and line items is one-to-many. The single valued path expression is the single_valued_cmr_field, which is a cmr-field name in one-to-one or many-to-one relationship. Since the relationship between order and line is one-to-many, the result yields a collection_valued_path_expression type.

Therefore, the SELECT clause must be specified to return a single valued expression. The SELECT clause of a query defined for a finder method must always correspond to the abstract schema type of entity bean for which the finder method is defined. The SELECT clause of a query defined for a select method returns abstract schema types of other entity beans, as well as the values for cmp-fields.

Now, you will see some queries of select methods whose return type is that of cmp-field. To select the names of all products that have been ordered:

SELECT DISTICT li.product.name FROM order o, IN (o.lineItems) li

Here, order is the abstract-type-name of OrderEJB. lineItems and products are cmr-field names. name is the cmp-field name defined for the entity bean ProductEJB.

EJB QL also provides built-in functions for performing simple operations on objects of String class and primitive types. Specifically, EJB QL provides the following built-in functions:

String Functions:

Arithmetic Functions:

Advantages

Limitations

Summary

The addition of EJB QL to the new EJB 2.0 specification justifies the distributed component architecture as the standard way of defining queries. EJB QL allows applications to be more portable. I believe future versions of EJB QL may provide support for more built-in functions, as well as other SQL features like ORDER BY. In the EJB 2.0 specification, the data model for CMP does not currently support inheritance; therefore, you cannot compare objects or value classes of different types. This may be addressed in future versions of EJB QL.

Jeelani B. Shaik is a senior Java software engineer at Infinity Markets in Santa Clara, Calif.


Return to ONJava.com.

Copyright © 2009 O'Reilly Media, Inc.