Mapping CMRs to SQL Databases

This article describes the mechanisms used by JBoss CMP to map Container Managed Relationships (CMR) defined in the EJB tier to relational databases using SQL. Familiarity is assumed with the operation of relationships from the EJB perspective as described in the Container Managed Realtionships article.

Relational modeling supports four main types of relationships:

1. Identifying which is a One-to-One or One-to-Many relationship where the primary key from one entity is part of the primary key of another identified entity. This is a form of aggregation in that tuples of the identified entity cannot exist without a related tuple in the other. It also couples the identities of the two entities. In SQL this relationship manifests as a FOREIGN KEY constraint where the foreign key columns form part of the PRIMARY KEY of the identified table.
    
2. Mandatory which is a One-to-One or One-to-Many relationship where a secondary entity must always be associated with a primary. Like an identifying relationship, this is a form of aggregation; however, the two entities have separate identities (primary keys) allowing the secondary entity to potentially be associated with different primaries. In SQL this relationship manifests as a FOREIGN KEY constraint where the foreign key columns have a NOT NULL constraint on them but are not part of the primary key.
    
3. Optional which is a One-to-One or One-To-Many relationship where the secondary entity need not be associated with a primary. In SQL this relationship manifests as a FOREIGN KEY constriaint where the foreign key columns are nullable.
    
4. Relation-Table which a where two entities are related using an intermediate table. This is typically used to support Many-to-Many relationships, but can be used to support other types although this is less efficient than the other types of relationship described above. In SQL this manifests as an additional table with identifying relationships to the two related entities; the columns in this table are the union of all primary key columns from the related tables and the primary key includes all columns. The relationship between the two entities is defined by the presence or absense of a of row in the relation table. This can be considered a One-to-Many-to-One relationship where the relation table plays the middle identified role.

In the database all relationships are implicitly navigable in both directions. For One-to-Many or Many-to-One relationships, which entity is the primary and which is the secondary is determined by the multiplicity - only the secondary can be on the Many end.

For One-to-One relationships, either can be the primary and the choice is typically determined by physical concerns; indeed although not truly normalized, physical concerns often justify using two One-to-One relationships with each end as the primary.

Issues Mapping CMRs and Database Relationships

There is not a simple mapping between CMRs and the relationships in a relational database.

One area of complexity is that EJB does not specifically deal with the concept of aggregation (except partially with cascade-delete). The specification allows for the initialization of fields in ejbCreate() and for realtionships in ejbPostCreate(), but does not address how that initialization maps to the Identifying and Mandatory relationships found in databases. This leaves it to container vendors to determine behaviour.

The specification also does not address the potential overlap between cmp-fields and cmr-fields if they happen to map to the same physical meme (such as the same column). The behaviour in such circumstances is undefined, although in reality most vendors use mappings of this nature to support Identifying and Mandatory relationships in a similar manner.

Finally, EJB defines the notion of navigability with realtionships being Uni- or Bi- Directional. CMRs are made navigable by declaring a cmr-field and defining an accessor method. In a relational database, however, relationships are always navigable in both directions. The result of this is that different definitions at the EJB level may be supported by the same database configuration; for example, a Optional foreign key relationship between Order and LineItem could be used to support a Uni-Directional One-to-Many CMR from Order to LineItem, a Uni-Directional Many-to-One CMR from LineItem to Order, a Bi-Directional One-To-Many CMR between Order and LineItem, or even a Bi-Directional One-to-One CMR. Indeed, a Relation-Table mapping can support any CMR, although not necessarily as efficiently.

As a result, there are many different mappings between EJB relationships and database relationships. The following sections describe the mechanisms supported in JBoss.

Optional Relationships

Optional relationships are the easiest to establish from both the EJB and database perspective. They are the most portable relationship between vendors as they do not rely on any overlapping field definitions; however, they provide the least database-level checking in the validity of the data and are often unpopular with DBAs.

