Finding Entity Beans with EJB-QL
Resin 3.0

Web Applications
Servlets and Filters
Admin (JMX)
EJB 3.0

User's Guide

Basic CMP
Find with EJB-QL
Creating and Removing
1-n Relationship
ejbSelect EJB-QL
1-1 Relationship
n-m Relationship
Map/Compound PK
Basic CMP
Creating and Removing

Find this tutorial in: /usr/local/resin/webapps/resin-doc/cmp/tutorial/cmp-find
Try the Tutorial

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.

  1. Demo
  2. Database Schema
  3. EJB-QL
  4. Client Servlet
  5. Entity Bean Classes
  6. Deployment Descriptor

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.


Database Schema

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

  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.
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();

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.





      <ejb-ql>SELECT o FROM courses o</ejb-ql>

      <ejb-ql>SELECT o FROM courses o WHERE o.instructor=?1</ejb-ql>

queryContains the query information for a find method.
query-methodThe method descriptor
method-nameThe method name
ejb-qlThe query for the find method.

Try the Tutorial

Basic CMP
Creating and Removing
Copyright © 1998-2005 Caucho Technology, Inc. All rights reserved.
Resin® is a registered trademark, and HardCoretm and Quercustm are trademarks of Caucho Technology, Inc.