4
EJBQL Support

Version 2.0 of the EJB specification presents a new query language, called EJBQL. EJBQL is similar to SQL, but differs in that it presents queries from an object model perspective, as opposed to a database perspective.

This chapter discusses the following:

• Why use EJBQL?

• EJBQL structure

• Using EJBQL with TopLink

EJBQL is designed to be compiled to the target language of the persistent data store used by a persistence manager. What differentiates it primarily from SQL is that it includes path expressions that enable navigation over the relationships defined for entity beans and dependent objects. The complete EJB specification, including EJBQL can be found at http://java.sun.com/products/ejb/2.0.html.

Why use EJBQL?

TopLink uses EJBQL to enable users to declare queries using the attributes of each abstract entity bean in the object model. This offers the following advantages:

• There is no need to know the database structure (tables, fields).

• Relationships can be used in a query, via navigation from attribute to attribute.

• Users can construct queries using the attributes of the entity beans instead of using database tables and fields.

• Queries are database-independent so they are portable.

• Users can use "SELECT" to specify the query's reference class (entity bean which you are querying against)

• EJBQL includes support for an attribute level "SELECT" to pick a specific field out of a result set, and answer it.

EJBQL structure

An EJBQL query can contain any of the following components:

FROM clause

The FROM clause defines the scope of the query. All identification variables used in the rest of the query are defined in this clause. This clause may also contain the key words IN and AS. Queries must contain a FROM clause to be valid.

SELECT clause

The SELECT defines the return values of the EJBQL query. Return values can be either an attribute, or Entity bean or Java object.

WHERE clause

The WHERE clause is a conditional expression used to restrict the results of a query. The WHERE clause is optional.

A note about notation

The examples in this section use a modified Backus-Naur Form (BNF). For more information on BNF, see "About Backus Naur Form" .

Basic structure

All EJBQL statements follow the same basic structure:

SELECT selectClause FROM fromClause [WHERE whereExpression]

The FROM clause

The FROM clause defines the scope of the query by declaring identification variables. The FROM clause designates the domain of the query, which may be constrained by path expressions. This is a mandatory part of the EJBQL statement, and must be in the following syntax:

FROM {identification variabledeclaration}+

The {identification variabledeclaration}+ argument resolves to {AbstractSchemaName entityBeanVariable}, and may be followed by any number of either of the following:

AbstractSchemaName entityBeanVariable, IN(entityBeanVariablePath) [AS] 
oneToManyVariable 

Note: If a FROM clause contains more than one identification variabledeclaration, the expressions must be separated by commas.

This syntax requires entityBeanVariablePath to be specified using the following syntax:

entityBeanVariable[.oneToOneRelationshipAttribute]*.oneToManyRelationshipAttribu
te 

The FROM clause defined

There are two components to the FROM clause.

FROM {AbstractSchemaName entityBeanVariable}

AbstractSchemaName is the name specified as an alias for the entity bean using the tag abstract-schema-name in the ejb-jar.xml file. For example:

<abstract-schema-name>EmployeeBean</abstract-schema-name>

As indicated, there is always at least one {AbstractSchemaName entityBeanVariable} element, and there may be more. If a FROM clause contains more than one AbstractSchemaName expression, the expressions must be separated by commas

[oneToManyVariable IN entityBeanVariablePath]

This sub clause associates the oneToManyVariable with the oneToManyRelationshipAttribute at the end of the entityBeanVariablePath. This element is optional.

Using the FROM clause: a few examples

A simple example

The simplest query consists of only select and from clauses:

SELECT OBJECT(employee) FROM EmployeeBean employee

This query declares employee as a variable representing the EmployeeBean entity bean, and returns all employees in the database.

Using IN to query reference classes

The IN keyword designates that the preceding identifier will evaluate to a collection. You can include IN in FROM clause queries to search reference classes:

SELECT OBJECT(employee) FROM EmployeeBean employee, IN(employee.phoneNumbers) 
phoneNumber

In addition to employee, this declares phoneNumber as a variable representing PhoneNumber. This is because employee.phoneNumbers is a one-to-many relationship whose reference class is PhoneNumber.

SELECT OBJECT(employee) FROM EmployeeBean employee, 
IN(employee.manager.phoneNumbers) phoneNumber

