Skip navigation.
Downloads | Product Documentation | Support

Programming WebLogic Enterprise JavaBeans

# EJB Query Language (EJB-QL) and WebLogic Server

EJB Query Language (QL) is a portable query language that defines finder methods for 2.0 entity EJBs with container-managed persistence. Use this SQL-like language to select one or more entity EJB objects or fields in your query. You can create queries in the deployment descriptor for any finder method other than `findByPrimaryKey()`. `findByPrimaryKey` is automatically handled by the EJB container.

# EJB QL Requirement for EJB 2.0 Beans

The deployment descriptors must define each finder query for EJB 2.0 entity beans by using an EJB QL query string. You cannot use WebLogic Query Language (WLQL) with EJB 2.0 entity beans. WLQL is intended for use with EJB 1.1 container-managed persistence. For more information on WLQL and EJB 1.1 container-managed persistence, see Using WebLogic Query Language (WLQL) for EJB 1.1 CMP.

# Using the EJB 2.0 WebLogic QL Extension for EJB QL

WebLogic Server has an SQL-like language, called WebLogic QL, that extends the standard EJB QL. This language works with the finder expressions and is used to query EJB objects from the RDBMS. You define the `query` in the `weblogic-cmp-jar.xml` deployment descriptor using the `weblogic-ql` element.

There must be a query element in the `ejb-jar`.file that corresponds to the `weblogic-ql` element in the `weblogic-cmp-jar.xml` file. However, the value of the `weblogic-cmp-jar.xml` query element overrides the value of the `ejb-jar.xml` query element.

These topics provide guidelines for using the WebLogic QL extension to EJB 2.0 QL:

- upper and lower Functions
- Using ORDERBY
- Using Subqueries
- Using Aggregate Functions
- Using Queries that Return ResultSets
- Using Oracle SELECT HINTS
- Visual Indicator of Error in Query
- Multiple Errors Reported after a Single Compilation

## upper and lower Functions

The EJB WebLogic QL `upper` and `lower` extensions convert the case of arguments to allow finder methods to return results that match the characters in an search expression but not the case. The case change is transient, for the purpose of string matching, and is not persisted in database. The underlying database must also support `upper` and `lower` functions.

### upper

The `upper` function converts characters in its arguments from any case to upper case before string matching is performed. Use the `upper` function with an upper-case expression in a query to return all items that match the expression, regardless of case. For example:

`select name from products where upper(name)='DETERGENT';`

### lower

The `lower` function converts characters in its arguments from any case to lower case before string matching is performed. Use the `lower` function with an lower-case expression in a query to return all items that match the expression, regardless of case.

`select type from products where lower(name)='domestic';`

## Using ORDERBY

The EJB WebLogic QL extension `ORDERBY` is a keyword that works with the `Finder` method to specify the CMP field selection sequence for your selections.

Listing 8-13 ORDERBY Extension Showing Order by id

>     ORDERBY

>     	SELECT OBJECT(A) from A for Account.Bean

>     		ORDERBY A.id

You can specify an `ORDERBY` with ascending [`ASC`] or descending [`DESC`] order for multiple fields as follows:.

Listing 8-14 ORDERBY Extension Showing Order by id with ASC and DESC

>     ORDERBY <field> [ASC|DESC], <field> [ASC|DESC]

>     	SELECT OBJECT(A) from A for Account.Bean, OBJECT(B) from B for Account.Bean

>     		ORDERBY A.id ASC; B.salary DESC

Note: `ORDERBY` defers all sorting to the DBMS. Thus, the order of the retrieved result depends on the particular DBMS installation on top of which the bean is running

## Using Subqueries

WebLogic Server supports the use of the following features with subqueries in EJB QL:

- Subquery return type

- Single `cmp-field`s
- Aggregate functions
- Beans with simple primary keys

- Subqueries as comparison operands
- Correlated subqueries
- Uncorrelated subqueries
- `DISTINCT` clauses with subqueries

