Full Syntax

This section discusses the EJB QL syntax, as defined in the Enterprise JavaBeans specification. Much of the following material paraphrases or directly quotes the specification.

BNF Symbols

Table 29-1 describes the BNF symbols used in this chapter.

Table 29-1 BNF Symbol Summary 
Symbol
Description
::=
The element to the left of the symbol is defined by the constructs on the right.
*
The preceding construct may occur zero or more times.
{...}
The constructs within the curly braces are grouped together.
[...]
The constructs within the square brackets are optional.
|
An exclusive OR.
BOLDFACE
A keyword (although capitalized in the BNF diagram, keywords are not case-sensitive).
Whitespace
A whitespace character can be a space, a horizontal tab, or a linefeed.

BNF Grammar of EJB QL

Here is the entire BNF diagram for EJB QL:

EJB QL ::= select_clause from_clause [where_clause] 
[orderby_clause]

from_clause ::=FROM identification_variable_declaration
    [, identification_variable_declaration]*

identification_variable_declaration ::= 
collection_member_declaration |
    range_variable_declaration

collection_member_declaration ::= IN ( 
collection_valued_path_expression) [AS ] identifier

range_variable_declaration ::= 
    abstract_schema_name [AS ] identifier

cmp_path_expression ::=
    {identification_variable |
     single_valued_cmr_path_expression}.cmp_field

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

single_valued_path_expression ::=
    cmp_path_expression | single_valued_cmr_path_expression

collection_valued_path_expression ::=
    identification_variable.[single_valued_cmr_field.]
    *collection_valued_cmr_field

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

select_expression ::= single_valued_path_expression | 
aggregate_select_expression

aggregate_select_expression ::=
    {AVG |MAX |MIN |SUM |COUNT }( [DISTINCT ]
     cmp_path_expression) |
    COUNT ( [DISTINCT ] identification_variable |
     single_valued_cmr_path_expression)

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_primary

conditional_primary ::= simple_cond_expression |
     (conditional_expression)

simple_cond_expression ::=
    comparison_expression | between_expression | 
    like_expression | in_expression |
    null_comparison_expression |
    empty_collection_comparison_expression |
    collection_member_expression

between_expression ::=
    arithmetic_expression [NOT ]BETWEEN
    arithmetic_expression AND arithmetic_expression

in_expression ::=
    cmp_path_expression [NOT ] IN
    ( {literal | input_parameter} 
    [, { literal | input_parameter} ]*)

like_expression ::=
    cmp_path_expression [NOT ] LIKE 
    pattern_value [ESCAPE escape_character]
    
null_comparison_expression ::=
    {single_valued_path_expression | 
    input_parameter}IS [NOT ] NULL

empty_collection_comparison_expression ::=
    collection_valued_path_expression IS [NOT] EMPTY

collection_member_expression ::=
    {single_valued_cmr_path_expression |
     identification_variable | input_parameter}
    [NOT ] MEMBER [OF ] collection_valued_path_expression

comparison_expression ::=
    string_value comparison_operator string_expression |
    boolean_value {= |<> } boolean_expression} |
    datetime_value comparison_operator datetime_expression |
    entity_bean_value {= |<> } entity_bean_expression |
    arithmetic_value comparison_operator arithmetic_expression

arithmetic_value ::= cmp_path_expression |
     functions_returning_numerics

comparison_operator ::= 
    = |> |>= |< |<= |<>

arithmetic_expression ::= arithmetic_term |
    arithmetic_expression {+ |- } arithmetic_term

arithmetic_term ::= arithmetic_factor |
     arithmetic_term {* |/ } arithmetic_factor

arithmetic_factor ::= [{+ |- }] arithmetic_primary

arithmetic_primary ::= cmp_path_expression | literal |
    (arithmetic_expression) | input_parameter |
    functions_returning_numerics

string_value ::= cmp_path_expression |
     functions_returning_strings

string_expression ::= string_primary | input_parameter

string_primary ::= cmp_path_expression | literal |
    (string_expression) | functions_returning_strings

