Skip Headers

Oracle® Application Server Containers for J2EE Enterprise JavaBeans Developer's Guide 10g Release 2 (10.1.2) Part No. B15505-02 |
| Library |  | Contents | Index

Previous | Next |

# 7 EJB Query Language

In EJB 2.0, you can specify query methods using the standardized query language, EJB Query Language (EJB QL).

Chapter 11 of the EJB 2.0 specification and various off-the-shelf books document EJB QL extensively. This chapter briefly overviews the development rules for these methods, but does not describe the EJB QL syntax in detail.

Refer to the EJB 2.0 specification and the following books for detailed syntax:

- Enterprise JavaBeans, 3rd Edition by Richard Monson-Haefel, O'Reilly Publishers

- Special Edition Using Enterprise JavaBeans 2.0 by Chuck Cavaness and Brian Keeton, Que Publishers

This chapter covers the following subjects:

- EJB QL Overview

- Query Methods Overview

- Deployment Descriptor Semantics

- Finder Method Example

- Select Method Example

- Oracle EJB QL Type Extensions: Date, Time, Timestamp, and SQRT

## EJB QL Overview

EJB QL is a query language that is similar to SQL. In fact, your knowledge of SQL is beneficial in using EJB QL. SQL applies queries against tables, using column names. EJB QL applies queries against entity beans, using the abstract schema name and the CMP and CMR fields of the bean within the query. The EJB QL statement retains the object terminology.

The container translates the EJB QL statement to the appropriate database SQL statement when the application is deployed. Thus, the container is responsible for converting the entity bean name, CMP field names, and CMR field names to the appropriate database tables and column names. EJB QL is portable to all databases supported by your container.

## Query Methods Overview

Query methods can be finder or select methods:

- Finder Methods: Use finder methods to retrieve entity bean references.

- Select Methods: Select methods are for internal use for the entity bean only. Use them to retrieve either entity bean references or CMP values.

Both query method types must throw the `FinderException`.

### Finder Methods

Finder methods are used to retrieve entity bean references. The `findByPrimaryKey` finder method is always defined in both home interfaces (local and remote) to retrieve the entity reference for this bean using a primary key. You can define other finder methods in either or both the home interfaces to retrieve one or several entity bean references.

Do the following to define finder methods:

1. Define the `find<name>` method in the desired home interface. You can specify different finder methods in the remote or the local home interface. If you define the same finder method in both home interfaces, it maps to the same bean class definition. The container returns the appropriate home interface type.

2. Define the full query or just the conditional statement (the `WHERE` clause) for the finder method in the deployment descriptor.

   You can define the query using either EJB QL syntax or OC4J-specific syntax. You can specify either a full query or only the conditional part of the query (the `WHERE` clause).

   - EJB QL syntax is defined within the `ejb-jar.xml` file. The syntax is defined by Sun Microsystems in Chapter 11 of the EJB 2.0 specification. An EJB QL statement is created for each finder method in its own `<query>` element. The container uses this statement to translate the condition on how to retrieve the entity bean references into the relevant SQL statements.

     Currently, EJB QL has limited support for `GROUP` `BY` and `ORDER` `BY` functions, such as `AVERAGE` and `SUM`.

     See "Specifying Finder Methods With EJB QL Syntax" for more information.

   - OC4J-specific syntax is defined within the `orion-ejb-jar.xml` file. When you deploy your application, OC4J translates the EJB QL syntax into the OC4J-specific syntax, which is specified in the `query` attribute of the `<finder-method>` element. You can modify the statement in the `query` attribute for a more complex query using the OC4J syntax. The OC4J-specific query statement in the `orion-ejb-jar.xml` file takes precedence over its EJB QL statement in the `ejb-jar.xml` file.

     See "Specifying Finder Methods With OC4J-Specific Syntax" for more information.

If you retrieve only a single entity bean reference, the container returns the same type as returned in the `find<name>` method. If you request multiple entity bean references, you must define the return type of the `find<name>` method to return a `Collection`. If you want to ensure that no duplicates are returned, specify the `DISTINCT` keyword in the EJB QL statement. An empty `Collection` is returned if no matches are found.

