resin-ee $ee/ejb-tut/cmp-find.xtp Finding Entity Beans with EJB-QL

Scenario: Headmaster Dumbledore needs to list all the courses and find the Potions instructor.

This example:

  • Creates a collection finder, findAll, to finds all Courses
  • Creates a single-entity finder, findByInstructor, to find a Course by its teacher.
tutorial find

Most applications need to query the database to find entities with special properties. With the query language EJB-QL, entity beans can define find methods in the home interface to look up beans with arbitrary properties.

The container managed fields in the previous example is great if you already know which entity bean to use, but in most applications the database entries are not hardcoded. Finding the right bean in the first place requires a query language.

Adding a find method has two steps:

  1. Add a find method to the home interface.
  2. Add the EJB-QL query to the deployment descriptor.

Resin-CMP uses the EJB-QL query language from the EJB 2.0 specification. EJB-QL resembles SQL with a few restrictions and extensions appropriate to handling entity beans. For example, EJB-QL extends SQL with direct support for relations and for method arguments.

In the following example, the school's headmaster needs to know which courses are being taught and needs to find the course taught by a named teacher.


The example uses the same database table as the previous basic example.

CREATE TABLE find_courses ( course_id VARCHAR(250) NOT NULL, instructor VARCHAR(250), PRIMARY KEY(course_id) );

The and methods use the EJB Query Language (EJB-QL) to select a teacher from the database. EJB-QL resembles a restricted SQL, but adds direct support for relations and function arguments.

The findAll method finds all the courses in the database. The schema name is from the abstract-schema-name. The field is the cmp-field name. These names are not necessarily the same as the SQL table and columns. If the deployment changes the SQL table and columns, the EJB-QL still refers to the same abstract names.

SELECT o FROM courses o

findByInstructor uses the method's argument to select the course by the instructor. Since the instructor is an argument, it needs to be specified in the query. The EJB-QL syntax for a function argument is , where is the argument number, starting at 1.

SELECT o FROM courses o WHERE o.instructor = ?1

The example servlet uses three finders: the usual , the collection-valued , and the single-argument . Find methods always return the local interface, either as a single value or a collection. The pairing of the local object and its home interface matches the home interfaces's role as a factory pattern. (Remote home finders always return the remote interface or a collection of the remote interface.) If applications need to return other values, including other local beans, they need to use ejbSelect methods.

The method returns all the courses in the database as a collection. The client code just iterates through the courses.

... Collection c = home.findAll(); Iterator iter = c.iterator(); while (iter.hasNext()) { Course course = (Course); out.println(course.getCourseId() + " is taught by " + course.getInstructor() + "<br>"); } ... Potions is taught by Severus Snape Transfiguration is taught by Minerva McGonagall Defense Against the Dark Arts is taught by Remus Lupin

returns the course taught by an instructor. Single-valued finder methods expect to return exactly one bean. If there are no matching courses, throws an . If more than one courses are taught by the instructor, it will throw a .

... Course course = home.findByInstructor(teacher); out.println(course.getCourseId() + " is taught by " + course.getInstructor() + "<br>"); ... Potions is taught by Severus Snape

The interface defines the and methods. As with , the implementation bean does not implement the method. Resin-CMP will generate that code automatically.

The other classes are included for completeness, but follow the same idea as in the persistent field tutorial.

package example.cmp.find; import java.util.*; import javax.ejb.*; public interface CourseHome extends EJBLocalHome { Course findByPrimaryKey(String name) throws FinderException; Collection findAll() throws FinderException; Course findByInstructor(String instructor) throws FinderException; } package example.cmp.find; import javax.ejb.*; public interface Course extends EJBLocalObject { String getCourseId(); String getInstructor(); } package example.cmp.find; abstract public class CourseBean extends com.caucho.ejb.AbstractEntityBean { abstract public String getCourseId(); abstract public String getInstructor(); }

The main new feature of the deployment descriptor is the /> section. Although we've specified the SQL mapping using the sql-table and sql-column elements, the queries use the abstract names.

<ejb-jar> <enterprise-beans> <entity> <ejb-name>find_CourseBean</ejb-name> <local-home>example.cmp.find.CourseHome</local-home> <local>example.cmp.find.Course</local> <ejb-class>example.cmp.find.CourseBean</ejb-class> <persistence-type>Container</persistence-type> <reentrant>True</reentrant> <abstract-schema-name>courses</abstract-schema-name> <sql-table>find_courses</sql-table> <primkey-field>name</primkey-field> <prim-key-class>String</prim-key-class> <cmp-field><field-name>courseId</field-name></cmp-field> <cmp-field> <field-name>instructor</field-name> <sql-column>teacher</sql-column> </cmp-field> <query> <query-method> <method-name>findAll</method-name> </query-method> <ejb-ql>SELECT o FROM courses o</ejb-ql> </query> <query> <query-method> <method-name>findByInstructor</method-name> </query-method> <ejb-ql>SELECT o FROM courses o WHERE o.instructor=?1</ejb-ql> </query> </entity> </enterprise-beans> </ejb-jar> TagMeaning queryContains the query information for a find method. query-methodThe method descriptor method-nameThe method name ejb-qlThe query for the find method.