datetime_value ::= cmp_path_expression

datetime_expression ::= datetime_value | input_parameter

boolean_value ::= cmp_path_expression

boolean_expression ::= cmp_path_expression | literal |
    input_parameter

entity_bean_value ::= single_valued_cmr_path_expression |
    identification_variable

entity_bean_expression ::= entity_bean_value | input_parameter

functions_returning_strings ::=
    CONCAT( string_expression, string_expression) |
    SUBSTRING( string_expression, arithmetic_expression,
    arithmetic_expression)


functions_returning_numerics ::=
    LENGTH( string_expression) |
    LOCATE( string_expression, string_expression
    [, arithmetic_expression]) |
    ABS( arithmetic_expression) |
    SQRT( arithmetic_expression) |
    MOD( arithmetic_expression, arithmetic_expression)

orderby_clause ::= ORDER BY orderby_item [, orderby_item]*

orderby_item ::= cmp_path_expression [ASC |DESC ] 

FROM Clause

The FROM clause defines the domain of the query by declaring identification variables. Here is the syntax of the FROM clause:

from_clause ::= FROM identification_variable_declaration 
    [, identification_variable_declaration]*


identification_variable_declaration ::=
    collection_member_declaration |
    range_variable_declaration

collection_member_declaration ::= 
    IN (collection_valued_path_expression) [AS] identifier
 
range_variable_declaration ::= 
    abstract_schema_name [AS] identifier 

Identifiers

An identifier is a sequence of one or more characters. The first character must be a valid first character (letter, $, _) in an identifier of the Java programming language (hereafter in this chapter called simply "Java"). Each subsequent character in the sequence must be a valid nonfirst character (letter, digit, $, _) in a Java identifier. (For details, see the J2SE API documentation of the isJavaIdentifierStart and isJavaIdentifierPart methods of the Character class.) The question mark (?) is a reserved character in EJB QL and cannot be used in an identifier. Unlike a Java variable, an EJB QL identifier is not case-sensitive.

An identifier cannot be the same as an EJB QL keyword:

AND
AS
ASC
AVG
BETWEEN
BY
COUNT
DESC
DISTINCT
EMPTY
FALSE
FROM
IN
IS
LIKE
MAX
MEMBER
MIN
MOD
NOT
NULL
OBJECT
OF
OR
ORDER
SELECT
SUM
TRUE
UNKNOWN
WHERE

EJB QL keywords are also reserved words in SQL. In the future, the list of EJB QL keywords may expand to include other reserved SQL words. The Enterprise JavaBeans specification recommends that you not use other reserved SQL words for EJB QL identifiers.

Identification Variables

An identification variable is an identifier declared in the FROM clause. Although the SELECT and WHERE clauses can reference identification variables, they cannot declare them. All identification variables must be declared in the FROM clause.

Because an identification variable is an identifier, it has the same naming conventions and restrictions as an identifier. For example, an identification variable is not case-sensitive, and it cannot be the same as an EJB QL keyword. (See the preceding section for more naming rules.) Also, within a given EJB JAR file, an identifier name must not match the name of any entity bean or abstract schema.

The FROM clause can contain multiple declarations, separated by commas. A declaration can reference another identification variable that has been previously declared (to the left). In the following FROM clause, the variable t references the previously declared variable p:

FROM Player p, IN (p.teams) AS t 

Even if an identification variable is not used in the WHERE clause, its declaration can affect the results of the query. For an example, compare the next two queries. The following query returns all players, whether or not they belong to a team:

SELECT OBJECT(p)
FROM Player p 

In contrast, because the next query declares the t identification variable, it fetches all players that belong to a team:

SELECT OBJECT(p)
FROM Player p, IN (p.teams) AS t 

The following query returns the same results as the preceding query, but the WHERE clause makes it easier to read:

SELECT OBJECT(p)
FROM Player p
WHERE p.teams IS NOT EMPTY 

An identification variable always designates a reference to a single value whose type is that of the expression used in the declaration. There are two kinds of declarations: range variable and collection member.