See the "Finder Method Example" for more information on both types of finder methods.

### Select Methods

Select methods are used primarily to return values for CMP or CMR fields. All values are returned in their own object type; any primitive types are wrapped in objects that have similar functions (for example, a primitive `int` type is wrapped in an `Integer` object). See section 10.5.7 of the EJB 2.0 specification for more information on select methods.

These methods are for internal use within the bean. These methods cannot be called from a client. Thus, you do not define them in the home interfaces. Select methods are used to retrieve entity bean references or the value of a CMP field.

Do the following to define select methods:

1. Define an `ejbSelect<name>` method in the bean class for each select method. Each method is defined as `public` `abstract`. The SQL that is necessary for this method is not included in the implementation.

2. Define the full query or just the conditional statement (the WHERE clause) for the select method in the deployment descriptor. An EJB QL statement is created for each select method in its own `<query>` element. The container uses this statement to translate the condition into the relevant SQL statements.

See the "Select Method Example" for more information on both types of finder methods.

#### Return Objects

Here are the rules for defining return types for the select method:

- No objects: If no objects are found, a `FinderException` is raised.

- Single object: If you retrieve only a single item, the container returns the same type as returned in the `ejbSelect<name>` method. If multiple objects are returned, a `FinderException` is raised.

- Multiple objects: If you request multiple items, you must define the return type of the `ejbSelect<name>` method as either a `Set` or `Collection`. A `Set` eliminates duplicates. A `Collection` may include duplicates. For example, if you want to retrieve all zip codes of all customers, use a `Set` to eliminate duplicates. To retrieve all customer names, use a `Collection` to retrieve the full list. An empty `Collection` or `Set` is returned if no matches are found.

  - Bean interface: If you return the bean interface, the default interface type returned within the `Set` or `Collection` is the local bean interface. You can change this to the remote bean interface in the `<result-type-mapping>` element, as follows:

        <result-type-mapping>Remote</result-type-mapping>

  - CMP values: If you return a `Set` or `Collection` of CMP values, the container determines the object type from the EJB QL select statement.

## Deployment Descriptor Semantics

The structure required for defining both types of query methods is the same in the deployment descriptor.

1. You must define the `<abstract-schema-name>` element in the `<entity>` element for each entity bean referred to in the EJB QL statement. This element defines the name that identifies the entity bean in the EJB QL statement. Thus, if you define your `<abstract-schema-name>` as `Employee`, then the EJB QL uses `Employee` in its EJB QL to refer to the `EmpBean` entity bean.

2. Define the `<query>` element for each query method (finder and select), except for the `findByPrimaryKey` finder method.

   Note: If you want to use the OC4J-specific syntax, you still start with configuring the EJB QL <query> element. Then, after deployment, you modify the query in the `orion-ejb-jar.xml` file to be the statement that you want.

   The `<query>` element has two main elements:

   - The `<method-name>` element identifies the finder or select method. The finder method is the same name as defined in the component home interfaces. The select method is the same name as defined in the bean class.

   - The `<ejb-ql>` element contains the EJB QL statement for this method.

Example 7-1 Employee FindAll Deployment Descriptor Definition

The following example shows the `EmpBean` entity bean definition.

- The `<entity>` element defines its `<abstract-schema-name>` as `Employee`.

- Two `<query>` elements define finder methods, `findAll` and `findByEmpNo`, in which the EJB QL statement refers to the `Employee` name.

    <entity>
      <display-name>EmpBean</display-name>
      <ejb-name>EmpBean</ejb-name>
      ...
      <abstract-schema-name>Employee</abstract-schema-name>
      <cmp-field><field-name>empNo</field-name></cmp-field>
      <cmp-field><field-name>empName</field-name></cmp-field>
      <cmp-field><field-name>salary</field-name></cmp-field>
      <primkey-field>empNo</primkey-field>
        <prim-key-class>java.lang.Integer</prim-key-class>
        ...
        <query>
         <description></description>
          <query-method>
            <method-name>findAll</method-name>
            <method-params />
          </query-method>
         <ejb-ql>Select OBJECT(e) From Employee e</ejb-ql>
        </query>

    <query>
     <description></description>
     <query-method>
        <method-name>findByEmpNo</method-name>
        <method-params>
          <method-param>java.lang.Integer</method-param>
        </method-params>
      </query-method>
      <ejb-ql>SELECT OBJECT(e) FROM Employee e WHERE e.empNo = ?1
      </ejb-ql>
    </query>

    ...
    </entity>

