The EJB Query Language (EJB-QL) -Part 2

The EJB Query Language (EJB-QL)

Part -2
EJB-QL Syntax
An EJB-QL query contains three parts:
1. A required SELECT clause
2. A required FROM clause
3. An optional WHERE clause

We now discuss the intimate details of each of these clauses. We'll do the SELECT clause last because that indicates the return results of a query.

The FROM clause
The FROM clause constricts the domain of a query. It indicates what part of the data storage you are querying over--that is, what entity beans you are going to be looking at. In the case of a relational database, the FROM clause would typically restrict which tables you are querying over. For example, the following FROM clause means we are only looking at Order entity beans:

SELECT OBJECT(o)
FROM Order AS o

What we're doing here is declaring a variable in the FROM clause. We are creating a variable, o, which can be used later in the query. In this case, we are re-using that variable in the SELECT clause. You can also re-use that variable in the WHERE clause.

Note that declaring variables will restrict your queries even if you don't use the variables. For example:

SELECT OBJECT(o)
FROM Order AS o, Customer AS c

The above query finds all orders that have customers. Even though we aren't using the variable c anywhere else, we are still excluding orders without customers.

Finally, you should note that the phrase AS is optional and is merely syntactic sugar to help make the query look better. This query produces the same result as the previous one:

SELECT OBJECT(o)
FROM Order o, Customer c

Declaring collection variables
Sometimes you need to declare variables in the FROM clause that represent a collection of values. For example, let's say we want to find all of the line-items which are attached to orders. The following query achieves that:

SELECT OBJECT(l)
FROM Order AS o, IN(o.lineItems) l
The above EJB-QL declares two variables.
· The phrase Order AS o declares a variable o that represents any order entity bean.
· The phrase IN(o.lineItems) l declares a variable l that represents any line-item linked off any order bean.
Thus, you use the AS syntax when declaring a variable representing a single value, and the IN syntax when declaring a variable representing a collection of values. And since the evaluation order is left-to-right, you can use variables on the right that were declared on the left.

Variables only represent one value at a time
Next, consider the following query, which returns all line-items that are attached to orders which are attached to customers:

SELECT OBJECT(l)
FROM Customer AS c, IN(c.orders) o, IN(o.lineItems) l

Notice the phrase o.lineItems. While o is a collection variable, it only represents one element of that collection at a time. Thus it is perfectly legal to use the phrase o.lineItems because in that phrase, o represents an individual order, not a collection of orders.

The WHERE clause
The EJB-QL WHERE clause restricts the results of a query. It is where you choose the values you want from the declared variables in the FROM clause. The general syntax of the WHERE clause is "WHERE ". For example:
SELECT OBJECT(o)
FROM Order o
WHERE o.lineItems IS NOT EMPTY

The above query finds all orders that have line-items.

Handling Input Parameters
When performing a query, often times you'll want to query based upon parameters supplied by the client.
For example, to implement the following finder method which finds a product based on a description:
findProductByDescription(String s)
A WHERE clause can be used as follows:
SELECT OBJECT(p)
FROM Product p
WHERE p.description = ?1
Here, ?1 represents the first parameter passed-in. Additional parameters would be numbered as ?2, ?3, and so-on. Note that you don't need to use all variables declared in the finder/select method.

Conditional Expressions
There are many conditional expressions that are built-in to EJB-QL. Here is the complete list.@@@COMP: The CODE is inside the TB@@@

Conditional Expression	Example	Notes
Mathematical operations: +, -, *, / Comparison operations: =, >, >=, <, <=, <> (not equal) Logical operators: NOT, AND, OR	Find all products that are computer chips and whose profit margin is positive: SELECT OBJECT(p) FROM Product p WHERE (p.description = "chip") AND (p.basePrice - p.cost > 0)	· Two entity beans are equal if and only if they share the same primary key value. · You cannot compare two different entity bean classes.
Between expressions	Find all products whose price is at least 1000 and at most 2000: SELECT OBJECT(p) FROM Product p WHERE p.basePrice BETWEEN 1000 AND 2000	Can also use NOT BETWEEN to return all data that is not between two values.
In expressions	Find all products whose manufacturer is either Intel or Sun: SELECT OBJECT(p) FROM Product p WHERE p.manufacturer IN ('Intel', 'Sun')	Can also use NOT IN to return all data that is not in a range.
Like expressions	Find all products with ids that begin with '12' and end with '3'. For example, '123' or '12993' qualifies, but not '1234': SELECT OBJECT(p) FROM Product p WHERE product.productID LIKE '12%3' Find all products with ids that begin with '123' and are a total of four characters long. For example, '123c' qualifies, but not '14' nor '12345': SELECT OBJECT(p) FROM Product p WHERE product.productID LIKE '123_'	· % stands for any sequence of zero or more characters · _ stands for a single character · You can represent the literal % or _ character by using special escape sequences (see the EJB spec for more) · You can also use NOT LIKE to achieve the opposite effect
Null comparison expressions	Find all products that have NULL descriptions: SELECT OBJECT(p) FROM Product p WHERE product.description IS NULL	You can also use NOT NULL to find all data that has non-NULL values.
Empty collection comparison expressions	Find all orders that have no line-items: SELECT OBJECT(o) FROM Order o WHERE o.lineItems IS EMPTY	· You can also use IS NOT EMPTY to find valid collections. · In this special case, you can declare collections in the WHERE clause rather than declaring them as variables first in the FROM clause
Collection member expressions	Find all line-items that are attached to orders: SELECT OBJECT(l) FROM Order o, LineItem l WHERE l MEMBER OF o.lineItems	· The word OF is optional · In this special case, you can declare collections in the WHERE clause rather than declaring them as variables first in the FROM clause · Can also use NOT MEMBER OF to locate data where elements are not members of collections

Table X.X EJB-QL Conditional Expressions
Note that you can have more than one conditional expression, and use parenthesis to denote order of execution. Your container may provide proprietary extensions to these conditional expressions as well, perhaps in a separate deployment descriptor.