Search This Blog

Monday 19 December 2011

Creating a Ternary Relation -1

In the previous post we saw how to manage a many to many relation with join data. The same concept can be extended further to create a simple ternary relation. In the earlier example  the "shopName" attribute of the join table was a simple String. This could be changed to a Shop Entity instead.
A diagrammatic representation of the relation would be something similar to the below pic:
Database schema
The Entities in the example would now be
public class Chocolate {
    private Integer id;
    private String name;
    private String brand;
    
    @Override
    public int hashCode() {
        int hash = this.getName().hashCode();
        hash = hash * 17 + this.getBrand().hashCode();
        return hash;
    }

    @Override
    public boolean equals(Object obj) {
        boolean isEqual = false;
        if (obj instanceof Chocolate) {
            Chocolate chocolate = (Chocolate) obj;
            isEqual = chocolate.getName().equals(this.getName())
                    && chocolate.getBrand().equals(this.getBrand());
        }
        return isEqual;
    }
      //setter-getter methods
}
public class Shop {
    private Integer id;
    private String name;
    private String shopCode;
    
    @Override
    public int hashCode() {
        int hash = this.getName().hashCode();
        hash = hash * 17 + this.getShopCode().hashCode();
        return hash;
    }

    @Override
    public boolean equals(Object obj) {
        boolean isEqual = false;
        if (obj instanceof Shop) {
            Shop otherShop = (Shop) obj;
            isEqual = otherShop.getShopCode().equals(this.getShopCode());
        }
        return isEqual;
    }
      //setter-getter methods
}
The people class which includes the details of the ternary relation is :
public class People {
    private Integer id;
    private String name;
    private Set<FirstTrialInfo> favouriteChocolateDetails = new HashSet<FirstTrialInfo>();

    public synchronized void addChocolates(final Chocolate chocolate,
            Date firstTryDate, Shop shop) {
        final FirstTrialInfo firstTrialInfo = new FirstTrialInfo();
        firstTrialInfo.setChocolate(chocolate);
        firstTrialInfo.setFirstTaste(firstTryDate);
        firstTrialInfo.setShop(shop);
        firstTrialInfo.setPeople(this);
        this.getFavouriteChocolateDetails().add(firstTrialInfo);
    }

    public synchronized void removeChocolateFromShop(final Chocolate chocolate, final Shop shop) {
        FirstTrialInfo firstTrialInfo = new FirstTrialInfo();
        firstTrialInfo.setChocolate(chocolate);
        firstTrialInfo.setShop(shop);
        firstTrialInfo.setPeople(this);
        this.getFavouriteChocolateDetails().remove(firstTrialInfo);
    }

    @Override
    public int hashCode() {
        int hash = this.getName().hashCode();
        return hash;
    }

    @Override
    public boolean equals(Object obj) {
        boolean isEqual = false;
        if (obj instanceof People) {
            People people = (People) obj;
            isEqual = people.getName().equals(this.getName());
        }
        return isEqual;
    }
    //setter-getter methods
}
The FirstTrialInfo class acts as the component here.
public class FirstTrialInfo {
    private People people;
    private Chocolate chocolate;
    private Shop shop;
    private Date firstTaste;
    
    @Override
    public boolean equals(Object obj) {
        boolean isEqual = false;
        if (obj instanceof FirstTrialInfo) {
            FirstTrialInfo firstTrialInfo = (FirstTrialInfo) obj;
            isEqual = firstTrialInfo.getChocolate().equals(this.getChocolate())
                    && firstTrialInfo.getShop().equals(this.getShop())
                    && firstTrialInfo.getPeople().equals(this.getPeople());
        }
        return isEqual;
    }

    @Override
    public int hashCode() {
        int hash = this.getChocolate().hashCode();
        hash = hash * 17 + this.getPeople().hashCode();
        hash = hash * 41 + this.getShop().hashCode();
        return hash;
    }

    //setter-getter methods
}
The hibernate mappings for the entities is as below:
Chocolate.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.ternary.component">
    <class name="Chocolate" table="CHOCOLATE">
        <id name="id" type="integer">
            <column name="ID" />
            <generator class="identity" />
        </id>
        <property name="name" type="string">
            <column name="NAME" />
        </property>
            <property name="brand" type="string">
            <column name="BRAND" />
        </property>

    </class>
</hibernate-mapping>
Shop.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.ternary.component">
    <class name="Shop" table="SHOP">
        <id name="id" type="integer">
            <column name="ID" />
            <generator class="identity" />
        </id>
        <property name="name" type="string">
            <column name="NAME" />
        </property>

        <property name="shopCode" type="string">
            <column name="SHOP_CODE" unique="true"/>
        </property>

    </class>
