Search This Blog

Friday 23 December 2011

Creating a Ternary Relation -2

In the absence of any extra information, a map can be used to represent a ternary relationship. Considering the same example of People, chocolates and the Shops selling them. The relation could be maintained as  a map:
class Person {
    Map<Shop, Chocolate> shopChocolate;
}
I had to get rid of the additional join columns from the previous example. The detailed example would involve the same entities as last time.
Only the Person entity would change here. I shall only add the code and hbm for the Person class.The hbm and java classes for Chocolate and Shop are same as before:
public class People {
    private Integer id;
    private String name;
    private Map<Chocolate,Shop> shopChocolates = new HashMap<Chocolate,Shop>();
    //chocolate can only occur once here

    public synchronized void addChocolates(final Chocolate chocolate, Shop shop) {
        shopChocolates.put(chocolate, shop);
    }

    public synchronized void removeChocolateEntry(final Chocolate chocolate) {
        shopChocolates.remove(chocolate);
    }
//hashcode(),equals() and setter-getters
}
<?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.map">
    <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>
        
        <map name ="shopChocolates" table ="CHOCOLATE_FAN">
            <key column ="FAN_ID" foreign-key="CHOCOLATE_FAN_FK1"/>
            <map-key-many-to-many column ="CHOCOLATE_ID" class ="Chocolate" 
                    foreign-key="CHOCOLATE_FAN_FK2"/>
            <many-to-many column ="SHOP_ID" class ="Shop" 
                    foreign-key="CHOCOLATE_FAN_FK3"/>
        </map>
    </class>
</hibernate-mapping>
In the hbm , the representation of the map element is interesting. The key was earlier mapped for the map using the <map-key> element. However here the map-key does not refer to just a column, but it refers to an entity. The column CHOCOLATE_ID represents a map key as well as an Entity association.
For such a mapping Hibernate provides us with the <map-key-many-to-many> element.
On start up the DDL generated is 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,
        SHOP_ID integer not null,
        CHOCOLATE_ID integer not null,
        primary key (FAN_ID, CHOCOLATE_ID)
    )
    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 code to add an association would be
static void create() {
    Chocolate chocolate1 = new Chocolate();
    chocolate1.setName("Eclairs");
    chocolate1.setBrand("Cadburys");

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

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

    Session session = sessionFactory.openSession();
    Transaction t = session.beginTransaction();
    session.save(chocolate1);
    session.save(shop);
    session.save(people1);
    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());
}
The logs indicate the records were created and the association established:
3078 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        CHOCOLATE
        (NAME, BRAND) 
    values
        (?, ?)
...
3125 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        SHOP
        (NAME, SHOP_CODE) 
    values
        (?, ?)
...
3188 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        PEOPLE
        (NAME) 
    values
        (?)
...
3235 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        CHOCOLATE_FAN
        (FAN_ID, CHOCOLATE_ID, SHOP_ID) 
    values
        (?, ?, ?)
The Person with name Vijay was created with id 1
Chocolate1 saved with id 1 for shop Chocolate Factory
Similarly the code to remove an association would be simply removing the entry from the map.
static void deleteElements() {
    Session session = sessionFactory.openSession();
    Transaction t = session.beginTransaction();
    People people1 = (People) session.get(People.class, 1);
    Chocolate chocolate1 = (Chocolate) session.get(Chocolate.class, 1);
    people1.removeChocolateEntry(chocolate1);// only removal from join table
    t.commit();
    session.close();
}
The queries generated for above code is :
  1. Load the Person record
    select
            people0_.ID as ID0_0_,
            people0_.NAME as NAME0_0_ 
        from
            PEOPLE people0_ 
        where
            people0_.ID=?
     
  2. Load the Chocolate record
    select
            chocolate0_.ID as ID2_0_,
            chocolate0_.NAME as NAME2_0_,
            chocolate0_.BRAND as BRAND2_0_ 
        from
            CHOCOLATE chocolate0_ 
        where
            chocolate0_.ID = ?
    
  3. Load the Chocolate-Shop Map(Shop was loaded fully not the Chocolates)
    select
            shopchocol0_.FAN_ID as FAN1_1_,
            shopchocol0_.SHOP_ID as SHOP2_1_,
            shopchocol0_.CHOCOLATE_ID as CHOCOLATE3_1_,
            shop1_.ID as ID3_0_,
            shop1_.NAME as NAME3_0_,
            shop1_.SHOP_CODE as SHOP3_3_0_ 
        from
            CHOCOLATE_FAN shopchocol0_ 
        left outer join
            SHOP shop1_ 
                on shopchocol0_.SHOP_ID=shop1_.ID 
        where
            shopchocol0_.FAN_ID=?
    
  4. To compare between the chocolates in the map, it loads the chocolate record fully (because of the hash-code implementation)
    select
            chocolate0_.ID as ID2_0_,
            chocolate0_.NAME as NAME2_0_,
            chocolate0_.BRAND as BRAND2_0_ 
        from
            CHOCOLATE chocolate0_ 
        where
            chocolate0_.ID = ?
    
  5. Delete the entry for this Chocolate from the Map
    delete 
        from
            CHOCOLATE_FAN 
        where
            FAN_ID = ? 
            and CHOCOLATE_ID = ?
    
This completes the delete process.

1 comment:

  1. Is there a way to do this with JPA annotations ?

    ReplyDelete