Range Variable Declarations

To declare an identification variable as an abstract schema type, you specify a range variable declaration. In other words, an identification variable can range over the abstract schema type of an entity bean. In the following example, an identification variable named p represents the abstract schema named Player:

FROM Player p 

A range variable declaration can include the optional AS operator:

FROM Player AS p 

In most cases, to obtain objects a query uses path expressions to navigate through the relationships. But for those objects that cannot be obtained by navigation, you can use a range variable declaration to designate a starting point (or root).

If the query compares multiple values of the same abstract schema type, then the FROM clause must declare multiple identification variables for the abstract schema:

FROM Player p1, Player p2 

For a sample of such a query, see Example 9 .

Collection Member Declarations

In a one-to-many relationship, the multiple side consists of a collection of entity beans. An identification variable can represent a member of this collection. To access a collection member, the path expression in the variable's declaration navigates through the relationships in the abstract schema. (For more information on path expressions, see the following section.) Because a path expression can be based on another path expression, the navigation can traverse several relationships. See Example 6 .

A collection member declaration must include the IN operator, but it can omit the optional AS operator.

In the following example, the entity bean represented by the abstract schema named Player has a relationship field called teams. The identification variable called t represents a single member of the teams collection.

FROM Player p, IN (p.teams) AS t 

Path Expressions

Path expressions are important constructs in the syntax of EJB QL, for several reasons. First, they define navigation paths through the relationships in the abstract schema. These path definitions affect both the scope and the results of a query. Second, they can appear in any of the three main clauses of an EJB QL query (SELECT, WHERE, FROM). Finally, although much of EJB QL is a subset of SQL, path expressions are extensions not found in SQL.

Syntax

Here is the syntax for path expressions:

cmp_path_expression ::=
{identification_variable |
single_valued_cmr_path_expression}.cmp_field

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

single_valued_path_expression ::=
cmp_path_expression | single_valued_cmr_path_expression

collection_valued_path_expression ::=
identification_variable.[single_valued_cmr_field.]
*collection_valued_cmr_field

In the preceding diagram, the cmp_field element represents a persistent field, and the cmr_field element designates a relationship field. The term single_valued qualifies the relationship field as the single side of a one-to-one or one-to-many relationship; the term collection_valued designates it as the multiple (collection) side of a relationship. The single_valued_cmr_path_expression is the abstract schema type of the related entity bean.

The period (.) in a path expression serves two functions. If a period precedes a persistent field, it is a delimiter between the field and the identification variable. If a period precedes a relationship field, it is a navigation operator.

Examples

In the following query, the WHERE clause contains a cmp_path_expression. The p is an identification variable, and salary is a persistent field of Player.

SELECT DISTINCT OBJECT(p)
FROM Player p 
WHERE p.salary BETWEEN ?1 AND ?2 

The WHERE clause of the next example also contains a cmp_path_expression. The t is an identification variable, league is a single-valued relationship field, and sport is a persistent field of league.

SELECT DISTINCT OBJECT(p)
FROM Player p, IN (p.teams) AS t 
WHERE t.league.sport = ?1 

In the next query, the WHERE clause contains a collection_valued_path_expression. The p is an identification variable, and teams designates a collection-valued relationship field.

SELECT DISTINCT OBJECT(p)
FROM Player p 
WHERE p.teams IS EMPTY 

Expression Types

The type of an expression is the type of the object represented by the ending element, which can be one of the following:

For example, the type of the expression p.salary is double because the terminating persistent field (salary) is a double.

In the expression p.teams, the terminating element is a collection-valued relationship field (teams). This expression's type is a collection of the abstract schema type named Team. Because Team is the abstract schema name for the TeamBean entity bean, this type maps to the bean's local interface, LocalTeam. For more information on the type mapping of abstract schemas, see the section Return Types.

Navigation

