7 EJB Query Language

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.

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.

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.

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

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