Introducing advanced EJB-QL for Finder Methods.

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 `SELECT` clause
- the `FROM` clause
- the `WHERE` clause (optional)

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.