A path expression enables the query to navigate to related entity beans. The terminating elements of an expression determine whether navigation is allowed. If an expression contains a single-valued relationship field, the navigation can continue to an object that is related to the field. However, an expression cannot navigate beyond a persistent field or a collection-valued relationship field. For example, the expression p.teams.league.sport is illegal, because teams is a collection-valued relationship field. To reach the sport field, the FROM clause could define an identification variable named t for the teams field:

FROM Player AS p, IN (p.teams) t 
WHERE t.league.sport = 'soccer' 

WHERE Clause

The WHERE clause specifies a conditional expression that limits the values returned by the query. The query returns all corresponding values in the data store for which the conditional expression is TRUE. Although usually specified, the WHERE clause is optional. If the WHERE clause is omitted, then the query returns all values. The high-level syntax for the WHERE clause follows:

where_clause ::= WHERE conditional_expression 

Literals

There are three kinds of literals: string, numeric, and Boolean.

String Literals

A string literal is enclosed in single quotes:

'Duke' 

If a string literal contains a single quote, you indicate the quote by using two single quotes:

'Duke''s' 

Like a Java String, a string literal in EJB QL uses the Unicode character encoding.

Numeric Literals

There are two types of numeric literals: exact and approximate.

An exact numeric literal is a numeric value without a decimal point, such as 65, -233, and +12. Using the Java integer syntax, exact numeric literals support numbers in the range of a Java long.

An approximate numeric literal is a numeric value in scientific notation, such as 57., -85.7, and +2.1. Using the syntax of the Java floating-point literal, approximate numeric literals support numbers in the range of a Java double.

Boolean Literals

A Boolean literal is either TRUE or FALSE. These keywords are not case-sensitive.

Input Parameters

An input parameter is designated by a question mark (?) followed by an integer. For example, the first input parameter is ?1, the second is ?2, and so forth.

The following rules apply to input parameters:

Conditional Expressions

A WHERE clause consists of a conditional expression, which is evaluated from left to right within a precedence level. You can change the order of evaluation by using parentheses.

Here is the syntax of a conditional expression:

conditional_expression ::= conditional_term |
    conditional_expression OR conditional_term

conditional_term ::= conditional_factor | 
    conditional_term AND conditional_factor

conditional_factor ::= [NOT ] conditional_primary

conditional_primary ::= simple_cond_expression |
     (conditional_expression)

simple_cond_expression ::=
    comparison_expression | between_expression | 
    like_expression | in_expression |
    null_comparison_expression |
    empty_collection_comparison_expression |
    collection_member_expression 

Operators and Their Precedence

Table 29-2 lists the EJB QL operators in order of decreasing precedence.

Table 29-2 EJB QL Operator Precedence
Type
Precedence Order
Navigation
. (a period)
Arithmetic

+ - (unary)
* / (multiplication and division)
+ - (addition and subtraction)
Comparison




=
>
>=
<
<=
<> (not equal)
Logical

NOT
AND
OR

BETWEEN Expressions

A BETWEEN expression determines whether an arithmetic expression falls within a range of values. The syntax of the BETWEEN expression follows:

between_expression ::= 
  arithmetic_expression [NOT] BETWEEN 
  arithmetic_expression AND arithmetic_expression  

These two expressions are equivalent:

p.age BETWEEN 15 AND 19 
p.age >= 15 AND p.age <= 19 

The following two expressions are also equivalent:

p.age NOT BETWEEN 15 AND 19 
p.age < 15 OR p.age > 19 

If an arithmetic expression has a NULL value, then the value of the BETWEEN expression is unknown.

IN Expressions

An IN expression determines whether or not a string belongs to a set of string literals. Here is the syntax of the IN expression:

in_expression ::=
    cmp_path_expression [NOT ] IN
    ( {literal | input_parameter} 
    [, { literal | input_parameter} ]*) 

The path expression must have a string or numeric value. If the path expression has a NULL value, then the value of the IN expression is unknown.

In the following example, if the country is UK the expression is TRUE. If the country is Peru it is FALSE.

o.country IN ('UK', 'US', 'France') 

LIKE Expressions

A LIKE expression determines whether a wildcard pattern matches a string. Here is the syntax:

