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

The EJB Query Language (EJB-QL)
Part -3
EJB-QL also contains the following built-in functions:
CONCAT(String, String) combines two strings into one and returns a String.
SUBSTRING(String, start, length) cuts a String into a smaller String, beginning at start and being length long.
LOCATE(String, String [, start]) returns an int denoting where a String is located within another String. You can use the optional start parameter to indicate where to begin locating.
LENGTH(String) gives you a string's length, returned as an int.
ABS(number) returns the absolute value of a number, which can be an int, float, or double.
SQRT(double) takes the square-root of a number and returns it as a double.

Dealing with collections
Normally if you want to use collections in the WHERE clause, you should declare those collections as variables in the FROM clause. For example, the following is invalid:

SELECT OBJECT(l)
FROM Order AS o
WHERE o.lineItems.product.name = 'chip'

The above is invalid because we are trying to reference a variable from a collection. The following is the correct way to write this EJB-QL:

SELECT OBJECT(l)
FROM Order AS o, IN(o.lineItems) l
WHERE l.product.name = 'chip'

The two special exceptions to this rule are when you use the EMPTY or MEMBER conditional expressions, shown in Table X.X. In these cases, you can use collections in the WHERE clause.

Performing comparisons
Sometimes you may need to declare more than one variable that represents the same entity bean. When you are performing comparisons this comes in very handy. For example:

SELECT OBJECT(p1)
FROM Product p1, Product p2
WHERE p1.quantityInStock > p2.quantityInStock AND
p2.name='Pentium 866'

The above query finds all products that have a greater quantity in-stock than a Pentium 866 chip.
The SELECT clause
The EJB-QL SELECT clause specifies the return results of a query. To understand why we need the SELECT clause, consider the following query, which returns all orders that contain line-items:
SELECT OBJECT(o)
FROM Order AS o, IN(o.lineItems) l
In this query, we have defined two variables in the FROM clause: o and l. The SELECT clause is necessary because it affirms that we want to return o (and not l) to the client who called the query.

How to traverse relationships
The SELECT clause can traverse relationships. For example, the following query returns all the products in all the orders that contain line-items:
SELECT l.product FROM Order AS o, IN(o.lineItems) l

As you can see, we can use the convenient dot-notation to traverse relationships in the SELET clause. Behind the scenes, a SQL JOIN statement might occur.

If you've been paying careful attention, you may have noticed that in the earlier example we wrapped a variable o with the phrase OBJECT(), but in this example, we didn't use the phrase OBJECT() at all. The EJB-QL rule is that you only wrap your return result with the phrase OBJECT() if you are returning a standalone variable that does not traverse a relationship using the dot-notation.

How to deal with collections
Let's say we want to find all line-items on all orders. We are thus asking for a collection of return results. Unfortunately, the following SELECT clause will not work:

SELECT o.lineItems
FROM Order AS o

The reason the above doesn't work is because SELECT clauses may only return single variables, not collections. To get around this restriction, you need to define a variable in the FROM clause. The following demonstrates this as a legal way to find all line-items on all orders:

SELECT OBJECT(l)
FROM Order AS o, IN(o.lineItems) l

How to filter for duplicates
You can control whether SELECT clauses return duplicates. For example, take our previous EJB-QL query that finds all products in all order line-items:

SELECT l.product FROM Order AS o, IN(o.lineItems) l

The above query may return duplicate products, because two different people may have ordered the same product. To get a unique list, you must apply the DISTINCT filter, as follows:

SELECT DISTINCT l.product FROM Order AS o, IN(o.lineItems) l

Another choice you have is to declare your finder or select method to return a java.util.Set, which may not contain duplicates compared to a java.util.Collection. If you use a java.util.Set, then both of the above EJB-QL statements would return the same unique results.

How to control what gets returned in finders
EJB-QL queries return results differently depending on how the client initiates the query. For example, take the following finder queries (thrown exceptions omitted):

// declared on the home interface
public java.util.Collection findAllProducts();

// declared on the local home interface
public java.util.Collection findAllProducts();

We want EJB objects to be returned for the first query, and EJB local objects to be returned for the second query. The EJB-QL code in the deployment descriptor for both of these query methods could be:
findAllProducts

What's great here is that we wrote our EJB-QL once, yet we can reuse it for both the home interface and local home interface. The container will automatically wrap the return results in an EJBObject or EJBLocalObject, respectively. These are the only possible types you can return from a finder query.

How to control what gets returned in selects
With finder methods, the container knows whether the results of a finder should be EJB objects or EJB local objects, because the container could look at whether the query was defined on the home interface or local home interface, respectively. But what about ejbSelect() methods?

Consider the following ejbSelect():
public abstract java.util.Collection ejbSelectAllProducts();

Here, we define the ejbSelect() method on the entity bean class, which doesn't give the container any information about whether our query should return EJB objects or EJB local objects. How does the container know what objects to wrap around the results?

To get around this, EJB requires that you setup a special stanza in the deployment descriptor to inform the container about whether the results should be local or remote objects:
ejbSelectAllProducts Local
The above code will cause the ejbSelect() method to return a collection of EJB local objects. If you want the results to be a collection of EJB objects, then change the result-type-mapping element to have the value Remote.

Finally, note that ejbSelect() methods can also return container-managed fields.
For example:
public java.lang.String ejbSelectProductName();

Finder methods cannot return container-managed fields because finder methods operate remotely and at the granularity of entity beans, not parts of entity beans.

Truth Tables
Let's wrap-up our EJB-QL lesson with a look at the truth tables for how the operations AND, OR, and NOT evaluate. In the tables, the case of unknown means expressions that produce an unknown result, such as the clause:
WHERE NULL IN ('Intel', 'Sun')

AND	True	False	Unknown
True	True	False	Unknown
False	False	False	False
Unknown	Unknown	False	Unknown

Table X.X The AND truth table.

OR	True	False	Unknown
True	True	True	True
False	True	False	Unknown
Unknown	True	Unknown	Unknown

Table X.X The OR truth table.







Table X.X The NOT truth table.

NOT
True	False
False	True
Unknown	Unknown

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.