</hibernate-mapping>
The People.hbm.xml file provides details of the ternary association.
<?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.ternary.component">
    <class name="People" table="PEOPLE">
        <id name="id" type="integer">
            <column name="ID" />
            <generator class="identity" />
        </id>
        <property name="name" type="string">
            <column name="NAME" />
        </property>

        <set name="favouriteChocolateDetails" table="CHOCOLATE_FAN">
            <key column="FAN_ID" foreign-key="CHOCOLATE_FAN_FK1" />
            <composite-element class="FirstTrialInfo">
                <parent name="people" />
                <many-to-one name="chocolate" class="Chocolate" column="CHOCOLATE_ID"
                    foreign-key="CHOCOLATE_FAN_FK2" not-null="true" />
                <many-to-one name="shop" class="Shop" column="SHOP_ID"
                    foreign-key="CHOCOLATE_FAN_FK3" not-null="true" />
                <property name="firstTaste" type="timestamp">
                    <column name="FIRST_TASTE_DATE" not-null="true" />
                </property>
            </composite-element>
        </set>
    </class>
</hibernate-mapping>
The tables created by Hibernate on startup is :
create table CHOCOLATE (
        ID integer not null auto_increment,
        NAME varchar(255),
        BRAND varchar(255),
        primary key (ID)
    )
    create table CHOCOLATE_FAN (
        FAN_ID integer not null,
        CHOCOLATE_ID integer not null,
        SHOP_ID integer not null,
        FIRST_TASTE_DATE datetime not null,
        primary key (FAN_ID, CHOCOLATE_ID, SHOP_ID, FIRST_TASTE_DATE)
    )
    create table PEOPLE (
        ID integer not null auto_increment,
        NAME varchar(255),
        primary key (ID)
    )
    create table SHOP (
        ID integer not null auto_increment,
        NAME varchar(255),
        SHOP_CODE varchar(255) unique,
        primary key (ID)
    )
    alter table CHOCOLATE_FAN 
        add index CHOCOLATE_FAN_FK3 (SHOP_ID), 
        add constraint CHOCOLATE_FAN_FK3 
        foreign key (SHOP_ID) 
        references SHOP (ID)
    alter table CHOCOLATE_FAN 
        add index CHOCOLATE_FAN_FK1 (FAN_ID), 
        add constraint CHOCOLATE_FAN_FK1 
        foreign key (FAN_ID) 
        references PEOPLE (ID)
    alter table CHOCOLATE_FAN 
        add index CHOCOLATE_FAN_FK2 (CHOCOLATE_ID), 
        add constraint CHOCOLATE_FAN_FK2 
        foreign key (CHOCOLATE_ID) 
        references CHOCOLATE (ID)
The component manages the ternary relation. The code to create one such relation would be as below:
static void create() {
    Chocolate chocolate1 = new Chocolate();
    chocolate1.setName("Eclairs");
    chocolate1.setBrand("Cadburys");
    Chocolate chocolate2 = new Chocolate();
    chocolate2.setName("Melody");
    chocolate2.setBrand("Parles");

    Shop shop = new Shop();
    shop.setName("Chocolate Factory");
    shop.setShopCode("#4555");

    People people1 = new People();
    people1.setName("Naina");
    people1.addChocolates(chocolate1, new Date(), shop);

    Session session = sessionFactory.openSession();
    Transaction t = session.beginTransaction();
    session.save(chocolate1);
    session.save(shop);
    session.save(people1);
    session.save(chocolate2);//no association to any entity
    t.commit();
    System.out.println("The Person with name " + people1.getName()
            + " was created with id " + people1.getId());
    System.out.println("Chocolate1 saved with id " + chocolate1.getId()
            + " for shop " + shop.getName()
            + " and Chocolate2 saved with id " + chocolate2.getId());
}
The logs indicate successful creation:
3157 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        CHOCOLATE
        (NAME, BRAND) 
    values
        (?, ?)
...
3344 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        SHOP
        (NAME, SHOP_CODE) 
    values
        (?, ?)
...
3438 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        PEOPLE
        (NAME) 
    values
        (?)
...
3438 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        CHOCOLATE
        (NAME, BRAND) 
    values
        (?, ?)
...
3516 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        CHOCOLATE_FAN
        (FAN_ID, CHOCOLATE_ID, SHOP_ID, FIRST_TASTE_DATE) 
    values
        (?, ?, ?, ?)
The Person with name Naina was created with id 1
Chocolate1 saved with id 1 for shop Chocolate Factory and Chocolate2 saved with 
id 2

3 comments:

  1. Thanks a lot for your post. It helps me too much.

    ReplyDelete
  2. Hi Robin Varghese,
    This is one good post with ternary relations but did you ever practically face such need and in such case what is the practical approach used, is it good to create a new entity or just use the map collection map-key-many-to-many field in XML?

    ReplyDelete
    Replies
    1. Hi,
      I never had to use this case in a real project.
      If you do have it your project, than the Map option seems simpler, but then you cannot extend the model when your join table grows to include more data. The Component is more work but a more extensible model too.

      Delete