ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

Learning EJB QL
Pages: 1, 2, 3

The SELECT clause

The SELECT clause denotes the result of the query. The SELECT clause syntax is below, in BNF format:

SELECT [DISTINCT] {single_valued_path_expression | OBJECT (identification_variable)

DISTINCT works in the same way as SQL in selecting unique values from the query result.

You may also restrict the return type to contain only unique values by declaring java.util.Set as the return type for the finder or select methods. Since java.util.Set doesn't allow duplicate values, whenever the return type is java.util.Set, the container internally applies DISTINCT to the query. Therefore, it is not required to explicitly use the DISTINCT identifier in the query string. But when the return type is a java.util.Collection, then it requires an explicit DISTINCT identifier in the query expression to get unique values.

The SELECT clause determines the type of values returned by a query. For example, to get all orders, the query is:


Here, the order is the abstract-schema-name for OrderEJB.

Similarly, to get all products that are associated with a line item:

SELECT li.product FROM Order As o, IN(o.lineItems) li

These line items are the cmr-field name of LineItemsEJB.

Now, we see the following query, to get all line items related to an Order:

SELECT o.lineItems FROM Order As o

Looking carefully, the above query doesn't work, because the return type of the query is not a single-valued expression. The earlier query to get all products associated with line items works fine. This is because the relationship between line item and product is one-to-one, whereas the relationship between order and line items is one-to-many. The single valued path expression is the single_valued_cmr_field, which is a cmr-field name in one-to-one or many-to-one relationship. Since the relationship between order and line is one-to-many, the result yields a collection_valued_path_expression type.

Therefore, the SELECT clause must be specified to return a single valued expression. The SELECT clause of a query defined for a finder method must always correspond to the abstract schema type of entity bean for which the finder method is defined. The SELECT clause of a query defined for a select method returns abstract schema types of other entity beans, as well as the values for cmp-fields.

Now, you will see some queries of select methods whose return type is that of cmp-field. To select the names of all products that have been ordered:

SELECT DISTICT li.product.name FROM order o, IN (o.lineItems) li

Here, order is the abstract-type-name of OrderEJB. lineItems and products are cmr-field names. name is the cmp-field name defined for the entity bean ProductEJB.

EJB QL also provides built-in functions for performing simple operations on objects of String class and primitive types. Specifically, EJB QL provides the following built-in functions:

String Functions:

  • CONCAT (String, String) - This function returns the concatenated string value.
  • SUBSTRING (String, start, length) - This function returns the substring of the original string.
  • LOCATE (String, String [, start]) This function returns int.
  • LENGTH (String) This function returns the length of the string passed as parameter.

Arithmetic Functions:

  • ABS (number)
  • SQRT (double)


  • EJB QL provides a standard way to define queries for entity beans in a portable way. An entity bean with EJB QL defined for its finder and select methods may be portable across any EJB container that adheres to the EJB 2.0 specification container managed persistence.

  • An EJB Container parses and validates EJB QL queries before entity beans are deployed, since EJB QL uses abstract schema types and cmr-fields to navigate across entity beans.


  • Some of the useful features of SQL are not yet provided by EJB QL. For example, the ORDER BY identifier, which becomes very handy as the application becomes large and complex, is not yet supported in EJB QL. Some of the application servers may provide these features, but usage of the same may limit portability across application servers.

  • Date and time values should be passed as millisecond value using Java primitive type, long.

  • Related Reading

    Enterprise JavaBeans, 3rd EditionEnterprise JavaBeans, 3rd Edition
    By Richard Monson-Haefel
    Table of Contents
    Sample Chapter
    Full Description
    Read Online -- Safari

  • EJB QL does not support fixed decimal comparison in arithmetic expressions.

  • String and Boolean comparison is restricted to = and <>. However, the built-in functions(CONCAT, SUBSTRING, etc.) can be used to perform other operations on String.

  • EJB QL does not support comments.


The addition of EJB QL to the new EJB 2.0 specification justifies the distributed component architecture as the standard way of defining queries. EJB QL allows applications to be more portable. I believe future versions of EJB QL may provide support for more built-in functions, as well as other SQL features like ORDER BY. In the EJB 2.0 specification, the data model for CMP does not currently support inheritance; therefore, you cannot compare objects or value classes of different types. This may be addressed in future versions of EJB QL.

Jeelani B. Shaik is a senior Java software engineer at Infinity Markets in Santa Clara, Calif.

Return to ONJava.com.