From the database perspective, an optional relationship is a foreign key constraint with nullable columns. Example SQL for the Order-LineItem relationship would be:

CREATE TABLE ORDER_DATA (
    ORDER_ID    INTEGER NOT NULL,
    PRIMARY KEY (ORDER_ID)
)
 
CREATE TABLE LINEITEM (
    ITEM_ID     INTEGER NOT NULL,
    FK_ORDER_ID INTEGER,
    PRIMARY KEY (ITEM_ID),
    FOREIGN KEY (FK_ORDER_ID) REFERENCES (ORDER_DATA)
)
 
CREATE INDEX FK_LINEITEM_ORDER ON LINEITEM(FK_ORDER_ID)

The declaration of this in ejb-jar.xml would be:

  
    Order
    ...
    java.lang.Integer
    
      orderId
    
    orderId
  
    LineItem
    ...
    java.lang.Integer
    
      itemId
    
    itemId
  


  
    Order-Has-LineItems
    
      Order
      One
      
        Order
      

      
        lineItems
        java.util.Set
      
    
    
      Many
      
        LineItem
      

      
        order
      
    
  

In the EJBs, all access to the relationship is handled by the CMR fields resulting in a very simple implementations:

public abstract class OrderEJB implements EntityBean {
    public Integer ejbCreate(Integer orderId) throws CreateException {
       setOrderId(orderId);
       return null;
    }
 
    public void ejbPostCreate(Integer orderId) {
    }
 
    public abstract Integer getOrderId();
    public abstract void setOrderId(Integer orderId);
 
    public abstract Set getLineItems();
    public abstract void setLineItems(Set lineItems);
}
 
public abstract class LineItemEJB implements EntityBean {
    public Integer ejbCreate(Integer itemId) throws CreateException {
       setItemId(itemId);
       return null;
    }
 
    public void ejbPostCreate(Integer itemId) {
    }
 
    /**
     * Create method which immediately associates the lineitem with an order
     */
    public Integer ejbCreate(Integer itemId, OrderLocal order) throws CreateException {
       setItemId(itemId);
       return null;
    }
 
    public void ejbPostCreate(Integer itemId, OrderLocal order) {
       setOrder(order);
    }
 
    public abstract Integer getItemId();
    public abstract void setItemId(Integer itemId);
 
    public abstract OrderLocal getOrder();
    public abstract void setOrder(OrderLocal order);
}

To map this EJB relationship to the database, the following entries must be defined in jbosscmp-jdbc.xml:

  
    
      Order
      ORDER_DATA
      
        orderId
        ORDER_ID
      
    
    
      LineItem
      LINEITEM
      
        itemId
        ITEM_ID
      
    
  
  
    
      
      Order-Has-LineItems
 
      
      
 
      
        Order
 
        
        
          
            orderId
            FK_ORDER_ID
          
        
      
      
        LineItems
      
    
  

A critical thing to note is the key-fields element that defines the mapping between the primary key field orderId and the foreign key column FK_ORDER_ID. This definition is placed in the ejb-relationship-role element for the "One" side of the relationship; in other words on the role that corresponds to the primary key side and not on the role where the foreign key columns are located.

This is particularly important for One-to-One relationships where potentially either side could be used as the primary. In fact, for a One-to-One relationship the key-fields element can be specified on both sides which supports a denormalised relation.

The relationship between an Order and a LineItem can be established in a couple of different ways. One option is to simply create both and then add the Lineitem to the Set of those for an Order:

    OrderLocal newOrder = orderHome.create(orderId);
    LineItemLocal newItem = lineitemHome.create(itemId);
    newOrder.getLineItems().add(newItem);
    assertTrue(newItem.getOrder().isIdentical(newOrder));

An alternative is to associate the lineitem with the order during its creation:

    OrderLocal newOrder = orderHome.create(orderId);
    LineItemLocal newItem = lineitemHome.create(itemId, newOrder);
    assertTrue(newOrder.getLineItems().contains(newItem));

