多对多关系 - 在 MySQL 中获取双行

Question

就像标题所说的那样，我的项目中有很多对多的关系，当客户可以有很多优惠券时，反之亦然。为了完成这项工作，我在 MySQL 中制作了另一个表，其中包括优惠券 ID 和客户 ID(每行(，但不知何故，每次我向客户添加优惠券时，它都会将其表中的行加倍coupon_customer。 例如：

优惠券> ID 1

客户>ID 4

首先添加

现在我向同一客户添加了另一张优惠券(id 2(，结果如下：

第二次添加

我的代码：

客户：

@Entity
@Table(name = "customer")
public class Customer {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private int id;
@Column(name = "name")
private String name;
@Column(name = "password")
private String password;
@ManyToMany(fetch = FetchType.EAGER, cascade = { CascadeType.PERSIST, CascadeType.MERGE, CascadeType.DETACH,
CascadeType.REFRESH })
@JoinTable(name = "coupon_customer", joinColumns = @JoinColumn(name = "customer_id"), inverseJoinColumns = @JoinColumn(name = "coupon_id"))
private List<Coupon> coupons;
public Customer() {
}
public Customer(String name, String password) {
this.name = name;
this.password = password;
this.coupons = new ArrayList<Coupon>();
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@JsonIgnore
public List<Coupon> getCoupons() {
return coupons;
}
public void setCoupons(ArrayList<Coupon> coupons) {
this.coupons = coupons;
}
@Override
public String toString() {
return "Customer [id=" + id + ", name=" + name + ", password=" + password + "]";
}
}

息票：

@Entity
@Table(name = "coupon")
public class Coupon {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private int id;
@Column(name = "title")
private String title;
@Column(name = "start_date")
private Date startDate;
@Column(name = "end_date")
private Date endDate;
@Column(name = "amount")
private int amount;
@Enumerated(EnumType.STRING)
@Column(name = "type")
private CouponType type;
@Column(name = "message")
private String message;
@Column(name = "price")
private double price;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "company_id")
private Company company;
@ManyToMany(fetch = FetchType.LAZY, cascade = { CascadeType.PERSIST, CascadeType.MERGE, CascadeType.DETACH,
CascadeType.REFRESH })
@JoinTable(name = "coupon_customer", joinColumns = @JoinColumn(name = "coupon_id"), inverseJoinColumns = @JoinColumn(name = "customer_id"))
private List<Customer> customers;
public Coupon() {
}
public Coupon(String title, Date startDate, Date endDate, int amount, CouponType type, String message,
double price) {
this.title = title;
this.startDate = startDate;
this.endDate = endDate;
this.amount = amount;
this.type = type;
this.message = message;
this.price = price;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public Date getStartDate() {
return startDate;
}
public void setStartDate(Date startDate) {
this.startDate = startDate;
}
public Date getEndDate() {
return endDate;
}
public void setEndDate(Date endDate) {
this.endDate = endDate;
}
public int getAmount() {
return amount;
}
public void setAmount(int amount) {
this.amount = amount;
}
public CouponType getType() {
return type;
}
public void setType(CouponType type) {
this.type = type;
}
public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
@JsonIgnore
public Company getCompany() {
return company;
}
public void setCompany(Company company) {
this.company = company;
}
@JsonIgnore
public List<Customer> getCustomers() {
return customers;
}
public void setCustomers(List<Customer> customers) {
this.customers = customers;
}
@Override
public String toString() {
return "Coupon [id=" + id + ", title=" + title + ", startDate=" + startDate + ", endDate=" + endDate
+ ", amount=" + amount + ", type=" + type + ", message=" + message + ", price=" + price + "]";
}

客户控制器：

@RequestMapping(value = "/purchaseCoupon")
public ResponseEntity<CouponSystemResponse> purchaseCoupon(@RequestParam(value = "id") int id) {
try {
Coupon coupon = couponService.getCoupon(id);
getEntity().getCoupons().add(coupon); --> getEntity() gets the customer 
coupon.setAmount(coupon.getAmount() - 1);
customerService.updateCustomer(getEntity()); --> updates customer after purchase coupon
couponService.updateCoupon(coupon); --> update coupon after been purchased(amount -1)
.....

如果这有助于 MySQL 脚本：

DROP SCHEMA IF EXISTS `couponsystem`;
CREATE SCHEMA `couponsystem`;
use `couponsystem`;

DROP TABLE IF EXISTS `company`;
CREATE TABLE `company` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL UNIQUE,
`password` varchar(20) NOT NULL,
`email` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
);

DROP TABLE IF EXISTS `coupon`;
CREATE TABLE `coupon` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(20) NOT NULL UNIQUE,
`start_date` datetime DEFAULT NULL,
`end_date` datetime DEFAULT NULL,
`amount` int DEFAULT NULL,
`type` varchar(15) DEFAULT NULL,
`message` varchar(50) DEFAULT NULL,
`price` float DEFAULT NULL,
`company_id` int(11),
PRIMARY KEY (`id`),
KEY `FK_company_id` (`company_id`),
CONSTRAINT `FK_company_id` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL UNIQUE,
`password` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE `coupon_customer`(
`coupon_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
/*
PRIMARY KEY (`coupon_id`,`customer_id`), --> that's in comment only cause I got exception every time row doubles itself and tried looking for solutions
*/
CONSTRAINT `FK_coupon_id` FOREIGN KEY (`coupon_id`) REFERENCES `coupon` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

客户服务：

@Service
public class CustomerService {
@Autowired
CustomerRepository customerRepo;

.....
public void updateCustomer(Customer customer) {
customerRepo.save(customer);
}
.....

优惠券服务：

@Service
public class CouponService {
@Autowired
CouponRepository couponRepo;
......
public void updateCoupon(Coupon coupon) {
couponRepo.save(coupon);
}
......

奇怪的东西。就像它需要所有最后几行添加它们，然后添加另一行一样。我以为我有级联的东西，但无法做到这一点......感谢任何帮助。

Answer 1

首先，我会向coupon_customer表添加另一个约束，这是一个独特的组合，带有一个插入忽略conmand，它将跳过插入错误，它将为此类错误提供基本的数据库保护

ALTER TABLE coupon_customer ADD  UNIQUE KEY coupon_customer (coupon_id, customer_id);

插入页应为：

INSERT IGNORE INTO...

除此之外，生成查询的函数应该为每个键只接收一个参数，并生成最简单的查询。如果使用 select 构建的插入 js 或函数在带有数组的函数上工作，那么这些可能会产生错误，如您所描述的

public function add coupon($customer_id, $coupon_id) {
... 
$sql =  "INSERT IGNORE INTO coupon_customer VALUES (". $customer_id . ",". $coupon_id . ");" ;
... 
} 

Answer 2

您的Coupon_Customer主键应由两个字段(customer_id和coupon_id(组成。

查看代码，此表中没有任何主键。这是主要问题。

为了在 Spring Data JPA 中创建组合主键，您确实需要一个@Embeddable注释类，它将代表您的coupon_customer_id。

如下所示：

优惠券客户 ID.java

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Embeddable;
@Embeddable
public class CouponCustomerId implements Serializable {
@Column(name = "coupon_id")
private Long couponId;
@Column(name = "customer_id")
private Long customerId;
public CouponCustomerId(Long couponId, Long customerId) {
this.couponId = couponId;
this.customerId = customerId;
}
// getters and setters..
}

现在，您需要创建一个带有@EmbeddedId的 CouponCustomer 实体，该实体将表示您组合的主键。

优惠券客户.java

import java.util.ArrayList;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.ManyToOne;
import javax.persistence.MapsId;
import javax.persistence.OneToMany;
@Entity
public class CouponCustomer {
@EmbeddedId
private CouponCustomerId id;
@ManyToOne(fetch = FetchType.LAZY) // add your own ManyToOne configurations
@MapsId("couponId")
private Coupon coupon;
@ManyToOne(fetch = FetchType.LAZY) // add your own ManyToOne configurations
@MapsId("customerId")
private Customer customer;
// getters and setters..
}

现在，在您的Customer实体中，您必须将List<Coupon>更改为List<CouponCustomer>并将关系更改为@OneToMany。

客户.java

....
@OneToMany(mappedBy = "customer", fetch = FetchType.LAZY, cascade = {
CascadeType.PERSIST,
CascadeType.MERGE,
CascadeType.DETACH,
CascadeType.REFRESH
})
private List<CouponCustomer> coupons;
....

Coupon实体也是如此。

优惠券.java

....
@OneToMany(mappedBy = "coupon" fetch = FetchType.LAZY, cascade = {
CascadeType.PERSIST,
CascadeType.MERGE,
CascadeType.DETACH,
CascadeType.REFRESH
})
private List<CouponCustomer> customers;
....

现在，每次您向客户添加优惠券时，您只需要关联其ID。

如下所示：

@RequestMapping(value = "/purchaseCoupon")
public ResponseEntity < CouponSystemResponse > purchaseCoupon(@RequestParam(value = "id") int id) {
try {
Coupon coupon = couponService.getCoupon(id);
coupon.setAmount(coupon.getAmount() - 1);
couponService.updateCoupon(coupon); -->update coupon after been purchased(amount - 1)
CouponCustomer cc = new CouponCustomer();
// assuming that getEntity() gets your Customer
cc.setCoupon(coupon);
cc.setCustomer(getEntity();
cc.setId(new CouponCustomerId(coupon.getId(), getEntity().getId()));
couponCustomerService.save(cc);
.....

请记住，为了更新Coupon并在Coupon_customer中创建记录，您无需调用customerService.updateCustomer。

在

cc.setId(new CouponCustomerId(coupon.getId(), getEntity().getId()));
couponCustomerService.save(cc);

您正在使用组合主键 (coupon_id，customer_id( 创建coupon_customer表的记录。

希望这有帮助。