Mapping a link table with additional fields

richardgundersen · **Joined:** Fri Nov 24, 2006 10:33 am **Posts:** 42

Hello

I'd like to map a many-to-many relationship to a table I have BUT the linking table also holds additional information (about the relationship).

Does anyone know how to do this?

This is what I have:

[A]>---<[L]>---<[B]

This would be a fairly straightforward many-many mapping, but table L has another field (called 'state') which represents additional info about the relationship between A and B.

To further complicate matters, L also has a foreign key (which is nullable) to table C.

If someone could show me how to do this I would be very very grateful

Thanks!

Richard

andresgr · **Posted:** Tue Nov 28, 2006 8:13 am

I had this problem some time ago.

I first use the composite-element mapping:

<class name="Sector">
(...)
<set name="actividadesSector" table="ActividadSector" cascade="all" lazy="false">
<key column="id_sector" />
<composite-element class="ActividadSector">
<property name="activa"/>
<many-to-one name="actividad" column="id_actividad" class="Actividad" unique="true"
cascade="none" lazy="false"/>

</composite-element>
</set>
</class>

This defines an many-to-many link table called "ActividadSector" between Sector and Actividad tables.

But in the end, because i wanted id_actividad and id_sector belong to the same unique key and couldn't make it with this configuration i changed it to a more flexible approach: defined a new POJO called ActividadSector with the following mapping:

<class name="ActividadSector">
<id name="id">
<generator class="native"/>
</id>
<property name="activa"/>
<many-to-one name="sector" column="id_sector" not-null="true" unique-key="actividadsector"/>
<many-to-one name="actividad" column="id_actividad" not-null="true" unique-key="actividadsector" lazy="false" cascade="lock"/>
</class>

So i have all flexibility to add components, manage the unique-keys etc.

The only disadvantage is that you have a new object that represents the link between one table and other. It was no problem for me anyway...

wykoff · **Posted:** Tue Nov 28, 2006 8:28 am

It sounds like your situation is a classic one. In the Hibernate Reference under the Best Practices section, it says:

Quote:

Don't use exotic association mappings.
Good usecases for a real many-to-many associations are rare. Most of the time you need additional information
stored in the "link table". In this case, it is much better to use two one-to-many associations to an intermediate
link class. In fact, we think that most associations are one-to-many and many-to-one, you should
be careful when using any other association style and ask yourself if it is really neccessary.

andresgr · **Posted:** Tue Nov 28, 2006 8:32 am

Totally agree with this (from personal experience).

richardgundersen · **Joined:** Fri Nov 24, 2006 10:33 am **Posts:** 42

Hi all

Thanks for the replies - I've only just seen them because I've been away. Argh, I initially had a bit of a bad feeling about this, but I asked my DBAs and they said it was perfectly acceptable to store extra info in the link table since it's information about that specific relationship.

I'll try out andresgr's solution first, and if I can't get it to work then I guess it's back to the drawing board.

Thanks again - really appreciate your input

Richard

andresgr · **Posted:** Wed Nov 29, 2006 11:10 am

If my answer did help, please rate it.

Regards

richardgundersen · **Joined:** Fri Nov 24, 2006 10:33 am **Posts:** 42

Yup, you've given me plenty to think about, and I think it will help so I'll rate it :)

I think the composite-element of the set is the important thing here (am I right?) where you store the extra bits of info.

I'll try it out tomorrow at work (tired now...!)

Thanks again

wykoff · **Posted:** Thu Nov 30, 2006 5:41 am

richardgundersen wrote:

I initially had a bit of a bad feeling about this, but I asked my DBAs and they said it was perfectly acceptable to store extra info in the link table since it's information about that specific relationship.

Richard,

I don't understand your uneasiness. The document mentions that the case of putting extra information in the link table is perfectly normal and is often the case. It also says that in order to do this, you just need to make a one-to-many and many-to-one assocation with an entity representing the link.

This is exactly your situation, and the solution that andresgr suggested. So I don't understand your 'back to the drawing board' comment.

richardgundersen · **Joined:** Fri Nov 24, 2006 10:33 am **Posts:** 42

My uneasiness was just due to the fact that I've not come across this before, and I thought it 'sounded' like the sort of thing our DBAs might complain about.

As long as nobody minded, I was very happy to use the approach since it works great (in theory) for me.

The "back to the drawing board" comment was me being tired and fed up and not feeling totally confident that I would be able to get the suggested solution working.

I have no doubt it does work (and I have rated andresgr accordingly) - more that I doubt whether *I* will be able to get it working (can you hear the violins yet? :)

In actual fact, I've managed to find the time to start playing around with it today, so I may well be posting another reply soon. If you would like to help I would be very grateful!

-Richard

richardgundersen · **Joined:** Fri Nov 24, 2006 10:33 am **Posts:** 42

Right, here's my first attempt at implementing a composite-element. Please could someone take a look at my mapping and tell me why I'm getting the following error?

Code:

could not initialize a collection: [com.lsb.uk.mqs.value.ChecklistValue.checklistItemRecords#64]
........
ORA-00904: "CHECKLISTI0_"."NOTEID": invalid identifier

The column in my DB table is NOTE_ID, not NOTEID. So I'm not sure where Hibernate is getting that from.

Thanks!

Code:

    <class name="com.lsb.uk.mqs.value.ChecklistValue" table="CHECKLIST">
      <id name="checklistId" column="CHECKLIST_ID" unsaved-value="0">
         <generator class="sequence">
            <param name="sequence">checklist_seq</param>
         </generator>
      </id>         
      <property name="applicationId" column="APPLICATION_ID"/>
      <property name="adHocRequirements" column="AD_HOC_REQUIREMENTS"/>
      <property name="applicationException" column="APPLICATION_EXCEPTION"/>               
       
      <list name="checklistItemRecords" table="CHECKLIST_ITEM_RECORD" lazy="false">
         <key column="CHECKLIST_ID" />         
         <list-index column="CHECKLIST_ITEM_RECORD_ID"/>
          <composite-element class="com.lsb.uk.mqs.value.ChecklistItemRecordValue">
              <property name="checklistItemId"/>
              <property name="checklistId"/>          
              <property name="noteId"/>
            <property name="checked"/>
          </composite-element>         
         <!-- many-to-many class="com.lsb.uk.mqs.value.ChecklistItemRecordValue" column="CHECKLIST_ITEM_ID" lazy="false"/-->
      </list>        

       
    </class>       
      
    
    <class name="com.lsb.uk.mqs.value.ChecklistItemRecordValue" table="CHECKLIST_ITEM_RECORD">
      <composite-id unsaved-value="any">
           <key-property name="checklistItemId" column="CHECKLIST_ITEM_ID"/>
           <key-property name="checklistId" column="CHECKLIST_ID"/>                      
      </composite-id>            
      <property name="noteId" column="NOTE_ID"/>
      <property name="checked" column="CHECKED"/>            
    </class> 

richardgundersen · **Joined:** Fri Nov 24, 2006 10:33 am **Posts:** 42

For what it's worth, I ended up doing it the simple way - map the join table as a class on its own, as described here

http://www.hibernate.org/118.html#A10

It's basic, and its not as clever as using a composite element, but I ran out of time. And it works, so I'm happy.

Maybe when I've become more confident with Hibernate I'll revisit it and try again, but when you've got umpteen other problems to fix, sometimes the easy way is OK.