如何在Spring Boot中使用Hibernate/JPA向MySQL关联表插入数据

Question

我有以下模式

首先，它是正确的吗？例如，一个供应商有多个付款，并且供应商有一个身份，即他的信息。

我想要插入供应商支付的付款。 我目前正在发送一个具有以下元素的json文件。

{
   "date":"2017-02-17",
   "dueDate":"2018-02-17",
   "payable":2000,
   "paid":1000,
   "supplierId":1
}

现在在控制器中，我正在读取请求的JSON，提取供应商的 Id ，然后找到其类的对象，然后将其传递给 Payment 以添加付款。这是正确的方式吗？

case "payment": {

            logger.debug("The condition for the insertion of the payment.");
            try {
                logger.debug("Populating the request Map with the request to identify the type of .");
                requestMap = mapper.readValue(requestBody, Map.class);
            } catch (IOException e) {
                e.printStackTrace();
            }


            logger.debug("Identifying the key for the payment is it for customer or supplier.");
            if ((requestMap.containsKey("customerId")) || requestMap.containsKey("pending")){

                logger.debug("The request for the adding payment of the customer has been received.");
                Customer customer = customerRepository.findById(Integer.parseInt(requestMap.get("customerId")));

 //                    Payment payment = new Payment(requestMap.get("dueDate"), requestMap.get("pending"), customer, requestMap.get("data"))
 //                    paymentRepository.save()
            } else if (requestMap.containsKey("supplierId") || requestMap.containsKey("outstanding")){

            }
} 

这里有一个信息模型。

@Entity // This tells Hibernate to make a table out of this class
@Table(name = "Information")
public class Information {

private Integer id;

    private String name;

    private String contactNo;

    private String email;

    private String address;

    private Customer customer;

    private Supplier supplier;


    public Information() {
    }

    public Information(String name, String contactNo, String email, String address) {
        this.name = name;
        this.contactNo = contactNo;
        this.email = email;
        this.address = address;
    }

    public Information(Customer customer) {
        this.customer = customer;
    }

    public Information(Supplier supplier) {
        this.supplier = supplier;
    }

    /**
     *
     * @return
     */

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @Column(name="name", nullable = false)
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Column(name = "contact_no", unique = true, nullable = false)
    public String getContactNo() {
        return contactNo;
    }

    public void setContactNo(String contactNo) {
        this.contactNo = contactNo;
    }

    @Column(name = "email", unique = true, nullable = false)
    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @OneToOne(mappedBy = "information")
    @JsonBackReference(value = "customer-reference")
    public Customer getCustomer() {
        return customer;
    }

    public void setCustomer(Customer customer) {
        this.customer = customer;
    }

    @OneToOne(mappedBy = "information")
    @JsonBackReference(value = "supplier-reference")
    public Supplier getSupplier() {
        return supplier;
    }

    public void setSupplier(Supplier supplier) {
        this.supplier = supplier;
    }

    @Override
    public String toString() {
        return "Information{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", contactNo='" + contactNo + '\'' +
                ", email='" + email + '\'' +
                ", address='" + address + '\'' +
                '}';
    }

}

这里有一个客户，还有一些其他相关的关系，不用理会，我只想了解这个概念。

@Entity
@Table(name = "Customer") //maps the entity with the table. If no @Table is defined,
// the default value is used: the class name of the entity.
public class Customer {

    private Integer id;

    private Information information;

    private Set<Payment> payments;

    private Set<Sale> sales;

    private Set<Orders> orders;

    public Customer() {
    }

    public Customer(Information information) {
        this.information = information;
    }



    /**
     *
     * @return
     */

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @OneToOne(cascade = CascadeType.ALL ,fetch = FetchType.EAGER)
    @JoinColumn(name = "Information_id")
    @JsonManagedReference(value = "customer-reference")
    public Information getInformation() {
        return information;
    }

    public void setInformation(Information information) {
        this.information = information;
    }

    @OneToMany(mappedBy = "customer", cascade = CascadeType.ALL)
    public Set<Payment> getPayments() {
        return payments;
    }

