One to one association that is optional

In previous posts I have tried out one to one associations involving shared primary keys and foreign keys.In the that example an Order had a one to one relation between BillDetail.Consider the scenario wherein an Order is created separately from the BillDetail. The Bill Detail information is added at a later date. This means that the one-to-one relation between the two entities is optional and may not be always present.The relation would require that

• An Order can be created without BillDetail.
• One Order can be associated with only one BillDetail and vice versa
• Any BillDetail can be associated with only one and one Order

The relation is best achieved using a join table. This table can be used to hold the relation between an Order and a BillDetail.The tables would look as below:

As can be seen from above the Order_DATA_ID and BILL_DETAIL_ID are in reality foreign keys from the ORDER_DATA and BILL_DETAIL tables. Also the ORDER_DATA_ID is a primary key for this table. We need to enure that a BILL_DETAIL_ID only occurs once in a combination with ORDER_DATA_ID.
The hibernate mappings are as below:
OrderData.hbm.xml

<?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>

        <join table="ORDER_BILLING_LINK" optional="true">
            <key column="ORDER_DATA_ID" />
            <many-to-one name="billDetail" foreign-key="OB_LINK_FK_2"
                class="BillDetail" not-null="true" unique="true">
                <column name="BILL_DETAIL_ID"></column>
            </many-to-one>
        </join>
    </class>
</hibernate-mapping>

BillDetail.hbm.xml

<?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>    
        
        <join table="ORDER_BILLING_LINK" optional="true" inverse ="true">
            <key column ="BILL_DETAIL_ID"/>
            <many-to-one name="referenceOrder" foreign-key="OB_LINK_FK_1"
                class="Order" not-null="true" unique="true">
                <column name="ORDER_DATA_ID"></column>
            </many-to-one>
        </join>
    </class>
</hibernate-mapping>

The java classes remain the same. I created the tables using Hibernate's auto-create functionality. To get the desired SQL, I had to create the db in parts using create and update options. The final SQL generated 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_BILLING_LINK (
        ORDER_DATA_ID integer not null,
        BILL_DETAIL_ID integer,
        primary key (ORDER_DATA_ID)
    )
    create table ORDER_DATA (
        ID integer not null auto_increment,
        CODE varchar(255),
        primary key (ID)
    )
    alter table ORDER_BILLING_LINK 
        add index OB_LINK_FK_2 (BILL_DETAIL_ID), 
        add constraint OB_LINK_FK_2 
        foreign key (BILL_DETAIL_ID) 
        references BILL_DETAIL (BILL_DETAIL_ID)
    alter table ORDER_BILLING_LINK 
        add index OB_LINK_FK_1 (ORDER_DATA_ID), 
        add constraint OB_LINK_FK_1 
        foreign key (ORDER_DATA_ID) 
        references ORDER_DATA (ID)

The code to create an Order is as below:

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");
    Session session = sessionFactory.openSession();
    Transaction transaction = session.beginTransaction();
    session.save(order);
    order.setBillDetail(billDetail);
    billDetail.setReferenceOrder(order);
    session.save(billDetail);
    transaction.commit();
}

The creation logs indicate the below SQL was fired:

2391 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        ORDER_DATA
        (CODE) 
    values
        (?)
...
2422 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        BILL_DETAIL
        (FULL_NAME, PAN_CODE, NET_COST) 
    values
        (?, ?, ?)
...
2422 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        ORDER_BILLING_LINK
        (BILL_DETAIL_ID, ORDER_DATA_ID) 
    values
        (?, ?)

The order was created first. The BillDetail was created later and also assigned as to Order resulting in the second and third queries being fired.
To delete a bill_detail and de-link it from the Order_Data record

transaction = session.beginTransaction();
order.setBillDetail(null);
session.delete(billDetail);
transaction.commit();

The delete scripts is as below:

2859 [main] DEBUG org.hibernate.SQL  - 
    delete
    from
        ORDER_BILLING_LINK 
    where
        ORDER_DATA_ID=?
...
2890 [main] DEBUG org.hibernate.SQL  - 
    delete
    from
        BILL_DETAIL 
    where
        BILL_DETAIL_ID=?

Consider the code to load an Order record.

public static void loadOrderAndBillDetail() {
    Session session = sessionFactory.openSession();
    Order order = (Order) session.load(Order.class, 1);
    System.out.println(order.getId());
    System.out.println(order.getBillDetail().getFullName());
}

The query generated by Hibernate is:

    select
        order0_.ID as ID0_0_,
        order0_.CODE as CODE0_0_,
        order0_1_.BILL_DETAIL_ID as BILL2_1_0_ 
    from
        ORDER_DATA order0_ 
    left outer join
        ORDER_BILLING_LINK order0_1_ 
            on order0_.ID=order0_1_.ORDER_DATA_ID 
    where
        order0_.ID = ?

As can be seen Hibernate needs to check the join table to see if a linked Bill Detail is present. Otherwise it cannot decide whether to use a proxy or treat the association as null.
To display the name property of the BillDetail it then had to fire an additional select query:

    select
        billdetail0_.BILL_DETAIL_ID as BILL1_2_0_,
        billdetail0_.FULL_NAME as FULL2_2_0_,
        billdetail0_.PAN_CODE as PAN3_2_0_,
        billdetail0_.NET_COST as NET4_2_0_,
        billdetail0_1_.ORDER_DATA_ID as ORDER1_1_0_ 
    from
        BILL_DETAIL billdetail0_ 
    left outer join
        ORDER_BILLING_LINK billdetail0_1_ 
            on billdetail0_.BILL_DETAIL_ID=billdetail0_1_.BILL_DETAIL_ID 
    where
        billdetail0_.BILL_DETAIL_ID = ?

Similar is the case when we load a BillDetail. In fact the above query indicates the join executed so as to decide if the Order link in the BillDetail exists or is to be left null.