Download PDF Home History |  | API Search Feedback FAQ

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

> Figure 26-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 26-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 application, you can 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 this directory:

>     <INSTALL>/j2eetutorial14/examples/ejb/storagebin/src/

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

>        makeConnection();
>        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();
>        releaseConnection();
>        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 StorageBinBean Example

> 1. Create the `storagebin` database table.
> 1. In a terminal window, go to this directory:

> `<``INSTALL``>/j2eetutorial14/examples/ejb/storagebin/`

> 2. Type this command:

> `asant create-db_common`

> 2. In `deploytool`, deploy the `StorageBinApp.ear` file, which is in this directory:

> `<``INSTALL``>/j2eetutorial14/examples/ejb/provided-ears/`

> 3. Run the client.
> 1. In a terminal window, go to this directory:

> `<``INSTALL``>/j2eetutorial14/examples/ejb/storagebin/`

> 2. Type the following command on a single line:

> `appclient -client StorageBinAppClient.jar`

> 3. The client should display the following:

> `...
> 777 388 500 1.0 Duct Tape
> ...`

> ### 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 only 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 you should use a helper class to represent the table. In an online shopping application, for example, each order submitted by a customer can have multiple line items. The application stores the information in the database tables shown by Figure 26-2.

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

> Not only does a line item belong to an order, but 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 does an entity bean.

> The source code for the following example is in this directory:

>     <INSTALL>/j2eetutorial14/examples/ejb/order/src/

> The `LineItem` and `OrderBean` classes show how to implement a one-to-many relationship using 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 `OrderBean` 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 OrderBean Example

> 1. Create the `order` database table.
> 1. In a terminal window, go to this directory:

> `<``INSTALL``>/j2eetutorial14/examples/ejb/order/`

> 2. Type this command:

> `asant create-db_common`

> 2. In `deploytool`, deploy the `OrderBean.ear` file, which is in this directory:

> `<``INSTALL``>/j2eetutorial14/examples/ejb/provided-ears/`

> 3. Run the client.
> 1. In a terminal window, go to this directory:

> `<``INSTALL``>/j2eetutorial14/examples/ejb/order/`

> 2. Type the following command on a single line:

> `appclient -client OrderBeanClient.jar`

> 3. The client should display the following lines:

> `...
> 123 1 p23 12.0
> 123 2 p67 89.0
> 123 3 p11 41.0
> 123
> 456`

> #### 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 application 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 using a database application. In the database, each row in the `salesrep` table (parent) matches multiple rows in the `customer` table (child). Figure 26-3 illustrates this relationship.

> Figure 26-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 this directory:

>     <INSTALL>/j2eetutorial14/examples/ejb/salesrep/src/

> 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 and not in `ejbCreate`. The container invokes `setEntityContext` only once--when it creates the bean instance--thereby ensuring that `customerIds` is instantiated only 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();

>       try {
>         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, `loadCustomerIds` is a private method that refreshes the `customerIds` variable. There are two approaches to coding a method such as `loadCustomerIds`: fetch the identifiers from the `customer` database table, or get them from the `CustomerBean` entity bean. Fetching the identifiers from the database might be faster, but it exposes the code in the `SalesRepBean` class to the `CustomerBean` bean's underlying database table. In the future, if you were to change the `CustomerBean` bean's table (or move the bean to a different Application Server), you might need to change the `SalesRepBean` code. But if the `SalesRepBean` class gets the identifiers from the `CustomerBean` entity bean, no coding changes would be required. The two approaches present a trade-off: performance versus flexibility. The `SalesRepBean` example opts for flexibility, loading the `customerIds` variable by calling the `findBySalesRep` and `getPrimaryKey` methods of `CustomerBean`. 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 `CustomerBean` class. The next time a business method of the `SalesRepBean` class 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 as follows:

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

> The `salesRepId` value 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 `CustomerBean` entity bean by calling its `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);
>     }

> #### Running the SalesRepBean Example

> 1. Create the `salesrep` database table.
> 1. In a terminal window, go to this directory:

> `<``INSTALL``>/j2eetutorial14/examples/ejb/salesrep/`

> 2. Type this command:

> `asant create-db_common`

> 2. In `deploytool`, deploy the `SalesRepApp.ear` file, which is in this directory:

> `<``INSTALL``>/j2eetutorial14/examples/ejb/provided-ears/`

> 3. Run the client.
> 1. In a terminal window, go to this directory:

> `<``INSTALL``>/j2eetutorial14/examples/ejb/salesrep/`

> 2. Type the following command on a single line:

> `appclient -client SalesRepAppClient.jar`

> 3. The client should display the following lines:

> `...
> customerId = 221
> customerId = 388
> customerId = 456
> customerId = 844
> 987: Mary Jackson
> 221: Alice Smith
> 388: Bill Williamson
> 456: Joe Smith
> 844: Buzz Murphy
> ...`

> ### Many-to-Many Relationships

> In a many-to-many relationship, each entity can 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 26-4, the cross-reference table is the `enrollment` table. These tables are accessed by the `StudentBean`, `CourseBean`, and `EnrollerBean` classes.

> Figure 26-4 Many-to-Many Relationship: Students and Courses

> The source code for this example is in this directory:

>     <INSTALL>/j2eetutorial14/examples/ejb/enroller/src/

> The `StudentBean` and `CourseBean` classes are complementary. Each class contains an `ArrayList` of foreign keys. The `StudentBean` class contains an `ArrayList` named `courseIds`, which identifies the courses the student is enrolled in. Similarly, the `CourseBean` class contains an `ArrayList` named `studentIds`.

> The `ejbLoad` method of the `StudentBean` class adds elements to the `courseIds` `ArrayList` by calling `loadCourseIds`, a private method. The `loadCourseIds` method gets the course identifiers from the `EnrollerBean` 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 `getCourseIds` method of the `EnrollerBean` class queries the `enrollment` table:

>     select courseid from enrollment
>     where studentid = ?

> Only the `EnrollerBean` class accesses the `enrollment` table. Therefore, the `EnrollerBean` 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 `EnrollerBean` class does not delete the matching row from the `student` table. That action is performed by the `ejbRemove` method of the `StudentBean` class. To ensure that both deletes are executed as a single operation, you must ensure that they belong to the same transaction. See Chapter 30 for more information.

> #### Running the EnrollerBean Example

> 1. Create the `enroller` database table.
> 1. In a terminal window, go to this directory:

> `<``INSTALL``>/j2eetutorial14/examples/ejb/enroller/`

> 2. Type this command:

> `asant create-db_common`

> 2. In `deploytool`, deploy the `EnrollerApp.ear` file, which is in this directory:

> `<``INSTALL``>/j2eetutorial14/examples/ejb/provided-ears/`

> 3. Run the client.
> 1. In a terminal window, go to this directory:

> `<``INSTALL``>/j2eetutorial14/examples/ejb/enroller/`

> 2. Type the following command on a single line:

> `appclient -client EnrollerAppClient.jar`

> 3. The client should display the following lines:

> `...
> Denise Smith:
> 220 Power J2EE Programming
> 333 XML Made Easy
> 777 An Introduction to Java Programming
> An Introduction to Java Programming:
> 823 Denise Smith
> 456 Joe Smith
> 388 Elizabeth Willis
> ...`

Download PDF Home History |  | API Search Feedback FAQ

All of the material in The J2EE(TM) 1.4 Tutorial is copyright-protected and may not be published in other works without express written permission from Sun Microsystems.