Additional field in the many-to-many junction table.

norm3000 · **Joined:** Mon May 08, 2006 3:18 pm **Posts:** 4

Hibernate version: 3.1.3

I'm trying to map a unidirectional many-to-many relationship where the junction table has an additional field that is a condition for the relationship. A simple example is an Adult to Child relationship.

Code:

    _____________        __________________
   |             |      |                  |       _____________
   |   ADULT     |      |   ADULT_CHILD    |      |             |
   |_____________|      |__________________|      |    CHILD    |
   |             |      |                  |      |_____________|
   | *ADULT_ID   | <--> | *ADULT_ID        |      |             |
   |  NAME|      |      | *CHILD_ID        | <--> | *CHILD_ID   |
   |_____________|      | RELATION         |      |  NAME       |
                        |__________________|      |_____________|

TABLE: ADULT

ADULT_ID NAME
-------- --------- 
1        Bob          
2        Jeanny          
3        Falco

TABLE: CHILD

CHILD_ID NAME
-------- ---------
1        Jimmy
2        Suzie          
3        Medb

TABLE: ADULT_CHILD

ADULT_ID CHILD_ID RELATION
-------- -------- ----------
1        2        Niece
2        2        Daughter
3        2        Daughter
1        1        Son
2        1        Nefew
3        1        Nefew

In my case, I only need unidirectional mapping (Adult to related kids) even though it's a many-to-many relationship. I'd like to be able to treat the relation as another attribute to the child class but actually have that mapped to the junction table. For example:

Load Objects:

Code:

Adult adult=(Adult) session.load(Adult.class,adultID);
Set related_kids=adult.getRelated_kids();
Iterator it=related_kids.iterator();
while (it.hasNext())
{
   Child thisChild=(Child) it.next();
   System.out.println("Related Kid: " + thisChild.getName() + " Relation: " + thisChild.getRelation());
}

Save Objects:

Code:

Adult adult=(Adult) session.load(Adult.class,new Integer(1));
Child child=new Child();

child.setName("Lexi");
child.setRelation("Friend");

adult.getRelated_kids().add(child);

session.saveOrUpdate(adult);

I've come close with the following mapping. Via the code above I can load the objects properly.

Code:

    <class name="Relation.Adult" table="ADULT">
       <id name="adult_id" type="int">
          <column name="ADULT_ID" />
          <generator class="native" />
       </id>
        <property name="name"/>
        <set name="related_kids" table="ADULT_CHILD" cascade="save-update,persist">
           <key column="ADULT_ID" />
           <many-to-many class="Relation.Child" column="CHILD_ID" unique="true" />
        </set>
    </class>
    
    <class name="Relation.Child" table="CHILD">
       <id name="child_id" type="int">
          <column name="CHILD_ID" />
          <generator class="native" />
       </id>
       <property name="name" />
       <join table="ADULT_CHILD" optional="true">
          <key column="CHILD_ID" />
          <property name="relation" />
       </join>
    </class>

The problem is that Saving fails. With the "cascade" option enabled, it tries to insert the child object first and then (presumably) establish the many-to-many relationship:

Code:

Hibernate: insert into CHILD (name, CHILD_ID) values (?, default)
Hibernate: insert into ADULT_CHILD (relation, CHILD_ID) values (?, ?)

The second insert fails because it's inserting into the junction table with only half of the primary key.

Am I missing anything here? Is there any easy way to do this? It would be nice if I could specify the additonal field in the many-to-many element.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

norm3000 wrote:

The second insert fails because it's inserting into the junction table with only half of the primary key.

Try adding not-null="true" to the <key> element.

norm3000 wrote:

Am I missing anything here? Is there any easy way to do this? It would be nice if I could specify the additonal field in the many-to-many element.

I do this using a mapped component. This solution requires an extra java class, but you can delegate from this class to the nested class if you want to hide that fact from the user. Here's the code:

Code:

<set name="Children" table="ADULT_CHILD">
  <key column="ADULT_ID" not-null="true"/>
  <composite-element class="ChildRelation">
    <many-to-many name="Child" class="Child"/>
    <property name="Relation" type="string"/>
  </composite-element>
</set>

Then adult.getChildren() returns a Set<ChildRelation>. ChildRelation needs accessors and mutators for the String "Relation" and the object "Child".

