许多到许多桌子里的许多到许多 [英] Many to Many inside Many to Many Table

查看：118
本文介绍了许多到许多桌子里的许多到许多的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我拥有包含产品列表的订单。对于与特定订单对应的每个产品，我需要存储地址列表，其中每个产品（每个订单的产品基准）应交付给

  + ---------------- + ------------ + 
 | Order_ID | Product_ID | 
 + ---------------- + ------------ + 
 | 1 | 1000 | 
 | 2 | 1000 | 
 | 2 | 1001 | 
 + ---------------- + ------------ +

因此，我有这张表格，它是订单和产品上的多对多表格，我需要映射每个上表中的条目添加到地址列表中。所以我需要像 

  | PK | Order_ID | Product_ID | 
 | 1 | 1 | 1000 | 
 | 2 | 2 | 1000 | 
 | 3 | 2 | 1001 |

并将上表中的条目映射到地址列表我需要另一个多对多表上面的PK和地址ID（我的地址表的主键）

  | PK_Order_Product | Address_ID | 
 | 1 | 1 | 
 | 2 | 1 | 
 | 2 | 3 |

这里PK_Order_Product是上表的主键（PK）的外键
我是使用JPA保存到我的MYSQL数据库中请使用代码片段来帮助我，如果我的设计是正确的。如何使用JPA注释在Java中执行此操作

解决方案这实际上是一个很值得研究和尝试的好问题。有很多方法可以进行映射。实现更好的设计实际上取决于您的应用需求。但这里是我认为如何实现映射的一种有效方法：

对于 Order ，产品和地址。



我们将不会实现两个实体 Order 和 Product 之间通常的多对多关系，其中每边都有另一个的集合。相反，我将创建另一个实体来表示 Order 和 Product 之间的关系，并将其命名为 ProductOrder 。以下是他们的关系如何映射：


  • 订单具有一对多的关系与 ProductOrder 。

  • ProductOrder 具有多对一的关系， code> Order 。

  • Product 与<$ c具有一对多关系$ c> ProductOrder 。

  • ProductOrder 与产品。



ProductOrder 的主要键将由 Order 主键和 Product 的主键组成 - 所以这将是一个组合键。因此，我们需要使用 @IdClass 来映射组合键。



现在，多对多关系中的多对多关系：

ProductOrder 与多对多关系具有多对多关系地址。



请参阅上述每个实体的示例代码：

ORDER ENTITY 

  @Entity 
 @Table（name =ORDERS ）
 public class Order {
 
 @Id 
 @GeneratedValue（strategy = GenerationType.IDENTITY）
 @Column（name =ORDER_ID）
私人长ID; 
 
 private int数量; 
 
 @OneToMany（mappedBy =order）
 private List< ProductOrder> productOrderList = new ArrayList< ProductOrder>（）; 
 ... 
}





产品实体 

  @Entity 
 @Table（name =PRODUCT） 
 public class Product {
 
 @Id 
 @GeneratedValue（strategy = GenerationType.IDENTITY）
 @Column（name =PRODUCT_ID）
 private Long ID; 
 
私人字符串名称; 
 
 @OneToMany（mappedBy =product）
 private List< ProductOrder> productOrderList = new ArrayList< ProductOrder>（）; 
 ... 
}





地址实体 

  @Entity 
 @Table（name =ADDRESS） 
 public class Address {
 
 @Id 
 @GeneratedValue（strategy = GenerationType.IDENTITY）
 @Column（name =ADDRESS_ID）
 private Long ID; 
 
私有字符串状态; 
 
 @ManyToMany（mappedBy =addressList）
私人列表< ProductOrder> productOrderList = new ArrayList< ProductOrder>（）; 
 ... 
}





PRODUCTORDER ENTITY 

  @Entity 
 @Table（name =PRODUCT_ORDER） 
 @IdClass（ProductOrderId.class）
 public class ProductOrder {
 
 @Id 
 @ManyToOne 
 @JoinColumn（name =ORDER_ID）
私人订单; 
 
 @Id 
 @ManyToOne 
 @JoinColumn（name =PRODUCT_ID）
私人产品产品; 
 
 @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>（）; 
 ... 
}





<对于ProductOrder实体@IdClass 

  public class ProductOrderId {
 
私人长期订单; 
私人长款产品; 
 ... 
}





<以下是创建实体并持久化它们的示例代码： 

  EntityManager em = emf.createEntityManager（）; 
 em.getTransaction（）。begin（）; 
 
订单=新订单（）; 
 order.setQuantity（10）; 
 em.persist（order）; 
 
产品产品=新产品（）; 
 product.setName（Coffee）; 
 em.persist（product）; 
 
地址=新地址（）; 
 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（）;





以下是在MySQL数据库中生成架构的方式： 

  Hibernate：
 create table ADDRESS（
 ADDRESS_ID bigint not null auto_increment，
 state varchar （255），
主键（ADDRESS_ID）
）
 Hibernate：
创建表ORDERS（
 ORDER_ID bigint不为null auto_increment，
数量整数非空，
主键（ORDER_ID）
）
 Hibernate：
 create table PRODUCT（
 PRODUCT_ID bigint不为null auto_increment，
 name varchar（255），
主键（PRODUCT_ID）
）
 Hibernate：
 create table PRODUCT_ORDER（
 ORDER_ID bigint，
 PRODUCT_ID bigint，
主键（ORDER_ID， PRODUCT_ID）
）
 Hibernate：
 create table PRODUCT_ORDER_ADDRESS（
 ORDER_ID bigint不为null，
 PRODUCT_ID bigint非空，
 ADDRESS_ID bigint不为空
）
 Hibernate：
 alter table PRODUCT_ORDER 
添加约束FK_sl39bwx60xjbvoiujpaes74ty 
外键（ORDER_ID）
引用ORDERS（ORDER_ID）
 Hibernate：
 alter table PRODUCT_ORDER 
添加约束FK_n0i7uxq6rxsc0mcred1cds4m9 
外键（PRODUCT_ID）
引用PRODUCT（PRODUCT_ID）
 Hibernate：
 alter table PRODUCT_ORDER_ADDRESS 
添加约束FK_kad6crei9lgrv1nuuuff42vs8 
外键（ADDRESS_ID）
引用ADDRESS（ADDRESS_ID）
 Hibernate：
 alter table PRODUCT_ORDER_ADDRESS 
添加约束FK_hpx0e467dvpqi5i6kxmujns2b 
外键（ORDER_ID，PRODUCT_ID）
引用PRODUCT_ORDER（ORDER_ID，PRODUCT_ID）


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)

这篇关于许多到许多桌子里的许多到许多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Java开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