aspose file tools*
The moose likes Object Relational Mapping and the fly likes ManyToMany relationship with extra field in the joinTable    Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "ManyToMany relationship with extra field in the joinTable    " Watch "ManyToMany relationship with extra field in the joinTable    " New topic
Author

ManyToMany relationship with extra field in the joinTable

Tadili Saad
Greenhorn

Joined: Sep 17, 2008
Posts: 20
Hi,

I wanted to make a ManyToMany unidirectional relationship using these two entities.

Invoice (id)
Product (id, price)

The problem is that I want in the resulting joinTable another field named QUANTITY

I don’t think there is a way to obtain a join table with an extra field from a ManyToMany relationship, am I right ?

So I decided to add an InvoiceLine table

With Invoice and InvoiceLine tables having a OneToMany unidirectional relationship.
And InvoiceLine and Product tables having a ManyToOne unidirectional relationship.

This is the code I made







The problem is that I want the InvoiceLine table to have a composite primary key (from the two foreign keys in the table invoice_id and product_id).
Can anyone tell me how I can do this.

Thanks.
Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper

Joined: Aug 26, 2006
Posts: 4968
    
    1

This is a common problem.

Why not just make a third table, and do two one to many relationships on each side? That solves the problem.

-Cameron McKenzie
Lalit Bhatt
Ranch Hand

Joined: Dec 27, 2007
Posts: 69

Check @IdClass annotation details.


Java-JavaEE Hibernate Spring Software Development Business Process Analysis
Tadili Saad
Greenhorn

Joined: Sep 17, 2008
Posts: 20
Cameron Wallace McKenzie wrote:This is a common problem.

Why not just make a third table, and do two one to many relationships on each side? That solves the problem.

-Cameron McKenzie


Hi

This is what I made (see the example above). The problem is that I can't seem to be able to

Tadili Saad wrote:
The problem is that I want the InvoiceLine table to have a composite primary key (from the two foreign keys in the table invoice_id and product_id).




Tadili Saad
Greenhorn

Joined: Sep 17, 2008
Posts: 20
Lalit Bhatt wrote:Check @IdClass annotation details.


Hi

The InvoiceLine class doesn't have a reference to the Invoice class (it's a unidirectionnal ManyToOne relationship). Even if I make it bidirectionnal by adding a reference to the Invoice class in the InvoiceLine class I don't get the result I want because in the database the primary keys (id) of Invoice and Product doesn't migrate in the InvoiceLine table. I just get a foreign key of type VARBINARY.

What I want in the invoiceLine table is

id
quantiy
product_id
invoice_id
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: ManyToMany relationship with extra field in the joinTable