like_expression ::=
    cmp_path_expression [NOT ] LIKE 
    pattern_value [ESCAPE escape_character] 

The path expression must have a string or numeric value. If this value is NULL, then the value of the LIKE expression is unknown. The pattern value is a string literal that can contain wildcard characters. The underscore (_) wildcard character represents any single character. The percent (%) wildcard character represents zero or more characters. The ESCAPE clause specifies an escape character for the wildcard characters in the pattern value. Table 29-3 shows some sample LIKE expressions.

Table 29-3 LIKE Expression Examples
Expression
TRUE
FALSE
address.phone LIKE '12%3'
'123'
'12993'
'1234'
asentence.word LIKE 'l_se'
'lose'
'loose'
aword.underscored LIKE '\_%' ESCAPE '\'
'_foo'
'bar'
address.phone NOT LIKE '12%3'
'1234'
'123'
'12993'

NULL Comparison Expressions

A NULL comparison expression tests whether a single-valued path expression or an input parameter has a NULL value. Usually, the NULL comparison expression is used to test whether or not a single-valued relationship has been set. Here is the syntax of a NULL comparison expression:

null_comparison_expression ::=
    {single_valued_path_expression |
   
 input_parameter}IS [NOT ] NULL 

Empty Collection Comparison Expressions

An empty collection comparison expression tests whether a collection-valued path expression has no elements. In other words, it tests whether or not a collection-valued relationship has been set. Here is the syntax:

empty_collection_comparison_expression ::= 
   collection_valued_path_expression IS [NOT] EMPTY 

If the collection-valued path expression is NULL, then the empty collection comparison expression has a NULL value.

Collection Member Expressions

The collection member expression determines whether a value is a member of a collection. The value and the collection members must have the same type. The expression syntax follows:

collection_member_expression ::=
    {single_valued_cmr_path_expression |
    identification_variable | input_parameter}
    [NOT ] MEMBER [OF ] collection_valued_path_expression 

If either the collection-valued or single-valued path expression is unknown, then the collection member expression is unknown. If the collection-valued path expression designates an empty collection, then the collection member expression is FALSE.

Functional Expressions

EJB QL includes several string and arithmetic functions, which are listed in the following tables. In Table 29-4, the start and length arguments are of type int. They designate positions in the String argument. The first position in a string is designated by 1. In Table 29-5, the number argument can be either an int, a float, or a double.

Table 29-4 String Expressions 
Function Syntax
Return Type
CONCAT(String, String)
String
LENGTH(String)
int
LOCATE(String, String [, start])
int
SUBSTRING(String, start, length)
String

Table 29-5 Arithmetic Expressions 
Function Syntax
Return Type
ABS(number)
int, float, or double
MOD(int, int)
int
SQRT(double)
double

NULL Values

If the target of a reference is not in the persistent store, then the target is NULL. For conditional expressions containing NULL, EJB QL uses the semantics defined by SQL92. Briefly, these semantics are as follows:

Equality Semantics

In EJB QL, only values of the same type can be compared. However, this rule has one exception: Exact and approximate numeric values can be compared. In such a comparison, the required type conversion adheres to the rules of Java numeric promotion.

EJB QL treats compared values as if they were Java types and not as if they represented types in the underlying data store. For example, if a persistent field could be either an integer or a NULL, then it must be designated as an Integer object and not as an int primitive. This designation is required because a Java object can be NULL but a primitive cannot.

Two strings are equal only if they contain the same sequence of characters. Trailing blanks are significant; for example, the strings 'abc' and 'abc ' are not equal.

Two entity beans of the same abstract schema type are equal only if their primary keys have the same value. Table 29-8 shows the operator logic of a negation, and Table 29-9 shows the truth values of conditional tests.

Table 29-8 NOT Operator Logic 
NOT Value
Value
T
F
F
T
U
U

Table 29-9 Conditional Test 
Conditional Test
T
F
U
Expression IS TRUE
T
F
F
Expression IS FALSE
F
T
F
Expression is unknown
F
F
T

SELECT Clause

