EJB QL

By now, you should have a good idea as to how a bean's CMP fields and CMR fields are managed and persisted by the EJB container. However, there is still one area that you covered yesterday when you were learning about BMP Entity beans that has not yet been touched on today, and that is how to specify finder methods. For this, you need to know something about the EJB Query Language, or EJB QL.

EJB QL was introduced in the EJB 2.0 specification to make beans more portable across EJB containers. Previously, each EJB container vendor created their own proprietary mechanism for specifying the semantics of finder methods. This is no longer the case.

As a language, EJB QL is based on ANSI SQL, which you have seen embedded within the case study code (for example, on Day 5, “Session EJBs” and Day 6, “Entity EJBs”). EJB QL also bears some similarity with the Object Constraint Language (OCL), part of UML. Familiarity with ANSI SQL (and ideally OCL too) will see you well on the way to picking up EJB QL.

Select Methods

The EJB 2.0 specification also introduces another concept that also uses EJB QL queries, namely that of select methods. These are like finder methods in that their purpose is to return data from the persistent data store, but there are also some important differences. Table 7.2 is an expanded version of the comparison that appears in the EJB specification.

You'll be learning how to write select methods later, as part of specifying and implementing CMP beans.

Syntax and Examples

The EJB Specification formally defines the syntax of EJB QL queries using Bacchus Normal Form (BNF). This can be somewhat heavy going to wade through, but is comprehensive. This section introduces EJB QL using BNF, with annotations and examples along the way.

An EJB QL query is defined as follows:

EJB QL ::= select_clause from_clause [where_clause]

This says that a query consists of a select_clause and a from_clause, and optionally a where_clause. Immediately, you can see the similarity with ANSI SQL.

In ANSI SQL, you may recall that the columns listed in the SELECT clause relate to the tables identified in the FROM clause. So in exploring EJB QL, it makes sense to look at the from_clause before the select_clause; after all, both are needed for any well-formed EJB QL query.

from_clause

The from_clause is defined as follows:

from_clause ::= FROM identification_variable_declaration [,
								 identification_variable_declaration]*

This says that in an EJB QL query, the from_clause consists of one or more identification_variable_declaration clauses. This corresponds to ANSI SQL where there are one or more tables/views listed in a SQL FROM clause. The identification_variable_declaration clause is then defined as follows:

identification_variable_declaration ::= {range_variable_declaration |
 collection_member_declaration} [AS ] identification_variable

where

range_variable_declaration ::= abstract_schema_name

and

collection_member_declaration ::= IN (collection_valued_path_expression)
collection_valued_path_expression ::= identification_variable. [single_valued_cmr_field.]*
 collection_valued_cmr_field

In other words, each item in the from_clause either is just an abstract_schema_name, or is an expression of the form IN (o.abc.def.xyz). The abstract_schema_name is probably the easier expression to understand. Each CMP Entity bean has a corresponding abstract_schema_name, ultimately declared in the deployment descriptor. So in ANSI SQL terms, this is very similar to just listing the “table” that corresponds to the bean.

The IN (o.abc.def.xyz) expression is similar to an OCL expression. Here o refers to the identication_variable assigned by the previous AS phrase. In other words, it corresponds to some abstract_schema_name (that is, bean) also in the from_clause. The abc and def are single_value_cmr_fields. In other words, these are fields of the o bean that return a single element. The abc is a field of o, returning a reference to a bean, and def is a field of this reference that has a field xyz. This xyz field, in turn, returns a Collection or List of some other data (a bean or otherwise).

That's pretty heavy, so some examples from the case study may clarify things. Assuming that the Job bean has an abstract schema name called Job, that the Customer bean has an abstract schema name called Customer, and so on,

FROM Job AS j

sets up an identification_variable called j that refers to the Job schema. The comparison with ANSI SQL is obvious; the syntax is the same.

FROM Job AS j, IN (j.skills) AS s

sets up the j identification_variable as before and also an identification_variable called s that refers to each of the skills related to the Job bean in turn.

Comparing this to ANSI SQL, this most likely would correspond to

FROM       Job AS j
INNER JOIN JobSkill AS s
        ON s.customer = j.customer
       AND s.ref = j.ref

or if you prefer the old-fashioned way:

FROM Job AS j, JobSkill AS s
WHERE s.customer = j.customer
  AND s.ref = j.ref

The following is another example:

FROM Job AS j, IN (j.location.jobs) AS k

sets up the j identification_variable as before and also an identification_variable called k that refers to all of the jobs that have the same location as the original job. To see this, note that j.location returns a reference to the Location bean for the Job, and then that j.location.jobs returns the Collection of Jobs for that Location. Of course, this Collection will include the original job, but it will include others as well.

