Mapping a many-to-many join table with extra column using JPA
It is not straightforward to realise a many-to-many association with JPA when in the join table there is at least an extra column. In this small tutorial I’m going to show how to design entity objects that will handle the many-to-many relation and which annotations are needed in order to fix a redundancy that we will see in the solution adopted in the following wiki.
This tutorial is a mix up of different sources. The first solution I’m going to show is the one suggested in a wiki.
Mapping a Join Table with Additional Columns (in a JPA pure style)
A frequent problem is that two classes have a ManyToMany relationship, but the relational join table has additional data. For example if Employee has a ManyToMany with Project but the PROJ_EMP join table also has an IS_TEAM_LEAD column. In this case the best solution is to create a class that models the join table. So an ProjectAssociation class would be created. It would have a ManyToOne to Employee and Project, and attributes for the additional data. Employee and Project would have a OneToMany to the ProjectAssociation. Some JPA providers also provide additional support for mapping to join tables with additional data.
Unfortunately mapping this type of model becomes more complicated in JPA because it requires a composite primary key. The association object’s Id is composed of the Employee and Project ids. The JPA spec does not allow an Id to be used on a ManyToOne so the association class must have two duplicate attributes to also store the ids, and use an IdClass, these duplicate attributes must be kept in synch with the ManyToOne attributes. Some JPA providers may allow a ManyToOne to be part of an Id, so this may be simpler with some JPA providers. To make your life simpler, I would recommend adding a generated Id attribute to the association class. This will give the object a simpler Id and not require duplicating the Employee and Project ids.
This same pattern can be used no matter what the additional data in the join table is. Another usage is if you have a Map relationship between two objects, with a third unrelated object or data representing the Map key. The JPA spec requires that the Map key be an attribute of the Map value, so the association object pattern can be used to model the relationship.
If the additional data in the join table is only required on the database and not used in Java, such as auditing information, it may also be possible to use database triggers to automatically set the data.
Example join table association object annotations
As you can see in this solution the ProjectAssociation class contains twice the information related to Employee and Project. As explained above, this is due to JPA specification. Googling I found another solution to this problem that allowed me to avoid the redundancy.
Hibernate annotations: The many-to-many association with composite key (in a pure JPA style without redundancy)
This post contains an evolution of another solution realized in 2006. Since things changed a bit in the meanwhile, the original solution was not the best.
Basically what the author is trying to do is to hold a relation between three item: Produc, Item and ProductItem.
Here is the last part of the post in which the author introduce the solution:
The database part is the same: we have three tables (item, product and product_item), two POJO classes, and two classes for a many-to-many association and its primary key. The main difference from Marsel’s solution is that I’m not using any kind of “fake” properties on ProductItem in order to reference Item and Product, but just a plain transient properties delegating to ProductItemPk.
Here is the source:
This solution is perfect from a model point of view. I used this solution in my project and together with Spring, Hibernate and Maven I’ve been able to generate the schema in an automatic fashion (hbm2dll plugin). The schema produced is exactly what you would expect. Unfortunately JPA doesn’t allow developer to use this configuration to work with inserts and updates. For instance if you have an Item object with few ProductItem and you perform an insert on the Item, ProductItem object contained in the list will not be inserted in the database. Same stuff happens for a Product and its ProductItem list. JPA in this case won’t help us anymore. The only way to make inserts and updates to work in cascade, we must recur to provider’s specific annotation. The source code below is the final evolution of the previous example. The JPA provider I used is Hibernate. Here is the code for the Product class, use the same annotation for the Item class as well and Hibernate will take care to insert/update ProductItem (if any) as well.
Since I only have experience with Hibernate I invite all the reader that have expertise with other JPA provider to reply to this post with the solution offered by other framework such as iBatis.
Thanks for have reading this tutorial and feel free to reply with comments.