The EJB QL statement for the `findAll` method is simple. It selects objects, identified by the variable `e`, from the `Employee` entity beans. Thus, it selects all `Employee` entity bean objects. The EJB QL statement for the `findByEmpNo` method selects all objects where the employee name is equal to the first input parameter to the method. After deployment, OC4J translates the EJB QL statements into `<finder-method>` elements in the `orion-ejb-jar.xml` file, as follows:

    <finder-method query=""> /*the empty where clause finds all employees*/
    <finder-method query="$empname = $1"> /*this finds all records where 	employee is equal to the first input parameter.*/

See "Finder Method Example" for more information and examples.

## Finder Method Example

To define finder methods in a CMP entity bean, do the following:

1. Define the finder method in one or both of the home interfaces.

2. Define the finder method definition in the deployment descriptor.

The following sections demonstrate how to create finder methods using either the EJB QL syntax or the OC4J-specific syntax:

- Specifying Finder Methods With EJB QL Syntax

- Specifying Finder Methods With OC4J-Specific Syntax

### Specifying Finder Methods With EJB QL Syntax

There are two steps for creating a finder method:

1. Define the Finder Method in the Home Interface

2. Define the Finder Method Definition in the Deployment Descriptor

#### Define the Finder Method in the Home Interface

You must add the finder method to the home interface. For example, if you want to retrieve all employees, define the `findAll` method in the home interface (local home interface for this example), as follows:

    public Collection findAll() throws FinderException;

To retrieve data for a single employee, define the `findByEmpNo` in the home interface, as follows:

    public EmployeeLocal findByEmpNo(Integer empNo) throws FinderException;

The returned bean interface is the local interface, `EmployeeLocal`. The input parameter is an employee number, `empNo`, which is substituted in the EJB QL `?1` parameter.

#### Define the Finder Method Definition in the Deployment Descriptor

Each finder method is defined in the deployment descriptor in a `<query>` element. Example 7-1 contains the EJB QL statement for the `findAll` method. The following example shows the deployment descriptor for the `findByEmpNo` method:

    <query>
      <description></description>
      <query-method>
        <method-name>findByEmpNo</method-name>
        <method-params>
          <method-param>java.lang.Integer</method-param>
        </method-params>
      </query-method>
      <ejb-ql>SELECT OBJECT(e) FROM Employee e WHERE e.empNo = ?1
      </ejb-ql>
    </query>

The EJB QL statement for the `findByEmpName` method selects the `Employee` object where the employee number is substituted in the EJB QL `?1` parameter. The `?` symbol denotes a place holder for the method parameters. Thus, the `findByEmpNo` is required to supply at least one parameter. The `empNo` passed in on the `findByEmpNo` method is substituted in the ?1 position here. The variable, `e`, identifies the `Employee` object in the `WHERE` condition.

#### Relationship Finder Example

For the EJB QL statement that involves a relationship between entity beans, both entity beans are referenced within the EJB QL statement. The following example shows the `findByDeptNo` method. This finder method is defined within the employee bean, which references the department entity bean. This method retrieves all employees that belong to a department.

    <query>
      <description></description>
      <query-method>
        <method-name>findByDeptNo</method-name>
        <method-params>
          <method-param>java.lang.Integer</method-param>
        </method-params>
      </query-method>
      <ejb-ql>SELECT OBJECT(e) From Employee e, IN (e.dept)
             AS d WHERE d.deptNo = ?1
      </ejb-ql>
    </query>

The `<abstract-schema-name>` element for the employee bean is `Employee`. The employee bean defines a relationship with the department bean through a CMR field, called `dept`. Thus, the department bean is referenced in the EJB QL through the `dept` CMR field. The department primary key is `deptNo`. The department number that the query is executed with is given in the input parameter and substituted in `?1`.

