Mapping Relationships in EJB 3.0

Rather than create my own schema for testing EJB 3.0 features, I decided to use the schema used in TPC-C Benchmarks. This approach has the benefit that it forces me to design an object-relational mapping for a pre-existing schema, which is probably what most developers will do in practice.

If you are designing a new schema, then bear in mind that EJB 3.0 favours a design where each table has a surrogate primary key, i.e., a meaningless numeric key that is either auto-generated or generated using a sequence. Using surrogate primary keys makes it easier to map the primary key of the table, as you can simply mark the relevant field using the @Id attribute. It also makes it easier to map relationships, as the EntityManager is able to exploit natural Has-A relationships (Composite design pattern) between objects. For example, in the TPC-C schema, an Order has a one-to-many relationship with OrderLines. This can be mapped as follows:

1. An Order object should contain a Collection of OrderLine objects.
2. An OrderLine object should have a reference to the Order that it is related to.

Given below is the relevant code:

public class Order {
  set<OrderLine> orderLines;

  @OneToMany(mappedBy="order")
  public Set<OrderLine> getOrderLines() {
    return orderLines;
  }
}

public class OrderLine {
  Order order;

  @ManyToOne
  @JoinColumn(name="OL_O_ID", referencedColumnname="O_ID")
  public Order getOrder() {
    return order;
  }
}

An important point you must note here is that the OrderLine Entity does not contain an explicit field for the foreign key column "OL_O_ID" that references the primary key column "O_ID" in Order; the Persistence Engine automatically uses the appropriate field from the Order entity when inserting rows in the OrderLine table.

The situation becomes more complex when tables are not designed to use surrogate primary keys. In this scenario, it is often necessary to use composite primary keys.

I will use the Customer and District entities as examples because both have composite primary keys and there exists a many-to-one relationship between a Customer and a District. First of all, let's recall that to map a composite primary key, we need to create a primary key class that identifies the fields that form part of the composite key. This primary class is then associated with the Entity using the @IdClass annotation. The Entity itself also contains the fields used in the primary key; these are annotated using @Id and @Column. Since I described how this works in my previous post, I will only show the resulting key fields in the Customer entity here:

@Entity
public class Customer {

  @Id
  @Column(name="C_ID", nullable=false)
  public int getCustomerId() {
    return customerId;
  }

  @Id
  @Column(name="C_D_ID", nullable=false)
  public int getDistrictId() {
    return districtId;
  }

  @Id
  @Column(name="C_W_ID", nullable=false)
  public int getWarehouseId() {
    return warehouseId;
  }
}

Now in order to map the relationships, we add the following:

@Entity
public class Customer {
  District district;

  @ManyToOne
  @JoinColumns({
    @JoinColumn(name="C_W_ID", referencedColumnName="D_W_ID"),
    @JoinColumn(name="C_D_ID", referencedColumnName="D_ID")
  })
  public District getDistrict() {
    return district;
  }
}

@Entity
public class District {

  Set<customer> customers;

  @OneToMany(mappedBy="district")
  public Set<customer> getCustomers() {
    return customers;
  }
}

The problem is that now there are two ways of updating the foreign key columns in the Customer entity, because the foreign key columns are present in the referenced District entity, but also present as fields in the entity in order to satisfy the requirements for a composite primary key. If you try to execute this code, you will encounter an Exception such as this (in Glassfish):

Multiple writable mappings exist for the field [TPCC.CUSTOMER.C_W_ID]. Only one may be defined as writable, all others must be specified read-only.

Multiple writable mappings exist for the field [TPCC.CUSTOMER.C_D_ID]. Only one may be defined as writable, all others must be specified read-only.

To resolve this problem, you need to modify the Customer Entity definition and ensure that the columns TPCC.CUSTOMER.C_W_ID and TPCC.CUSTOMER.C_D_ID are marked as readonly, by setting insertable=false and updatable=false:

@Entity
public class Customer {

  @Id
  @Column(name="C_D_ID", nullable=false,
          insertable=false, updatable=false)
  public int getDistrictId() {
    return districtId;
  }

  @Id
  @Column(name="C_W_ID", nullable=false,
          insertable=false, updatable=false)
  public int getWarehouseId() {
    return warehouseId;
  }
}