TechFAQ

EJB QL is a query specification language for the FINDER and SELECT methods of entity beans with container-managed persistence (CMP). EJB QL can be compiled to a target language, such as SQL, of a database or other persistent store. This allows the execution of queries to be shifted to the native language facilities provided by the persistent store, instead of requiring queries to be executed on the runtime representation of the entity beans' state. As a result, query methods can be optimizable as well as portable.

The Enterprise JavaBeans query language uses the abstract persistence schemas of entity beans, including their relationships, for its data model. It defines operators and expressions based on this data model. The Bean Provider uses EJB QL to write queries based on the abstract persistence schemas and the relationships defined in the deployment descriptor. EJB QL depends on navigation and selection based on the cmp-fields and cmr-fields of the related entity beans. The Bean Provider can navigate from an entity bean to other entity beans by using the names of cmr-fields in EJB QL queries.

When defining EJB QL, we are limited to referring only to elements described below:

An EJB QL query is a string which consists of the following three clauses:

EJB QL :: = SELECT_clause FROM_clause [WHERE_clause]

An EJB QL query MUST always have a SELECT and a FROM clause. The WHERE clause is OPTIONAL.

An EJB QL query MAY have parameters that correspond to the parameters of the finder or select method for which it is defined.

An EJB QL query is statically defined in the ejb-ql deployment descriptor element. There must be ONE ejb-ql tag per query definition for each finder and select method.

<entity>
	<ejb-name>CustomerEJB</ejb-name>
	...
	<abstract-schema-name>Customer</abstract-schema-name>
	<cmp-field><field-name>id</field-name></cmp-field>
	<cmp-field><field-name>lastName</field-name></cmp-field>
	<cmp-field><field-name>firstName</field-name></cmp-field>
	<cmp-field><field-name>hasGoodCredit</field-name></cmp-field>
	...
	<query>
		<query-method>
			<method-name>findByName</method-name>
			<method-params>
				<method-param>java.lang.String</method-param>
				<method-param>java.lang.String</method-param>
			</method-params>
		</query-method>
		<ejb-ql>
			SELECT OBJECT(c) FROM Customer c
			WHERE c.lastName = ?1 AND c.firstName = ?2
		</ejb-ql>
	</query>

	<query>
		<query-method>
			<method-name>findByGoodCredit</method-name>
			<method-params/>
		</query-method>
		<ejb-ql>
			SELECT OBJECT(c) FROM Customer c
			WHERE c.hasGoodCredit = TRUE
		</ejb-ql>
	</query>

	<query>
		<query-method>
			<method-name>ejbSelectLastNames</method-name>
			<method-params/>
		</query-method>
		<ejb-ql>
			SELECT c.lastName FROM Customer AS c
		</ejb-ql>
	</query>
	...
</entity>

					

EJB QL is used for two types of query methods:

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:

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.