The SELECT clause defines the types of the objects or values returned by the query. The SELECT clause has the following syntax:

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

select_expression ::= single_valued_path_expression |
    aggregate_select_expression

aggregate_select_expression ::=
    {AVG |MAX |MIN |SUM |COUNT }( [DISTINCT ]
    cmp_path_expression) |
    COUNT ( [DISTINCT ] identification_variable |
    single_valued_cmr_path_expression) 

Return Types

The return type defined by the SELECT clause must match that of the finder or select method for which the query is defined.

For finder method queries, the return type of the SELECT clause is the abstract schema type of the entity bean that defines the finder method. This abstract schema type maps to either a remote or a local interface. If the bean's remote home interface defines the finder method, then the return type is the remote interface (or a collection of remote interfaces). Similarly, if the local home interface defines the finder method, the return type is the local interface (or a collection). For example, the LocalPlayerHome interface of the PlayerBean entity bean defines the findall method:

public Collection findAll() throws FinderException; 

The EJB QL query of the findall method returns a collection of LocalPlayer interface types:

SELECT OBJECT(p) 
FROM Player p 

For select method queries (except for aggregate function queries), the return type of the SELECT clause can be one of the following:

The PlayerBean entity bean, for example, implements the ejbSelectSports method, which returns a collection of String objects for sport. The sport is a persistent field of the LeagueBean entity bean. See Example 11 .

A SELECT clause cannot specify a collection-valued expression. For example, the SELECT clause p.teams is invalid because teams is a collection. However, the clause in the following query is valid because the t is a single element of the teams collection:

SELECT t
FROM Player p, IN (p.teams) AS t 

For select method queries with an aggregate function (AVG, COUNT, MAX, MIN, or SUM) in the SELECT clause, the following rules apply:

DISTINCT and OBJECT Keywords

The DISTINCT keyword eliminates duplicate return values. If the method of the query returns a java.util.Collection--which allows duplicates--then you must specify the DISTINCT keyword to eliminate duplicates. However, if the method returns a java.util.Set, the DISTINCT keyword is redundant because a java.util.Set cannot contain duplicates.

The OBJECT keyword must precede a stand-alone identification variable, but it must not precede a single-valued path expression. If an identification variable is part of a single-valued path expression, it is not stand-alone.

Aggregate Functions

The SELECT clause can contain an aggregate function with the following syntax:

aggregate_select_expression ::=
    {AVG |MAX |MIN |SUM |COUNT }( [DISTINCT ]
    cmp_path_expression) |
    COUNT ( [DISTINCT ] identification_variable |
    single_valued_cmr_path_expression) 

Except for the COUNT function, the path expression argument for an aggregate function must terminate in a persistent field. For the COUNT function, the path expression argument can terminate in a persistent field, a relationship field, or an identification variable.

The arguments of the SUM and AVG functions must be numeric. The arguments of the MAX and MIN functions must be orderable: numeric, string, character, or date.

If the argument is empty, the COUNT function returns 0 and the other aggregate functions return NULL.

If the DISTINCT keyword is specified, duplicate values are eliminated before the aggregate function is applied. NULL values are always eliminated before the function is applied, whether or not the DISTINCT keyword is used.

ORDER BY Clause

As its name suggests, the ORDER BY clause orders the values or objects returned by the query. The syntax of the clause follows:

orderby_clause ::= ORDER BY orderby_item [, orderby_item]*

orderby_item ::= cmp_path_expression [ASC |DESC ] 

If the ORDER BY clause contains multiple orderby_item elements, the left-to-right sequence of the elements determines the high-to-low precedence.

The ASC keyword specifies ascending order (the default), and the DESC keyword indicates descending order.

If the ORDER BY clause is used, then the SELECT clause must be one of the following:

If the SELECT clause is an identification variable or a single_valued_cmr_path_expression, then the orderby_item must be an orderable persistent field of the entity bean returned by the SELECT clause. If the SELECT clause is a cmp_path_expression, then the cmp_path_expression and the orderby_item must evaluate to the same persistent field of the same entity bean.