The relationship between WebLogic QL and subqueries is similar to the relationship between SQL queries and subqueries. Use WebLogic QL subqueries in the `WHERE` clause of an outer WebLogic QL query. With a few exceptions, the syntax for a subquery is the same as a WebLogic QL query.

To specify WebLogic QL, see Using the EJB 2.0 WebLogic QL Extension for EJB QL. Use those instructions with a `SELECT` statement that specifies a subquery as shown the following sample.

The following query selects all above average students as determined by the provided grade number:

>     SELECT OBJECT(s) FROM studentBean AS s WHERE s.grade > (SELECT AVG(s2.grade) FROM StudentBean AS s2)

Note that in the above query the subquery, (SELECT AVG(s2.grade) FROM StudentBean AS s2), has the same syntax as an EJB QL query.

You can create nested subqueries.The depth is limited by the underlying database's nesting capabilities.

In a WebLogic QL query, the identifiers declared in the `FROM` clauses of the main query and all of its subqueries must be unique. This means that a subquery may not re-declare a previously declared identifier for local use within that subquery.

For example, the following example is not legal because employee bean is being declared as emp in both the query and the subquery:

>     SELECT OBJECT(emp)
>     	FROM EmployeeBean As emp
>     		WHERE emp.salary=(SELECT MAX(emp.salary) FROM
>     			EmployeeBean AS emp WHERE employee.state=MA)

Instead, this query should be written as follows:

>     SELECT OBJECT(emp)
>     	FROM EmployeeBean As emp
>     		WHERE emp.salary=(SELECT MAX(emp2.salary) FROM
>     			EmployeeBean AS emp2 WHERE emp2.state=MA)

The above examples correctly declares the subquery's employee bean to have a different identifier from the main query's employee bean.

### Subquery Return Types

The return type of a WebLogic QL subquery can be one of a number of different types, such as:

Single cmp-field Type Subqueries

WebLogic Server supports a return type consisting of a `cmp-field.` The results returned by the subquery may consists of a single value or collection of values. An example of a subquery that returns value(s) of the type `cmp-field` is as follows:

>     SELECT emp.salary FROM EmployeeBean AS emp WHERE emp.dept = `finance'

This subquery selects all of the salaries of employees in the finance department.

Aggregate Functions

WebLogic Server supports a return type consisting of an aggregate of a `cmp-field`. As an aggregate always consist of a single value, the value returned by the aggregate is always a single value. An example of a subquery that return a value of the type aggregate (`MAX`) of a `cmp-field` is as follows:

>     SELECT MAX(emp.salary) FROM EmployeeBean AS emp WHERE emp.state=MA

This subquery selects the single highest employee salary in Massachusetts.

For more information on aggregate functions, see Using Aggregate Functions.

Beans with Simple Primary Key

WebLogic Server supports a return type consisting of a `cmp-bean` with a simple primary key.

The following example illustrates a subquery that returns the value(s) of the type bean with a simple primary key:

>     SELECT OBJECT(emp) FROM EMployeeBean As emp WHERE emp.department.budget>1,000,000

This subquery provides a list of all employee in departments with budgets greater than $1,000,000.

Note: Beans with compound primary keys are NOT supported. Attempts to designate the return type of a subquery to a bean with a compound primary key will fail when you compile the query.

### Subqueries as Comparison Operands

Use subqueries as the operands of comparison operators and arithmetic operators. WebLogic QL supports subqueries as the operands of:

- these comparison operators: `[NOT]IN, [NOT]EXISTS`

and

- these arithmetic operators: `<, >, <=, >=, =, <> with ANY and ALL`

[NOT]IN

The `[NOT]IN` comparison operator tests whether the left-had operand is or is not a member of the subquery operand on the right-hand side.

An example of a subquery which is the right-hand operand of the `NOT IN` operator is as follows:

>     SELECT OBJECT(item)
>     	FROM ItemBean AS item
>     		WHERE item.itemId NOT IN
>     			(SELECT oItem2.item.itemID
>     					FROM OrderBean AS orders2, IN(orders2.orderItems)oIttem2

The subquery selects all items from all orders.

The main query's `NOT IN` operator selects all the items that are not in the set returned by the subquery. So the end result is that the main query selects all unordered items.

[NOT]EXISTS

The `[NOT]EXISTS` comparison operator tests whether the set returned by the subquery operand is or is not empty.

An example of a subquery which is the operand of the `NOT EXISTS` operand is as follows:

>     SELECT (cust) FROM CustomerBean AS cust
>     	WHERE NOT EXISTS
>     		(SELECT order.cust_num FROM OrderBean AS order
>     			WHERE cust.num=order_num)

>     This is an example of a query with a correlated subquery. See Correlated and Uncorrelated Subqueries for more information. the following query returns all customers that have not placed an order.

>     SELECT (cust) FROM CustomerBean AS cust
>     	WHERE cust.num NOT IN
>     		(SELECT order.cust_um FROM OrderBean AS order
>     			WHERE cust.num=order_num)

Arithmetic Operators

Use arithmetic operators for comparison when the right-hand subquery operand returns a single value. If the right hand subquery instead returns multiple values, then the qualifiers `ANY` or `ALL` must precede the subquery.

An example of a subquery which uses the `=' operator is as follows:

>     SELECT OBJECT (order)
>     	FROM OrderBean AS order, IN(order.orderItems)oItem
>     		WHERE oItem.quantityOrdered =
>     			(SELECT MAX (subOItem.quantityOrdered)
>     				FROM Order ItemBean AS subOItem
>     					WHERE subOItem,item itemID = ?1)
>     AND oItem.item.itemId = ?1

For a given `itemId`, the subquery returns the maximum quantity ordered of that item. Note that this aggregate returned by the subquery is a single value as required by the `=' operator.

For the same given `itemId`, the main query's ``='` comparison operator checks which order's `OrderItem.quantityOrdered` equals the maximum quantity returned by the subquery. The end result is that the query returns the OrderBean that contains the maximum quantity of a given item that has been ordered.

Use arithmetic operators in conjunction with `ANY` or `ALL`, when the right-hand subquery operand may return multiple values.

An example of a subquery which uses `ANY` and `ALL` is as follows:

>     SELECT OBJECT (order)
>     	FROM OrderBean AS order, IN(order.orderItems)oItem
>     		WHERE oItem.quantityOrdered > ALL
>     			(SELECT subOItem.quantityOrdered
>     				FROM OrderBean AS suborder IN (subOrder.orderItems)subOItem
>     					WHERE subOrder,orderId = ?1)

For a given `orderId`, the subquery returns the set of `orderItem.quantityOrdered` of each item ordered for that `orderId`. The main query's `>' `ALL` operator looks for all orders whose `orderItem.quantityOrdered` exceeds all values in the set returned by the subquery. The end result is that the main query returns all orders in which all `orderItem.quantityOrdered` exceeds every `orderItem.quantityOrdered` of the input order.

Note that since the subquery can return multi-valued results that they `>'`ALL` operator is used rather then the ``>'` operator.

### Correlated and Uncorrelated Subqueries

WebLogic Server supports both correlated and Uncorrelated subqueries.

UnCorrelated Subqueries

Uncorrelated subqueries may be evaluated independently of the outer query. An example of an uncorrelated subquery is as follows:

>     SELECT OBJECT(emp) FROM EmployeeBean AS emp
>     	WHERE emp.salary>
>     	(SELECT AVG(emp2.salary) FROM EmployeeBean AS emp2)