This declares phoneNumber as a variable representing PhoneNumber. In this case, manager is the owner of the one-to-many relationship. This implies that phoneNumber will be related to the manager of the employee(s) in the result set, as opposed to the employees themselves.

Using AND

The AND operator enables you to combine logical arguments in your query. For example, the following query searches employees with the last name, "Smith", and the phone number area code, "613":

SELECT OBJECT(employee) FROM EmployeeBean employee, IN(employee.phoneNumbers) 
phoneNumber
WHERE employee.lastName = "Smith" AND
phoneNumber.areaCode = "613"

Using AS

The FROM clause can contain an AS used to designate an identifier for the rest of the query. The following two queries are semantically equivalent.

SELECT OBJECT(employee) FROM EmployeeBean AS employee WHERE employee.id = 12
SELECT OBJECT(employee) FROM EmployeeBean employee WHERE employee.id = 12

The SELECT clause

The SELECT clause defines the types of values to be returned by the query. The return type must be a container-managed relationship (CMR) or a container-managed field (CMF) field for the bean associated with the query.

The SELECT clause defines the types of values to be returned by the query. For TopLink, this defines the reference class and attribute (if specified) returned by the query. It must conform to the following syntax:

SELECT OBJECT(<entity bean variable.>)
SELECT entityBeanVariable{.attribute}+

Using the SELECT clause: a few examples

A simple example

This example returns a collection of EmployeeBeans:

SELECT OBJECT(employee) from EmployeeBean employee

Adding attributes

Adding an attribute to the end of the entityBeanVariable enables you select only that attribute from the result set. For example, this query returns a collection of the areaCodes of the associated PhoneNumbers:

SELECT phoneNumber.areaCode FROM PhoneNumber phoneNumber

Using DISTINCT

Adding the DISTINCT keyword to a query specifies that the query must eliminate duplicate values from the result set.

SELECT DISTINCT OBJECT(employee) FROM EmployeeBean employee

The WHERE clause

The WHERE clause is by far the most complex and powerful of the clauses in EJBQL. It is used to define the selection criteria of a query, and consists of a combination of one or more of the following:

• Literals

• Identification variables

• Path expressions

• Input parameters

• Conditional expressions

• Logical

• Boolean and Comparison operators

The Range expressions IN, LIKE and BETWEEN are specified and are modeled on the equivalent SQL behavior. There are also NULL tests and several Functional Expressions (ABS, CONCAT, LENGTH, SQRT, SUBSTRING).

The WHERE clause is an optional part of the EJBQL statement, and must be in the following syntax:

WHERE conditionalExpression

The WHERE syntax requires conditionalExpression to be defined using the following syntax:

conditionalTerm [OR conditionalTerm]*

The conditionalExpression syntax requires conditionalTerm to be defined using the following syntax:

conditionalFactor [AND conditionalFactor]*

Using constants

The WHERE clause supports the use of String, Integers, Floats, ... in defining the selection criteria for the query. Strings are delimited by quotation marks ("<string>").

Comparison Operators

EJBQL supports "=", "<", ">", ">=", "<=", and "<>" comparison operators for arithmetic functions. It also supports "=" and "<>" for non-arithmetic comparisons

=

The = operator checks to see if the value or string on the left side of the expression is equal to the value or string on the right. It supports both arithmetic and non-arithmetic comparisons:

Arithmetic example

The employee whose id = 25001

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.id = 25001 

Non arithmetic example

Any employee whose first name is Bob

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.firstName = "Bob"

<

The < operator checks to see if the value on the left side of the expression is less than the value on the right.

Example

All employees whose id is less than 25001

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.id < 25001

>

The > operator checks to see if the value on the left side of the expression is greater than the value on the right.

Example

All employees whose id is greater than 25001

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.id > 25001

<=

The <= operator checks to see if the value on the left side of the expression is less than or equal to the value on the right.

Example

All employees whose id is less than or equal to 25001

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.id <= 25001

>=

The >= operator checks to see if the value on the left side of the expression is greater than or equal to than the value on the right.

Example

All employees whose id is greater than or equal to 25001

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.id >= 25001

< >

The <> operator checks to see if the value on the left side of the expression is not equal to than the value on the right.

Arithmetic example

All employees whose id does not equal 25001

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.id <> 25001