Comparing this to ANSI SQL, you can see that this is a self-join:

FROM       Job AS j
INNER JOIN Job AS k
        ON j.location = k.location

or in the old money:

FROM Job AS j, Job AS k
WHERE j.location = k.location
							

select_clause

The select_clause is defined as follows:

select_clause ::= SELECT [DISTINCT ] {single_valued_path_expression | OBJECT
								(identification_variable)}

The easy case is the SELECT OBJECT(o) style of the select_clause, where o is an identication_variable defined by the from_clause. (You see now why the from_clause was presented first.) This returns all the data from the data store required to instantiate a bean. In ANSI SQL terms, you might think of it as an intelligent SELECT * FROM ....

All finder methods must use the SELECT OBJECT(o) style, where the objects returned are of the schema associated with the bean for which the finder is being specified. The other style of the select_clause, using a single_valued_path_expression is for use only by select methods that you were introduced to briefly earlier. (More on these to follow.)

The following are some examples from the case study.

SELECT OBJECT(j)
FROM Job as j

will return all jobs. If the JobLocalHome interface defined a finder method called findAll(), this would be the corresponding EJB QL query.

The next example

SELECT DISTINCT OBJECT(s)
FROM Job as j, IN (j.skills) as s

will return back all skills used by any job. Because some skills will be required by more than one job, the DISTINCT keyword is used to eliminate duplicates. This EJB QL query might perhaps be associated with a finder method called findAllRequiredSkills() on the SkillLocalHome interface.

The other style of the select_clause uses a single_valued_path_expression. This is defined as follows:

single_valued_path_expression ::= {single_valued_navigation | identification_variable} 
.cmp_field | single_valued_navigation

where a single_valued_navigation is

single_valued_navigation ::= identification_variable.[single_valued_cmr_field.]*
 single_valued_cmr_field

Taking these definitions together, a single_valued_path_expression is effectively just a chain of (none or many) single_valued_cmr_fields (cmr-fields returning a reference to a single bean, and not a collection), eventually finishing with a cmp-field.

In the following examples

SELECT DISTINCT j.location.name
FROM Job as j

location is the cmr-field of the Job schema (identified by j), and name is the cmp-field of the bean referenced by the cmr-field (a Location bean, obviously).

This returns the names of the locations where there are jobs. Comparing this to ANSI SQL, you can see that EJB QL is actually simpler (because of its use of the OCL-like path expressions to navigate between beans):

SELECT DISTINCT l.name
FROM Job as j INNER JOIN Location as l ON j.location = l.location

However, the following is not allowed:

SELECT DISTINCT j.skills.name
FROM Job as j

This is because the skills cmr-field of Job returns a collection of skills, not a single skill. The correct way to phrase this query is as follows:

SELECT DISTINCT s.name
FROM Job as j, IN (j.skills) AS s

You might like to think of the s identification variable as an iterator over the collection of skills returned by the skills cmr-field.

Note that the select_clause in EJB QL can only ever return a single item of information, so the following also is not allowed:

SELECT DISTINCT j.location.name, j.location.description
FROM Job as j
							

where_clause

The where_clause is optional in an EJB QL query, but will be present in the majority of cases. It is defined in BNF as follows:

where_clause ::= WHERE conditional_expression

conditional_expression ::= conditional_term | conditional_expression OR conditional_term
conditional_term ::= conditional_factor | conditional_term AND conditional_factor
conditional_factor ::= [NOT ] conditional_test

This just says that clauses can be combined using the usual AND, OR, and NOT. Much of the rest of the formal BNF definitions for the where_clause also makes somewhat heavy work of some fairly straightforward concepts, so to paraphrase,

• conditional_tests can involve =, >, <, >=, <=, and <> operators. These apply variously to numbers and datetimes (all operators), and strings, Booleans, and Entity beans (the = and <> operators). You will recall that Entity beans are considered identical if their primary keys are equal.

• Comparisons can involve input parameters, where these correspond to the arguments of the finder or select method. More on this topic shortly.

• Arithmetic expressions can use the BETWEEN...AND operator, just as in ANSI SQL.

• String expressions can be compared against lists using the IN operator and against patterns using the LIKE operator. These also exist within ANSI SQL. Unlike Java, string literals should appear in single quotes.

• The IS NOT NULL operator exists to determine if an object is null. Again, this syntax is borrowed from ANSI SQL.

There are some more operators to EJB QL and some built-in functions, but first, some examples using these operators are in order.

