The J2EETM Tutorial
Home
TOC 		PREV TOC NEXT Search
Feedback

Mapping Table Relationships For Bean-Managed Persistence

In a relational database, tables can be related by common columns. The relationships between the tables affect the design of their corresponding entity beans. The entity beans discussed in this section are backed up by tables with the following types of relationships:

One-to-One Relationships

In a one-to-one relationship, each row in a table is related to a single row in another table. For example, in a warehouse application a storagebin table might have a one-to-one relationship with a widget table. This application would model a physical warehouse where each storage bin contains one type of widget and each widget resides in one storage bin.

Figure 8 illustrates the storagebin and widget tables. Because the storagebinid uniquely identifies a row in the storagebin table, it is that table's primary key. The widgetid is the primary key of the widget table. The two tables are related because the widgetid is also a column in the storagebin table. By referring to the primary key of the widget table, the widgetid in the storagebin table identifies which widget resides in a particular storage bin in the warehouse. Because the widgetid of the storagebin table refers to the primary key of another table, it is called a foreign key. (The figure denotes a primary key with PK and a foreign key with FK.)

Figure 8 One-to-One Table Relationship

A dependent (child) table includes a foreign key that matches the primary key of the referenced (parent) table. The values of the foreign keys in the storagebin (child) table depend on the primary keys in the widget (parent) table. For example, if the storagebin table has a row with a widgetid of 344, then the widget table should also have a row whose widgetid is 344.

When designing a database application, you may choose to enforce the dependency between the parent and child tables. There are two ways to enforce such a dependency: by defining a referential constraint in the database or by performing checks in the application code. The storagebin table has a referential constraint named fk_widgetid: 

CREATE TABLE storagebin
   (storagebinid VARCHAR(3) 
    CONSTRAINT pk_storagebin PRIMARY KEY,
    widgetid VARCHAR(3),
    quantity INTEGER,
    CONSTRAINT fk_widgetid
    FOREIGN KEY (widgetid)
     REFERENCES widget(widgetid));

The source code for the following example is in the examples/src/ejb/storagebin directory. To compile the code, go to the examples/src directory and type ant storagebin.

The StorageBinEJB and WidgetEJB classes illustrate the one-to-one relationship of the storagebin and widget tables. The StorageEJB class contains variables for each column in the storagebin table, including the foreign key, widgetId: 

private String storageBinId;
private String widgetId;
private int quantity;
The ejbFindByWidgetId method of the StorageEJB class returns the storageBinId that matches a given widgetId:
public String ejbFindByWidgetId(String widgetId)
   throws FinderException {

   String storageBinId;

   try {
      storageBinId = selectByWidgetId(widgetId);
    } catch (Exception ex) {
        throw new EJBException("ejbFindByWidgetId: " + 
           ex.getMessage());
    }

   if (storageBinId == null) {
      throw new ObjectNotFoundException
         ("Row for widgetId " + widgetId + " not found.");
   }
   else {
      return storageBinId;
   }
}

The ejbFindByWidgetId method locates the widgetId by querying the database in the selectByWidgetId method: 

private String selectByWidgetId(String widgetId) 
   throws SQLException {

   String storageBinId;

   String selectStatement =
         "select storagebinid " +
         "from storagebin where widgetid = ? ";
   PreparedStatement prepStmt =
         con.prepareStatement(selectStatement);
   prepStmt.setString(1, widgetId);

   ResultSet rs = prepStmt.executeQuery();

   if (rs.next()) {
      storageBinId = rs.getString(1);
   }
   else {
      storageBinId = null;
   }

   prepStmt.close();
   return storageBinId;
}

To find out which storage bin a widget resides in, the StorageBinClient program calls the findByWidgetId method: 

String widgetId = "777";
StorageBin storageBin = 
   storageBinHome.findByWidgetId(widgetId);
String storageBinId = (String)storageBin.getPrimaryKey();
int quantity = storageBin.getQuantity();

Tips for Running the StorageBinEJB Example

  1. Create the database tables by typing ant create-storagebin-table.
  2. Specify bean-managed persistence for both entity beans.
  3. For the business methods, specify the Required transaction attribute.
  4. For the StorageBinBean, specify jdbc/StorageBinDB as the coded name for the resource reference.
  5. For the WidgetBean, specify jdbc/WidgetDB as the coded name for the resource reference.
  6. For the StorageBinClient, add two enterprise bean references: ejb/SimpleStorageBin and ejb/SimpleWidget
  7. Use the JNDI names listed in the following table.

    Table 4 JNDI Names for the StorageBinEJB Example 
    Component or
    Reference Name
         		JNDI Name
    StorageBinBean
         		MyStorageBin
    WidgetBean
         		MyWidget
    jdbc/StorageBinDB
         		jdbc/Cloudscape
    jdbc/WidgetDB
         		jdbc/Cloudscape
    ejb/SimpleStorageBin
         		MyStorageBin
    ejb/SimpleWidget
         		MyWidget

