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:
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:
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 |
| OR | True | False | Unknown |
True | True | True | True |
False | True | False | Unknown |
Unknown | True | Unknown | Unknown |
Table X.X The OR truth table.
NOT | |
|---|---|
True | False |
False | True |
Unknown | Unknown |
No comments:
Post a Comment