### Specifying Finder Methods With OC4J-Specific Syntax

There are two steps for creating a finder method:

1. Add the Finder Method to Home Interface

2. Add the Query to the OC4J-Specific Deployment Descriptor

#### Add the Finder Method to Home Interface

You must first add the finder method to the home interface. For example, with the employee entity bean, if we wanted to retrieve all employees, the `findAll` method would be defined within the home interface, as follows:

    public Collection findAll() throws FinderException, RemoteException;

#### Add the Query to the OC4J-Specific Deployment Descriptor

After specifying the finder method in the home interface, modify the `orion-ejb-jar.xml` file with the finder method query.

The `<finder-method>` element defines all finder methods—excluding the `findByPrimaryKey` method. The simplest finder method to define is the `findByAll` method. The `query` attribute in the `<finder-method>` element can specify a full query or just the `WHERE` clause for the query. If you want all rows retrieved, then an empty query (`query=""`) returns all records.

OC4J-specific finder methods are configured in the `orion-ejb-jar.xml` file in a `<finder-method>` element. Each `<finder-method>` element specifies a partial or full SQL statement in its `query` attribute, as follows:

    <finder-method query=""> /*the empty where clause finds all */
    OR
    <finder-method query="$empname = $1"> /*this finds all records where 	employee is equal to the first input parameter.*/

If you have a `<finder-method>` with a `query` attribute, it takes precedence over any EJB QL modifications to the same method in the `ejb-jar.xml` file.

To define a complex finder method, do the following:

1. Define a simple query that is similar using EJB QL in the `ejb-jar.xml` file.

2. Deploy the application. When you deploy, OC4J translates the EJB QL statement to the OC4J-specific equivalent. The full SQL statement that will be executed is displayed in a comment.

3. Modify the `query` attribute of the `<finder-method>` in the `orion-ejb-jar.xml` file to have the exact complexity you desire. When you redeploy, OC4J translates the new query and will write out a new comment with the exact SQL statement that will be executed. Check the comment to verify that you have the right syntax.

If you want to use the EJB QL syntax and you have an existing definition in `orion-ejb-jar.xml` file, then do the following:

1. Erase the `query` attribute of the `<finder-method>` in the `orion-ejb-jar.xml` file.

2. Redeploy the application. OC4J notes that the `query` attribute is not present and uses the EJB QL methodology from the `ejb-jar.xml` file instead.

Example 7-2 OC4J-Specific Finder Syntax

The following example retrieves all records from the `EmployeeBean`. The method name is `findAll`, and it requires no parameters because it returns a `Collection` of all employees.

    <finder-method query="">
    <!-- Generated SQL: "select EmployeeBean.empNo, EmployeeBean.empName,
        EmployeeBean.salary from EmployeeBean" -->
      <method>
         <ejb-name>EmployeeBean</ejb-name>
         <method-name>findAll</method-name>
             <method-params></method-params>
         </method>
    </finder-method>

After deployment, OC4J will add the commented line of what query will be. Use the comment to verify that it is the type of query that you expect.

To be more specific, modify the `query` attribute with the appropriate `WHERE` clause. This clause refers to passed in parameters using the '$' symbol: the first parameter is denoted by $1, the second by $2. All `<cmp-field>` elements that are used within the `WHERE` clause are denoted by `$<cmp-field>` name.

The following example specifies a `findByName` method (which should be defined in the home interface) where the name of the employee is given as in the method parameter, which is substituted for the $1. It is matched to the CMP name, "`empName`". Thus, our `query` attribute is modified to contain the following for the `WHERE` clause: "`$empname`=`$1`".

    <finder-method query="$empname = $1">
      <method>
        <ejb-name>EmployeeBean</ejb-name>
        <method-name>findByName</method-name>
        <method-params>
          <method-param>java.lang.String</method-param>
        </method-params>
      </method>
    </finder-method>