Note: This can also be represented as: FROM EmployeeBean emp WHERE NOT(emp.id = 25001)

Non arithmetic example

Any employee who does not live in Ottawa

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.address.city <> "Ottawa"

Logical operators

EJBQL supports AND, OR, and NOT as logical operators. The precedence order is NOT, AND then OR. This can be modified with brackets which take precedence over all other operators.

AND

Use of the AND operator enables you to combine two or more conditions into a single query.

Example

This will return all employees with the first name "Sandra" and the last name "Smitty":

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.firstName = "Sandra" AND 
emp.lastName = "Smitty"

OR

Use of the OR operator enables you to search for records that contain one or more of the specified values or strings. Use of OR does not imply exclusivity; returned records will satisfy at least one of the specified conditions, but may satisfy more.

Example

This will return all employees who have an id of 25001 OR 25002

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.id = 25001 OR emp.id = 25002

This can be extended to multiple ORs

FROM EmployeeBean emp WHERE emp.id = 25001 OR emp.id = 25002 OR emp.id = 25003

NOT

A NOT can be added to further modify the query result set by specifying conditions that must not be met by the selected records.

Example

The following will return all employees whose first name is not Bob

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE NOT (emp.firstName = "Bob")

The query could also have been written as follows:

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.firstName <> "Bob"

Figure 4-1 Result Set Using "=" and "NOT" or "< >"

Text description of the illustration ejbqla.gif

Combining operators

Operators can be combined to create more complex queries. For example, the following will return any employees who meet the following criteria:

• They have a first name of John OR

• They have a first name of Bob and a last name of Smith

  SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.firstName = "John" OR 
  emp.firstName = "Bob" AND emp.lastName = "Smith"
  

Figure 4-2 Result Set Using "OR"

Text description of the illustration ejbqla2.gif

This query is slightly different because of the brackets. Only employees who have a last name of Smith with a first name of John or Bob will be returne.d

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE (emp.firstName = "John" OR 
emp.firstName = "Bob") AND emp.lastName = "Smith"

Figure 4-3 Result Set using "=" and "OR"

Text description of the illustration ejbqla3.gif

Null Comparison Expressions: Null

The null comparison operator enable you to search for records with no content for a specified field.

Example

All employees whose first name is not included in the database:

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.firstName IS NULL;

Similarly, by adding the NOT logical operator, you can search for all employees whose first name appears in the database:

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.firstName IS NOT NULL;

Range Expressions

Similar to SQL, EJBQL supports a number of Range expressions. They are LIKE, BETWEEN and IN. You can also modify these expression with NOT.

LIKE

LIKE enables you to use pattern matching to search for records containing a specific patterns. Support for pattern matching for LIKE is as follows:

_

signifies that a match must be made for a single character. For example, the expression 12_4 will match 1234 but not 12334.

%

signifies that a match should be made for a range of characters. For example, the expression 12%4 will match 1234, 1299994 but not 124.

Examples

All employees whose first name starts with "Ji"

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.firstName LIKE "Ji%"

Similarly, you can search for All employees whose first name does not start with "Ji"

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.firstName NOT LIKE "Ji%"

BETWEEN

Lets you choose a contiguous range of numeric values. Always includes the modifier AND.

Examples

Any employee aged 26 to 36 inclusive

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.age BETWEEN 26 AND 36

Similarly, you can search for any employee not aged 55 to 65 inclusive

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.age NOT BETWEEN 55 AND 65

IN

Lets you specify a group of values used as criteria for the search.

Example

You can use IN to search for any employee whose salary is a specific amount:

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.salary IN (30000, 40000, 
50000)

Similarly, you can search for any employee not earning those specific salaries:

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.salary NOT IN (30000, 40000, 
50000)

Functional Expressions

EJBQL also supports several functions: CONCAT; SUBSTRING; LENGTH; SQRT; and ABS.

Note: The EJBQL implementation in TopLink does not support the LOCATE function because it is not currently supported in the Expression framework.

ABS

The ABS operator represents the mathematical absolute value of the selected field.

Example

Any employee whose salary's absolute value is 35000

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE ABS(emp.salary) = 35000

CONCAT

CONCAT enables you to combine variables together and search using the result.

Example

