Welcome to the Enterprise Java Technologies Tech Tips for September 29, 2003. Here you'll get tips on using enterprise Java technologies and APIs, such as those in Java 2 Platform, Enterprise Edition (J2EE).

This issue covers:

Advanced EJB QL
EJB QL Select Methods

These tips were developed using Java 2 SDK, Standard Edition, v 1.4 and Java 2 SDK, Enterprise Edition, v 1.3.1 (Reference Implementation).

This issue of the Tech Tips is written by Mark Johnson, president of elucify technical communications, and co-author of Designing Enterprise Applications with the J2EE Platform, 2nd Edition. Mark Johnson runs an open forum for discussion of the tips.

You can download the sample archive for these tips. The context root for the application is ttsep2003, and the index.html welcome file indicates how to use the sample code. Any use of this code and/or information below is subject to the license terms.

ADVANCED EJB QL

The August, 2003 issue of the Enterprise Java Technologies Tech Tips provided introductory examples of using Enterprise JavaBeans Query Language (EJB QL). Specifically, it showed how to use EJB QL to create finder methods for entity beans in EJB version 2.0 and above. This month's tips provide more examples of using EJB QL. The first tip provides examples of more advanced EJB QL, and the second tip shows how to write and use EJB select methods.

SQL vs. EJB QL: Relational and Object Query Languages

EJB QL is an SQL-like query language. EJB QL queries are, however, somewhat different from relational SQL. SQL queries find data using a relational model. By contrast, EJB QL queries find data using the "abstract schema" of the enterprise beans in your application. So, instead of using SQL to query tables, rows, and columns, you use EJB QL to query enterprise beans, their persistent state, and their relationships. The result of an SQL query is a set of rows, with each row having the same sequence of columns. The result of an EJB QL query is either a collection of entity objects of a given type, a single entity object, or a collection of values retrieved from CMP (container-managed persistence) fields.

Typically, entity beans are stored in a relational database. EJB QL is designed so that the EJB container can map EJB QL queries directly to SQL. The resulting SQL queries are then executed against the relational database to retrieve the entity bean data.

Relational and object models differ in how they model relationships. A relational model uses foreign keys and joins to represent relationships. An object model uses a named relationship from one object type to another. Many of the joins in WHERE clauses of relational SQL queries join foreign keys to one another. Instead of joining primary keys in a WHERE clause, EJB QL queries use the "." operator and CMR field names to traverse relationships.

Understanding the data model of your application's enterprise beans is crucial to writing effective EJB QL queries. The following section explains the data model of the enterprise beans in this month's sample code.

Sample Code Data Model

The sample code for this month's tip is a Web application that implements a small university course registration system. A servlet (PersonServlet) uses several enterprise beans to implement searches on student registration data.

The sample code servlet accesses a data model consisting of the following entities:

• A Person entity represents a person in the system. It has two 1-to-1 relationships with Address ("workAddress" and "homeAddress"), and a 1-to-many relationship with Phone ("phones"). A Person's primary key is its "id" field.
  
  
• A Course entity represents a course offered by the university. It has a many-to-many relationship with Instructor ("instructors"), and a many-to-many relationship with Student ("students"). Its primary key is its "id" field.
  
  
• A Phone entity represents a phone number for a single Person. It has no relationships to other entities.
  
  
• An Address entity represents an address for a single Person. It has no relationships to other entities.
  
  
• A Student represents a student in the university. It has a 1-to-1 relationship to Person ("studentPerson"), and a many-to-many relationship with Course ("coursesEnrolledIn").
  
  
• An Instructor represents an instructor in the university. It has a 1-to-1 relationship to Person ("instructorPerson"), and a many-to-many relationship with Course ("coursesTaught").

Notice that in this data model, some relationships are unidirectional. For example, the relationship instructorPerson indicates the Person associated with an Instructor, but there's no way (using CMR fields) to access an Instructor associated with a Person.

Now that you understand the underlying data model, you can write EJB QL queries to find object instances. You can execute the example queries in the following sections by first deploying the sample application in your J2EE server, and then activating the appropriate link or form in the application's index.html page. For instructions on how to deploy and run the sample application, see the section "Running the Sample Code."

Using "IN" to Access CMR Collections In A FROM Clause

The EJB QL keyword "IN" has two uses. In the first case, the IN() keyword allows the programmer to use CMR collections in a query's FROM clause. The example in the sample code is the query for the finder method CourseLocalHome.findByPersonEnrolled. Given a PersonLocal interface instance as an argument, the following query returns a collection of the courses in which this Person (as a Student) is enrolled:

   SELECT OBJECT(c)
   FROM Person AS p, IN(p.student.coursesEnrolledIn) AS c
   WHERE p = ?1

The code in the PersonServlet class that uses the associated finder method looks like this:

   String person_id = req.getParameter("ID");
   PersonLocal person =
      Util.findPersonLocalHome().findByPrimaryKey(person_id);
   Collection courses =
      Util.findCourseLocalHome().findByPersonEnrolled(person);