If you have more than one method parameter, each parameter type is defined in successive `<method-param>` elements and referred to in the query statement by successive $n, where n represents the number.

Note: You can also specify a SQL JOIN in the `query` attribute.

If you wanted to specify a full query and not just the section after the WHERE clause, specify the `partial` attribute to FALSE and then define the full query in the `query` attribute. The default value for `partial` is true, which is why it is not specified on the previous finder-method example.

    <finder-method partial="false"
                   query="select * from EMP where $empName = $1">
            <!-- Generated SQL: "select * from EMP where EMP.ENAME = ?" -->
            <method>
                    <ejb-name>EmployeeBean</ejb-name>
                    <method-name>findByName</method-name>
                    <method-params>
                            <method-param>java.lang.String</method-param>
                    </method-params>
            </method>
    </finder-method>

Specifying the full SQL query is useful for complex SQL statements.

For entity bean finder methods, lazy loading can cause the select method to be invoked more than once. By default, lazy loading is turned off. If you are retrieving large numbers of objects, and you are accessing only a few of them, you should turn on lazy loading.

To turn on lazy loading, set the `lazy-loading` property to true.

    <finder-method partial="false"
                   query="select * from EMP where $empName = $1"
                   lazy-loading=true>
            <!-- Generated SQL: "select * from EMP where EMP.ENAME = ?" -->
            <method>
                    <ejb-name>EmployeeBean</ejb-name>
                    <method-name>findByName</method-name>
                    <method-params>
                            <method-param>java.lang.String</method-param>
                    </method-params>
            </method>
    </finder-method>

Additionally, you can state how many rows the JDBC driver fetches at a time by setting the `prefetch-size` attribute, as follows:

    <finder-method partial="false"
                   query="select * from EMP where $empName = $1"
                   prefetch-size="15" >
            <!-- Generated SQL: "select * from EMP where EMP.ENAME = ?" -->
            <method>
                    <ejb-name>EmployeeBean</ejb-name>
                    <method-name>findByName</method-name>
                    <method-params>
                            <method-param>java.lang.String</method-param>
                    </method-params>
            </method>
    </finder-method>

Oracle JDBC drivers include extensions that allow you to set the number of rows to prefetch into the client while a result set is being populated during a query. This reduces round trips to the database by fetching multiple rows of data each time data is fetched—the extra data is stored in client-side buffers for later access by the client. The number of rows to prefetch can be set as desired. The default number of rows to prefetch to the client is 10. The number set here is passed along to the JDBC driver. See the Oracle 9i JDBC Developer's Guide and Reference for more information on using prefetch with a JDBC driver.

## Select Method Example

To define select methods in a CMP entity bean, do the following:

1. Define the select method in the bean class as `ejbSelect<name>`.

2. Define the select method definition in the deployment descriptor.

   Note: You cannot modify the query statement for an `ejbSelect` method in the `orion-ejb-jar.xml` file, as you can for finder methods.

### Define the Select Method in the Bean Class

Add the select method in the bean class as an abstract method. For example, if you want to retrieve all employees whose salary falls within a range, define the `ejbSelectBySalaryRange` method, as follows:

    public abstract Collection ejbSelectBySalaryRange(Float s1, Float s2)
    	throws FinderException;

Because the select method retrieves multiple employees, a `Collection` is returned. The low and high end of the salary range are input parameters, which are substituted in the EJB QL ?1 and ?2 parameters. The first input parameter is returned in ?1; the second input parameter is returned in ?2. The order of the all declared method parameters is the same as the order of the ?1, ?2, ... ?n EJB QL parameters.

### Define the Select Method Definition in the Deployment Descriptor