The full name of any employees whose first name is "John".

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE CONCAT(emp.firstName, 
emp.lastName) LIKE "John%"

LENGTH

LENGTH enables you to search for data that is a specific number of characters in length.

Example

Any employee whose first name is 5 letters long

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE LENGTH(emp.firstName) = 5

SQRT

The SQRT operator represents the mathematical operatoion, square root. It enables you to search for data the square root of which satisfies some criteria.

Example

Any employee whose salary's square root is 200

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE SQRT(emp.salary) = 200

SUBSTRING

SUBSTRING enables you to extract a portion of a given string for use in a WHERE clause. SUBSTRING includes numeric arguments as follows:

• The first number represents the start position in the string.

• The second number represents the number of characters in the string to be considered.

Example

Any employee record for which the first two characters of the firstName field are "bo".

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE SUBSTRING(emp.firstName, 0, 2) = 
"Bo"

Input Parameters

Input parameters enable you to take advantage of finders written on the home interface of an EJB in which parameters have been specified. Input parameters can be linked to the EJBQL using "?" followed by the index (integer) of the required parameter in the finder method.

A simple example

A finder with one parameter:

Finder: employeeHome.findByLastName(lastNameParameter)

EJBQL: SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.lastName = ?1

"?1" is replaced at run-time with the lastNameParameter passed from the client.

A complex example

A finder can contain more than one parameter. For example:

Finder: employeeHome.findBy(firstName,lastName)

EJBQL: This finder can accommodate three EJBQL statements:

SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.firstName = ?1
SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.lastName = ?2
SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.firstName = ?1 AND emp.lastName = ?2

Combining Clauses

You can include all three types of clauses in your queries to make them more effective.

Multiple clauses: a few examples

The following clause returns telephone numbers whose area code are "613":

SELECT OBJECT(phone) FROM PhoneNumber phone 

WHERE phone.areaCode = "613"

The following return telephone numbers whose area code are "613" and whose employee first name starts with "Bo".

SELECT OBJECT(phone) FROM PhoneNumber phone 
WHERE phone.areaCode = "613" AND phone.owner.firstName LIKE "Bo%"

Using EJBQL with TopLink

EJBQL can be used several different ways in conjunction with TopLink. It may be specified when mapping an object and its attributes to a table via the Mapping workbench. It can also be built and used dynamically at run time via a ReadQuery or the TopLink session.

For information on using EJBQL queries with the TopLink Mapping Workbench, see the Oracle9iAS TopLink Mapping Workbench Reference Guide.

ReadAllQuery

The basic API for using a ReadAll query with EJBQL is:

setEJBQLString("...")

A reference class will also be required if no SELECT clause is provided. The query can then be executed as any other query would be executed.

Example 1

A simple ReadAllQuery using EJBQL

ReadAllQuery theQuery = new ReadAllQuery();
theQuery.setReferenceClass(EmployeeBean.class);
theQuery.setEJBQLString("SELECT OBJECT(emp) FROM EmployeeBean emp");
...
Vector returnedObjects = (Vector)aSession.executeQuery(theQuery);

Example 2

A simple ReadAllQuery using EJBQL and passing arguments

The query is defined as in Example 1 but a vector of arguments is created, filled and passed into the executeQuery method

// First define the query
ReadAllQuery theQuery = new ReadAllQuery();
theQuery.setReferenceClass(EmployeeBean.class);
theQuery.setEJBQLString("SELECT OBJECT(emp) FROM EmployeeBean emp WHERE 
emp.firstName = ?1");
...
// Next define the Arguments
Vector theArguments = new Vector();
theArguments.add("Bob");
...
// Finally execute the query passing in the arguments
Vector returnedObjects = (Vector)aSession.executeQuery(theQuery, theArguments);

Session

EJBQL can be executed directly against the session. This will return a Vector of the objects specified by the reference class. The basic API is as follows:

aSession.readAllObjects(<ReferenceClass>, <EJBQLCall>)
// <EJBQLCall> is the EJBQL string to be executed and <ReferenceClass> is the 
return class type.
// Call ReadAllObjects on a session.
Vector theObjects = (Vector)aSession.readAllObjects(EmployeeBean.class, new 
EJBQLCall( "SELECT OBJECT (emp) from EmployeeBean emp));