One-to-Many Relationships

If the primary key in a parent table matches multiple foreign keys in a child table, then the relationship is one-to-many. This relationship is common in database applications. For example, an application for a sports league might access a team table and a player table. Each team has multiple players and each player belongs to a single team. Every row in the child table (player), has a foreign key identifying the player's team. This foreign key matches the team table's primary key.

The sections that follow describe how you might implement one-to-many relationships in entity beans. When designing such entity beans, you must decide whether both tables are represented by entity beans, or just one.

A Helper Class for the Child Table

Not every database table needs to be mapped to an entity bean. If a database table doesn't represent a business entity, or if it stores information that is contained in another entity, then the table should be represented with a helper class. For example, in an online shopping application each order submitted by a customer can have multiple line items. The application stores the information in the database tables shown by the following figure.

Figure 9 One-to-Many Relationship: Order and Line Items

Not only does a line item belong to an order, it does not exist without the order. Therefore, the lineitems table should be represented with a helper class and not with an entity bean. Using a helper class in this case is not required, but doing so might improve performance because a helper class uses fewer system resources than an entity bean.

The source code for the following example is in the examples/src/ejb/order directory. To compile the code, go to the examples/src directory and type ant order.

The LineItem and OrderEJB classes show how to implement a one-to-many relationship with a helper class (LineItem) and an entity bean (OrderEJB). The instance variables in the LineItem class correspond to the columns in the lineitems table. The itemNo variable matches the primary key for the lineitems table and the orderId variable represents the table's foreign key. Here is the source code for the LineItem class: 

public class LineItem implements java.io.Serializable {

   String productId;
   int quantity;
   double unitPrice;
   int itemNo;
   String orderId;
   

   public LineItem(String productId, int quantity,
     double unitPrice, int itemNo, String orderId) {

      this.productId = productId;
      this.quantity = quantity;
      this.unitPrice = unitPrice;
      this.itemNo = itemNo;
      this.orderId = orderId;
   }

   public String getProductId() {
      return productId;
   }

   public int getQuantity() {
      return quantity;
   }

   public double getUnitPrice() {
      return unitPrice;
   }

   public int getItemNo() {
      return itemNo;
   }

   public String getOrderId() {
      return orderId;
   }

}

The OrderEJB class contains an ArrayList variable named lineItems. Each element in the lineItems variable is a LineItem object. The lineItems variable is passed to the OrderEJB class in the ejbCreate method. For every LineItem object in the lineItems variable, the ejbCreate method inserts a row into the lineitems table. It also inserts a single row into the orders table. The code for the ejbCreate method follows: 

public String ejbCreate(String orderId, String customerId,
    String status, double totalPrice, ArrayList lineItems)
    throws CreateException {

    try {
       insertOrder(orderId, customerId, status, totalPrice);
       for (int i = 0; i < lineItems.size(); i++) {
          LineItem item = (LineItem)lineItems.get(i);
          insertItem(item);
       }
    } catch (Exception ex) {
        throw new EJBException("ejbCreate: " + 
           ex.getMessage());
    }

    this.orderId = orderId;
    this.customerId = customerId;
    this.status = status;
    this.totalPrice = totalPrice;
    this.lineItems = lineItems ;

    return orderId;
}

The OrderClient program creates and loads an ArrayList of LineItem objects. The program passes this ArrayList to the entity bean when it invokes the create method: 

ArrayList lineItems = new ArrayList();
lineItems.add(new LineItem("p23", 13, 12.00, 1, "123"));
lineItems.add(new LineItem("p67", 47, 89.00, 2, "123"));
lineItems.add(new LineItem("p11", 28, 41.00, 3, "123"));
. . .
Order duke = home.create("123", "c44", "open",
   totalItems(lineItems), lineItems);

Other methods in the OrderEJB class also access both database tables. The ejbRemove method, for example, deletes not only a row from the orders table, but also deletes all corresponding rows in the lineitems table. The ejbLoad and ejbStore methods synchronize the state of an OrderEJB instance, including the lineItems ArrayList, with the orders and lineitems tables.

The ejbFindByProductId method enables clients to locate all orders that have a particular line item. This method queries the lineitems table for all rows with a particular productId. The method returns a Collection of productId String objects. The OrderClient program iterates through the Collection and prints the primary key of each order: 

Collection c = home.findByProductId("p67");
Iterator i=c.iterator();
while (i.hasNext()) {
   Order order = (Order)i.next();
   String id = (String)order.getPrimaryKey();
   System.out.println(id);
 }

