EJB Queries

EJB queries allow you to do most of the things with EJBs that DBMS queries allow you to do in a conventional relational database.  The language in which EJB queries are expressed is referred to as EJB QL.

SQL-like queries among entity EJBs are a standard part of the EJB 2.0 specification.  See, especially, Chapters 10 & 11.  Also see the discussions in Kyle Brown's book, especially chapters 24 and 25.  Several useful tutorials include O'Reilly's/Jeelani Shaik's Learning EJB QL and IBM's audio tutorial on EJB QL.

Below is a checklist of steps that may be used to create a query on a cmp top-down EJB using IBM's WSAD 5.1.2.

Implementing EJB QL statements

A checklist of key steps for specifying this follows:.

  1. Create the EJBs that will participate in the relationship in the conventional (e.g., top-down) manner.
  2. Open the Deployment Descriptor for the EJB project and scroll down to the section labeled Queries.  Select Add.
  3. When the wizard opens, designate new, find method, local as Type, and an appropriate Return type for the query (e.g., Collection, if you expect more than one result object).  Also provide a name for the query (e.g., findByValues).  Finally, for each parameter that will participate in the query, provide its name as it is known in the EJB and its type (e.g., String)  Click next.
  4. In the next panel, you specify the actual query, expressed within the syntax of EJB QL.  You can get help with this by Selecting a sample query and then editing it.  For example,
    SELECT object(o) FROM User o WHERE o.nameFirst LIKE ?1
    AND o.nameMiddle LIKE ?2 AND o.nameLast LIKE ?3
    AND o.address1 LIKE ?4 AND o.address2 LIKE ?5
    AND o.city LIKE ?6 AND o.state LIKE ?7 AND o.zip LIKE ?8
    AND o.phoneHome LIKE ?9 AND o.phoneWork LIKE ?10
    AND o.phoneFax LIKE ?11 AND o.phoneCell LIKE ?12
    AND o.email LIKE ?13  AND o.url LIKE ?14
  5. Note that parameters, as specified in the previous step, are designated ?1, ?2, etc.
  6. I call your attention, especially, to the LIKE operand, which allows you to designate wild cards in your expression with the percent (%) symbol.
  7. Note, also, that EJB QL queries may include cmr roles as well as conventional EJB fields.  See the EJB 2.0 Specification for details.
  8. Select Finish and save the Deployment Descriptor.
  9. Regenerate the Deploy and RMIC code.  You may have to delete old generated classes, but be careful not to delete your basic EJB classes (i.e., the ejb bean, container, and home interfaces).
  10. Go back to the EJBs and look at their respective bean and local interface classes.  Note the methods inserted into them that support the queries.  Note, also, that these methods are defined on the Local interface, not the Remote interface. Consequently, you must access these EJBs through these interfaces using JNDI lookup, not a factory class.