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.