Finding Entity Beans with EJB-QL

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:

Add a find method to the home interface.
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.

Demo

Demo

Database Schema

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

courses.sql

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

EJB-QL

The findAll and findByInstructor 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 courses is from the abstract-schema-name. The field courseId 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 ?n, where n is the argument number, starting at 1.

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

Client Servlet

The example servlet uses three finders: the usual findByPrimaryKey, the collection-valued findAll, and the single-argument findByInstructor. 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 findAll method returns all the courses in the database as a collection. The client code just iterates through the courses.

Finding all Courses

... Collection c = home.findAll(); Iterator iter = c.iterator(); while (iter.hasNext()) { Course course = (Course) iter.next(); 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

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

Finding a Course by its Instructor

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

Potions is taught by Severus Snape

Entity Bean Classes

The CourseHome interface defines the findAll and findByInstructor methods. As with findByPrimaryKey, 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.

CourseHome.java

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; }

Course.java

package example.cmp.find; import javax.ejb.*; public interface Course extends EJBLocalObject { String getCourseId(); String getInstructor(); }

CourseBean.java

package example.cmp.find; abstract public class CourseBean extends com.caucho.ejb.AbstractEntityBean { abstract public String getCourseId(); abstract public String getInstructor(); }

Deployment Descriptor

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

WEB-INF/cmp-find.ejb

<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>

Tag Meaning

query Contains the query information for a find method.

query-method The method descriptor

method-name The method name

ejb-ql The query for the find method.

Try the Tutorial

Basic CMP

Tutorials

Creating and Removing