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
)


Comments:

“We are all functioning at a small fraction of our capacity to live fully in its total meaning of loving, caring, creating and adventuring. Consequently, the actualizing of our potential can become the most exciting adventure of our lifetime.” – Herbert Ottto

Posted by Lenard Ledger on January 05, 2011 at 07:08 AM PST #

I don't usually comment but I gotta admit appreciate it for the post on this special one : D.

Posted by HDMI splitter on January 16, 2011 at 02:12 AM PST #

Thank for your Answer. Hope to see more good answer.

Posted by chamroeun on March 16, 2011 at 10:43 AM PDT #

This is very cool. Just because its not so much work but so effective. Look on this site they use something similliar Importera bil från tyskland

Posted by Köpa bil från tyskland on April 06, 2011 at 02:03 AM PDT #

Major thanks for the article post. Will read on…

Posted by Monte Oakden on April 19, 2011 at 11:03 PM PDT #

I dont comment often but now I had to!

Great share of this article. Seems so effective like above..

Posted by guest on September 29, 2011 at 10:04 AM PDT #

thank u .... very nice article

Posted by guest on July 19, 2013 at 01:58 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

The official blog for Oracle WebLogic Server fans and followers!

Stay Connected

Search

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