    public void setPayments(Set<Payment> payments) {
        this.payments = payments;
    }

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "CustomerSales", joinColumns = @JoinColumn(name = "Customer_id",
            referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "Sale_id", referencedColumnName = "id"))
    public Set<Sale> getSales() {
        return sales;
    }

    public void setSales(Set<Sale> sales) {
        this.sales = sales;
    }

    @OneToMany(mappedBy = "customer", cascade = CascadeType.ALL)
    public Set<Orders> getOrders() {
        return orders;
    }

    public void setOrders(Set<Orders> orders) {
        this.orders = orders;
    }

    @Override
    public String toString() {
        return String.format("Customer{id = %d, " +
                "name = %s, " +
                "contact_no = %s, " +
                "address = %s, " +
                "email = %s}",
                id,information.getName(), information.getContactNo(), information.getAddress(), information.getEmail());
    }
}

这里是供应商模型。

@Entity
@Table(name = "Supplier")
public class Supplier {

    private Integer id;

    private Information information;

    private Set<Payment> payments;

    private Set<Orders> orders;

    private Set<Purchase> purchases;


    public Supplier() {
    }

    public Supplier(Information information) {
        this.information = information;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "InformationId")
    @JsonManagedReference(value = "supplier-reference")
    public Information getInformation() {
        return information;
    }

    public void setInformation(Information information) {
        this.information = information;
    }

    @OneToMany(mappedBy = "supplier", cascade = CascadeType.ALL)
    public Set<Payment> getPayments() {
        return payments;
    }

    public void setPayments(Set<Payment> payments) {
        this.payments = payments;
    }

    @OneToMany(mappedBy = "supplier", cascade = CascadeType.ALL)
    public Set<Orders> getOrders() {
        return orders;
    }

    public void setOrders(Set<Orders> orders) {
        this.orders = orders;
    }

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "SupplierPurchases", joinColumns = @JoinColumn(name = "Supplier_id",
            referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "Purchase_id", referencedColumnName = "id"))
    public Set<Purchase> getPurchases() {
        return purchases;
    }

    public void setPurchases(Set<Purchase> purchases) {
        this.purchases = purchases;
    }
}

最后但同样重要的是我们有支付模型。

@Entity
@Table(name="Payment")
public class Payment {

    private Integer id;

    private Date dueDate;

    private Long paid;// When you are in debit you have to pay to supplier

    private Long payable; // When you have to take from customer.

    private Date date;

    private Customer customer;

    private Supplier supplier;

    private PaymentMethod paymentMethod;

    public Payment() {
    }

    public Payment(Date dueDate, Long payable, Date date, Customer customer, PaymentMethod paymentMethod) {
        this.dueDate = dueDate;
        this.paid = payable;
        this.date = date;
        this.customer = customer;
        this.paymentMethod = paymentMethod;
    }

    public Payment(Date dueDate, Long paid, Date date, Supplier supplier, PaymentMethod paymentMethod) {
        this.dueDate = dueDate;
        this.paid = paid;
        this.date = date;
        this.supplier = supplier;
        this.paymentMethod = paymentMethod;
    }






    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @Column(nullable = false)
    public Date getDueDate() {
        return dueDate;
    }

    public void setDueDate(Date dueDate) {
        this.dueDate = dueDate;
    }

    public Long getPaid() {
        return paid;
    }

    public void setPaid(Long paid) {
        this.paid = paid;
    }

    public Long getPayable() {
        return payable;
    }

    public void setPayable(Long payable) {
        this.payable = payable;
    }


    @ManyToOne
    @JoinColumn(name = "CustomerId")//mappedBy indicates the entity is the inverse of the relationship.
    public Customer getCustomer() {
        return customer;
    }

    public void setCustomer(Customer customer) {
        this.customer = customer;
    }

    @ManyToOne
    @JoinColumn(name = "SupplierId")
    public Supplier getSupplier() {
        return supplier;
    }