Each select method is defined in the deployment descriptor in a `<query>` element. The following example shows the deployment descriptor for both the `ejbSelectBySalaryRange` and `ejbSelectNameBySalaryRange` methods:

    <query>
      <description></description>
      <query-method>
        <method-name>ejbSelectBySalaryRange</method-name>
        <method-params>
          <method-param>java.lang.Float</method-param>
          <method-param>java.lang.Float</method-param>
        </method-params>
      </query-method>
      <ejb-ql>SELECT DISTINCT OBJECT(e) From Employee e
               WHERE e.salary BETWEEN ?1 AND ?2
      </ejb-ql>
    </query>
    <query>
      <description></description>
      <query-method>
        <method-name>ejbSelectNameBySalaryRange</method-name>
        <method-params>
          <method-param>java.lang.Float</method-param>
          <method-param>java.lang.Float</method-param>
        </method-params>
      </query-method>
      <ejb-ql>SELECT e.empName From Employee e
               WHERE e.salary BETWEEN ?1 AND ?2
      </ejb-ql>
    </query>

Both of these methods provide two input parameters of type float. The types of these expected input parameters are defined in the `<method-param>` elements.

The EJB QL is defined in the `<ejb-ql>` element. Both methods evaluate the CMP field of salary within the EJB QL statement by the `e.salary`. The `e` represents the `Employee` objects; the `salary` represents the CMP field within that object. Separating it with a period shows the relationship between the entity bean and its CMP field.

The two input parameters designate the low and high salary ranges and are substituted in the `?1` and `?2` positions respectively.

The `ejbSelectBySalaryRange` method returns objects, where the `DISTINCT` keyword ensures that no duplicate records are returned. The `ejbSelectNameBySalaryRange` returns only the names of the employees, which is a `String`. This demonstrates one of the advantages of using select statements, in that you can return only the values of CMP fields within your objects.

## Oracle EJB QL Type Extensions: Date, Time, Timestamp, and SQRT

Even though the current version of the EJB specification does not support `Date`, `Time`, `Timestamp`, and `SQRT`, we have added support for these types, as follows:

- `SQRT`(v) : Both the double primitive type and the `java.lang.Double` types are supported for arguments.

- `java.util.Date`, `java.sql.Date`, `java.sql.Time`. `and java.sql.Timestamp` are allowed in an EJB QL binary expression, such as equality expressions.

The following show examples of how to use these EJB QL type extensions:

Example 7-3 Using SQRT

    <query>
      <query-method>
        <method-name>ejbSelectDoubleTypeSqrt</method-name>
        <method-params>
          <method-param>double</method-param>
        </method-params>
      </query-method>
      <result-type-mapping>Remote</result-type-mapping>
      <ejb-ql>
        SELECT OBJECT(a) FROM Dept a WHERE a.deptDoubleType = SQRT(?1)
      </ejb-ql>
    </query>

Example 7-4 Date Example

    <query>
      <query-method>
       <method-name>ejbSelectDate</method-name>
       <method-params>
        <method-param>java.util.Date</method-param>
       </method-params>
      </query-method>
      <result-type-mapping>Remote</result-type-mapping>
      <ejb-ql>
        SELECT OBJECT(a) FROM Dept a WHERE a.deptDate = ?1
       </ejb-ql>
    </query>

Example 7-5 Another Date Example

    <query>
      <query-method>
       <method-name>ejbSelectSqlDate</method-name>
       <method-params>
        <method-param>java.sql.Date</method-param>
       </method-params>
      </query-method>
      <result-type-mapping>Remote</result-type-mapping>
      <ejb-ql>
        SELECT OBJECT(a) FROM Dept a WHERE a.deptSqlDate = ?1
      </ejb-ql>
    </query>

Example 7-6 Timestamp Example

    <query>
      <query-method>
       <method-name>findByTimestamp</method-name>
       <method-params>
        <method-param>java.sql.Timestamp</method-param>
       </method-params>
      </query-method>
      <result-type-mapping>Remote</result-type-mapping>
      <ejb-ql>
        SELECT OBJECT(a) FROM Dept a WHERE a.deptTimestamp = ?1
      </ejb-ql>
    </query>

Example 7-7 Time Example

    <query>
      <query-method>
       <method-name>findByTimestamp</method-name>
       <method-params>
        <method-param>java.sql.Time</method-param>
       </method-params>
      </query-method>
      <result-type-mapping>Remote</result-type-mapping>
      <ejb-ql>
        SELECT OBJECT(a) FROM Dept a WHERE a.deptTime = ?1
      </ejb-ql>
    </query>