jQuery in Action, 2nd edition*
The moose likes Object Relational Mapping and the fly likes Hibernate - Annotations - many to many association Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate - Annotations - many to many association" Watch "Hibernate - Annotations - many to many association" New topic
Author

Hibernate - Annotations - many to many association

Gilbert johnson
Ranch Hand

Joined: Jul 10, 2005
Posts: 45
Hello,
I am using Spring,Hibernate Annotations based approach.
I have two entities. Product and Retailer which have a many to many association.
A product can belong to MANY retailers
A retailer can have MANY products

So I have a join table retailer_product which has the following columns
productId - referring to Product Table (id)
retailerId - referring to Retailer Table (id)
price

So how do I map these associations. This is what I have so far that doesnt work.
Product.java
@Entity
@Table(name = "product")
public class Product {
@OneToMany(mappedBy = "product")
private Set<ProductRetailers> productRetailers = new HashSet<ProductRetailers>(0);
}
********************************************************************************
Retailer.java
@Entity
@Table(name = "retailer")
public class Retailer {
@OneToMany(mappedBy = "retailer")
private Set<ProductRetailers> productRetailers = new HashSet<ProductRetailers>(0);
}
*******************************************************************************
@Entity
@Table(name = "retailer_product")
public class RetailerProduct {
@ManyToOne
@JoinColumn(name = "retailerId", referencedColumnName = "id")
private Retailer retailer;

@ManyToOne
@JoinColumn(name = "productId", referencedColumnName = "id")
private Product product;
}
******************************************************************************
Obviously there is some problem here and it doesnt work.
What I want to be able to do is retrieve all retailers that sell a particular product
and find all products that are sold by a particular retailer.

Can anyone help me with this. Will really appreciate it.
Thanks a lot in advance
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

I don't think you need the RetailerProduct object. I have something somewhat similar. I have Product and ProductOffer. A Product can have many ProductOffer(s) and a ProductOffer can be associated to many Products. My mapping looks like this:





Letting Hibernate/JPA create the tables for me I get a Product_ProductOffer table which consist of 2 cols, product_id and product_offer_id. Is this the direction you were wanting to go?


GenRocket - Experts at Building Test Data
Gilbert johnson
Ranch Hand

Joined: Jul 10, 2005
Posts: 45
Hey Greg,
Thanks for your detailed response. The approach you mentioned would work fine if the joining table did not have any extra columns. Like in your case Product_ProductOffer table consist of only 2 cols, product_id and product_offer_id.

But in my case the association table consists of an extra column (price) apart from the two foreign keys.
productId
retailerId
price

So from what I've read, I need to create a seperate class RetailerProduct and do two oneToMany associations. But I'm not able to get that to work.

Thanks
Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper

Joined: Aug 26, 2006
Posts: 4968
    
    1

Yeah, you're definitely hitting a many-to-many association here.

On my website, I map a many to many relationship of this type:



This is as simple as you can get.

The annotations for the two classes then looks something like this:





In doing so, you DO NOT need to introduce any new class into your domain model for the join table, although a join table will exist in the database. Here's the object model:




Check out the tutorial for more information. Let me know if it helps.

Tutorial on Mapping Many To Many Associations with Hibernate and JPA Annotations

-Cameron McKenzie (My SCJP Blog)
[ July 20, 2008: Message edited by: Cameron Wallace McKenzie ]
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Cameron,

what about the fact that he has a price column in the join table? My first thought was bad design but I think its because there is a price that is determined based on the Product - Retailer combination.
Pedro Erencia
Ranch Hand

Joined: Apr 03, 2008
Posts: 70
Hi Gilbert,

you are using


but your class definition is:

public class RetailerProduct {

That will be a first problem i think..
Gilbert johnson
Ranch Hand

Joined: Jul 10, 2005
Posts: 45
Pedro - Yes that was something I did change

Cameron - Thankyou for the detailed response you provided. But as Gregg mentioned, I have an extra column in the association table. Like in your case, the join table has only two columns that refer to the two entities. But in my case the join table has an extra column apart from the two foreign keys.

Gregg - Yes you are right. The price is based on the combination of product and retailer.

While searching I came accross this site - and was able to implement this solution. Hope this helps someone else.
http://java-aap.blogspot.com/2006/04/hibernate-annotations-composite.html


I appreciate all the help everyone provided.
thankyou very much
Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper

Joined: Aug 26, 2006
Posts: 4968
    
    1

Wow...That's some sick JPA mappings.

-Cameron McKenzie
[ July 20, 2008: Message edited by: Cameron Wallace McKenzie ]
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Originally posted by Cameron Wallace McKenzie:
Wow...That's some sick JPA mappings.

-Cameron McKenzie


Yea, sick is one word for it. When it gets to this point I really start to wonder where the benefit is.
Gilbert johnson
Ranch Hand

Joined: Jul 10, 2005
Posts: 45
I actually do agree with both of you.
Its actually more of a hack to get it to work.

So does that mean - Hibernate does not support many to many if the join table has extra columns
Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper

Joined: Aug 26, 2006
Posts: 4968
    
    1

I like the actual website's description of the problem.

creates a 3th table to create the many to many...


3th? We need to create new words to describe the problem.

I actually thought about this a bit yesterday. It's not that unusual. Even my student/course scenario. What if you want to keep the grade for each student in each of their courses this way?

It's a neat scenario, but not unheard of, as you can see by the comments on the bottom of the page. Maybe it demonstrates the elegance of Hibernate and JPA in the fact that you can do it if you apply the framework/design creatively.

I do remember a CMP time when you wanted to do something, and the mapping tools simply said "we don't support that, and that's it."

I actually think I might add to my tutorial/chapter in my book about the many-to-many join column and add a little something like this. It scares me, but I also think it's kinda kewl.

-Cameron McKenzie
[ July 20, 2008: Message edited by: Cameron Wallace McKenzie ]
Boris Kirzner
Greenhorn

Joined: Jul 21, 2008
Posts: 1
Hi

I recently faced the same problem.
I post my solution here: http://boris.kirzner.info/blog/archives/2008/07/19/hibernate-annotations-the-many-to-many-association-with-composite-key/

Hope this helps
Boris Kirzner
[ July 21, 2008: Message edited by: Boris Kirzner ]
 
Consider Paul's rocket mass heater.
 
subject: Hibernate - Annotations - many to many association