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:

<enterprise-beans>
<entity>
<ejb-name>Order</ejb-name> ...
<prim-key-class>java.lang.Integer</prim-key-class>
<cmp-field>
<field-name>orderId</field-name>
</cmp-field>
<primkey-field>orderId</primkey-field>
</entity>
<ejb-name>LineItem</ejb-name> ...
<prim-key-class>java.lang.Integer</prim-key-class>
<cmp-field>
<field-name>itemId</field-name>
</cmp-field>
<primkey-field>itemId</primkey-field>
</entity>
</enterprise-beans>
<relationships> <ejb-relation>
<ejb-relation-name>Order-Has-LineItems</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>Order</ejb-relationship-role-name> <multiplicity>One</multiplicity>
<relationship-role-source>
<ejb-name>Order</ejb-name> </relationship-role-source> <cmr-field>
<cmr-field-name>lineItems</cmr-field-name>
<cmr-field-type>java.util.Set</cmr-field-type>
</cmr-field> </ejb-relationship-role> <ejb-relationship-role>
<multiplicity>Many</multiplicity> <relationship-role-source> <ejb-name>LineItem</ejb-name> </relationship-role-source> <cmr-field>
<cmr-field-name>order</cmr-field-name> </cmr-field> </ejb-relationship-role> </ejb-relation> </relationships>

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:

<jbosscmp-jdbc>
<enterprise-beans> <entity> <ejb-name>Order</ejb-name> <table-name>ORDER_DATA</table-name>
<cmp-field>
<field-name>orderId</field-name> <column-name>ORDER_ID</column-name>
</cmp-field>
</entity>
<entity> <ejb-name>LineItem</ejb-name> <table-name>LINEITEM</table-name>
<cmp-field>
<field-name>itemId</field-name> <column-name>ITEM_ID</column-name>
</cmp-field>
</entity>
</enterprise-beans> <relationships>
<ejb-relation> <!-- ejb-relation-name must match the one in ejb-jar.xml -->
<ejb-relation-name>Order-Has-LineItems</ejb-relation-name>

<!-- define that this relationship uses a foreign key -->
<foreign-key-mapping/>

<ejb-relationship-role>
<ejb-relationship-role-name>Order</ejb-relationship-role-name>

<!-- define the primary key fields from the One side and how
they map to foreign key fields in the Many side -->
<key-fields>
<key-field>
<field-name>orderId</field-name>
<column-name>FK_ORDER_ID</column-name>
</key-field>
</key-fields>
</ejb-relationship-role> <ejb-relationship-role>
<ejb-relationship-role-name>LineItems</ejb-relationship-role-name>
</ejb-relationship-role> </ejb-relation>
</reationships>
</jbosscmp-jdbc>

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:

<enterprise-beans>
<entity>
<ejb-name>Order</ejb-name> ...
<prim-key-class>java.lang.Integer</prim-key-class>
<cmp-field>
<field-name>orderId</field-name>
</cmp-field>
<primkey-field>orderId</primkey-field>
</entity>
<ejb-name>LineItem</ejb-name> ...
<prim-key-class>java.lang.Integer</prim-key-class>
<cmp-field>
<field-name>itemId</field-name>
</cmp-field>
<cmp-field>
<field-name>orderId</field-name>
</cmp-field>
<primkey-field>itemId</primkey-field>
</entity>
</enterprise-beans>
<relationships> <ejb-relation>
<ejb-relation-name>Order-Has-LineItems</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>Order</ejb-relationship-role-name> <multiplicity>One</multiplicity>
<relationship-role-source>
<ejb-name>Order</ejb-name> </relationship-role-source> <cmr-field>
<cmr-field-name>lineItems</cmr-field-name>
<cmr-field-type>java.util.Set</cmr-field-type>
</cmr-field> </ejb-relationship-role> <ejb-relationship-role>
<multiplicity>Many</multiplicity> <relationship-role-source> <ejb-name>LineItem</ejb-name> </relationship-role-source> <cmr-field>
<cmr-field-name>order</cmr-field-name> </cmr-field> </ejb-relationship-role> </ejb-relation> </relationships>

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