Both these approaches will result in the same SQL statements being executed:

INSERT INTO ORDER_DATA(ORDER_ID) VALUES(orderId);
INSERT INTO LINEITEM(ITEM_ID, ORDER_ID) VALUES(itemId, NULL)
...
UPDATE LINEITEM SET FK_ORDER_ID=orderID WHERE ITEM_ID=itemID

The INSERT statements are executed between the calls to ejbCreate() and ejbPostCreate(), first for Order and then for LineItem; the UPDATE statement will be deferred until the end of the transaction (unless some other synchoronization event occurs).

Mandatory Relationships

The simplicity of the Optional Relationship is possible because a NULL value can be inserted into the foreign key columns. This is necessary because the relationship cannot be initialized using the cmr-field set accessor until ejbPostCreate(). The EJB specification does not allow cmr-field accessors to be used in ejbCreate() so when the row is inserted no relationship exists. For a Mandatory relationship, this is not valid and will result in a CreateException.

One way of solving this issue is to ensure all mandatory column values are known before ejbCreate() returns. As cmr accessors cannot be used, this must be done using cmp-field accessors, which requires an overloaded mapping between the EJB fields and the columns in the database table.

As a quick aside, some other containers implement this by deferring the insert until after ejbPostCreate() has been called. However, this means that no row is present in the database at this time leading to potential integrity issues. Also many databases use identity columns to generate primary keys which requires an insert to be performed; if the insert is delayed, the primary key is not known when ejbPostCreate() is called which is a violation of the specification.

The database structure for a Mandatory Relationship is identical to an Optional except that the foreign key columns are NOT NULL:

CREATE TABLE ORDER_DATA (
    ORDER_ID    INTEGER NOT NULL,
    PRIMARY KEY (ORDER_ID)
)
 
CREATE TABLE LINEITEM (
    ITEM_ID     INTEGER NOT NULL,
    FK_ORDER_ID INTEGER NOT NULL,
    PRIMARY KEY (ITEM_ID),
    FOREIGN KEY (FK_ORDER_ID) REFERENCES (ORDER_DATA)
)
 
CREATE INDEX FK_LINEITEM_ORDER ON LINEITEM(FK_ORDER_ID)

In order to initialize this relationship in ejbCreate(), an additional cmp-field is required in the LineItem EJB which maps to the foreign key column:

  
    Order
    ...
    java.lang.Integer
    
      orderId
    
    orderId
  
    LineItem
    ...
    java.lang.Integer
    
      itemId
    
    
      orderId
    
    itemId
  


  
    Order-Has-LineItems
    
      Order
      One
      
        Order
      

      
        lineItems
        java.util.Set
      
    
    
      Many
      
        LineItem
      

      
        order
      
    
  

The definition in jbosscmp-jdbc.xml is also identical to the Optional one except for the additional cmp-field:

  
    
      Order
      ORDER_DATA
      
        orderId
        ORDER_ID
      
    
    
      LineItem
      LINEITEM
      
        itemId
        ITEM_ID
      
      
        orderId
        FK_ORDER_ID
      
    
  
  
    
      
      Order-Has-LineItems
 
      
      
 
      
        Order
 
        
        
          
            orderId
            FK_ORDER_ID
          
        
      
      
        LineItems
      
    
  

This cmp-field must be initialized in ejbCreate() and the simplest mechanism is to pass in a reference to the appropriate Order EJB:

public abstract class LineItemEJB implements EntityBean {
    public Integer ejbCreate(Integer itemId, OrderLocal order) throws CreateException {
       setItemId(itemId);
       Integer orderId = (Integer) order.getPrimaryKey());
       setOrderId(orderId.intValue());
       return null;
    }
 
    public void ejbPostCreate(Integer itemId, OrderLocal order) {
    }
 
    public abstract Integer getItemId();
    public abstract void setItemId(Integer itemId);
 
    public abstract int getOrderId();
    public abstract void setOrderId(int orderId);
 
    public abstract OrderLocal getOrder();
    public abstract void setOrder(OrderLocal order);
}

