Query Methods and EJB QL

Find and select methods for CMP (2.0) entity beans are defined using EJB QL. This query language, similar to SQL used in relational databases, is used to select one or more entity EJBs or entity bean fields. The WebLogic platform fully supports EJB QL 2.0 and offers a number of additional methods that can be used in conjunction with EJB QL.

Note. The EJB QL is used for all query methods, with the exception of findByPrimaryKey, which is automatically generated by the EJB container.

The topics in this section are:

Find Methods

A finder method is invoked by other EJBs or client applications on a CMP entity bean's local or remote home interface, and returns local or remote references to one or more instances of this entity bean that match the query. A find method can return a reference to a single entity instance, such as findByPrimaryKey, or to multiple entity instances returned as a java.util.Collection. Finder methods must start with the prefix find.

The following list shows common uses of EJB QL queries with find methods:

Select All. The method Collection findAll(), defined in the home interface of the CustomerBean, returns all records in the database table:
```
SELECT OBJECT(o) from CustomerBean as o
```
Notice that an EJB QL query always uses the EJB's abstract schema name CustomerBean to reference it. This name is typically the same as the EJB's descriptive name. For more details on these names, see the @ejbgen:entity Annotation.
Input Parameters. The method Collection findByItemName(java.lang.String itemname), defined in the home interface of the ItemsBean, returns all references to Itemsbean instances matching the item name:
```
SELECT OBJECT(o) from ItemsBean as o WHERE o.itemname = ?1
```
Notice that the method argument itemname is matched to input parameter ?1.
Literal Values. The method Collection findUSManufacturers(), defined in the home interface of the ManufacturerBean, returns all references to ManufacturerBean instances who are US manufacturers:
```
SELECT OBJECT(o) from ManufacturerBean as o WHERE o.usManufacturer = 1
```
Relationship Queries. A ManufacturerBean and an ItemsBean are defined to have an entity relation, such that each item has a manufacturer, and a manufacturer can produce multiple items. For each item, the ItemsBean's CMR field manufacturer stores a unique index to a manufacturer. The method Collection findAllManufacturers(), defined in the home interface of the ManufacturerBean, queries the ItemsBean to return the different manufacturers for all the items. It is possible that multiple items are created by the same manufacturer, yielding multiple references to the same manufacturer in the returned results:
```
SELECT OBJECT(m) from ItemsBean as o, IN(o.manufacturer) AS m
```
Notice that the keyword IN is used to return object references via a CMR field.
Unique Records. A ManufacturerBean and an ItemsBean are defined to have an entity relation, such that each item has a manufacturer, and a manufacturer can produce multiple items. For each item, the ItemsBean's CMR field manufacturer stores a unique index to a manufacturer. The method Collection findDistinctManufacturer(), defined in the home interface of the ManufacturerBean, queries the ItemsBean to return the different manufacturers for all the items. It is possible that multiple items are created by the same manufacturer, yielding multiple references to the same manufacturer, but the keyword DISTINCT is used to not return duplicates:
```
SELECT DISTINCT OBJECT(m) from ItemsBean as o, IN(o.manufacturer) AS m
```

Note. The Finder Methods Sample allows you to run these and other EJB QL queries against a prefilled database table and examine the returned results. For more detailed information on EJB QL queries, see your favorite J2EE documentation.

Select Methods

A select method is defined using EJB QL and it can either return (local or remote) references to entity beans or values of an individual CMP field. A select method is not defined in the EJB's interfaces. In other words, it is a private method that can only be used internally by a CMP entity bean class. When returning object references, a select method can return a reference to a single entity instance, or to multiple entity instances which are returned as a java.util.Collection or java.util.Set. Select methods must start with the prefix ejbSelect.

The following list shows common uses of EJB QL queries with select methods:

Object References. A select method can use the same queries as find methods to return object references. The method java.util.Collection ejbSelectAll(), defined in the ItemsBean class (but not its interfaces), returns all records in the database table:
```
SELECT OBJECT(o) from ItemsBean as o
```
CMP Fields. The method java.util.Collection ejbSelectItemNames(), defined in the ItemsBean class, returns only the item names of all items:
```
SELECT o.itemname from ItemsBean as o
```
Just as with a query that returns object references, a query that returns CMP fields can have input parameters, literal values, relationship querying with the IN keyword, and the DISTINCT keyword to avoid duplicate records. The method java.util.Collection ejbSelectByItemName(java.lang.String itemname), defined in the ItemsBean class, returns only the item names of the items that match the argument itemname:
```
SELECT o.itemname from ItemsBean as o WHERE o.itemname = ?1
```