This example of a uncorrelated subquery selects the employees whose salaries are above average. This examples uses the `>' arithmetic operator.

Correlated

Correlated subqueries are subqueries in which values from the outer query are involved in the evaluation of the subquery. An example of a correlated subquery is as follows:

>     SELECT OBJECT (mainOrder) FROM OrderBean AS mainOrder
>     	WHERE 10>
>     		(SELECT COUNT (DISTINCT subOrder.ship_date)
>     	FROM OrderBean AS subOrder
>     	WHERE subOrder.ship_date>mainOrder.ship_date
>     	AND mainOrder.ship_date IS NOT NULL

This example of a correlated subquery selects the last 10 shipped orders. This example uses the `NOT IN` operator.

Note: Keep in mind that correlated subqueries can involve more processing overhead the uncorrelated subqueries.

#### DISTINCT Clause with Subqueries

Use the `DISTINCT` clause in a subquery to enable an SQL `SELECT DISTINCT` in the subquery's generated SQL. Using a `DISTINCT` clause in a subquery is different from using one in a main query because the EJB container enforces the `DISTINCT` clause in a main query; whereas the `DISTINCT` clause in the subquery is enforced by the generated SQL `SELECT DISTINCT`. The following is an example of a `DISTINCT` clause in a subquery:

>     SELECT OBJECT (mainOrder) FROM OrderBean AS mainOrder
>     	WHERE 10>
>     		(SELECT COUNT (DISTINCT subOrder.ship_date)
>     	FROM OrderBean AS subOrder
>     	WHERE subOrder.ship_date>mainOrder.ship_date
>     	AND mainOrder.ship_date IS NOT NULL

## Using Aggregate Functions

WebLogic Server supports aggregate functions with WebLogic QL. You only use these functions as `SELECT` clause targets, not as other parts of a query, such as a `WHERE` clause. The aggregate functions behave like SQL functions. They are evaluated over the range of the beans returned by the `WHERE` conditions of the query

To specify WebLogic QL, see Using the EJB 2.0 WebLogic QL Extension for EJB QL. Use those instructions with a SELECT statement that specifies an aggregate function as shown in the samples shown in the following table.

A list of the supported functions and sample statements follow:

Aggregate Function | Description | Sample Statement
--- | --- | ---
MIN(x) | Returns the minimum value of this field. | SELECT MIN(t.price) FROM TireBean AS t WHERE t.size=?1 This statement selects the lowest price for a tire of a given input size.
MAX(x) | Returns the maximum value of this field. | SELECT MAX(s.customer_count) FROM SalesRepBean AS s WHERE s.city='Los Angeles' This statement selects the maximum number of customers served by any single sales representative in Los Angeles.
AVG( [DISTINCT] x) | Returns the average value of this field | SELECT AVG(b.price) FROM BookBean AS b WHERE b.category='computer_science' This statement selects the Average Price of a book in the Computer Science category.
SUM( [DISTINCT] x) | Returns the sum of this field. | SELECT SUM(s.customer_count) FROM SalesRepBean AS s WHERE s.city='Los Angeles' This statement retrieves the total number of customers served by sales representatives in Los Angeles.
COUNT( [DISTINCT] x) | Returns the number of occurrences of a field. | SELECT COUNT(s.deal.amount) FROM SalesRepBean AS s, IN(deal)s WHERE s.deal.status='closed' AND s.deal.amount>=1000000 This statement retrieves the number of closed deals for at lease 1 million dollars.

You can return aggregate functions in ResultSets as described below.

## Using Queries that Return ResultSets

WebLogic Server supports `ejbSelect()` queries that return the results of multi-column queries in the form of a `java.sql.ResultSet`. To support this feature, WebLogic Server now allows you to use the SELECT clause to specify a comma delimited list of target fields as shown in the following query:

>     SELECT emmp.name, emp.zip FROM EmployeeBean AS emp

This query returns a `java.sqlResultSet` with rows whose columns are the values Employee's Name and Employee's Zip.

To specify WebLogic QL, see Using the EJB 2.0 WebLogic QL Extension for EJB QL. Use those instructions with a query specifying a ResultSet as shown in the above query to specify WebLogic QL, see Using the EJB 2.0 WebLogic QL Extension for EJB QL. Use those instructions with a SELECT statement that specifies an aggregate query like the samples shown in the following table.

.

ResultSets created in EJB QL can only return `cmp-field` values or aggregates of `cmp-field` values, they cannot return beans.

In addition, you can create powerful queries, as described in the following example, when you combine `cmp-fields` and aggregate functions.

The following rows (beans) show the salaries of employees in different locations:

Table E-1 CMP Fields Showing Salaries of Employees in California

Name | Location | Salary
--- | --- | ---
Matt | CA | 110,000
Rob | CA | 100,000

Table E-2 CMP Fields Showing Salaries of Employees in Arizona

Name | Location | Salary
--- | --- | ---
Dan | AZ | 120,000
Dave | AZ | 80,000

Table E-3 CMP Fields Showing Salaries of Employees in Texas

Name | Location | Salary
--- | --- | ---
Curly | TX | 70,000
Larry | TX | 180,000
Moe | TX | 80,00

Note: Each row represents a bean.

The following SELECT statement shows a query that uses ResultSets and the aggregate function (AVG) along with a GROUP BY statement and an ORDER BY statement using a descending sort to retrieve results from a multi-column query.

>     SELECT e.location, AVG(e.salary)
>     	FROM Finder EmployeeBean AS e
>     		GROUP BY e.location
>     			ORDER BY 2 DESC

The query shows the average salary in of employees at each location in descending order. The number, 2 means that the `ORDERBY` sort is on the second item in the `SELECT` statement. The `GROUP BY` clause specifies the `AVERAGE` salary of employees with a matching `e.location` attribute.

The ResultSet, in descending order is as follows:

Location | Average
--- | ---
TX | 110,000
AZ | 100,000
CA | 105,000

Note: You can only use integers as `ORDERBY` arguments in queries that return ResultSets. WebLogic Server does not support the use of integers as `ORDERBY` arguments in any Finder or `ejbselect()` that returns beans.

## Using Oracle SELECT HINTS

WebLogic Server supports an EJB QL extension that allows you to pass INDEX usage hints to the Oracle Query optimizer. With this extension, you can provide a hint to the database engine. For example, if you know that the database you are searching can benefit from an ORACLE_SELECT_HINT, you can define an ORACLE_SELECT_HINT clause that will take ANY string value and then insert that String value after the SQL SELECT statement as a hint to the database.

To use this option, declare a query that uses this feature in the `weblogic-ql` element in `weblogic-cmp-jar.xml`. The `weblogic-q`l element specifies a query that contains a WebLogic specific extension to the EJB-QL language.

The WebLogic QL keyword and usage is as follows:

>     SELECT OBJECT(a) FROM BeanA AS a WHERE a.field > 2 ORDERBY a.field SELECT_HINT '/*+ INDEX_ASC(myindex) */'

This statement generates the following SQL with the optimizer hint for Oracle:

`SELECT /*+ INDEX_ASC(myindex) */ column1 FROM ....`

In the WebLogic QL ORACLE_SELECT_HINT clause, whatever is between the single quotes (' ') is what gets inserted after the SQL SELECT. It is the query writer's responsibility to make sure that the data within the quotes makes sense to the Oracle database."get" and "set" Method Restrictions

WebLogic Server uses a series of accessor methods. The names of these methods begin with set and get. WebLogic Server uses these methods to read and modify container-managed fields. These container-generated classes must begin with "get" or "set" and use the actual name of a persistent field defined in `ejb-jar.xml`. The methods are also declared as `public`, `protected`, and `abstract`.

# Properties-Based Methods of the Query Interface

The `Query` interface contains both find and execute methods. The find methods work like standard EJB methods, in that they return EJBObjects. The execute methods work more like `Select` statements in that you can select individual fields.

The `Query` interface return type is a disconnected `ResultSet`, meaning you access the information from the returned object the same way you would access it from a `ResultSet`, except that the `ResultSet` does not hold open a database connection.

The `Query` interface's properties-based methods offer an alternate way of specifying settings particular to a query. The `QueryProperties` interface holds standard EJB query settings while the `WLQueryProperties` interface holds WebLogic-specific query settings.

Although the `Query` interface extends `QueryProperties`, the actual `Query` implementation extends `WLQueryProperties` so it can be safely casted, as in the example in Listing 8-15, which sets field group settings:

Listing 8-15 Setting Field Group Settings with WLQueryProperties

>     Query query=qh.createQuery(); ((WLQueryProperties) query).setFieldGroupName("myGroup"); Collection results=query.find(ejbql);

or

>     Query query=qh.createQuery(); Properties props = new Properties(); props.setProperty(WLQueryProperties.GROUP_NAME, "myGroup"); Collection results=query.find(ejbql, props);

# Migrating from WLQL to EJB QL

If you have an existing application that uses EJB 1.1, your container-managed entity EJBs may use WLQL for finder methods. This section provides a quick reference to common WLQL operations. Use this table to map the WLQL syntax to EJB QL syntax.

Sample WLQL Syntax | Equivalent EJB QL Syntax
--- | ---
`(= operand1 operand2)` | WHERE operand1 = operand2
`(< operand1 operand2)` | WHERE operand1 < operand2
`(> operand1 operand2)` | WHERE operand1 > operand2
`(<= operand1 operand2)` | WHERE operand1 <= operand2
`(>= operand1 operand2)` | WHERE operand1 >= operand2
`(! operand)` | WHERE NOT operand
`(& expression1 expression2)` | WHERE expression1 AND expression2
`(| expression1 expression2)` | WHERE expression1 OR expression2
`(like` `text_string``%)` | WHERE operand LIKE ``text_string%`'
`(isNull operand)` | WHERE operand IS NULL
`(isNotNull operand)` | WHERE operand IS NOT NULL

