I have a Table Orders which contains a List of Products. For each of the Products corresponding to the specific Order I need to store a List of Addresses where individual product(per Order per Product basis) should be delivered to
+----------------+------------+
| Order_ID | Product_ID |
+----------------+------------+
| 1 | 1000 |
| 2 | 1000 |
| 2 | 1001 |
+----------------+------------+
So I have this table which is a many to many on Orders and Products what I need is to map each of the entries in the above table to a List of Addresses. So I need something like
|PK|Order_ID |Product_ID |
| 1| 1 | 1000 |
| 2| 2 | 1000 |
| 3| 2 | 1001 |
and to map an entry in the above table to a list of Addresses I need another many to many table on the above PK and Address ID(primary key of my address table)
|PK_Order_Product |Address_ID|
| 1 | 1 |
| 2 | 1 |
| 2 | 3 |
Here PK_Order_Product is the foreign key on the previous table's primary key(PK) I am using JPA for persisting into my MYSQL DB Please help me with a code snippet if my design is correct.How to do this in Java using JPA annotations
This is actually a good question that is worth some research and experimenting. There are many ways to do the mapping. Coming up with a better design would actually depend on your application needs. But here is how I think would be an effective way to implement the mapping:
I'll have 3 separate entities for Order, Product and Address.
We will not implement the usual many-to-many relationship between the 2 entities, Order and Product, where each side has a collection of the other. Instead, I'll create another entity to represent the relationship between Order and Product, and let's name it ProductOrder. Here's how their relationships are mapped:
Order has one-to-many relationship with ProductOrder.ProductOrder has many-to-one relationship with Order.Product has one-to-many relationship with ProductOrder.ProductOrder has many-to-one relationship with Product.ProductOrder's primary key will be composed of the primary key of Order and primary key of Product - so this will be a composite key. Therefore we will need to use @IdClass to map composite keys.
Now, here's the trick to achieving the many-to-many within a many-to-many relationship:
ProductOrder has many-to-many relationship with Address.
See sample codes for each entity mentioned above:
ORDER ENTITY
@Entity
@Table(name = "ORDERS")
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ORDER_ID")
private Long id;
private int quantity;
@OneToMany(mappedBy = "order")
private List<ProductOrder> productOrderList = new ArrayList<ProductOrder>();
...
}
PRODUCT ENTITY
@Entity
@Table(name="PRODUCT")
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "PRODUCT_ID")
private Long id;
private String name;
@OneToMany(mappedBy = "product")
private List<ProductOrder> productOrderList = new ArrayList<ProductOrder>();
...
}
ADDRESS ENTITY
@Entity
@Table(name="ADDRESS")
public class Address {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ADDRESS_ID")
private Long id;
private String state;
@ManyToMany(mappedBy = "addressList")
private List<ProductOrder> productOrderList = new ArrayList<ProductOrder>();
...
}
PRODUCTORDER ENTITY
@Entity
@Table(name="PRODUCT_ORDER")
@IdClass(ProductOrderId.class)
public class ProductOrder {
@Id
@ManyToOne
@JoinColumn(name="ORDER_ID")
private Order order;
@Id
@ManyToOne
@JoinColumn(name="PRODUCT_ID")
private Product product;
@ManyToMany
@JoinTable(name="PRODUCT_ORDER_ADDRESS",
joinColumns={@JoinColumn(name="ORDER_ID", referencedColumnName="ORDER_ID"),
@JoinColumn(name="PRODUCT_ID", referencedColumnName="PRODUCT_ID")},
inverseJoinColumns=@JoinColumn(name="ADDRESS_ID", referencedColumnName="ADDRESS_ID"))
private List<Address> addressList = new ArrayList<Address>();
...
}
@IdClass for ProductOrder entity
public class ProductOrderId {
private Long order;
private Long product;
...
}
Here's a sample code for creating the entities and persisting them:
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
Order order = new Order();
order.setQuantity(10);
em.persist(order);
Product product = new Product();
product.setName("Coffee");
em.persist(product);
Address address = new Address();
address.setState("CA");
em.persist(address);
ProductOrder productOrder = new ProductOrder();
productOrder.setOrder(order);
productOrder.setProduct(product);
productOrder.getAddressList().add(address);
address.getProductOrderList().add(productOrder);
em.persist(productOrder);
em.getTransaction().commit();
Here's how the schema was generated in MySQL database:
Hibernate:
create table ADDRESS (
ADDRESS_ID bigint not null auto_increment,
state varchar(255),
primary key (ADDRESS_ID)
)
Hibernate:
create table ORDERS (
ORDER_ID bigint not null auto_increment,
quantity integer not null,
primary key (ORDER_ID)
)
Hibernate:
create table PRODUCT (
PRODUCT_ID bigint not null auto_increment,
name varchar(255),
primary key (PRODUCT_ID)
)
Hibernate:
create table PRODUCT_ORDER (
ORDER_ID bigint,
PRODUCT_ID bigint,
primary key (ORDER_ID, PRODUCT_ID)
)
Hibernate:
create table PRODUCT_ORDER_ADDRESS (
ORDER_ID bigint not null,
PRODUCT_ID bigint not null,
ADDRESS_ID bigint not null
)
Hibernate:
alter table PRODUCT_ORDER
add constraint FK_sl39bwx60xjbvoiujpaes74ty
foreign key (ORDER_ID)
references ORDERS (ORDER_ID)
Hibernate:
alter table PRODUCT_ORDER
add constraint FK_n0i7uxq6rxsc0mcred1cds4m9
foreign key (PRODUCT_ID)
references PRODUCT (PRODUCT_ID)
Hibernate:
alter table PRODUCT_ORDER_ADDRESS
add constraint FK_kad6crei9lgrv1nuuuff42vs8
foreign key (ADDRESS_ID)
references ADDRESS (ADDRESS_ID)
Hibernate:
alter table PRODUCT_ORDER_ADDRESS
add constraint FK_hpx0e467dvpqi5i6kxmujns2b
foreign key (ORDER_ID, PRODUCT_ID)
references PRODUCT_ORDER (ORDER_ID, PRODUCT_ID)