Search This Blog

Sunday 4 December 2011

One to One Association Using Foreign Key

In the earlier technique I  mapped a one-to-one relation using a shared primary key. Now I shall attempt to create the same relation-ship using foreign keys.
Both the tables in this case will have their own independent primary key and id generation strategies. One of the tables (mostly the entity owning the relation, in this case the Order_Data) has  a column which references the primary key from the other table.
The java classes are unchanged. The new mapping files are as follows:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<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="identity" />            
        </id>
        <property name="fullName" type="string">
            <column name="FULL_NAME" />
        </property>
        <property name="panCode" type="string">
            <column name="PAN_CODE" />
        </property>
        <property name="netCost" type="big_decimal">
            <column name="NET_COST" precision="10" />
        </property>    
        <one-to-one name="referenceOrder" class="Order"
            property-ref ="billDetail">
        </one-to-one>    
    </class>
</hibernate-mapping>
The BillDetail entity now uses an identity id generation strategy.
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.association.one_to_one">
    <class name="Order" table="ORDER_DATA">

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

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

        <many-to-one name="billDetail" 
             foreign-key="ORDER_DATA_FK1" class ="BillDetail"
            cascade="all" unique="true" >
            <column name="bill_detail_id" ></column>
        </many-to-one>
    </class>
</hibernate-mapping> 
The tables created is as below:
create table BILL_DETAIL (
        BILL_DETAIL_ID integer not null auto_increment,
        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),
        bill_detail_id integer unique,
        primary key (ID)
    )
  alter table ORDER_DATA 
        add index ORDER_DATA_FK1 (bill_detail_id), 
        add constraint ORDER_DATA_FK1 
        foreign key (bill_detail_id) 
        references BILL_DETAIL (BILL_DETAIL_ID)
As can be seen Order_Data includes an additional column that allows it to hold references to the Bill_Data row. The appearance of the foreign key indicates the possibility of a many -to-one relation between Order_Data and Bill_Detail. The attribute "unique" ensures that is not the case.
Also in the BillDetail table, there is no reference to the Order_Data table directly. Hence the entity mapping in BillDetail is slightly different.Instead of the "column" attribute there is a "property-ref" attribute. This tells hibernate that the value for this field is  the row in Order_Data whose value for property "bill_Detail" is the same as this entity. If we try to access the property then
select
        billdetail0_.BILL_DETAIL_ID as BILL1_1_1_,
        billdetail0_.FULL_NAME as FULL2_1_1_,
        billdetail0_.PAN_CODE as PAN3_1_1_,
        billdetail0_.NET_COST as NET4_1_1_,
        order1_.ID as ID0_0_,
        order1_.CODE as CODE0_0_,
        order1_.bill_detail_id as bill3_0_0_ 
    from
        BILL_DETAIL billdetail0_ 
    left outer join
        ORDER_DATA order1_ 
            on billdetail0_.BILL_DETAIL_ID=order1_.bill_detail_id 
    where
        billdetail0_.BILL_DETAIL_ID=?
On executing the code to create an Order
public static void createOrder() {
    BillDetail billDetail = new BillDetail();
    billDetail.setFullName("Robin Varghese");
    billDetail.setNetCost(new BigDecimal("124.76"));
    billDetail.setPanCode("AKHY765");
    Order order = new Order();
    order.setCode("#4356");
    order.setBillDetail(billDetail);
    billDetail.setReferenceOrder(order);
        
    Session session = sessionFactory.openSession();
    Transaction transaction = session.beginTransaction();
    session.save(order);
    transaction.commit();
}
The queries executed are as below:
2563 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        BILL_DETAIL
        (FULL_NAME, PAN_CODE, NET_COST) 
    values
        (?, ?, ?)
...
2594 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        ORDER_DATA
        (CODE, bill_detail_id) 
    values
        (?, ?)
If we tried to refer the data from the Order side then the SQL queries fired are:
Order order = (Order) session.load(Order.class, 1);
System.out.println(order.getId());
System.out.println(order.getBillDetail().getFullName());
The logs indicate the following queries:
2516 [main] DEBUG org.hibernate.SQL  - 
    select
        order0_.ID as ID0_0_,
        order0_.CODE as CODE0_0_,
        order0_.bill_detail_id as bill3_0_0_ 
    from
        ORDER_DATA order0_ 
    where
        order0_.ID=?
...
2563 [main] DEBUG org.hibernate.loader.Loader  - loading entity: [com.associatio
n.one_to_one.BillDetail#1]
2563 [main] DEBUG org.hibernate.SQL  - 
    select
        billdetail0_.BILL_DETAIL_ID as BILL1_1_1_,
        billdetail0_.FULL_NAME as FULL2_1_1_,
        billdetail0_.PAN_CODE as PAN3_1_1_,
        billdetail0_.NET_COST as NET4_1_1_,
        order1_.ID as ID0_0_,
        order1_.CODE as CODE0_0_,
        order1_.bill_detail_id as bill3_0_0_ 
    from
        BILL_DETAIL billdetail0_ 
    left outer join
        ORDER_DATA order1_ 
            on billdetail0_.BILL_DETAIL_ID=order1_.bill_detail_id 
    where
        billdetail0_.BILL_DETAIL_ID=?
As can be seen when the BillDetail object is loaded, a join query is needed as Hibernate has no way of knowing if the association with OrderData exists for this BillDetail record.
The same query will be executed for the below code:
BillDetail billDetail = (BillDetail) session.load(BillDetail.class, 1);
System.out.println(billDetail.getBillDetailId());
System.out.println(billDetail.getReferenceOrder().getCode());
The result is :
3031 [main] DEBUG org.hibernate.SQL  - 
    select
        billdetail0_.BILL_DETAIL_ID as BILL1_1_1_,
        billdetail0_.FULL_NAME as FULL2_1_1_,
        billdetail0_.PAN_CODE as PAN3_1_1_,
        billdetail0_.NET_COST as NET4_1_1_,
        order1_.ID as ID0_0_,
        order1_.CODE as CODE0_0_,
        order1_.bill_detail_id as bill3_0_0_ 
    from
        BILL_DETAIL billdetail0_ 
    left outer join
        ORDER_DATA order1_ 
            on billdetail0_.BILL_DETAIL_ID=order1_.bill_detail_id 
    where
        billdetail0_.BILL_DETAIL_ID=?

No comments:

Post a Comment