Search This Blog

Wednesday, 30 November 2011

One To One Association Using Shared Primary Keys

Consider the example of an Order. The Order includes various details such as items brought, billing information, mode of payment, delivery address etc.
One way to represent all these information is to have a single order table and then implement the different data as components. This would be great from the point of performance and ease of management.
However there may be cases wherein the data may be needed in separate tables.For example the Accounts module might need reference to the Billing Information. The Shipping module might need information related to the delivery address and so on.
The point is that we may be needed to implement these as individual entities with one-to-one relations among them. For example
an Order has a one to one relation with billing details.Hibernate allows us to do this in two ways.The first technique is to use the Shared primary key association.
In this method the primary key value of one entity is used as the value of the primary key for the other entity. Let us look at the entities first.
public class Order {
    private Integer id;
    private String code;
    private BillDetail billDetail;
public class BillDetail {
    private Integer billDetailId;
    private String fullName;
    private String panCode;
    private BigDecimal netCost;
    private Order referenceOrder;
The Order has a reference to Bill Detail and vice-versa. The mapping files is where the relationship intricacies are specified.
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
<hibernate-mapping package="com.association.one_to_one">
    <class name="Order" table="ORDER_DATA">

        <id name="id" type="integer" column="ID">
            <generator class="native" />

        <property name="code" type="string">
            <column name="CODE" />

        <one-to-one name="billDetail" class="BillDetail" cascade="all" />
        <!-- If parent updates/deletes/creates -> same action on child -->
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
<hibernate-mapping package="com.association.one_to_one">
    <class name="BillDetail" table="BILL_DETAIL">
        <id name="billDetailId" type="integer" >
            <column name="BILL_DETAIL_ID" />
            <generator class="foreign" >
                <param name="property">referenceOrder</param>
        <!-- To specify the foreign key constraint on billDetailId, 
             we have set constrained=”true” as below. -->
        <one-to-one name="referenceOrder" class="Order" constrained="true">
        <property name="fullName" type="string">
            <column name="FULL_NAME" />
        <property name="panCode" type="string">
            <column name="PAN_CODE" />
        <property name="netCost" type="big_decimal">
            <column name="NET_COST" precision="10" />
The Order mapping specifies the reference from Order class to Bill Detail. The cascade settings ties up the life-cycle of BillDetail with a particular Order.
The BillDetail mapping also has a reference to the Order class to ensure bi-directionality. The Bill detail class uses a foreign generator class that tells Hibernate that the primary key here is actually the primary key from some another table.(For details check this post).
The one-to-one element also includes a 'constrained ="true"' attribute that ensures  a foreign key constraint is setup between the primary key of the BillDetail table and the primary key of the Order_Data table. If it is removed, then any DDL generated will not include the foreign key. This is one of the two uses of constrained attribute.
The tables created by Hibernate indicate the same:
create table BILL_DETAIL (
        BILL_DETAIL_ID integer not null,
        FULL_NAME varchar(255),
        PAN_CODE varchar(255),
        NET_COST numeric(10,2),
        primary key (BILL_DETAIL_ID)
    create table ORDER_DATA (
        ID integer not null auto_increment,
        CODE varchar(255),
        primary key (ID)
    alter table BILL_DETAIL 
        add index FK7EA789B0A3DAC6 (BILL_DETAIL_ID), 
        add constraint FK7EA789B0A3DAC6 
        foreign key (BILL_DETAIL_ID) 
        references ORDER_DATA (ID)
To create a record
public static void createOrder() {
    BillDetail billDetail = new BillDetail();
    billDetail.setFullName("Robin Varghese");
    billDetail.setNetCost(new BigDecimal("124.76"));
    Order order = new Order();
    Session session = sessionFactory.openSession();
    Transaction transaction = session.beginTransaction
On executing the code, the logs indicate that the two entities were saved with the same primary key.
2610 [main] DEBUG org.hibernate.SQL  - 
2735 [main] DEBUG  - Natively genera
ted identity: 1
2735 [main] DEBUG org.hibernate.engine.Cascade  - processing cascade ACTION_SAVE
_UPDATE for: com.association.one_to_one.Order
2735 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener  - generated
 identifier: 1, using strategy:
2782 [main] DEBUG org.hibernate.pretty.Printer  - com.association.one_to_one.Bil
lDetail{netCost=124.76, referenceOrder=com.association.one_to_one.Order#1, fullN
ame=Robin Varghese, panCode=AKHY765, billDetailId=1}
2782 [main] DEBUG org.hibernate.pretty.Printer  - com.association.one_to_one.Ord
er{id=1, billDetail=com.association.one_to_one.BillDetail#1, code=#4356}
2782 [main] DEBUG org.hibernate.SQL  - 
        (?, ?, ?, ?)
The logs indicate that first the Order_Data row was created and assigned a key (generated via auto-increment). This same key was then assigned to the BillDetail class.
If we were to load the BillDetail class to access the relations, the SQL executed will indicate that the fetch were done using join queries. For example the code
BillDetail billDetail = (BillDetail) session.load(BillDetail.class, 1);
will generate the following sql clauses
        billdetail0_.BILL_DETAIL_ID as BILL1_1_0_,
        billdetail0_.FULL_NAME as FULL2_1_0_,
        billdetail0_.PAN_CODE as PAN3_1_0_,
        billdetail0_.NET_COST as NET4_1_0_ 
        BILL_DETAIL billdetail0_ 
        order0_.ID as ID0_1_,
        order0_.CODE as CODE0_1_,
        billdetail1_.BILL_DETAIL_ID as BILL1_1_0_,
        billdetail1_.FULL_NAME as FULL2_1_0_,
        billdetail1_.PAN_CODE as PAN3_1_0_,
        billdetail1_.NET_COST as NET4_1_0_ 
        ORDER_DATA order0_ 
    left outer join
        BILL_DETAIL billdetail1_ 
            on order0_.ID=billdetail1_.BILL_DETAIL_ID 
As can be seen the query use to retrieve BillDetail object was a simple Select query whereas the query for Order involved a join query. The constrained attribute plays a significant role in this difference.
In upcoming posts I shall use the same java classes to create a foreign key based association and also a one to one association that is optional

No comments:

Post a Comment