In the FROM clause of the query, the expression IN(p.student.coursesEnrolledIn) traverses the relationship "student" from Person to Student, and then traverses the relationship "coursesEnrolledIn" from Student to Course. The clause "AS c" defines an "identification variable" that can be used elsewhere in the clause to represent the results of the traversal.

Executing this method prints a list of the classes in which an individual Person is enrolled. You can execute this query by entering a valid Person ID (obtained from one of the other examples) in the form titled "Find all courses in which a particular Person is enrolled".

Using IN in WHERE Clauses

Another use of the "IN" keyword is to test the contents of a CMP field against any of a list of strings in a WHERE clause. In the sample code, an Instructor has a type string, which is one of ('ResearchFellow', 'Adjunct', 'Associate', 'Assistant', 'Full', or 'Emeritus'). In this University, research fellows and emeritus professors are considered instructors, but are never assigned teaching positions. The following query identifies such instructors:

   SELECT OBJECT(i)
   FROM Instructor AS i
   WHERE i.type IN ('ResearchFellow', 'Emeritus')

This query is used by the finder method InstructorLocalHome.findNonTeaching(). The PersonServlet in the sample code uses the method like this:

   ilh = Util.findInstructorLocalHome();
   Collection nonteachinginstructors =
       ilh.findNonTeaching();

To execute this query, select the link titled "List all non-teaching instructors (Emeritus and Research Fellows)" on the main page of the sample application.

Traversing Multiple Relationships

You can use more than one relationship in an EJB QL query. Remember that a Person can be both a Student and an Instructor. Finder method PersonLocalHome.findStudentInstructors identifies Person objects that have both Instructor and Student objects. The method uses the following query:

   SELECT OBJECT(p) FROM Person AS p,
       Instructor i, Student s
       WHERE i.instructorPerson = p AND
       s.studentPerson = p

Notice that the items in the WHERE clause being compared to one another are objects, not values (such as primary keys). In other words, this query selects all Persons for whom an Instructor exists and a Student exists. Persons missing either an Instructor or a Student do not meet the conditions of the WHERE clause, and are therefore excluded from the result set.

You can execute this query by clicking the link "Find all Persons who are both Instructor and Student" on the main page of the sample application.

Queries With Objects As Arguments - findByPersonEnrolled