Tips for Running the OrderEJB Example:

  1. Create the database tables by typing ant create-order-table.
  2. Add the LineItem.class file to the EJB JAR file that contains the OrderEJB.class file.
  3. Specify jdbc/OrderDB as the coded name for the resource reference.
  4. For the transaction attributes of the business methods, specify Required. This attribute value causes the container to call ejbLoad before (and ejbStore after) each business method invocation. These calls will synchronize the bean's state with the database tables.
  5. For the OrderClient, add the ejb/SimpleOrder enterprise bean reference.
  6. Use the JNDI names listed in the following table.

    Table 5 JNDI Names for the OrderEJB Example 
    Component or
    Reference Name
         		JNDI Name
    OrderBean
         		MyOrder
    jdbc/OrderDB
         		jdbc/Cloudscape
    ejb/SimpleOrder
         		MyOrder

An Entity Bean for the Child Table

You should consider building an entity bean for a child table under the following conditions:

These conditions exist in the following scenario. Suppose that each sales representative in a company has multiple customers and that each customer has only one sales representative. The company tracks its sales force with a database application. In the database, each row in the salesrep table (parent) matches multiple rows in the customer table (child). Figure 10 illustrates this relationship.

Figure 10 One-to-Many Relationship: Sales Representative and Customers

The SalesRepEJB and CustomerEJB entity bean classes implement the one-to-many relationship of the of sales and customer tables. (The source code for these classes is in the examples/src/ejb/salesrep directory.)

The SalesRepEJB class contains a variable named customerIds, which is an ArrayList of String elements. These String elements identify which customers belong to the sales representative. Because the customerIds variable reflects this relationship, the SalesRepEJB class must keep the variable up to date.

The SalesRepEJB class instantiates the customerIds variable in the setEntityContext method, not in ejbCreate. The container invokes setEntityContext just once-when it creates the bean instance-ensuring that customerIds is instantiated just once. Because the same bean instance can assume different identities during its life cycle, instantiating customerIds in ejbCreate might cause multiple and unnecessary instantiations. Therefore, the SalesRepEJB class instantiates the customerIds variable in setEntityContext: 

public void setEntityContext(EntityContext context) {

   this.context = context;
   customerIds = new ArrayList();

   try {
      makeConnection();
      Context initial = new InitialContext();
      Object objref = initial.lookup("java:comp/env/ejb/Customer");

      customerHome = 
        (CustomerHome)PortableRemoteObject.narrow(objref,
         CustomerHome.class);
   } catch (Exception ex) {
       throw new EJBException("setEntityContext: " +
          ex.getMessage());
   }
}

Invoked by the ejbLoad method, loadEnrollerIds is a private method that refreshes the customerIds variable. There are two approaches when coding a method such as loadCustomerIds: fetch the identifiers from the customer database table or get them from the Customer entity bean. Fetching the identifiers from the database might be faster, but exposes the SalesRepEJB code to the Customer bean's underlying database table. In the future, if you were to change the Customer bean's table (or move the bean to a different J2EE server), then you might need to change the SalesRepEJB code. But if the SalesRepEJB gets the identifiers from the Customer entity bean, no coding changes would be required. The two approaches present a trade-off: performance versus flexibility. The SalesRepEJB example opts for flexibility, loading the customerIds variable by calling the findSalesRep and getPrimaryKey methods of the Customer bean. Here is the code for the loadCustomerIds method: 

private void loadCustomerIds() {

   customerIds.clear();

   try {
      Collection c = customerHome.findBySalesRep(salesRepId);
      Iterator i=c.iterator();

      while (i.hasNext()) {
         Customer customer = (Customer)i.next();
         String id = (String)customer.getPrimaryKey();
         customerIds.add(id);
      }
   
   } catch (Exception ex) {
       throw new EJBException("Exception in loadCustomerIds: " +
          ex.getMessage());
    }
}

If a customer's sales representative changes, the client program updates the database by calling the setSalesRepId method of the CustomerEJB class. The next time a business method of the SalesRepEJB is called, the ejbLoad method invokes loadCustomerIds, which refreshes the customerIds variable. (To ensure that ejbLoad is invoked before each business method, set the transaction attributes of the business methods to Required.) For example, the SalesRepClient program changes the salesRepId for a customer named Mary Jackson: 

Customer mary = customerHome.findByPrimaryKey("987");
mary.setSalesRepId("543");

The salesRepId 543 identifies a sales representative named Janice Martin. To list all of Janice's customers, the SalesRepClient program invokes the getCustomerIds method, iterates through the ArrayList of identifiers, and locates each Customer bean by calling the Customer bean's findByPrimaryKey method: 

