5
EJB Query Language

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

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

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 headings. EJB QL applies queries against entity beans, using the entity bean name and its CMP and CMR fields 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, primary keys, foreign keys, 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 methods 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 conditional statement for the finder method in the deployment descriptor. 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.

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.

Backward Compatibility for Finder Methods

In Release 2 (9.0.2) and previous releases, OC4J had its own methodology for finder methods. These finder methods were configured in the orion-ejb-jar.xml file in a <finder-method> element. Each <finder-method> element specified a partial or full SQL statement in its query attribute, as follows:

<finder-method query="">
OR
<finder-method query="$empname = $1">

If you have a <finder-method> with a query attribute from a previous release, it overrides any EJB QL modifications to the same method in the ejb-jar.xml file. The orion-ejb-jar.xml configured <finder-method> query attribute definition has higher priority.

To have the previous finder method modified with EJB QL, erase the query attribute of the <finder-method> in the orion-ejb-jar.xml file and redeploy the application. OC4J notes that the query attribute is not present and places the EJB QL equivalent in the <finder-method> element.

Select Methods

Select 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 conditional statement 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.

Return Objects

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

• Single object: If you retrieve only a single item, the container returns the same type as returned in the ejbSelect<name> method.

• 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 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. You must define the <query> element for each query method (finder and select), except for the findByPrimaryKey finder method. 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 5-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.

• A <query> element defines a finder method, findAll, 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>
    ...
  </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.

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.

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

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.

Define the Select Method in the Bean Class

Add the select method in the bean class. For example, if you want to retrieve all employees whose salary falls within a range, define the ejbSelectBySalaryRange method in the bean class, 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 order of the 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 the ejbSelectBySalaryRange method:

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

The ejbSelectBySalaryRange method provides two input parameters, both 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. This select method evaluates the CMP field of salary. It is designated 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 DISTINCT keyword ensures that no duplicate records are returned.