Home TOC Index

Search Feedback

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 Enterprise JavaBeans Specification.

BNF Grammar of EJB QL

Here is the entire BNF diagram for EJB QL:

EJB QL ::= select_clause from_clause [where_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 	
	
single_valued_path_expression ::=	
    {single_valued_navigation |	
    identification_variable}.cmp_field |	
    single_valued_navigation	
	
single_valued_navigation ::= 	
    identification_variable.[single_valued_cmr_field.]*	
    single_valued_cmr_field	
	
collection_valued_path_expression ::= 	
    identification_variable.[single_valued_cmr_field.]*	
    collection_valued_cmr_field	
	
select_clause ::= SELECT [DISTINCT]	
    {single_valued_path_expression |	
    OBJECT(identification_variable)}	
	
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	
	
conditional_test :: = 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 ::= 	
    single_valued_path_expression 	
    [NOT] IN (string_literal [, string_literal]* ) 	
	
like_expression ::= 	
    single_valued_path_expression 	
    [NOT] LIKE pattern_value [ESCAPE escape-character] 	
	
null_comparison_expression ::= 	
    single_valued_path_expression IS [NOT] NULL	
	
empty_collection_comparison_expression ::= 	
    collection_valued_path_expression IS [NOT] EMPTY	
	
collection_member_expression ::=	
    {single_valued_navigation | identification_variable |	
    input_parameter}	
    [NOT] MEMBER [OF] collection_valued_path_expression	
	
comparison_expression ::= 	
    string_value { =|<>} string_expression | 	
    boolean_value { =|<>} boolean_expression} | 	
    datetime_value { = | <> | > | < } datetime_expression | 	
    entity_bean_value { = | <> } entity_bean_expression | 	
    arithmetic_value comparison_operator	
    single_value_designator	
	
arithmetic_value ::= single_valued_path_expression |	
    functions_returning_numerics	
	
single_value_designator ::= scalar_expression	
	
comparison_operator ::= 	
    = | > | >= | < | <= | <>	
	
scalar_expression ::= arithmetic_expression 	
	
arithmetic_expression ::= arithmetic_term |	
    arithmetic_expression { + | - } arithmetic_term	
	
arithmetic_term ::= arithmetic_factor | 	
    arithmetic_term { * | / } arithmetic_factor	
	
arithmetic_factor ::= { + |- } arithmetic_primary	
	
arithmetic_primary ::= single_valued_path_expression | 	
    literal | (arithmetic_expression) |	
    input_parameter | functions_returning_numerics	
	
string_value ::= single_valued_path_expression |	
    functions_returning_strings	
	
string_expression ::= string_primary | input_expression	
	
string_primary ::= single_valued_path_expression | literal |	
    (string_expression) | functions_returning_strings 	
	
datetime_value ::= single_valued_path_expression	
	
datetime_expression ::= datetime_value | input_parameter	
	
boolean_value ::= single_valued_path_expression	
	
boolean_expression ::= single_valued_path_expression | 	
   literal | input_parameter	
	
entity_bean_value ::= 	
    single_valued_navigation | 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) 
 

BNF Symbols

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

Table 8-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, horizontal tab, or form feed

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 non-first 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 BETWEEN DISTINCT EMPTY FALSE FROM IN IS LIKE

MEMBER NOT NULL OBJECT OF OR SELECT 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 may reference identification variables, they cannot declare them. All identification variables must be declared in the FROM clause.

Since 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 previous 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 may contain multiple declarations, separated by commas. A declaration may 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 may include the optional AS operator:

FROM Player AS p
 

In most cases, to obtain objects a query navigates through the relationships with path expressions. 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 may 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 may 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 may 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 named 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 may 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

There are two types of path expressions: single-valued and collection-valued. Here is the syntax for path expressions:

single_valued_path_expression ::=	
    {single_valued_navigation |	
    identification_variable}.cmp_field |	
    single_valued_navigation	
	