SELECT OBJECT(c)
FROM Customer AS c
WHERE c.name LIKE "J%"

This will find all customers whose name begins with the letter J. Note that the ANSI SQL wildcards (% to match none or many characters, _ to match precisely one character) are used.

The following

SELECT OBJECT(j)
FROM Jobs AS j
WHERE j.location IS NULL

will find all jobs where the location has not been specified.

SELECT l.description
FROM Location AS l
WHERE l.name IN ("London", "Washington")

returns the descriptions of the locations named London and Washington.

The where_clause can also include input parameters. These parameters correspond to the arguments of the finder or the select method, as defined in the local-home interface or bean, respectively.

For example, the Job bean declares the following finder method in the JobLocalHome interface:

Collection findByCustomer(String customer);

The EJB QL query for this finder method is as follows:

SELECT OBJECT(j)
FROM Job AS j
WHERE j.customer = ?1

?1 acts as a placeholder, with the 1 indicating that the first argument of the finder method (the customer string) be implicitly bound to this input parameter. Unlike JDBC SQL strings, the number is required because the binding is implicit, not explicit.

It is also needed in the cases where a single argument is used more than once in the query. For example, consider the following finder method:

Collection findLocationsNamedOrNamedShorterThan(String name);

This might have an EJB QL query of

SELECT OBJECT(l)
FROM Location AS l
WHERE l.name = ?1
OR LENGTH(l.name) < LENGTH(?1)

This example uses the built-in function LENGTH that returns the length of a String. In any case, this rather peculiar finder method will find those locations that have the exact name, and will also return any name whose length is strictly shorter than the supplied name. You can see that the ?1 placeholder appears more than once because the name argument needs to be bound to the query in two places.

EJB QL defines just a few more built-in functions. The functions that return a string are CONCAT and SUBSTRING. The functions that return a number are LENGTH, ABS, SQRT, and LOCATE. This last is effectively the same as String.indexOf(String str, int fromIndex).

EJB QL defines two final operators—IS [NOT] EMPTY and [NOT] MEMBER OF. Neither of these have any direct equivalents in ANSI SQL, but both do have equivalents in OCL.

The IS [NOT] EMPTY operator can is similar to the isEmpty operator of OCL. It can be used to determine whether a collection returned by a cmr-field is empty. For example,

SELECT OBJECT(s)
FROM Skill AS s
WHERE s.jobs IS EMPTY

will return all those skills that are not marked as required by any job. This might be the query for a finder method on the SkillLocalHome interface, called something like findNotNeededSkills().

In fact, this type of query can be expressed in ANSI SQL, though it does require a subquery:

SELECT s.*
FROM Skill AS s
WHERE NOT EXISTS
    (   SELECT *
        FROM JobSkill AS j
        WHERE s.skill = j.skill )

The [NOT] MEMBER OF operator is similar to the include operator of OCL. Consider the following finder method on the JobLocalHome interface:

Collection findJobsRequiringSkill(SkillLocal skill);

The EJB QL query for this would be

SELECT OBJECT(j)
FROM Job AS j
WHERE ?1 MEMBER OF j.skills

Again, this can be expressed in ANSI SQL, but only using a subquery:

SELECT j.*
FROM Job AS j
WHERE EXISTS
    (   SELECT *
        FROM JobSkill AS s
        WHERE j.customer = s.customer
        AND   j.ref      = s.ref
        AND   s.skill    = ?1         )
							

Further Notes

You may have heard of the “OO/relational impedance mismatch.” This is that to deal with objects, each must be instantiated and then a message sent to it. On the other hand, relational theory deals with sets of elements sharing some common attribute; to identify these elements without instantiating them effectively breaks encapsulation.

EJB QL does a pretty reasonable job of reconciling these concerns. By allowing queries to be expressed in a set-oriented syntax, the EJB container can easily map these to ANSI SQL when the persistent data store is an RDBMS. On the other hand, the generated implementations of finder methods return only objects or Collections of objects.

Nevertheless, EJB QL does have some limitations. For example, when constructing an EJB QL query, only declared relationships between beans can be followed. It is not possible to join arbitrary fields together (as it is in ANSI SQL). For example, those Customers who are also Applicants could not be identified using a condition such as Applicant.name = Customer.name.

There are a number of other cases where EJB QL is not (yet) as powerful as ANSI SQL. For example, EJB QL does not support grouping and aggregating, ordering, subqueries, and unions. Expect these features to be added as EJB QL matures. Also, even though EJB QL does not directly support subqueries, one might not be needed anyway thanks to the IS [NOT] EMPTY and [NOT] MEMBER OF operators.