ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

Learning EJB QL
Pages: 1, 2, 3

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:


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:

  • reserved identifier, IN
  • abstract-schema-types of range variables and
  • abstract-schema-types of associated entity beans.

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 {
public abstract java.util.Collection ejbSelectLineItems(int quantity)
throws FinderException;
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:

Method to find order specified no of lineItems</description>
SELECT OBJECT (o) FROM Order AS o IN (o.lineItems) li
WHERE li.quantity = ?1

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
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

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.

Pages: 1, 2, 3

Next Pagearrow