SalesRep janice = salesHome.findByPrimaryKey("543");
ArrayList a = janice.getCustomerIds();
i = a.iterator();

while (i.hasNext()) {
   String customerId = (String)i.next();
   Customer customer = customerHome.findByPrimaryKey(customerId);
   String name = customer.getName();
   System.out.println(customerId + ": " + name);
}

Tips for Running the SalesRepEJB Example:

  1. Create the database tables by typing ant create-salesrep-table.
  2. For both beans specify jdbc/SalesDB as the coded name for the resource reference.
  3. For both beans, set the transaction attributes of the business methods to Required.
  4. For the SalesRepEJB bean, add the ejb/Customer enterprise bean reference.
  5. For the SalesRepClient, add these enterprise bean references: ejb/SimpleSalesRep and ejb/SimpleCustomer.
  6. Specify the JNDI names listed in the following table.

    Table 6 JNDI Names for the SalesRepEJB Example 
    Component or
    Reference Name
         		JNDI Name
    SalesRepBean
         		MySalesRep
    CustomerBean
         		MyCustomer
    jdbc/SalesDB
         		jdbc/Cloudscape
    ejb/Customer
         		MyCustomer
    ejb/SimpleSalesRep
         		MySalesRep
    ejb/SimpleCustomer
         		MyCustomer

Many-to-Many Relationships

In a many-to-many relationship, each entity may be related to multiple occurrences of the other entity. For example, a college course has many students and each student may take several courses. In a database, this relationship is represented by a cross reference table containing the foreign keys. In Figure 11, the cross reference table is the enrollment table. (PK indicates a primary key and FK a foreign key.)

Figure 11 Many-to-Many Relationship: Students and Courses

These tables are accessed by the StudentEJB, CourseEJB, and EnrollerEJB classes. The source code for these classes is in the examples/src/ejb/enroller directory. To compile the code, go to the examples/src directory and type ant enroller.

The StudentEJB and CourseEJB classes are complementary. Each class contains an ArrayList of foreign keys. The StudentEJB class, for example, contains an ArrayList named courseIds, which identifies the courses the student is enrolled in. The ejbLoad method adds elements to the courseIds ArrayList by calling loadCourseIds, a private method. The loadCourseIds method gets the course identifiers from the Enroller session bean. The source code for the loadCourseIds method follows: 

private void loadCourseIds() {

   courseIds.clear();

   try {
      Enroller enroller = enrollerHome.create();
      ArrayList a = enroller.getCourseIds(studentId);
      courseIds.addAll(a);

   } catch (Exception ex) {
       throw new EJBException("Exception in loadCourseIds: " +
          ex.getMessage());
    }
}

Invoked by the loadCourseIds method, the getCourses method of the EnrollerEJB class queries the enrollment table: 

select courseid from enrollment
where studentid = ?

Only the EnrollerEJB class accesses the enrollment table. Therefore, the EnrollerEJB class manages the student-course relationship represented in the enrollment table. If a student enrolls in a course, for example, the client calls the enroll business method, which inserts a row: 

insert into enrollment
values (studentid, courseid)

If a student drops a course, the unEnroll method deletes a row: 

delete from enrollment
where studentid = ? and courseid = ?

And if a student leaves the school, the deleteStudent method deletes all rows in the table for that student: 

delete from enrollment
where student = ?

The EnrollerEJB class does not delete the matching row from the student table. That action is performed by the ejbRemove method of the StudentEJB class. To ensure that both deletes are executed as a single operation, they should belong to the same transaction. SeeTransactions for more information.

Tips for running the EnrollerEJB example:

  1. Create the database tables by typing ant create-enroller-table.
  2. For all three beans, specify jdbc/CollegeDB as the coded name for the resource reference.
  3. For all three beans, specify container-managed transactions.
  4. For the transaction attributes of the business methods, specify Required.
  5. For the StudentEJB and CourseEJB entity beans, specify the ejb/Enroller enterprise bean reference.
  6. For the EnrollerClient, specify an enterprise reference for each bean: ejb/SimpleCourse, ejb/SimpleStudent, ejb/SimpleEnroller.
  7. Use the JNDI names listed in the following table.

    Table 7 JNDI Names for the EnrollerEJB Example 
    Component or
    Reference Name
         		JNDI Name
    EnrollerBean
         		MyEnroller
    StudentBean
    		MyStudent
    CourseBean
         		MyCourse
    jdbc/CollegeDB
         		jdbc/Cloudscape
    ejb/SimpleEnroller
         		MyEnroller
    ejb/SimpleStudent
         		MyStudent
    ejb/SimpleCourse
         		MyCourse
Home
TOC 		PREV TOC NEXT Search
Feedback