EJB-QL is the EJB 2.0 query language. It is similar to SQL, but has the advantage that it can be applied to a variety of different backing store types. For relational databases, Resin-CMP compiles EJB-QL to SQL statements at deploytime.

In addition to being backing-store independent, EJB-QL allows you to reference your CMP and CMR fields inside queries. This means you're writing queries at an object-oriented level.

For our example, we have the following relationships:

    [COURSE](n)----(n)[STUDENT]
      (1)                   (n)
       |                      \
       |                       \
      (1)                      (1)
    [TEACHER]                 [HOUSE]
  

Basics

An EJB-QL query is composed of, at most, three clauses:

The FROM clause defines the domain from which elements can be chosen in the SELECT clause:

    SELECT house FROM ejbql_house AS house
  

ejbql_house is the abstract schema type (that is, the table) that stores our house entities. In the FROM clause, we assign this abstract schema type the name house (house is an identification variable). Now that we have declared the domain of the query, we can select house with the SELECT clause. Note that this query can evaluate to a Collection of Houses or just one House, depending on the return type that you have assigned to your finder method.

Navigating with CMR fields

Recall that CMR fields are virtual fields, and that they are implemented by the container. CMR fields are defined in the <relationships> section of the deployment descriptor. For example, in our Student bean, we have defined the CMR field courseList which resembles a Collection of all courses taken by a student.

We can use the courseList field in our query to obtain a Collection of all the teachers that a Student is taking classes from:

    SELECT course.teacher
    FROM ejbql_student student, IN(student.courseList) course
    WHERE student.name=?1
  

In the FROM clause, we declare student to be an identification variable for the ejbql_student table. From now on, we can use "student" to mean "for each student".
Next, we use the the IN indentifier to obtain the courseList for each student. Again we assign an indentification variable, course, this time to denote "for each one of the student's courses".
Now we use the WHERE clause to make sure we don't leave courses in the domain that are taken by students other than the one we care about.

Finally, we have collected all the student's courses. We can now return the name of those teachers whose courses the student is taking. Because the Course and Teacher entities are 1-1 related, we can simply select a Course's teacher with the '.' operator. We could not do this in the FROM clause, with student.courseList, because courseList is a Collection.