Reserved words are (keywords are NOT case sensitive): SELECT, FROM, WHERE, DISTINCT, OBJECT, NULL, TRUE, FALSE, NOT, AND, OR, BETWEEN, LIKE, IN, AS, UNKNOWN, EMPTY, MEMBER, OF, IS and ?.

SELECT clause

The SELECT clause defines the objects that are returned from a query.

select_clause ::== SELECT [DISTINCT] {single-valued-path-expr} | OBJECT(ident_var)

All standalone identification variables in the SELECT clause must be qualified by the OBJECT operator. The SELECT clause must not use the OBJECT operator to qualify path expressions.

The following example returns a collection of the CMP fields of type lastName (a collection of Strings):

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SELECT cust.lastName FROM Customer AS cust ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

The following example returns a collection of Customer instances:

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SELECT OBJECT(cust) FROM Customer AS cust ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

The optional keyword DISTINCT effects a removal of duplicates in the returned collection.

If the EJB QL query is specified for a method whose return type is java.util.Collection, the collection of values returned by the Container may contain duplicates if DISTINCT is not specified in the SELECT clause. If the query is specified for a method whose result type is java.util.Set, but does not specify DISTINCT, the container must interpret the query as if SELECT DISTINCT had been specified.

FROM clause

The FROM clause defines the set of beans from which the query results are formed and which are used in the WHERE clause.

FROM_clause ::== FROM ident_variable_d {,ident_variable_d}

ident_variable_d ::= collection_member_d | range_variable_d

Identification variables can be either declared by using a range variable declaration (AS) or a collection member declaration (IN). Identification variables can only be declared in the FROM clause. Keep in mind that the statement is evaluated from the left to the right side. This is important for referencing within declarations to other variable declarations; referenced identifiers must already be declared. Unlike a Java variable, an EJB QL identifier IS NOT case sensitive.

range_variable_d ::= abstract_schema_name [AS] identifier

The AS keyword is optional. It is assumed if we omit it.

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SELECT OBJECT(cust) FROM Customer AS cust;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SELECT OBJECT(cust) FROM Customer cust;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

collection_member_d ::== IN collection_valued_path_expr [AS] identifier

A collection member declaration declares an identification variable that represents an entity that is reached by navigating a CMR field in one-to-many or many-to-many relationships:

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SELECT OBJECT(cust) FROM Customer AS cust, IN(cust.accounts) ;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SELECT OBJECT(acct) FROM Account AS acct, IN(acct.transrecords) ;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

The IN specification enables us to use attributes of related entities in the WHERE clause of the EJB QL statement.

With a PATH EXPRESSION, we can address a CMP or a CMR field of a specified type. With path expressions, we are able to navigate along the container-managed relationships between entity beans. The navigation operator dot separates the type from the attribute, for example customer.accounts. A path expression that ends in a cmp-field is terminal and cannot be further composed.

Navigation to a related entity beans results in a value of the related entity bean's abstract schema type.

It is syntactically illegal to compose a path expression from a path expression that evaluates to a collection. To handle such a navigation, an identification variable must be declared in the FROM clause to range over the elements of the collection. Another path expression must be used to navigate over each such element in the WHERE clause of the query.

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SELECT OBJECT(o);;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
FROM Order AS o, IN(o.lineItems) l;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
WHERE l.product.name = 'widget';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

WHERE clause

As in SQL the WHERE clause contains a conditional expression. The WHERE clause is optional in a query statement.

WHERE_clause ::== WHERE conditional_expr

conditional_expr ::== conditional_term | conditional_expr {OR | AND} conditional_term

A conditional expression consists of other conditional expressions, logical or comparison operations or path expressions. At the end they have to evaluate to boolean values or boolean literals.

Conditional expressions can use:

• operators (+ - * /) .
• comparisons (= > >= < <= <>), for strings only = and <> .
• NOT .
• BETWEEN arithmetic-expression AND arithmetic-expression .
• IN: value IN (value1, value2, ...)
• LIKE, using wild card characters _ and %
• null comparison: IS NULL
• empty comparison: collection-valued-path-expr IS EMPTY
• collection member expression: MEMBER OF collection-valued-path-expr
• functional expressions: CONCAT, SUBSTRING, LOCATE, LENGTH, ABS, SQRT

To pass parameters to the query statement, the declared parameters of the finder method are accessible in the query statement as a question mark followed by a number, such as, ?1, ?2, ?3. The number is the position of the declaration in the finder method's signature. Input parameters are numbered starting from 1. The number of distinct input parameters in an EJB QL query must not exceed the number of input parameters for the FINDER or SELECT method. It is not required that the EJB QL query use all of the input parameters for the FINDER or SELECT method.

For example, matching finder method declaration and EJB QL statements are:

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
findCustomerByName(String lastName, String firstName); ;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SELECT OBJECT(c) FROM Customer c WHERE c.lastName = ?1 AND c.firstName = ?2;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

The type of the parameter in the statement is defined by the declaration of the correspondent parameter in the finder method signature. Parameters can be primitive types (int), wrapper classes (Integer) or EJB objects.

Using a parameter is only allowed in the WHERE clause within a conditional expression that involves a single-valued path expression, such as x.lastName.

________________
Author: Mikalai Zaikin. Please Click Here to visit Authors site for any updates and changes to the study notes.

First Page: SCBCD Study Notes : Chapter 9 : EJB-QL (Part-1)