In the other examples shown in this tip (and in last month's tip), arguments to queries have been values, such as "WHERE p.id = ?1". But take another look at the query for the finder findByPersonEnrolled (already discussed above):

   SELECT OBJECT(c)
   FROM Person AS p, IN(p.student.coursesEnrolledIn) AS c
   WHERE p = ?1

Notice that the parameter to the query is an object (of type Person), not a primitive value. This is perfectly legal usage. In such cases, the parameter passed to the finder method is the component interface type, as shown below:

   PersonLocal person =
      Util.findPersonLocalHome().findByPrimaryKey(person_id);
   Collection courses =
      Util.findCourseLocalHome().findByPersonEnrolled(person);

As mentioned earlier, you can execute this query in the sample application by submitting the form titled "Find all courses in which a particular Person is enrolled".

Searching With Patterns Using LIKE

The LIKE keyword, used in WHERE clauses, works in EJB QL just as it does in SQL. LIKE provides matching of a string attribute value against a very simple pattern. For example, the sample code method CourseLocalHome.findIntroductoryCourses is implemented by the following EJB QL query:

   SELECT OBJECT(c)
   FROM Course AS c
   WHERE c.description LIKE '%INTRODUCT%' OR c.id LIKE '1__'

The application defines an introductory course as any course whose description contains the string 'INTRODUCT', or any course with an 3-digit ID number between 100 and 199. The query performs the search using two LIKE clauses. The pattern string for a LIKE clause has two special characters. The special character % matches any string (including an empty string). The special character _ matches any single character. (Note that the pattern %INTRODUCT% matches strings beginning with INTRODUCT, because the first % matches the empty string.) Every other character in the string matches only itself.

To execute method findIntroductoryCourses in the sample application, click the link "List introductory courses" on the application's main page.

To match a % or a _ in an attribute value, you must define and use an escape character. The escape character is defined using the ESCAPE keyword in the LIKE clause, for example:

   SELECT OBJECT(Mortgage) AS m
   WHERE m.rate LIKE '3.%\%' ESCAPE '\'

In the expression 3.%\% above, the characters 3, and . match themselves, the first % matches any number of characters, and \% matches a percent sign.

back to top

EJB QL SELECT METHODS

The EJB QL queries shown in the first tip, "Advanced EJB QL" return either an individual object (if the cardinality of the relationship is one), or a collection of objects (if cardinality is many). EJB QL can also return collections of attribute values, but only in the context of an EJB select method.

A select method is an easy way for a bean developer to access an entity bean's persistent state, or to access entity beans related to the bean that defines the select method. A select method is an abstract method, defined in an entity bean class, and implemented by an EJB QL query in the bean's deployment descriptor. Instead of writing code that repeatedly finds home interfaces and then calls finder methods, you can simply define an abstract select method in the bean class, and then implement the desired search in EJB QL.

Select methods are similar to finder methods in several ways:

• Select methods and finder methods are both implemented by writing EJB QL SELECT statements.
  
  
• Both select methods and finder methods must declare that they may throw FinderException.

There are also several differences between select methods and finder methods:

• A select method is defined in a bean class, and is not accessible outside the class that defines it. A finder method is defined in both the home interface and the bean class, and can be invoked (through the home interface) by other classes. This restriction on select methods isn't an important limitation. If you want to make the results of a select method available to clients, you can create a home or component interface method that returns the results of the select method.
  
  
• A select method can return a collection of values retrieved from enterprise bean CMP fields, in addition to single objects or collections of objects. A finder method can return only objects or collections of objects. In addition, entity objects returned by a finder method must be of the same type as the bean on whose home interface the finder method is defined.
  
  
• A select method name must begin with the string 'ejbSelect'. A finder method name must begin with the string 'find'. An ejbSelect method is defined on the bean class. A finder method is defined on the home interface.

The sample application for this month's tip defines a select method, CourseBean.ejbSelectEnrolledStudentEmails. The method returns a collection of email address strings for all of the students enrolled in a given course. The method definition in the file CourseBean.java looks like this:

    public abstract Collection
       ejbSelectEnrolledStudentEmails(String course_id)
       throws FinderException;

The EJB QL query that implements this select method looks like this:

  SELECT DISTINCT s.email
  FROM Course AS c, IN(c.students) AS s
  WHERE c.id = ?1

This query selects the email attribute from all of the Student objects that are successors of a particular Course's students relationship. The Course is selected by the course_id, which is passed to the query as an argument. The keyword DISTINCT ensures that all addresses in query results are unique.

Remember that only class CourseBean can use this method. Method CourseBean.asHtml uses the select method in the following way:

   Collection emails = null;
   try {
      emails = ejbSelectEnrolledStudentEmails(getId());
   } catch (FinderException fex) {
      _logger.warning(fex.getMessage());
   }

So why use select methods? After all, anything a select method could do could also be done using finder methods. The answer is, because select methods are easier than manually-coded solutions. For example, the following hypothetical component interface method would do the same thing as the select method defined above:

   public Collection 
               selectEnrolledStudentEmails(String id) {
     HashSet emails = new HashSet();
     try {
       InitialContext ic = new InitialContext();
       CourseLocalHome clh =
         (CourseLocalHome)ic.lookup(
                      "java:comp/env/ejb/local/Course");
       CourseLocal thisCourse = 
                               clh.findByPrimaryKey(id);
       ArrayList students = thisCourse.getStudentList();
       for (int i = 0; i < students.size(); i++) {
          StudentLocal student = 
                          (StudentLocal)students.get(i);
          String email = student.getEmail();
          emails.add(email);
       }
     } catch (NamingException nex) {
        _logger.severe("Error: " + nex.getMessage());
        throw new EJBException(nex);
     } catch (FinderException fex) {
        throw new EJBException(fex);
     }
     return emails;
   }

The hypothetical component interface method above is not a recommended solution. Using select methods is the preferred technique for accessing entity object persistent state and relationships, for a number of reasons:

• Select methods are simply a lot less work. They require less coding, less debugging, and less maintenance than manually-written code.
  
  
• The EJB container can optimize the implementation of a select method, allowing it to be executed with a single query to the database, potentially providing much better performance than could bean code.

For these reasons, you should look for opportunities to use select methods when accessing entity bean persistent state and relationships. Doing so will make implementation and maintenance easier, and can improve your application's performance.

For more in-depth information about EJB QL see the publication Applying Enterprise JavaBeans

back to top

RUNNING THE SAMPLE CODE

Download the sample archive for these tips. The application's context root is ttsep2003.ear. The downloaded EAR file also contains the complete source code for the sample.

To run the sample program, follow these steps:

1. Be sure that both the j2ee server and database server are running. For the J2EE Reference Implementation, execute both:

        j2ee -verbose
   

   and

        cloudscape -start
   

2. Download the ear file as described above.
   
   
3. Deploy the ear file to your server. You can deploy the application archive (ttsep2003.ear) in the J2EE Reference Implementation using the deploytool program:

       $J2EE_HOME/deploytool -deploy ttsep2003.ear localhost
   

   Replace localhost with the name of the host on which the server is installed. For a standard installation on a single machine, the hostname typically (and literally) is localhost.
   
   
4. Access the application at http://localhost:8000/ttsep2003.

For a J2EE-compliant implementation other than the Reference Implementation, use your J2EE product's deployment tools to deploy the application on your platform.

Choose one or more of the functions listed on the index page of the application to run the associated EJB QL. For example, if you select:

List all non-teaching instructors 
   (Emeritus and Research Fellows)

It runs the EJB QL:

   SELECT OBJECT(i)
   FROM Instructor AS i
   WHERE i.type IN ('ResearchFellow', 'Emeritus')  

You should see the result:

back to top

Reader Feedback   Very worth reading    Worth reading    Not worth reading  If you have other comments or ideas for future technical tips, please type them here:   Have a question about Java programming? Use Java Online Support.

back to top