Implementation

This section describes how OTN developers implemented and used an EJB QL query in the FBS sample application to give users a way to retrieve forgotten user names and passwords.

First, the following code from <ibfbs_home>/etc/META-INF/ejb-jar.xml defines the query and other key data.

  <entity>
      <description>Entity Bean ( Container-managed Persistence )</description>
      <display-name>UserAccount</display-name>
      <ejb-name>UserAccount</ejb-name>
      <local-home>oracle.otnsamples.ibfbs.usermanagement.ejb.UserAccountHomeLocal</local-home>
      <local>oracle.otnsamples.ibfbs.usermanagement.ejb.UserAccountLocal</local>
      <ejb-class>oracle.otnsamples.ibfbs.usermanagement.ejb.UserAccountBean</ejb-class>
      <persistence-type>Container</persistence-type>
      <abstract-schema-name>UserAccount</abstract-schema-name>
        <cmp-version>2.x</cmp-version>
      <prim-key-class>java.lang.Integer</prim-key-class>
      <primkey-field>accountNumber</primkey-field>
      <reentrant>False</reentrant>
      <cmp-field><field-name>accountNumber</field-name></cmp-field>
      <cmp-field><field-name>password</field-name></cmp-field>
 
     ...
      <cmp-field><field-name>email</field-name></cmp-field>
      ...

      <query>    
        <description></description>
        <query-method>      
          <method-name>findByEmail</method-name>      
          <method-params>        
            <method-param>java.lang.String</method-param>   
          </method-params>    
        </query-method>    
        <ejb-ql>select distinct object(ua) from UserAccount ua where ua.email = ?1</ejb-ql>
      </query>
  </entity>

The XML tags themselves are pretty informative, but let's dissect this query element piece by piece.

• The query element is associated with a specific entity bean definition. An entity bean can have zero of more query definitions.
• The method name matches matches one of the methods defined on the home interface. If there is more than one method of the same name, the method parameters will be used to identify which specific method is being described.
• The method params provide information about the parameters that accompany the method call. The number of parameters and their types are required information and are used to associate logical values to be used in the query and to help disambiguate overloaded method names. It's important that you get the order and type of the parameters correct since these will be mapped onto the parameters passed into the query.
• The EJB-QL element defines the actual query that will be used. The most striking difference from standard SQL is the requirements to specify the OBJECT keyword when returning a single entity type. The entities to be used within the query are specified through the use of the <abstract-schema-name> that is supplied as part of the definition of the entity bean. To support parameterized queries, you use logical placeholders of the form ?n, where n represents the location of the value in the method params list, starting from an index of 1. EJB QL supports standard SQL operations of between and like, but it does not yet support aggregate and sorting functions (these are due to be supported in EJB 2.1).

So what we have here is a fully expressed query for the findByEmail method using EJB QL. It will take one parameter, a string representing an email address, and will then query the persistent store for all UserAccount entities which have a matching email address. If there are two entities with the same results, then it will return only one of the matching entities in the collection.

The following code from <ibfbs_home>/src/oracle/otnsamples/ibfbs/usermanagement/ejb/UserManagementSessionFacadeBean.java calls the findByEmail method to get an object representing a user account, then parses the object for account number, password, first name, and last name.

  public Hashtable getUserAccount(String email) throws RemoteException {
    Hashtable userHash = new Hashtable();
    try {
      // Find the User Account Local Entity Bean by passing the Email
      Collection uaColl = userAccountHomeLocal.findByEmail(email);
      Iterator   uaIter = uaColl.iterator();
      if (uaIter.hasNext()) {
        UserAccountLocal userAccountLocal = (UserAccountLocal) uaIter.next();
        // Add the queried values to the Hashtable
        userHash.put("ACCOUNTNUMBER", userAccountLocal.getAccountNumber());
        userHash.put("PASSWORD", userAccountLocal.getPassword());
        userHash.put("FIRSTNAME", userAccountLocal.getFirstName());
        userHash.put("LASTNAME", userAccountLocal.getLastName());
      }
    } catch (FinderException ex) {  // Trap Errors While Finding EJB Object
      System.out.println("Finder Exception in getUserAccount : "
                         + ex.toString());
      throw new RemoteException("Finder Exception While Getting User Account = " +
           ex.toString());
    }
    return userHash;
  }