ManyToMany relationship with extra field in the joinTable

[Tadili Saad]

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

  import java.util.Set;
import java.util.HashSet;
 
import javax.persistence.*;
 
@Entity
public class Invoice implements java.io.Serializable {
	private long id;
	private Set<InvoiceLine> InvoiceLines = new HashSet<InvoiceLine>();
	
	@Id
	@GeneratedValue
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	
	@OneToMany(cascade={CascadeType.ALL})
	@JoinColumn(name="INVOICE_ID")
	public Set<InvoiceLine> getInvoiceLines() {
		return InvoiceLines;
	}
	public void setInvoiceLines(Set<InvoiceLine> InvoiceLines) {
		this.InvoiceLines = InvoiceLines;
	}
}
  

   import javax.persistence.*;
 
@Entity
public class InvoiceLine implements java.io.Serializable {
	private long id;
	private Product product;
	private int quantity;
	
	@Id
	@GeneratedValue
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	
	@ManyToOne
	public Product getProduct() {
		return product;
	}
	public void setProduct(Product product) {
		this.product = product;
	}
	
	public int getQuantity() {
		return quantity;
	}
	public void setQuantity(int quantity) {
		this.quantity = quantity;
	}
}
 

  import javax.persistence.*;
 
@Entity
public class Product implements java.io.Serializable {
	private long id;
	private double price;
	
	public Product() {
	}
	
	public Product(double price) {
		this.price = price;
	}
	
	@Id
	@GeneratedValue
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	
	public double getPrice() {
		return price;
	}
	public void setPrice(double price) {
		this.price = price;
	}
}
  

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]

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]

Check @IdClass annotation details.

[Tadili Saad]

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]

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