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
  • One-to-many
  • Many-to-many

 

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 app, a storagebin table might have a one-to-one relationship with a widget table. This app would model a physical warehouse in which each storage bin contains one type of widget and each widget resides in one storage bin.

Figure 5-1 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 figures in this chapter denote a primary key with PK and a foreign key with FK.)

Figure 5-1 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 app, 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 app 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 j2eetutorial/examples/src/ejb/storagebin directory. To compile the code, go to the j2eetutorial/examples directory and type ant storagebin. A sample StorageBinApp.ear file is in the j2eetutorial/examples/ears directory.

The StorageBinBean and WidgetBean classes illustrate the one-to-one relationship of the storagebin and widget tables. The StorageBinBean 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 StorageBinBean 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 in which storage bin a widget resides, the StorageBinClient program calls the findByWidgetId method: 

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

Running the StorageBinEJB Example

  1. Create the storagebin database table.
    1. Go to the j2eetutorial/examples directory.
    2. Type ant create-storagebin-table.
  2. Deploy the StorageBinApp.ear file (located in the j2eetutorial/examples/ears directory).
  3. Run the client.
    1. Go to the j2eetutorial/examples/ears directory.
    2. Set the APPCPATH environment variable to StorageBinAppClient.jar.
    3. Type the following command on a single line: 
            runclient -client StorageBinApp.ear -name 
         StorageBinClient    -textauth
    4. At the login prompts, enter guest for the user name and guest123 for the password.

 

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 apps. For example, an app 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, 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. In an online shopping app, for example, each order submitted by a customer can have multiple line items. The app stores the information in the database tables shown by Figure 5-2.

Figure 5-2 One-to-Many Relationship: Order and Line Items

Not only does a line item belong to an order, it also 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 j2eetutorial/examples/src/ejb/order directory. To compile the code, go to the j2eetutorial/examples directory and type ant order. A sample OrderApp.ear file is in the j2eetutorial/examples/ears directory.

The LineItem and OrderBean classes show how to implement a one-to-many relationship with a helper class (LineItem). 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 OrderBean class contains an ArrayList variable named lineItems. Each element in the lineItems variable is a LineItem object. The lineItems variable is passed to the OrderBean 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 OrderBean class also access both database tables. The ejbRemove method, for example, not only deletes 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 product. This method queries the lineitems table for all rows with a specific productId. The method returns a Collection of Order 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);
}

Running the OrderEJB Example

  1. Create the orders database table:.
    1. Go to the j2eetutorial/examples directory.
    2. Type ant create-order-table.
  2. Deploy the OrderApp.ear file (located in the j2eetutorial/examples/ears directory).
  3. Run the client.
    1. Go to the j2eetutorial/examples/ears directory.
    2. Set the APPCPATH environment variable to OrderAppClient.jar.
    3. Type the following command on a single line: 
            runclient -client OrderApp.ear -name OrderClient 
      -textauth
    4. At the login prompts, enter guest for the user name and guest123 for the password.

An Entity Bean for the Child Table

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

  • The information in the child table is not dependent on the parent table.
  • The business entity of the child table could exist without that of the parent table.
  • The child table might be accessed by another app that does not access the parent table.

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 app. In the database, each row in the salesrep table (parent) matches multiple rows in the customer table (child). Figure 5-3 illustrates this relationship.

Figure 5-3 One-to-Many Relationship: Sales Representative and Customers

The SalesRepBean and CustomerBean entity bean classes implement the one-to-many relationship of the sales and customer tables.

The source code for this example is in the j2eetutorial/examples/src/ejb/salesrep directory. To compile the code, go to the j2eetutorial/examples directory and type ant salesrep. A sample SalesRepApp.ear file is in the j2eetutorial/examples/ears directory.

The SalesRepBean 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 SalesRepBean class must keep the variable up to date.

The SalesRepBean 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 SalesRepBean class instantiates the customerIds variable in setEntityContext: 

public void setEntityContext(EntityContext context) {

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