• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Hibernate - Annotations - many to many association

 
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Gilbert johnson
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author and cow tipper
Posts: 5009
1
Hibernate Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 5009
1
Hibernate Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Wow...That's some sick JPA mappings.

-Cameron McKenzie
[ July 20, 2008: Message edited by: Cameron Wallace McKenzie ]
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 5009
1
Hibernate Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
reply
    Bookmark Topic Watch Topic
  • New Topic