Note that the form of create that just took an itemId is no longer useful as a LineItem cannot be created without association to an Order. The relationship is automatically initialized by assigning the cmp-field and so there is no need to call the cmr set accessor in ejbPostCreate().

Due to the dual mapping, it is possible to change the relationship outside ejbCreate() by setting a new orderId using the cmp-field. However, such practice is confusing and should be avoided.

Identifying Relationships

An Identifying relationship is very similar to a Mandatory one in that the relationship must be initialized during ejbCreate() and before the row is inserted into the database. However, because the primary key of the child depends on the parent, any attempt to modify the relationship once ejbCreate() has returned will result in an IllegalStateException; this is because changing the related parent would require a change to the primary key which is explicitly prohibited. JBoss is very strict about this and simply invoking the set accessor will cause the Exception, even if the object being set is identical to the current object.

The database structure is the same as for a Mandatory relationship except that the foreign key field is now included in the primary key:

CREATE TABLE ORDER_DATA (
    ORDER_ID    INTEGER NOT NULL,
    PRIMARY KEY (ORDER_ID)
)
 
CREATE TABLE LINEITEM (
    ITEM_ID     INTEGER NOT NULL,
    FK_ORDER_ID INTEGER NOT NULL,
    PRIMARY KEY (FK_ORDER_ID, ITEM_ID),
    FOREIGN KEY (FK_ORDER_ID) REFERENCES (ORDER_DATA)
)

No index is typically needed on the FK_ORDER_ID column as it forms the first part of the primary key.

The LineItem EJB now has a compound primary key so we can no longer use a simple integer but must specify a specific primary key class. This changes the ejb-jar.xml definition as follows:

  
    Order
    ...
    java.lang.Integer
    
      orderId
    
    orderId
  
    LineItem
    ...
    LineItemPK
    
      itemId
    
    
      orderId
    
  


  
    Order-Has-LineItems
    
      Order
      One
      
        Order
      

      
        lineItems
        java.util.Set
      
    
    
      Many
      
        LineItem
      

      
        order
      
    
  

The LineItemPK class must be defined with fields named after the cmp-fields:

public abstract class LineItemPK implements Serializable {
    public int itemId;
    public int orderId;
    // overrides of equals and hashCode omitted for clarity
}
 
public abstract class LineItemEJB implements EntityBean {
    public LineItemPK ejbCreate(int itemId, OrderLocal order) throws CreateException {
       setItemId(itemId);
       Integer orderId = (Integer) order.getPrimaryKey());
       setOrderId(orderId.intValue());
       return null;
    }
 
    public void ejbPostCreate(int itemId, OrderLocal order) {
    }
 
    public abstract int getItemId();
    public abstract void setItemId(int itemId);
 
    public abstract int getOrderId();
    public abstract void setOrderId(int orderId);
 
    public abstract OrderLocal getOrder();
    public abstract void setOrder(OrderLocal order);
}

The mapping in jbosscmp-jdbc.xml is identical to the Mandatory case:

  
    
      Order
      ORDER_DATA
      
        orderId
        ORDER_ID
      
    
    
      LineItem
      LINEITEM
      
        itemId
        ITEM_ID
      
      
        orderId
        FK_ORDER_ID
      
    
  
  
    
      
      Order-Has-LineItems
 
      
      
 
      
        Order
 
        
        
          
            orderId
            FK_ORDER_ID
          
        
      
      
        LineItems
      
    
  

Relation-Table Mappings

Relation-table mappings typicaly support Many-to-Many relationships that cannot be modeled in the database using a single foreign-key. Whilst it is possible to model other relationships using them, it is generally less common.

