Oracle9iAS TopLink Foundation Library Guide Release 2 (9.0.3) Part Number B10064-01 |
|
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:
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.
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:
An EJBQL query can contain any of the following components:
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.
The SELECT
defines the return values of the EJBQL query. Return values can be either an attribute, or Entity bean or Java object.
The WHERE
clause is a conditional expression used to restrict the results of a query. The WHERE
clause is optional.
The examples in this section use a modified Backus-Naur Form (BNF). For more information on BNF, see "About Backus Naur Form" .
All EJBQL statements follow the same basic structure:
SELECT selectClause FROM fromClause [WHERE whereExpression]
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
This syntax requires entityBeanVariablePath
to be specified using the following syntax:
entityBeanVariable[.oneToOneRelationshipAttribute]*.oneToManyRelationshipAttribu te
There are two components to the FROM
clause.
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
This sub clause associates the oneToManyVariable
with the oneToManyRelationshipAttribute
at the end of the entityBeanVariablePath
. This element is optional.
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.
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.
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"
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 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}+
This example returns a collection of EmployeeBean
s:
SELECT OBJECT(employee) from EmployeeBean employee
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
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 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:
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]*
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>").
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:
The employee whose id = 25001
SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.id = 25001
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.
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.
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.
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.
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.
All employees whose id does not equal 25001
SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.id <> 25001
Any employee who does not live in Ottawa
SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.address.city <> "Ottawa"
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.
Use of the AND
operator enables you to combine two or more conditions into a single query.
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"
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.
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 OR
s
FROM EmployeeBean emp WHERE emp.id = 25001 OR emp.id = 25002 OR emp.id = 25003
A NOT
can be added to further modify the query result set by specifying conditions that must not be met by the selected records.
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"
Operators can be combined to create more complex queries. For example, the following will return any employees who meet the following criteria:
SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.firstName = "John" OR emp.firstName = "Bob" AND emp.lastName = "Smith"
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"
The null comparison operator enable you to search for records with no content for a specified field.
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;
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
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.
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%"
Lets you choose a contiguous range of numeric values. Always includes the modifier AND
.
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
Lets you specify a group of values used as criteria for the search.
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)
EJBQL also supports several functions: CONCAT
; SUBSTRING
; LENGTH
; SQRT
; and ABS
.
The ABS operator represents the mathematical absolute value of the selected field.
Any employee whose salary's absolute value is 35000
SELECT OBJECT(emp) FROM EmployeeBean emp WHERE ABS(emp.salary) = 35000
CONCAT
enables you to combine variables together and search using the result.
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
enables you to search for data that is a specific number of characters in length.
Any employee whose first name is 5 letters long
SELECT OBJECT(emp) FROM EmployeeBean emp WHERE LENGTH(emp.firstName) = 5
The SQRT
operator represents the mathematical operatoion, square root. It enables you to search for data the square root of which satisfies some criteria.
Any employee whose salary's square root is 200
SELECT OBJECT(emp) FROM EmployeeBean emp WHERE SQRT(emp.salary) = 200
SUBSTRING
enables you to extract a portion of a given string for use in a WHERE
clause. SUBSTRING
includes numeric arguments as follows:
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 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 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 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
You can include all three types of clauses in your queries to make them more effective.
The following clause returns telephone numbers whose area code are "613":
SELECT OBJECT(phone) FROM PhoneNumber phoneWHERE 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%"
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.
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.
ReadAllQuery theQuery = new ReadAllQuery(); theQuery.setReferenceClass(EmployeeBean.class); theQuery.setEJBQLString("SELECT OBJECT(emp) FROM EmployeeBean emp"); ... Vector returnedObjects = (Vector)aSession.executeQuery(theQuery);
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);
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));
|
Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|