single_valued_navigation ::= 	
    identification_variable.[single_valued_cmr_field.]*	
    single_valued_cmr_field	
	
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 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 single-valued 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 single-valued 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 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:

• Persistent field
• Single-valued relationship field
• Collection-valued relationship field

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 TeamEJB 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 may 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, since 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 with 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, +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, +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:

• They can be used only in a WHERE clause.
• Their use is restricted to a single-valued path expression within a conditional expression.
• They must be numbered, starting with the integer 1.
• The number of input parameters in the WHERE clause must not exceed the number of input parameters in the corresponding finder or select method.
• The type of an input parameter in the WHERE clause must match the type of the corresponding argument in the finder or select method.

Conditional Expressions

A WHERE clause consists of a conditional expression, which is evaluated from left to right within a precedence level. You may change the order of evaluation with 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_test	
	
conditional_test :: = 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 8-2 lists the EJB QL operators in order of decreasing precedence.

Table 8-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 ::= 	
    single_valued_path_expression 	
    [NOT] IN (string_literal [, string_literal]* )
 

The single-valued path expression must have a String value. If the single-valued 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 ::= 	
    single_valued_path_expression 	
    [NOT] LIKE pattern_value [ESCAPE escape-character]
 

The single-valued path expression must have a String value. If this value is NULL, then the value of the LIKE expression is unknown. The pattern value is a string literal that may 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 8-3 shows some sample LIKE expressions. The TRUE and FALSE columns indicate the value of the LIKE expression for a single-valued path expression.

Table 8-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 has a NULL value. Usually, this expression is used to test whether or not a single-valued relationship has been set. If a path expression contains a NULL value during evaluation, it returns a NULL value. Here is the syntax of a NULL comparison expression:

null_comparison_expression ::= 	
    single_valued_path_expression 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_navigation | identification_variable |	
    input_parameter}	
    [NOT] MEMBER [OF] collection_valued_path_expression
 

If the collection-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 8-4, the start and length arguments are of type int. They designate positions in the String argument. In Table 8-5, the number argument may be either an int, a float, or a double.

Table 8-4 String Expressions 

Function Syntax	Return Type
CONCAT(String, String)	String
SUBSTRING(String, start, length)	String
LOCATE(String, String [, start])	int
LENGTH(String)	int

Table 8-5 Arithmetic Expressions 

Function Syntax	Return Type
ABS(number)	int, float, or double
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:

• If a comparison or arithmetic operation has an unknown value, it yields a NULL value.
• If a path expression contains a NULL value during evaluation, it returns a NULL value.
• The IS NULL test converts a NULL persistent field or a single-valued relationship field to TRUE. The IS NOT NULL test converts them to FALSE.
• Boolean operators and conditional tests use the three-valued logic defined by the following tables. (In these tables, T stands for TRUE, F for FALSE, and U for unknown.)

  Table 8-6 AND Operator Logic 

  AND	T	F	U
  T	T	F	U
  F	F	F	F
  U	U	F	U

  Table 8-7 OR Operator Logic 

  OR	T	F	U
  T	T	T	T
  F	T	F	U
  U	T	U	U

  Table 8-8 NOT Operator Logic 

  NOT
  T	F
  F	T
  U	U

  Table 8-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

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, 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, 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.

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]	
    {single_valued_path_expression |	
    OBJECT(identification_variable)}
 

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 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). Likewise, 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 PlayerEJB 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, the return type of the SELECT clause may be one of the following:

• The abstract schema of the entity bean that contains the select method.
• The abstract schema of a related entity bean

  (By default, each of these abstract schema types maps to the local interface of the entity bean. Although uncommon, in the deployment descriptor you may override the default mapping by specifying a remote interface.)

• A persistent field

The PlayerEJB 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 LeagueEJB 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 SELECT 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
 

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 may not 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.

Home TOC Index

Search Feedback