<jbosscmp-jdbc>
<enterprise-beans> <entity> <ejb-name>Order</ejb-name> <table-name>ORDER_DATA</table-name>
<cmp-field>
<field-name>orderId</field-name> <column-name>ORDER_ID</column-name>
</cmp-field>
</entity>
<entity> <ejb-name>LineItem</ejb-name> <table-name>LINEITEM</table-name>
<cmp-field>
<field-name>itemId</field-name> <column-name>ITEM_ID</column-name>
</cmp-field>
<cmp-field>
<field-name>orderId</field-name> <column-name>FK_ORDER_ID</column-name>
</cmp-field>
</entity>
</enterprise-beans> <relationships>
<ejb-relation> <!-- ejb-relation-name must match the one in ejb-jar.xml -->
<ejb-relation-name>Order-Has-LineItems</ejb-relation-name>

<!-- define that this relationship uses a foreign key -->
<foreign-key-mapping/>

<ejb-relationship-role>
<ejb-relationship-role-name>Order</ejb-relationship-role-name>

<!-- define the primary key fields from the One side and how
they map to foreign key fields in the Many side -->
<key-fields>
<key-field>
<field-name>orderId</field-name>
<column-name>FK_ORDER_ID</column-name>
</key-field>
</key-fields>
</ejb-relationship-role> <ejb-relationship-role>
<ejb-relationship-role-name>LineItems</ejb-relationship-role-name>
</ejb-relationship-role> </ejb-relation>
</reationships>
</jbosscmp-jdbc>

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:

<enterprise-beans>
<entity>
<ejb-name>Order</ejb-name> ...
<prim-key-class>java.lang.Integer</prim-key-class>
<cmp-field>
<field-name>orderId</field-name>
</cmp-field>
<primkey-field>orderId</primkey-field>
</entity>
<ejb-name>LineItem</ejb-name> ...
<prim-key-class>LineItemPK</prim-key-class>
<cmp-field>
<field-name>itemId</field-name>
</cmp-field>
<cmp-field>
<field-name>orderId</field-name>
</cmp-field>
</entity>
</enterprise-beans>
<relationships> <ejb-relation>
<ejb-relation-name>Order-Has-LineItems</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>Order</ejb-relationship-role-name> <multiplicity>One</multiplicity>
<relationship-role-source>
<ejb-name>Order</ejb-name> </relationship-role-source> <cmr-field>
<cmr-field-name>lineItems</cmr-field-name>
<cmr-field-type>java.util.Set</cmr-field-type>
</cmr-field> </ejb-relationship-role> <ejb-relationship-role>
<multiplicity>Many</multiplicity> <relationship-role-source> <ejb-name>LineItem</ejb-name> </relationship-role-source> <cmr-field>
<cmr-field-name>order</cmr-field-name> </cmr-field> </ejb-relationship-role> </ejb-relation> </relationships>

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:

<jbosscmp-jdbc>
<enterprise-beans> <entity> <ejb-name>Order</ejb-name> <table-name>ORDER_DATA</table-name>
<cmp-field>
<field-name>orderId</field-name> <column-name>ORDER_ID</column-name>
</cmp-field>
</entity>
<entity> <ejb-name>LineItem</ejb-name> <table-name>LINEITEM</table-name>
<cmp-field>
<field-name>itemId</field-name> <column-name>ITEM_ID</column-name>
</cmp-field>
<cmp-field>
<field-name>orderId</field-name> <column-name>FK_ORDER_ID</column-name>
</cmp-field>
</entity>
</enterprise-beans> <relationships>
<ejb-relation> <!-- ejb-relation-name must match the one in ejb-jar.xml -->
<ejb-relation-name>Order-Has-LineItems</ejb-relation-name>

<!-- define that this relationship uses a foreign key -->
<foreign-key-mapping/>

<ejb-relationship-role>
<ejb-relationship-role-name>Order</ejb-relationship-role-name>

<!-- define the primary key fields from the One side and how
they map to foreign key fields in the Many side -->
<key-fields>
<key-field>
<field-name>orderId</field-name>
<column-name>FK_ORDER_ID</column-name>
</key-field>
</key-fields>
</ejb-relationship-role> <ejb-relationship-role>
<ejb-relationship-role-name>LineItems</ejb-relationship-role-name>
</ejb-relationship-role> </ejb-relation>
</reationships>
</jbosscmp-jdbc>

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:

<enterprise-beans>
<entity>
<ejb-name>Product</ejb-name> ...
<prim-key-class>java.lang.Integer</prim-key-class>
<cmp-field>
<field-name>productId</field-name>
</cmp-field>
<primkey-field>productId</primkey-field>
</entity>
<ejb-name>ProductCategory</ejb-name> ...
<prim-key-class>java.lang.Integer</prim-key-class>
<cmp-field>
<field-name>categoryId</field-name>
</cmp-field>
</entity>
</enterprise-beans>
<relationships> <ejb-relation>
<ejb-relation-name>Product-is-Categorized</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>Product</ejb-relationship-role-name> <multiplicity>Many</multiplicity>
<relationship-role-source>
<ejb-name>Product</ejb-name> </relationship-role-source> <cmr-field>
<cmr-field-name>categories</cmr-field-name>
<cmr-field-type>java.util.Set</cmr-field-type>
</cmr-field> </ejb-relationship-role>
<ejb-relationship-role>
<multiplicity>Many</multiplicity> <relationship-role-source> <ejb-name>ProductCategory</ejb-name> </relationship-role-source> <cmr-field>
<cmr-field-name>products</cmr-field-name> <cmr-field-type>java.util.Set</cmr-field-type>
</cmr-field> </ejb-relationship-role> </ejb-relation> </relationships>

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:

<jbosscmp-jdbc>
<enterprise-beans> <entity> <ejb-name>Product</ejb-name> <table-name>PRODUCT</table-name>
<cmp-field>
<field-name>productId</field-name> <column-name>PRODUCT_ID</column-name>
</cmp-field>
</entity>
<entity> <ejb-name>ProductCategory</ejb-name> <table-name>PRODUCT_CATEGORY</table-name>
<cmp-field>
<field-name>categoryId</field-name> <column-name>CATEGORY_ID</column-name>
</cmp-field>
</entity>
</enterprise-beans> <relationships>
<ejb-relation> <!-- ejb-relation-name must match the one in ejb-jar.xml -->
<ejb-relation-name>Product-is-Categorized</ejb-relation-name>

<!-- define that this relationship uses a relation table -->
<relation-table-mapping>
<!-- specify the name of the table -->
<table-name>PRODUCT_TO_CATEGORY</table-name>
</relation-table-mapping>

<ejb-relationship-role>
<ejb-relationship-role-name>Product</ejb-relationship-role-name>

<!-- map the cmp-fields for the primary key
to columns in the relation table -->
<key-fields>
<key-field>
<field-name>productId</field-name>
<column-name>RT_PRODUCT_ID</column-name>
</key-field>
</key-fields>
</ejb-relationship-role> <ejb-relationship-role>
<ejb-relationship-role-name>ProductCategory</ejb-relationship-role-name>

<!-- map the cmp-fields for the primary key
to columns in the relation table -->
<key-fields>
<key-field>
<field-name>categoryId</field-name>
<column-name>RT_CATEGORY_ID</column-name>
</key-field>
</key-fields>
</ejb-relationship-role> </ejb-relation>
</reationships>
</jbosscmp-jdbc>

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)