Advanced EJB QL

Advanced EJB QL

In the previous chapter you were first introduced to EJB QL query definitions for a container-managed entity. You learned how to define both find and ejbSelect queries as well as how to generate the methods necessary to support these queries. At that time you were introduced to the EJB QL finder wizard (Figure). Now, after learning how to create relationships between container-managed entities, we are ready to explore more complex EJB QL queries.

This section will concentrate on specific EJB QL statements and not necessarily the tools to create the queries or the query definitions within the deployment descriptor. We will start with more detailed information for each of the main clauses of the EJB QL query statement, SELECT clause, FROM clause, and WHERE clause. At the end of this section are a number of example find and ejbSelect query statements that could be created for the case study. You may want to refer to these examples while reading through the rest of this section to see more complex statements that can be built with EJB QL.

Before we begin talking about these clause sections of the EJB QL statement, we must first explore identifiers which span all three clauses. An EJB QL identifier must follow the same rules as Java identifiers. A start identifier will be a character for which Character.isJavaIdentifierStart returns true (e.g., '_' and '$') and a part character is a character for which Character.isJavaIdentifierPart returns true. Note the question mark (?) is a reserved character and cannot be used as an identifier. Here is a list of reserved identifiers in EJB QL: SELECT, FROM, WHERE, DISTINCT, OBJECT, NULL, TRUE, FALSE, NOT, AND, OR, BETWEEN, LIKE, IN, AS, UNKNOWN,[4] EMPTY, MEMBER, OF, and IS.

[4] Not currently used but reserved for future use.

1 The FROM Clause

We will start by explaining the FROM clause since it is where identification variables are defined and used within the other two clauses. Here is the syntax of the FROM clause as defined by the EJB 2.0 specification:

from_clause ::=FROM identification_variable_declaration [, graphics/ccc.gif identification_variable_declaration]* identification_variable_declaration ::= graphics/ccc.gif collection_member_declaration | range_variable_declaration collection_member_declaration : graphics/ccc.gif:= IN (collection_valued_path_expression) [AS ] identifier range_variable_declaration :: graphics/ccc.gif abstract_schema_name [AS ] identifier

An identification variable is used to designate instances of a specific abstract schema type of an entity bean. There may be multiple identification variables within the FROM clause separated by a comma. Identification variables cannot be a reserved identifier or the abstract schema name or the bean name. Let's look at an example of an EJB QL that could be defined for our case study.


FROM TimeSheetEJB o, IN(o.entries) e

In this example the identification variable e will evaluate to a TimeSheetEntryEJB that is directly reachable from TimeSheetEJB. The cmr-field entries represents a collection of the abstract schema type TimeSheetEntryEJB and the variable e refers to one of the items in the collection. Note that clauses are evaluated form left to right. This is why the second variable declaration, e, in this FROM clause can utilize the first variable, o.

From this example you can see that an identification variable can refer to a single abstract schema type instance (a range variable) or one element from a collection of abstract schema type instances (a collection member identification variable). That is, an identification variable always refers to a single value.

In the example, o is a range variable and it could optionally be defined with the AS operator (i.e., TimeSheetEJB AS o). Range variables are convenient for designating a reference point for objects which may not be reachable by navigation.

The e variable in the example is a collection member identification variable. A collection member identification variable is always declared using the reserved identifier IN within a functional expression and it takes a collection values path expression as a parameter. A path expression is a representation of the navigation of cmr-fields within the entity's abstract schema type. A path expression is defined as an identification variable followed by the navigation operator (.) and a cmp-field or cmr-field. Path expressions can be further composed of another expression if it ends with a single-valued cmr-field. A path expression that ends with a cmp-field or a multivalued cmr-field cannot be further composed.

For example, if range variable t designates a TimeSheetEJB, the following paths are valid: t.state, t.entries, The following would be an invalid path expression: This is an invalid path because the entries cmr-field resolves to a collection. Collection member identification variables must be used to reference a particular member of the collection. We could fix the invalid expression by declaring the variable e in the FROM clause as IN(t.entries) e and then the expression could be correctly written as

