Hibernate Bi- Directional many to many mapping advice!

Question

hi all, i woundered if anyone might be able to help me out. I am trying to work out what to google for (or any other ideas!!) basically i have a bidirectional many to many mapping between a user entity and a club entity (via a join table called userClubs) I now want to include a column in userClubs that represents the role so that when i call user.getClubs() I can also work out what level access they have. Is there a clever way to do this using hibernate or do i need to rethink the database structure? Thank you for any help (or just for reading this far!!)

the user.hbm.xml looks a bit like

<set name="clubs" table="userClubs" cascade="save-update">
         <key column="user_ID"/>
         <many-to-many column="activity_ID"
           class="com.ActivityGB.client.domain.Activity"/>
</set>

the activity.hbm.xml part

<set name="members" inverse="true" table="userClubs" cascade="save-update">
        <key column="activity_ID"/>
        <many-to-many column="user_ID"
            class="com.ActivityGB.client.domain.User"/>
</set>

The current userClubs table contains the fields id | user_ID | activity_ID

I would like to include in there id | user_ID | activity_ID | role

and be able to access the role on both sides...

Answer 1

The easiest way would be to create an entity out of the join table.

you could use the two foreign keys to the club and user entities as the id (using <composite-id>), but i would advise adding a technical id column, and just set a unique constraint on the foreign keys (otherwise, the cascade when you save Club or User won't work. more info here : http://docs.jboss.org/hibernate/core/3.3/reference/en/html/mapping.html#mapping-declaration-compositeid).

If you name your new entity UserClub, you would need a one-to-many from User to UserClub and from Club to UserClub, and many-to-one from UserClub to Club and from UserClub to User.

You will have some refactoring to do on the java code but you won't have to change the db (apart from adding the id and role columns).