Implementing EJB QL Finder Methods

This section describes the following:

Specifying Finder Methods Using EJB QL Syntax
Specifying Finder Methods Using OC4J-specific Syntax

Finder methods (ejbFind) define search criteria that let you query for an entity bean (see "Understanding Finder Methods" for more information).

To define finder methods, follow this procedure:

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 entity bean class definition. The container returns the appropriate home interface type.
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. 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.
  
  Note:
  In EJB 2.0, EJB QL has limited support for GROUP BY and ORDER BY functions, such as AVERAGE and SUM.
  
  See "Specifying Finder Methods Using 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 the corresponding EJB QL statement in the ejb-jar.xml file.
  
  See "Specifying Finder Methods Using OC4J-specific Syntax" for more information.

Note:

Finder methods must throw FinderException.

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.

Specifying Finder Methods Using EJB QL Syntax

There are two steps in creating a finder method using EJB QL syntax:

Defining Finder Methods in the Home Interface
Using the Deployment Descriptor to Provide the Finder Methods Definition

Defining Finder Methods 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 findByEmployeeNumber method in the home interface, as follows:

public EmployeeLocal findByEmployeeNumber(Integer empNumber) throws FinderException;

In the preceding example, the returned bean interface is the local interface, EmployeeLocal. The input parameter is an employee number, empNumber, which is substituted in the EJB QL ?1 parameter.

Using the Deployment Descriptor to Provide the Finder Methods Definition

Each finder method is defined in the deployment descriptor in a <query> element. The following example shows the deployment descriptor for the findByEmployeeNumber method:

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

The EJB QL statement for the findByEmployeeNumber method selects the Employee object where the employee number is substituted in the EJB QL ?1 parameter. The ? symbol denotes a placeholder for the method parameters. Thus, the findByEmployeeNumber method is required to supply at least one parameter. The empNumber passed in on the findByEmployeeNumber method is substituted in the ?1 position here. The variable, emp, identifies the Employee object in the WHERE condition.

Specifying Finder Methods Using OC4J-specific Syntax

There are two steps in creating a finder method using OC4J-specific syntax:

Adding Finder Methods to the Home Interface
Using the OC4J-specific Deployment Descriptor to Define Finder Methods

Adding Finder Methods to the Home Interface

You must first add the finder method to the home interface. For example, with the Employee entity bean, to retrieve all employees, you would define the findAll method within the home interface, as follows:

public Collection findAll() throws FinderException, RemoteException;

Using the OC4J-specific Deployment Descriptor to Define Finder Methods

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

The <finder-method> element defines all finder methods, except the findByPrimaryKey method. The simplest finder method to define is the findAll 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 specify an empty query (query="") as it 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:

/*the empty WHERE clause finds all*/
<finder-method query="">

/*finds all records where employee equals the first input parameter*/
<finder-method query="$empName=$1">

If you have a <finder-method> element 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, follow this procedure:

In the ejb-jar.xml file, define a simple query using EJB QL.
Deploy the application. When you deploy, OC4J translates the EJB QL statement to the OC4J-specific equivalent. The full underlying SQL statement to be executed is displayed in a comment (see Example 6-1, "OC4J-specific Syntax for findAll Method").
Modify the query attribute of the <finder-method> element in the orion-ejb-jar.xml file to achieve the desired level of complexity. When you redeploy, OC4J translates the new query and outputs a new comment with the SQL statement to 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:

Erase the query attribute of the <finder-method> element in the orion-ejb-jar.xml file.
Redeploy the application. OC4J notices that the query attribute of the <finder-method> element is not present and uses the EJB QL methodology from the ejb-jar.xml file instead.

WARNING:

There are limitations in the way OC4J generates SQL statements based on the partial finder query when executing complex queries that involve CMR. For example, there is no notation to dereference across a CMR join. You have to set the partial attribute of the <finder-method> element to false and use raw SQL when dealing with columns for container-managed relationships. For more information on partial queries, see "Element Description" and Example 6-3.

Note that this limitation is partially caused by the EJB 2.0 specification not supporting the LIKE clause.

Example 6-1 OC4J-specific Syntax for findAll Method

Example 6-1 demonstrates the retrieval of all records from the EmployeeBean. The finder method name is findAll. This method requires no parameters because it returns a Collection of all employees.

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

Note:

If you wish to add specifics to your query, you can modify the query attribute with the appropriate WHERE clause. This clause refers to passed-in parameters using the dollar ( $ ) symbol: the first parameter is denoted by $1, the second by $2, and so forth. All <cmp-field> elements that are used within the WHERE clause are denoted by $<cmp-field> name.

Example 6-2 OC4J-specific Syntax for findByName Method

Example 6-2 demonstrates the specification of a findByName method (which should be defined in the home interface). 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, the query attribute is modified to contain $empName=$1 for the WHERE clause.

<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>

Note:

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

Note:

You can specify a SQL JOIN in the query attribute.

Example 6-3 OC4J-specific Syntax for the Full Query

Example 6-3 shows how to specify a full query instead of just the section after the WHERE clause. In this case, you would have to set the partial attribute to false, and then define the full query in the query attribute. The default value for the partial attribute is true (that is the reason it is not specified in the Example 6-2).

<finder-method partial="false" query="select * from emp where $empName=$1">
    <!-- Generated SQL: "select * from emp where emp.empName=?" -->
    <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>

Note:

When using a generated SQL statement as the basis for a partial finder query, you need to ensure the following:

SQL-specific ? placeholders are translated to $n positional parameters.
certain XML characters, such as >, <, >=, are escaped.

Example 6-4 OC4J-specific Syntax for Enabling Lazy Loading

For entity bean finder methods, lazy loading can cause the select method (see "Understanding Select Methods") 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 by setting the lazy-loading property to true, as Example 6-4 shows:

<finder-method partial="false" query="select * from emp where $empName=$1"
lazy-loading=true>
    <!-- Generated SQL: "select * from emp where emp.empName=?" -->
    <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>

Example 6-5 OC4J-specific Syntax for Setting the Fetch Size

You can define how many rows at a time the JDBC driver fetches by setting the prefetch-size attribute, as Example 6-5 shows:

<finder-method partial="false" query="select * from emp where $empName=$1"
prefetch-size="15">
    <!-- Generated SQL: "select * from emp where emp.empName=?" -->
    <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 let you 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. This number is passed to the JDBC driver.