An example of a Many-to-Many relationship would be that between a Product and a ProductCategory: a Product can be in multiple categories and a ProductCategory would contain many products. A database definition for this could be:

CREATE TABLE PRODUCT (
    PRODUCT_ID  INTEGER NOT NULL,
    PRIMARY KEY (PRODUCT_ID)
)
 
CREATE TABLE PRODUCT_CATEGORY (
    CATEGORY_ID INTEGER NOT NULL,
    PRIMARY KEY (CATEGORY_ID)
)
 
CREATE TABLE PRODUCT_TO_CATEGORY (
    RT_PRODUCT_ID  INTEGER NOT NULL,
    RT_CATEGORY_ID INTEGER NOT NULL
)
CREATE UNIQUE INDEX UK_PRODUCT_CATEGORY ON PRODUCT_TO_CATEGORY(RT_PRODUCT_ID, RT_CATEGORY_ID)
CREATE UNIQUE INDEX UK_CATEGORY_PRODUCT ON PRODUCT_TO_CATEGORY(RT_CATEGORY_ID, RT_PRODUCT_ID)

The two indexes allow for access from Product to ProductCategory and from ProductCategory.

The definition of this relationship in ejb-jar.xml is very simple. Each EJB's definition includes a cmp-field for its primary key and a multi-valued cmr-field for accessing the other one:

  
    Product
    ...
    java.lang.Integer
    
      productId
    
    productId
  
    ProductCategory
    ...
    java.lang.Integer
    
      categoryId
    



  
    Product-is-Categorized
    
      Product
      Many
      
        Product
      

      
        categories
        java.util.Set
      
    
 
    
      Many
      
        ProductCategory
      

      
        products
        java.util.Set
      
    
  

The EJB implementation for this type of relationship comprises the definitions of the accessors for the primary key cmp-fields and the multi-valued cmr-fields:

public abstract class Product immplements EntityBean {
    public Integer ejbCreate(Integer productId) throws CreateException {
        setProductId(productId);
        return null;
    }
 
    public void ejbPostCreate(Integer productId) {
    }
 
    public abstract Integer getProductId();
    public abstract void setProductId(Integer productId);
 
    public abstract Set getCategories();
    public abstract void setCategories(Set categories);
}
 
public abstract class ProductCategory implements EntityBean {
    public Integer ejbCreate(Integer categoryId) throws CreateException {
        setCategoryId(categoryId);
        return null;
    }
 
    public void ejbPostCreate(Integer categoryId) {
    }
 
    public abstract Integer getCategoryId();
    public abstract void setCategoryId(Integer categoryId);
 
    public abstract Set getProducts();
    public abstract void setProducts(Set products);
}

The bulk of the configuration occurs in jbosscmp-jdbc.xml:

  
    
      Product
      PRODUCT
      
        productId
        PRODUCT_ID
      
    
    
      ProductCategory
      PRODUCT_CATEGORY
      
        categoryId
        CATEGORY_ID
      
    
  
  
    
      
      Product-is-Categorized
 
      
      
        
        PRODUCT_TO_CATEGORY
      
 
      
        Product
 
        
        
          
            productId
            RT_PRODUCT_ID
          
        
      
      
        ProductCategory
 
        
        
          
            categoryId
            RT_CATEGORY_ID
          
        
      
    
  

When a relationship is established, the a row will be inserted into relation table. For example, the code:

    ProductLocal product = productHome.create(productId);
    CategoryLocal category = categoryHome.create(categoryId);
    product.getCategories().add(category);

would result in the following SQL statements being issued:

INSERT INTO PRODUCT(PRODUCT_ID) VALUES(productId)
INSERT INTO CATEGORY(CATEGORY_ID) VALUES (categoryId)
INSERT INTO PRODUCT_TO_CATEGORY(RT_PRODUCT_ID, RT_CATEGORY_ID) VALUES (productId, categoryId)