# Known Issue with Implied Cross Products

When an EJB QL query contains an implied cross product—as opposed to an explicit one—the EJB-QL query can return an empty result.

Consider this example query:

>     SELECT OBJECT(e) FROM EmployeeBean AS e WHERE e.name LIKE 'Joe' OR e.acct.balance < 100

This query references `AccountEJB`, but `AccountEJB` is not listed in the `FROM` clause. The result of this query is identical to that of query with `AccountEJB` explicitly listed in the `FROM` clause.

# EJB QL Error-Reporting

Compiler error messages in EJB QL provide a visual aid to identify which part of the query is in error and allow the reporting of more than one error per compilation.

## Visual Indicator of Error in Query

When an error is reported, EJB QL indicates the location of the problem within these symbols: =>> <<=. These symbols are highlighted in red in the following sample compiler error report.

>     ERROR: Error from appc: Error while reading 'META-INF/FinderEmployeeBeanRDBMS.xml'. The error was:

>     Query:

>     EJB Name: FinderEmployeeEJB

>     Method Name: findThreeLowestSalaryEmployees

>     Parameter Types: (java.lang.String)

>     Input EJB Query: SELECT OBJECT(e) FROM FinderEmployeeBean e WHERE f.badField = '2' O

>     R (e.testId = ?1) ORDERBY e.salary

>     SELECT OBJECT(e ) FROM FinderEmployeeBean e
>     WHERE =>> f.badField <<= = '2' OR ( e.testId = ?1 ) ORDERBY e.salary

>     Invalid Identifier in EJB QL expression:

>     Problem, the path expression/Identifier 'f.badField' starts with an identifier: 'f'. The identifier 'f', which can be either a range variable identifier or a collection member identifier, is required to be declared in the FROM clause of its query or in the FROM clause of a parent query.

>     'f' is not defined in the FROM clause of either its query or in any parent query.

>     Action, rewrite the query paying attention to the usage of 'f.badField'.

## Multiple Errors Reported after a Single Compilation

If a query contains multiple errors, EJB QL is now capable of reporting more than one of these after a single compilation. Previously, the compiler could only report one error per compilation. Reporting of subsequent errors required recompilation.

The compiler is not guaranteed to report all errors after a single compilation.