Does that meet your requirements?

norm3000 · **Joined:** Mon May 08, 2006 3:18 pm **Posts:** 4

Thanks much tenwit, the mapping you suggested appears to be working well. I did make some minor changes so I'll post my mapping here in case anyone else is interested.

Code:

<hibernate-mapping default-lazy="true">

    <class name="Relation.Adult" table="ADULT">
       <id name="adult_id" type="int">
          <column name="ADULT_ID" />
          <generator class="native" />
       </id>
        <property name="name"/>
      <set name="related_kids" table="ADULT_CHILD" cascade="all">
           <key column="ADULT_ID" not-null="true"/>
           <composite-element class="Relation.ChildRelation">
             <many-to-one name="child" class="Relation.Child" column="CHILD_ID" />
             <property name="relation" type="string"/>
           </composite-element>
      </set>
    </class>
    
    <class name="Relation.Child" table="CHILD">
       <id name="child_id" type="int">
          <column name="CHILD_ID" />
          <generator class="native" />
       </id>
       <property name="name" />
    </class>

</hibernate-mapping>

norm3000 · **Joined:** Mon May 08, 2006 3:18 pm **Posts:** 4

Hmm, I've actually noticed an interesting "side effect". The save is working well, although it seems to be doing an awful lot of inserts. Looks to me like it completely recreates the Adult to ChildRelation relationship each time (rather than just updating the records that need to be.

Below, I create a new child, and add a relation to an existing adult:

Code:

session=HibernateSessionFactory.getInstance().getSessionFactory().getCurrentSession();
tx=session.beginTransaction();

Child child=new Child();
child.setName("Lexi");

Adult adult=(Adult) session.load(Adult.class,new Integer(1));

ChildRelation cr=new ChildRelation();
cr.setChild(child);
cr.setRelation("Friend");

adult.getRelated_kids().add(cr);

session.saveOrUpdate(child);
session.flush();
tx.commit();

This results in the recreation of all ChildRelations in the Set (even though I just added one):

Code:

Creating session factory from XML
Hibernate: select adult0_.ADULT_ID as ADULT1_0_0_, adult0_.name as name0_0_ from ADULT adult0_ where adult0_.ADULT_ID=?
Hibernate: select related_ki0_.ADULT_ID as ADULT1_1_, related_ki0_.CHILD_ID as CHILD2_1_, related_ki0_.relation as relation1_, child1_.CHILD_ID as CHILD1_2_0_, child1_.name as name2_0_ from ADULT_CHILD related_ki0_ left outer join CHILD child1_ on related_ki0_.CHILD_ID=child1_.CHILD_ID where related_ki0_.ADULT_ID=?
Hibernate: insert into CHILD (name, CHILD_ID) values (?, default)
Hibernate: values identity_val_local()
Hibernate: delete from ADULT_CHILD where ADULT_ID=? and CHILD_ID=? and relation=?
Hibernate: insert into ADULT_CHILD (ADULT_ID, CHILD_ID, relation) values (?, ?, ?)
Hibernate: insert into ADULT_CHILD (ADULT_ID, CHILD_ID, relation) values (?, ?, ?)
Hibernate: insert into ADULT_CHILD (ADULT_ID, CHILD_ID, relation) values (?, ?, ?)
Hibernate: insert into ADULT_CHILD (ADULT_ID, CHILD_ID, relation) values (?, ?, ?)
Hibernate: delete from ADULT_CHILD where ADULT_ID=? and CHILD_ID=? and relation=?
Hibernate: insert into ADULT_CHILD (ADULT_ID, CHILD_ID, relation) values (?, ?, ?)
Hibernate: insert into ADULT_CHILD (ADULT_ID, CHILD_ID, relation) values (?, ?, ?)
Hibernate: insert into ADULT_CHILD (ADULT_ID, CHILD_ID, relation) values (?, ?, ?)
Hibernate: insert into ADULT_CHILD (ADULT_ID, CHILD_ID, relation) values (?, ?, ?)

Even more interesting, when I load an adult, it does the same thing:

Code:

      session=HibernateSessionFactory.getInstance().getSessionFactory().getCurrentSession();
tx=session.beginTransaction();
      
adult=(Adult) session.load(Adult.class,new Integer(1));
tx.commit();

Resulting SQL:

Code:

Creating session factory from XML
Hibernate: select adult0_.ADULT_ID as ADULT1_0_0_, adult0_.name as name0_0_ from ADULT adult0_ where adult0_.ADULT_ID=?
Hibernate: select related_ki0_.ADULT_ID as ADULT1_1_, related_ki0_.CHILD_ID as CHILD2_1_, related_ki0_.relation as relation1_, child1_.CHILD_ID as CHILD1_2_0_, child1_.name as name2_0_ from ADULT_CHILD related_ki0_ left outer join CHILD child1_ on related_ki0_.CHILD_ID=child1_.CHILD_ID where related_ki0_.ADULT_ID=?
Hibernate: delete from ADULT_CHILD where ADULT_ID=? and CHILD_ID=? and relation=?
Hibernate: insert into ADULT_CHILD (ADULT_ID, CHILD_ID, relation) values (?, ?, ?)
Hibernate: insert into ADULT_CHILD (ADULT_ID, CHILD_ID, relation) values (?, ?, ?)
Hibernate: insert into ADULT_CHILD (ADULT_ID, CHILD_ID, relation) values (?, ?, ?)
Hibernate: insert into ADULT_CHILD (ADULT_ID, CHILD_ID, relation) values (?, ?, ?)

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

That's behaviour that I normally associate with the <bag> element, rather than the <set> element. However, it may be a side-effect of using the composite-element element.

First, check that your accessor and mutator methods aren't doing anything strange (code like "setValue(String val) { this.val = val.trim(); }" is going to cause problems...).

If that's all kosher, have a go at mapping ChildRelation independently. That is, change the related_kids mapping to be a simple many-to-one to ChildRelation, and make ChildRelation be a mapped class, with its own id (just ADULT_ID) and a many-to-one association to Child. If that fixes the multiple insert problem, then the issue is with hibernate. Have a look in JIRA for the problem, or add a new issue there.

P.S. If my first post helped, please consider rating it.

norm3000 · **Joined:** Mon May 08, 2006 3:18 pm **Posts:** 4

Thanks again for the suggestions Tenwit. I did what you suggested with a few small changes and it is working well now. Notably, I had to give the junction table it's own primary key rather than using a composite of the two foreign keys. I'll post my mapping for anyone that's interested:

Code:

<hibernate-mapping default-lazy="false">

    <class name="Relation.Adult" table="ADULT">
       <id name="adult_id" type="int" column="ADULT_ID" >
          <generator class="native" />
       </id>
        <property name="name"/>
      <set name="related_kids" cascade="all">
           <key column="ADULT_ID" not-null="true" />
            <one-to-many class="Relation.ChildRelation" />
      </set>
    </class>

    <class name="Relation.ChildRelation" table="ADULT_CHILD">
       <id name="ac_id" type="int">
          <column name="AC_ID" />
          <generator class="native" />
       </id>
      <property name="relation" />
      <many-to-one name="child" class="Relation.Child" column="CHILD_ID" cascade="all" />
   </class>

    <class name="Relation.Child" table="CHILD">
       <id name="child_id" type="int">
          <column name="CHILD_ID" />
          <generator class="native" />
       </id>
       <property name="name" />
    </class>

</hibernate-mapping>

This adds one additional class, but it's a minor change that I can live with. I can still easily get at the Related child through the Adult class and that's what I was looking for:

Code:

adult=(Adult) session.load(Adult.class,new Integer(1));
Set crs=adult.getRelated_kids();
Iterator crsIt=crs.iterator();
while (crsIt.hasNext())
{
   ChildRelation cr=(ChildRelation) crsIt.next();
   System.out.println("Child: " + cr.getChild().getName() + " Relation " + cr.getRelation());
}

selliott1 · **Joined:** Wed Jun 14, 2006 6:43 am **Posts:** 4

Hi, I am in the same situation as you and have found your posts very useful.
I am wondering why you needed to create a serperate primary key for the link table? Mainly as I am also coming to the same conclusion

I get the error:
Repeated column in mapping for entity: LinkVO column: INCL_IINT_ID (should be mapped with insert="false" update="false"

But since this is part of the primary key this cannot be done.

This seems a bit painful, ie. having to change the database to get the mapping to work. I have a lot of these link tables and didn't really want to alter each one.

Cheers
Stephen