2 The SELECT Clause

The SELECT clause is used to designate the query result. For a finder query, the clause can only contain a single range variable or a single valued path expression that is typed to the abstract schema type of the container-managed entity bean for which it is defined. For select queries, the SELECT clause can contain the same values as a finder query except that the single valued path expression can evaluate to any arbitrary type. These arbitrary types can be the entity bean's abstract schema type or the abstract schema types of other entity beans or values of cmp-fields.

Below is the syntax of the SELECT clause as defined by the EJB 2.0 specification:

select_clause ::=

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

The select clause has a number of rules. First, the OBJECT identifier must be used to select values of identification variables defined within the FROM clause. Also, any path expression used within the SELECT clause must be a single valued path expression and not a many valued expression. Finally, the DISTINCT key word is used to remove duplicates from the query result when the return type is a java.util.Collection. The DISTINCT key word is not required if the return type is java.util.Set.

Let's look at simple examples from the case study. A very common query would be one that returned all available instances of a given entity (i.e., findAll query). The following statement will return a collection of EmployeeEJB instances.


The following statement will return a collection EmployeeEJB instances which are the submitters from the EmployeeEJB's timesheets.

SELECT t.submitter FROM EmployeeEJB AS e, IN(e.timesheets) t

The following statement is invalid because the SELECT clause must use only single-valued expressions if expressions are used at all.

SELECT t.entries FROM EmployeeEJB AS e, IN(e.timesheets) t

3 The WHERE Clause

The WHERE clause provides the ability to restrict the result of the query. This is done by using a conditional expression as shown in this syntax:

where_clause ::=WHERE conditional_expression

The WHERE clause may use any identification variable defined within the FROM clause. The conditional expression may also contain Java literals such as strings, numbers, and Boolean literals. String literals are represented by single quotes and the use of a single quote in the literal is escaped by using two single quotes. The Boolean literals are TRUE and FALSE.


WHERE = 'Bob'

WHERE o.isActive = TRUE

Path expressions are another construct that is valid with the WHERE clause. There is, however, one exception: empty_collection_comparison_expression or collection_member_expression. We will talk about these expressions in just a moment. Note that the path expression will be unknown if it is composed by using an identification variable that designates an unknown value.

These constructs are important features of the WHERE clause but probably the most important method to restrict the query result would be the use of input parameters within the conditional expression. Input parameters are designated with a question mark (?) and a number starting from 1 (e.g., ?1, ?2, ?3). The number of distinct input parameters cannot exceed the number of parameters specified in the query method signature. However, the query itself does not need to make use of all the parameters from the method. The type of the input parameter will evaluate to the corresponding parameter from the query method.

In the next example, the ?1 variable will have a String type as it corresponds to the aName parameter.

public EmployeeEJB findByName(String aName);


We have already shown that the WHERE clause is made up of a conditional expression but it can also be made of multiple conditional expressions that are composed of one another. For example, you can use two conditional expressions where both must evaluate to True by using the AND identifier to join both expressions. You could also use two conditional expressions where only one evaluates to True by using the OR identifier to join the expressions. Any evaluation of a given conditional expression or composition of expressions can be negated with the NOT identifier preceding the expression. The ordering of the expressions within the WHERE clause can be controlled by using parentheses [()] to group expressions.

Here is a list of operators that may be used within a conditional expression in order of decreasing precedence as given in the EJB 2.0 specification.

  • Navigation operator (.)

  • Arithmetic operators:

    +, - unary

    *, / multiplication and division

    +, - addition and subtraction

  • Comparison operators: =, >, >=, <, <=, <> (not equal)

  • Logical operators: NOT, AND, OR

Figure provides additional operators that are used within specific expressions in the WHERE clause.

