Search This Blog

Sunday 18 December 2011

Adding columns to the join table

Previously we created a many to many relation between People and the chocolates they liked. This resulted in a "many people like many chocolates" relation. The relation data was mapped to a separate join table. Now we would like to hold some additional information such as date a person first tasted the chocolate and shop name from where the chocolate was eaten. (I know the example gets weirder and weirder but what the heck, I want to try this example out.)
This now means that the join table will have to hold information additional to the association details.
A simpler (and preferable) way to do this would be to treat the join table as an entity and have two one-to-many relations instead of one many-to-many.
The other approach would be to preserve the many to many association while mapping the join table columns as a component. We shall try this one now. The java class for the same would be as follows:
public class Chocolate {
    private Integer id;
    private String name;
    private String brand;
//setters and getters
}
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, String shopName) {
        final FirstTrialInfo firstTrialInfo = new FirstTrialInfo();
        firstTrialInfo.setChocolate(chocolate);
        firstTrialInfo.setFirstTaste(firstTryDate);
        firstTrialInfo.setShopName(shopName);
        firstTrialInfo.setPeople(this);
        this.getFavouriteChocolateDetails().add(firstTrialInfo);
    }

    public synchronized void removeChocolate(final Chocolate chocolate) {
        FirstTrialInfo firstTrialInfo = new FirstTrialInfo();
        firstTrialInfo.setChocolate(chocolate);
        firstTrialInfo.setPeople(this);
        this.getFavouriteChocolateDetails().remove(firstTrialInfo);
    }

//setters and getters
}
public class FirstTrialInfo {
    private People people;
    private Chocolate chocolate;
    private Date firstTaste;
    private String shopName;
//setters and getters
}
We need to override hashcode and equals for correct functioning in the Component class.
The mapping documents for the above classes is 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.many_to_many.moredata">
    <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> 
Person.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.many_to_many.moredata">
    <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_FK2" />
            <composite-element class="FirstTrialInfo">
                <parent name="people" />
                <many-to-one name="chocolate" class="Chocolate" column="CHOCOLATE_ID"
                    foreign-key="CHOCOLATE_FAN_FK1" not-null="true" />

                <property name="firstTaste" type="timestamp">
                    <column name="FIRST_TASTE_DATE" not-null="true" />
                </property>

                <property name="shopName" type="string">
                    <column name="SHOP_NAME" not-null="true" />
                </property>
            </composite-element>
        </set>
    </class>
</hibernate-mapping>
As can be seen above the People class now has a set of FirstTrialInfo objects. The chocolate has been mapped using a many-to-one element. Similarly the parent element gives a back reference to the People entity. The additional fields are placed as properties of this class.
The FirstTrialInfo objects are not entities but components and hence they do not have their own life-cycle. They exist only for the People entity. So insertion and removal is as straight as adding/removing from the set. However in this design the use of components has resulted in a loss of bi-directionality. The Chocolate class is not aware of the components and hence has no link to the fans.
On start up the tables created are as below:
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,
        FIRST_TASTE_DATE datetime not null,
        SHOP_NAME varchar(255) not null,
        primary key (FAN_ID, CHOCOLATE_ID, FIRST_TASTE_DATE, SHOP_NAME)
    )
    create table PEOPLE (
        ID integer not null auto_increment,
        NAME varchar(255),
        primary key (ID)
    )
    alter table CHOCOLATE_FAN 
        add index CHOCOLATE_FAN_FK2 (FAN_ID), 
        add constraint CHOCOLATE_FAN_FK2 
        foreign key (FAN_ID) 
        references PEOPLE (ID)
    alter table CHOCOLATE_FAN 
        add index CHOCOLATE_FAN_FK1 (CHOCOLATE_ID), 
        add constraint CHOCOLATE_FAN_FK1 
        foreign key (CHOCOLATE_ID) 
        references CHOCOLATE (ID)
I executed code to create a Fan and couple of chocolates (as in the previous posts)
static void create() {
    Chocolate chocolate1 = new Chocolate();
    chocolate1.setName("Eclairs");
    chocolate1.setBrand("Cadburys");

    Chocolate chocolate2 = new Chocolate();
    chocolate2.setName("Melody");
    chocolate2.setBrand("Parles");

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

    Session session = sessionFactory.openSession();
    Transaction t = session.beginTransaction();
    session.save(chocolate1);
    session.save(people1);
    session.save(chocolate2);
    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()
            + " and Chocolate2 saved with id " + chocolate2.getId());
}
On executing the above code the SQL generated is as follows:
2515 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        CHOCOLATE
        (NAME, BRAND) 
    values
        (?, ?)
...
2594 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        PEOPLE
        (NAME) 
    values
        (?)
...        
2609 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        CHOCOLATE
        (NAME, BRAND) 
    values
        (?, ?)        
...        
2640 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        CHOCOLATE_FAN
        (FAN_ID, CHOCOLATE_ID, FIRST_TASTE_DATE, SHOP_NAME) 
    values
        (?, ?, ?, ?)        
...
The Person with name Naina was created with id 2
Chocolate1 saved with id 2 and Chocolate2 saved with id 3

2 comments:

  1. Thank you very much!
    It's a rare example!

    ReplyDelete
  2. what if I want to add an extra column as a primary key of a third table, si it possible?

    ReplyDelete