Thursday Jan 14, 2010

EJB3: Mapping of One-to-One relationships when primary key in the source table is also a foreign key for the target entity

This blog entry will explain how to define the EJB3 object relational mappings for entities that have a 1-1 association when the primary key of the relationship's owner side is also the foreign key for the owned entity.

Consider the following diagram.

supplier-schema.JPG

The Supplier entity has a 1-1 relationship with the SupplierStatistics entity. A Supplier is identified by the ID key uniquely which is also the primary key of the SUPPLIER table. Each Supplier has an associated set of Statistics like the number of orders placed, accepted and delivered on time by a given Supplier. These statistics are persisted in the SUPPLIER_STATISTICS table. The records in the SUPPLIER and SUPPLIER_STATISTICS table are joined by the ID of the supplier. The ID column in the SUPPLIER table is also a foreign key which references the SUPPLIER_ID column in the SUPPLIER_STATISTICS table.

 

The EJB3 entity classes for the above relationship are given below.

 

package com.tutorial.entity;

 

import java.io.Serializable;

import javax.persistence.*;

 

/**

 * The persistent class for the SUPPLIER database table.

 *

 */

@Entity

public class Supplier implements Serializable {

  private static final long serialVersionUID = 1L;

 

  @Id

  @GeneratedValue(strategy=GenerationType.AUTO)

  private long id;

 

  private String name;

 

  //uni-directional one-to-one association to SupplierStatistic

  @OneToOne(cascade={CascadeType.PERSIST, CascadeType.REMOVE, CascadeType.REFRESH})

  @JoinColumn(name="ID", insertable=false, updatable=false)

  private SupplierStatistic supplierStatistic;

 

  public Supplier() {

      supplierStatistic = new SupplierStatistic();

  }

 

  public long getId() {

    return this.id;

  }

 

  public void setId(long id) {

    this.id = id;

  }

 

  public String getName() {

    return this.name;

  }

 

  public void setName(String name) {

    this.name = name;

  }

 

  public SupplierStatistic getSupplierStatistic() {

    return this.supplierStatistic;

  }

 

  @PrePersist

  public void initializeSupplierStatistic() {

    this.supplierStatistic.setSupplierId(id);

  }

     

}

 

package com.tutorial.entity;

 

import java.io.Serializable;

import javax.persistence.*;

import java.math.BigDecimal;

 

/**

 * The persistent class for the SUPPLIER_STATISTICS database table.

 */

@Entity

@Table(name="SUPPLIER_STATISTICS")

public class SupplierStatistic implements Serializable {

  private static final long serialVersionUID = 1L;

 

  @Id

  @GeneratedValue(strategy=GenerationType.AUTO)

  @Column(name="SUPPLIER_ID")

  private long supplierId;

 

  @Column(name="ORDERS_ACCEPTED")

  private BigDecimal ordersAccepted;

 

  @Column(name="ORDERS_DELIVERED_ON_TIME")

  private BigDecimal ordersDeliveredOnTime;

 

  @Column(name="ORDERS_PLACED")

  private BigDecimal ordersPlaced;

 

  SupplierStatistic() {}

 

  public long getSupplierId() {

    return this.supplierId;

  }

 

  public void setSupplierId(long supplierId) {

    this.supplierId = supplierId;

  }

 

  public BigDecimal getOrdersAccepted() {

    return this.ordersAccepted;

  }

 

  public void setOrdersAccepted(BigDecimal ordersAccepted) {

    this.ordersAccepted = ordersAccepted;

  }

 

  public BigDecimal getOrdersDeliveredOnTime() {

    return this.ordersDeliveredOnTime;

  }

 

  public void setOrdersDeliveredOnTime(BigDecimal ordersDeliveredOnTime) {

    this.ordersDeliveredOnTime = ordersDeliveredOnTime;

  }

 

  public BigDecimal getOrdersPlaced() {

    return this.ordersPlaced;

  }

 

  public void setOrdersPlaced(BigDecimal ordersPlaced) {

    this.ordersPlaced = ordersPlaced;

  }

 

}

 

Notice the salient features of the entity mappings.

 

  • The relationship is defined as a One-way 1-1 mapping from the Supplier, which is the owner of the relationship to the SupplierStatistic entity.

 

      //uni-directional one-to-one association to SupplierStatistic

@OneToOne(cascade={CascadeType.PERSIST, CascadeType.REMOVE, CascadeType.REFRESH})

@JoinColumn(name="ID", insertable=false, updatable=false)

private SupplierStatistic supplierStatistic;


The @JoinColumn annotation has the attributes insertable=false and updatable=false. This is because the ID column which is a primary key also happens to be the foreign key for the SupplierStatistic entity. Since we don't need to persist an additional column to persist the relationship which is implicitly defined by the primary key, we mark the JoinColumn as insertable and updateable = false. Otherwise you will run into errors depending upon your JPA provider.

 

With EclipseLink JPA provider available with Oracle Enterprise Pack for Eclipse (OEPE) I was getting the following error without these attributes.

 

javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 1.1.2.v20090612-r4475): org.eclipse.persistence.exceptions.DatabaseException

Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00957: duplicate column name

 

Error Code: 957

Call: INSERT INTO SUPPLIER (ID, NAME, ID) VALUES (?, ?, ?)

      bind => [4502, SupplierName1, 4502]

 

  • The supplierStatistic entity is implicitly created when the Supplier is created. Refer to the constructor. The OneToOne relationship is marked as CascadeType.PERSIST so the SupplierStatistic entity will be persisted along with the Supplier entity.


    public Supplier() {

    supplierStatistic = new SupplierStatistic();

  }


  • What about the SUPPLIER_ID column value for the SupplierStatistic entity? It needs to be the same value as the parent Supplier's ID value. This is achieved by injecting the ID of the parent into the child before persisting them. The initialiazeSupplierStatistic method is annotated with the @PrePresist annonation.

 

     @PrePersist

     public void initializeSupplierStatistic() {

       this.supplierStatistic.setSupplierId(id);

     }

 

This ensures that the id field value that has been either auto-generated or retrieved from the sequence table (depending upon the generation strategy for the primary key) by the JPA provider is passed onto the child entity thereby defining the 1-1 association.


  • As a best practice the constructor for the SupplierStatistic class is not public, so there is no explicit way to create and persist a SupplierStatistic entity. It gets created and persisted only in the context of the parent. Correspondingly a SupplierStatistic entity is removed along with the parent Supplier, since we also have CascadeType.REMOVE on the relationship.

 

This pattern can be applied to other situations where the same primary key value is shared between the two entities, for example Customer and Contact which can share the CUSTOMER_ID has the common key.

 

Appendix

 

DDL for the SUPPLIER and SUPPLIER_STATISTICS tables for the Oracle Database 10g Express Edition

 

CREATE TABLE "SUPPLIER" 
( "ID" NUMBER NOT NULL ENABLE, 
  "NAME" VARCHAR2(4000), 
  CONSTRAINT "SUPPLIER_PK" PRIMARY KEY ("ID") ENABLE, 
  CONSTRAINT "SUPPLIER_FK" FOREIGN KEY ("ID")
  REFERENCES "SUPPLIER_STATISTICS" ("SUPPLIER_ID") ENABLE
)
 
CREATE TABLE "SUPPLIER_STATISTICS" 
( "SUPPLIER_ID" NUMBER NOT NULL ENABLE, 
  "ORDERS_PLACED" NUMBER, 
  "ORDERS_ACCEPTED" NUMBER, 
  "ORDERS_DELIVERED_ON_TIME" NUMBER, 
  CONSTRAINT "SUPPLIER_STATISTICS_PK" PRIMARY KEY ("SUPPLIER_ID") ENABLE
)
[Read More]
About

The official blog for Oracle WebLogic Server fans and followers!

Stay Connected

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
5
6
7
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today