Specific-expression operators.





arithmetic_expression [NOT] BETWEEN arithmetic-expr AND arithmetic-expr

A shortcut instead of using >= and <= tests.


single_valued_path_expression [NOT] IN (string-literal [, string-literal]* )

A shortcut for testing of containment or noncontainment within a group of Strings instead of using multiple OR statements.


single_valued_path_expression [NOT] LIKE pattern-value [ESCAPE escape-character]

The path expression must evaluate to a String. The pattern-value can use an underscore (_) for single characters and a percent (%) for a sequence of characters. ESCAPE defines the escape character to be used when you want to escape the meaning of the underscore and percent in the pattern.


single_valued_path_expressionIS [NOT ] NULL

Test for a NULL value within a single valued-path expression.


collection_valued_path_expression IS [NOT] EMPTY

Test for no elements or some elements from a collection returned in a collection valued path expression.


{single_valued_navigation |

graphics/ccc.gif identification_variable | input_parameter }

[NOT] MEMBER [OF] collection_valued_path_expression

Tests whether a single object is or is not contained within a given collection.

There are some built-in functions for both Strings and arithmetic in EJB QL. The String functions include:

  • CONCAT(String, String) returns a String

  • SUBSTRING(String, start, length) returns a String (the start and length are int positions within the String)

  • LOCATE(String, String [, start]) return an int (start is an int position within the String)

  • LENGTH(String) returns an int

4 Examples

Here are several EJB QL example statements that reference EJBs defined in the case study.

4.1 Find Query Examples
  1. Return all EmployeeEJB instances.

  2. Find all states that have a TimeSheetEJB.

    SELECT DISTINCT t.state FROM TimeSheetEJB t
  3. Find all TimeSheetEJBs for all 'Smith' submitters.

    WHERE t.submitter LIKE '%Smith'
  4. Find all TimeSheetEJBs within one of the following states, WV, VA, NC, or SC.

    WHERE t.state IN ('WV', 'VA', 'NC', 'SC')
  5. Find all ProjectEJBs with a project number between 1000 and 1100.

    WHERE p.projNumber BETWEEN 1000 AND 1100
  6. Find all TimeSheetEJBs that have TimeSheetEntryEJBs.

    WHERE t.entries IS NOT EMPTY
  7. Find all EmployeeEJBs where they have a time sheet in a state different from their home state.

    SELECT DISTINCT OBJECT(e) FROM EmployeeEJB e, IN(e.timesheets) t
    WHERE e.homeAddress.state <> t.state
  8. Find all EmployeeEJBs that live is the given city and state.

    WHERE = ?1 AND e.state = ?2
  9. Find all TimeSheetEJBs for a given project name.

    SELECT DISTINCT OBJECT(t) FROM TimeSheetEJB t, IN(t.entries) e
    WHERE = ?1
4.2 Select Query Examples
  1. Select the distinct city names of all EmployeeEJBs.

  2. Select the names of all EmployeeEJB that have time sheets within a given state.

    SELECT DISTINCT FROM EmployeeEJB e, IN(e.timesheets) t
    WHERE t.state = ?1
  3. Select the AddressEJB for an EmployeeEJB with a given name.

    SELECT e.homeAddress FROM EmployeeEJB e
    WHERE = ?1
  4. Select the employee names for all employees within a particular state with a submitted time sheet entry that is greater than eight hours for a particular project.

    SELECT FROM EmployeeEJB e, IN(e.timesheets) t,
    IN(t.entries) l
    WHERE l.hours > 8.0 AND
    e.homeAddress.state = ?1 AND = ?2
  5. Select a distinct set of Project names from the employee's approved time sheet entries where the employee's state is the same as the TimeSheet state for a particular employee ZIP code.

    IN(e.approvedTimesheets) t, IN(t.entries) l
    WHERE e.homeAddress.state = t.state
    AND e.homeAddress.zipCode = ?1

     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows