java - 为什么这个复合主键被视为唯一，我该如何修复它？

Question

我正在开发一个基本的 Spring Boot 零售商应用程序，用于跟踪产品、其制造商以及这些产品所属的类别。类别和产品之间存在多对多的关系。当我尝试保存已添加新产品的类别时，即使 (category_id, Product_id) 的组合是唯一的，我也会收到一条错误，指示我违反了唯一主键约束。关于为什么会出现这种情况以及如何解决它有什么想法吗？

类别

@Entity
public class Category {

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

    private String name;

    @ManyToMany
    @JoinTable(name="product_categories", 
    joinColumns = @JoinColumn(name="category_id"), 
    inverseJoinColumns = @JoinColumn(name="product_id"))
    private Set<Product> products;

    `<rest of class omitted>`

产品

@Entity
public class Product {

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

    private String name;
    private double price;

    @ManyToOne
    private Manufacturer manufacturer;

    @ManyToMany(mappedBy = "products")
    private Set<Category> categories;

    '<rest of class omitted>'

DevBootstrap

    Manufacturer sony = new Manufacturer("Sony");
    manufacturerRepository.save(sony);

    Category console = new Category("Game Console");
    categoryRepository.save(console);

    Category robot = new Category("Robot");
    categoryRepository.save(robot);

    Category electronics = new Category("Electronic Device");
    categoryRepository.save(electronics);

    Product ps4 = new Product("Playstation 4", 300, sony);
    ps4.getCategories().add(electronics);
    ps4.getCategories().add(console);
    productRepository.save(ps4);
    electronics.getProducts().add(ps4);
    console.getProducts().add(ps4);
    categoryRepository.save(electronics);
    categoryRepository.save(console);
    sony.getProducts().add(ps4);
    manufacturerRepository.save(sony);

    Product aibo = new Product("AIBO Robotic Dog", 150, sony);
    aibo.getCategories().add(electronics);
    aibo.getCategories().add(robot);
    productRepository.save(aibo);
    electronics.getProducts().add(aibo);
    robot.getProducts().add(aibo);
    System.out.println("Ids: " + electronics.getId() + ", " + aibo.getId());
    categoryRepository.save(electronics); // <- Error occurs here
    categoryRepository.save(robot);
    sony.getProducts().add(aibo);
    manufacturerRepository.save(sony);

错误

org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "PUBLIC.PRIMARY_KEY_5 ON PUBLIC.PRODUCT_CATEGORIES(CATEGORY_ID, PRODUCT_ID) VALUES 1"; SQL statement:
insert into product_categories (category_id, product_id) values (?, ?) [23505-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:457) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:427) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.get(DbException.java:205) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.get(DbException.java:181) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:103) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.mvstore.db.MVSecondaryIndex.checkUnique(MVSecondaryIndex.java:220) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.mvstore.db.MVSecondaryIndex.add(MVSecondaryIndex.java:196) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.mvstore.db.MVTable.addRow(MVTable.java:546) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.dml.Insert.insertRows(Insert.java:180) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.dml.Insert.update(Insert.java:132) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.CommandContainer.update(CommandContainer.java:133) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.Command.executeUpdate(Command.java:267) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:200) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:154) ~[h2-1.4.199.jar:1.4.199]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-3.2.0.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-3.2.0.jar:na]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
    ... 58 common frames omitted

Answer 1

您的代码中存在多个问题。

1. 您错误地使用了save。请注意CrudRepository.save method有一个返回值，文档说:

Use the returned instance for further operations as the save operation might have changed the entity instance completely.

您不需要在ManyToMany 两侧调用save 来保存关系。一侧就足够了。

您的关系管理非常不惯用。我强烈建议您向 Product 添加 addCategory 方法，该方法将在 Product.categories 和 Category.products 中添加条目

更好的是，重新考虑您的映射。对于一个玩具应用程序来说已经足够好了，但是您真的想要拥有 Category.products 集合吗？当您的数据库增长时，获取它会降低您的性能。