6 Implementing Query Methods for an Entity Bean With Container-Managed Persistence

You can express your entity bean queries using finder or select methods. This chapter provides details on implementing Orion EJB 2.0 EJB QL finder and select methods.

This chapter includes information on the following topics:

Implementing EJB QL Finder Methods
Implementing EJB QL Select Methods

For more information, see the following:

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.

Implementing EJB QL Select Methods

Select methods (ejbSelect) are used to retrieve entity bean references or values of container-managed persistent or relationship fields (see "Understanding Select Methods").

The format for an ejbSelect method definition is as follows:

public abstract type ejbSelect<METHOD>(...);

Although the select method is not based on the identity of the entity bean instance on which it is invoked, it can use the primary key of an entity bean as an argument. This creates a query that is logically scoped to a particular entity bean instance. For information on how to define the select method return type, see "Defining the Return Type for the Select Method".

To define a select method, follow this procedure:

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.
Define the full query or the conditional statement alone (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.

Note:

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

Example 6-6 Defining the Select Method in the Bean Class

Example 6-6 demonstrates the definition of a select method to retrieve all employees whose salary falls within a specified range:

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

Because the preceding select method retrieves multiple employees, a Collection is returned. The low and high ends of the salary range are input parameters, which are substituted in the EJB QL for?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.

Example 6-7 Providing the Select Method Definition in the Deployment Descriptor

Each select method is defined in the deployment descriptor in a <query> element. Example 6-7 shows the deployment descriptor for the ejbSelectBySalaryRange method defined in the bean class in Example 6-6:

<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(emp) From Employee emp 
            WHERE emp.salary BETWEEN ?1 AND ?2</ejb-ql>
</query>

The ejbSelectBySalaryRange method has 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. The ejbSelectBySalaryRange method evaluates the persistent field of salary within the EJB QL statement by the emp.salary. The emp represents the Employee object; the salary represents the persistent field within that object. The separating period between them indicates the relationship between the entity bean and its persistent 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.

Note:

Select methods must throw FinderException.

Defining the Return Type for the Select Method

The following is the list of conditions that you must consider when defining return types for your select methods:

If the select method does not find any objects, a FinderException is raised.
If you want your select method to find a single object, the container returns the same type as returned in the ejbSelect<NAME> method. If multiple objects are returned, a FinderException is raised.
If you want your select method to find multiple objects, 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.
- If your select method returns a 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>
```
- If your select method returns a Set or Collection of CMP values, the container determines the object type from the EJB QL select statement.