    public void setSupplier(Supplier supplier) {
        this.supplier = supplier;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    @ManyToOne
    @JoinColumn(name = "PaymentMethodId")
    public PaymentMethod getPaymentMethod() {
        return paymentMethod;
    }

    public void setPaymentMethod(PaymentMethod paymentMethod) {
        this.paymentMethod = paymentMethod;
    }

    @Override
    public String toString() {
        return "Payment{" +
                "id=" + id +
                ", dueDate=" + dueDate +
                ", paid=" + paid +
                ", payable=" + payable +
                ", date=" + date +
                '}';
    }

还有没有更好的方法来执行这个任务？

感激不尽:)

- Root

---

如果你正在使用JPA，请不要考虑表结构。你正在处理对象。因此，关系是在那里建模的，并将由JPA / Hibernate映射。 - Jens

@Jens，您说的“我没有使用对象”是什么意思？您想要查看建模关系吗？是的，我非常了解表结构，它只是我的架构的一部分。 - Root

你已经定义了实体类，对吧？在那里你建立了对象之间的依赖关系。你能展示一下这些类吗？ - Jens

是的，我已经定义了实体类，但我没有分享代码，因为它非常混乱，并且包含与其他实体的关系。 - Root

你能否更改模式或它的遗留问题？ - wypieprz

Answer 1

我正在阅读请求JSON，提取供应商的Id，然后查找其类的对象，再将其传递给Payment以添加支付。这是正确的方式吗？

让Spring框架为您完成这项工作。首先稍微修改JSON请求：

{
   "date":"2017-02-17",
   "dueDate":"2018-02-17",
   "payable":2000,
   "paid":1000,
   "supplier":{"id":1}
}

我可以猜测你的REST控制器目前是什么样子的，但一个示例实现如下所示：

@RestController
@RequestMapping("/payment")
public class PaymentController {

    @Autowired
    private final PaymentRepository paymentRepository;

    public PaymentController(PaymentRepository paymentRepository) {
        this.paymentRepository = paymentRepository;
    }

    @RequestMapping(method = RequestMethod.POST)
    ResponseEntity<?> insert(@RequestBody Payment payment) {
        paymentRepository.save(payment);
        return ResponseEntity.accepted().build();

    }
}

这种方法会将请求主体直接映射到Payment对象，同时自动实例化Supplier字段，因此只需将其保存到存储库中即可。这很容易维护和简洁。

显然，这里没有考虑Customer和PaymentMethod关系，但您可以轻松扩展JSON请求以支持它们。

引用块中提到的模式一般上没问题，但通常取决于您想要实现什么目标。这肯定有效，但数据建模会影响性能和内存等方面的考虑因素：

- 插入 - Payment有单独的外键用于Customer和Supplier，实际上这两者几乎相同（Sales vs Purchases），因此最好有一个表Contractor来聚合客户和供应商以及他们的信息。 - 查询 - 请求一次性获取Customers和Suppliers的付款以及他们的Information，更不用说他们的Orders或Sales / Purchases，需要跨层级表执行多个连接。

在这里，您可以找到利用JPA继承的替代方法：

@Entity
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "ContractorType")
public class Contractor {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "name", nullable = false)
    private String name;

    @Column(name = "contact_no", unique = true, nullable = false)
    private String contactNo;

    @Column(name = "email", unique = true, nullable = false)
    private String email;

    private String address;
}

上述实体替换了“客户”、“供应商”和“信息”实体，创建以下表格，但仅聚合它们的共同部分：

Contractor
----------
contractor_type  <----  discriminator column for Customer and Supplier
id
name
contact_no          
email           
address         

The remaining, specific fields (sales, purchases, orders) need to be placed in derived entities let's say CustomerOperations and SupplierOperations. They create two join tables like before so nothing changes here.

@Entity
@DiscriminatorValue("C")
public class CustomerOperations extends Contractor {

    @OneToMany(mappedBy = "customer", cascade = CascadeType.ALL)
    private Set<Payment> payments;

    @OneToMany(mappedBy = "customer", cascade = CascadeType.ALL)
    private Set<Orders> orders;

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "CustomerSales", 
       joinColumns = @JoinColumn(name = "Customer_id", referencedColumnName = "id"), 
       inverseJoinColumns = @JoinColumn(name = "Sale_id", referencedColumnName = "id"))
    private Set<Sale> sales;
}

@Entity
@Table(name = "SupplierPurchases ")
@DiscriminatorValue("S")
public class SupplierPurchases extends Contractor {

    @OneToMany(mappedBy = "supplier", cascade = CascadeType.ALL)
    private Set<Payment> payments;

    @OneToMany(mappedBy = "supplier", cascade = CascadeType.ALL)
    private Set<Orders> orders;

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "SupplierPurchases", 
        joinColumns = @JoinColumn(name = "Supplier_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "Purchase_id", referencedColumnName = "id"))
    private Set<Purchase> purchases;
}

Such approach tends to work faster as the underlying SQL require less joins thus execution plan could be more efficient.

It's worth to mention JPA provides other inheritance strategies. Choosing the right one is not an easy task as each of them has advantages and drawbacks. If you are interested in nuances take a closer look here.

- wypieprz