Note. The Select Methods Sample allows you to run these and other EJB QL queries against a prefilled database table and examine the returned results.

Standard EJB QL Operators

EJB QL 2.0 defines a number of standard operators. Some of these, like IN, DISTINCT, and the use of '.' as a navigational operator (for instance, to access a EJB's CMP field) have been described above. Other operators include:

The comparison operators <, >, <=, >=, =, and <>.
The logical operators NOT, AND, and OR.
The arithmetic operators +, - (unary); *, /, +, and -.
The arithmetic functions ABS(number), returning the absolute value of a (int, double, or float) number, and SQRT(double) returning the square root.
LIKE is used for pattern matching with String fields. Use '%' to match with any number of characters and '_' to match with exactly one character (use '\' if these characters actually occur in the pattern). For instance, the following query will select all items whose price ends in '.95':
```
SELECT OBJECT(o) from ItemsBean as o WHERE o.price LIKE '%.95'
```
The String functions CONCAT(String1, String2), LENGTH(String), LOCATE(StringToFind, ContainingString [, starting position]) - equivalent to java.lang.String.indexOf -, and SUBSTRING(String, startposition, endposition).
BETWEEN specifies a range of values (inclusive). For instance, the following query returns all items between $20 and $40:
```
SELECT OBJECT(o) from ItemsBean as o WHERE o.price BETWEEN 20.00 AND 40.00
```
IS NULL is used to test for null fields. CMP fields that hold an object (such as a String) and CMR fields that hold a single object can be null, while CMP fields holding primitive values and CMR fields holding a collection of objects cannot. For instance, the following query returns all items whose manufacturer is not known (assuming the same entity relationship as mentioned above):
```
SELECT OBJECT(o) from ItemsBean as o WHERE o.manufacturer IS NULL
```
IS EMPTY is used to test for empty sets, in particular CMR fields that holds a collection of objects. For example, the following query returns all manufacturers without known items in the database:
```
SELECT OBJECT(o) from ManufacturerBean as o WHERE o.items IS EMPTY
```
MEMBER OF is used to evaluate whether an object is part of a collection-based relationship. For example, the following query returns the manufacturer of a given item:
```
SELECT OBJECT(o) from ManufacturerBean as o, IN (o.items) AS allItems, ItemsBean oneItem 
    WHERE oneItem.itemname = ?1 AND oneItem MEMBER OF allItems
```

For more detailed information on EJB QL queries and the operators defined in this language, see the Finder and Select Methods Samples, or your favorite J2EE documentation.

WebLogic QL

The WebLogic platform offers an EJB QL extension, called WebLogic QL to run advanced queries. The following advanced query types are available:

Ordering Query Results

To order the results of a find or select query, you can used the WebLogic keyword ORDERBY. You can specify in ascending order (the default) or descending order, and sort on multiple fields.

The following query returns ItemBean instances, ordered by item name, that are produced by a US manufacturer:

SELECT OBJECT(i) from ManufacturerBean as o, IN(o.items) AS i WHERE o.usManufacturer = 1 ORDERBY i.itemname

The following query for a select method returns all item names, ordered by price in descending order and by the quantity in stock in ascending order:

SELECT o.itemname from ItemsBean as o ORDERBY o.price DESC, o.quantityavailable ASC

Note. Because the actual ordering is done by the underlying DBMS, the exact order is database dependent.

Aggregate Functions

The following WebLogic aggregate functions are supported:

AVG() returns the average value of this field. Can be used in combination with DISTINCT, as in AVG(DISTINCT o.quantityavailable).
COUNT() returns the number of occurrences of a field. Can be used in combination with DISTINCT.
GROUP BY aggregates entire records on the basis of a field value. An example is shown below.
MIN() returns the minimum value of a field.
MAX()Returns the maximum value of a field.
SUM() returns the sum of a field. Can be used in combination with DISTINCT.

The following query returns all ItemsBean instances that are more expensive than the average price of all the items:

SELECT OBJECT(o) from ItemsBean as o WHERE o.price > (SELECT AVG(s.price) FROM ItemsBean AS s)

Returning Multiple CMP Fields

WebLogic QL supports select methods that, unlike the standard EJB QL, return the results of multi-field queries in the form of a java.sql.ResultSet. It is not possible to return object references as a ResultSet.

The following query returns the name and the quantity in stocks of all the items in the database:

SELECT o.itemname, o.quantityavailable from ItemsBean as o

The following query, which combines this functionality with the aggregate and sorting capabilities, returns the sorted names of all manufacturers of the items, the number of items offered by a manufacturer, and the average price of these items. The ManufacturerBean and ItemsBean are defined to have an entity relation, such that each item has a manufacturer, and a manufacturer can produce multiple items. For each item, the ItemsBean's CMR field manufacturer stores a unique index to a manufacturer instance:

SELECT o.manufacturer.manufacturername, COUNT(o.itemnumber), AVG(o.price) from ItemsBean as o 
   GROUP BY o.manufacturer.manufacturername 
   ORDERBY o.manufacturer.manufacturername

Using Subqueries

WebLogic QL supports the use of subqueries. One example of such a query was already shown above:

SELECT OBJECT(o) from ItemsBean as o WHERE o.price > (SELECT AVG(s.price) FROM ItemsBean AS s)

This query returns all ItemsBean instances that are more expensive than the average price of all the items, the average price of the items first being computed in a subquery. Subqueries follow the same syntax rules as standard WebLogic QL queries. You must, however, make sure that unique identifiers are used in the subquery and outer query. For instance, in the above query identifier o is used for ItemsBean in the outer query, while identifier c is used for ItemsBean in the subquery.

Note. It is also possible to create nested subqueries. The nesting depth is limited by the underlying database's nesting capabilities.

Correlated and Uncorrelated SubQueries

The above query is an example of an uncorrelated subquery. That is, the subquery is evaluated independently of the containing query. In contrast, in correlated subqueries values drawn from the main query are used in the subquery. Correlated queries are usually more processing intensive because the subquery is re-evaluated for every possible value obtained from the main query.

The following correlated subquery returns the five cheapest ItemsBean instances:

SELECT OBJECT(mainItem) from ItemsBean as mainItem WHERE 5 > 
   (SELECT COUNT(subItem.itemname) FROM ItemsBean AS subItem 
           WHERE subItem.price < mainItem.price)

In this example the subquery evaluates for every mainItem, which is an instance of ItemsBean from the outer query, how many other items are less expensive, using the COUNT aggregate function. If there are less than 5 items cheaper than the items currently evaluated in the outer query, the outer query will include this instance in the returned collection.

Return Values

In the subquery examples shown above, the subquery returns a single value obtained through an aggregate function on a CMP field (COUNT and AVG are shown; other possible functions are MIN, MAX, and SUM as described above). The return value of a subquery can also be a single value for a CMP field, a set of values for a CMP field, a single object instance or multiple objects. The subquery cannot return values for multiple CMP fields.

When the subquery returns a set (of field values or objects), you must use one of these additional operators in the outer query to evaluate against the set:

[NOT] IN evaluates whether the value in the outer query is (not) contained in the set returned by the subquery.
[NOT] EXISTS evaluates whether the set returned by the subquery is (not) empty.
ANY, in combination with one of the comparison operators (<, >, <=, >=, =, and <>), evaluates whether the value of the outer value is less than (greater than, and so forth) any of the values in the set returned by the subquery.
ALL, in combination with one of the comparison operators (<, >, <=, >=, =, and <>), evaluates whether the value of the outer value is less than (greater than, and so forth) all of the values in the set returned by the subquery.

The following query uses a correlated subquery to select all items in the subquery that are more expensive than the current item in the outer query. The outer query only selects an object when the set returned by the subquery is empty, using NOT EXISTS. In other words, this query returns the most expensive item(s):

SELECT OBJECT(mainItem) from ItemsBean as mainItem WHERE NOT EXISTS 
   (SELECT OBJECT(subItem) FROM ItemsBean as subItem  WHERE subItem.price > mainItem.price)

The following query uses a uncorrelated subquery that returns a set of CMP field values, and uses '> ALL' to select objects in the outer queries whose field value is larger than all the values in the set. Specifically this query uses a subquery to select the quantities in stock of all the items by a certain manufacturer, and the outer query returns all the object for which larger quantities are in stock:

SELECT OBJECT(mainItem) from ItemsBean as mainItem WHERE mainItem.quantityavailable > ALL 
   (SELECT subItem.quantityavailable from ItemsBean as subItem WHERE subItem.manufacturer.manufacturername = ?1)

Note. Queries using the ANY or ALL keyword do not properly run on PointBase.

Using Oracle Hints

To pass hints to an Oracle Query, you can use the WebLogic QL keyword SELECT_HINT and enter the quoted hint, as is shown in the following example:

SELECT OBJECT(o) from ItemsBean as o WHERE o.price > 20 SELECT_HINT '/*+ FULL(ItemsBean) */'

For more information on hints, see your favorite Oracle documentation.