代码之家  ›  专栏  ›  技术社区  ›  jdub

jpa/hibernate:manytomany删除联接表上的关系

  •  1
  • jdub  · 技术社区  · 7 年前

    免责声明: 这是我的第一个Java项目。

    背景: 我继承了一个遗留数据库,在这个数据库上构建一个新的RESTfulAPI。我们正在使用 Elide 带弹簧保护套 JSON API 合规服务。

    参考文献: Example source code

    问题: 通过一个联接表,我们拥有彼此之间以及彼此之间具有多对多关系的实体。考虑以下架构:

    CREATE TABLE ALPHA (
      ID VARCHAR(255),
      NAME VARCHAR(255),
      CONSTRAINT PK_ALPHA PRIMARY KEY (ID)
    );
    
    CREATE TABLE BRAVO (
      ID VARCHAR(255),
      NAME VARCHAR(255),
      CONSTRAINT PK_BRAVO PRIMARY KEY (ID)
    );
    
    CREATE TABLE RELATIONSHIP (
      ID INT AUTO_INCREMENT,
      FROM_ID VARCHAR(255),
      TO_ID VARCHAR(255)
    );
    

    其中,资源实体建模如下:

    public class Alpha implements Serializable {
    
        private String id;
        private String name;
        private Set<Alpha> alphas = new HashSet<>();
        private Set<Bravo> bravos = new HashSet<>();
    
        @Id
        @Column(name = "ID", unique = true, nullable = false)
        @GeneratedValue(generator = "uuid")
        @GenericGenerator(name = "uuid", strategy = "uuid")
        public String getId() {
            return id;
        }
    
        public String getName() {
            return name;
        }
    
        @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
        @JoinTable(
                name = "RELATIONSHIP",
                joinColumns = @JoinColumn(name = "FROM_ID"),
                inverseJoinColumns = @JoinColumn(name = "TO_ID")
        )
        public Set<Alpha> getAlphas() {
            return alphas;
        }
    
        @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
        @JoinTable(
                name = "RELATIONSHIP",
                joinColumns = @JoinColumn(name = "FROM_ID"),
                inverseJoinColumns = @JoinColumn(name = "TO_ID")
        )
        public Set<Bravo> getBravos() {
            return bravos;
        }
    
    }
    

    关系表:

    public class Relationship implements Serializable {
    
        private Integer id;
        private String fromId;
        private String toId;
    
        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        public Integer getId() {
            return id;
        }
    
        @Column(name = "FROM_ID")
        public String getFromId() {
            return fromId;
        }
    
        @Column(name = "TO_ID")
        public String getToId() {
            return toId;
        }
    
    }
    

    现在假设我们有一个 Alpha 记录 A1 与…有关系 A2 , A3 , B1 B2 . 首先,我们删除与 A2 .

    从我们的API来看,这将是一个 DELETE 请求 http://localhost:9000/api/alphas/a1/relationships/alphas 带主体

    {
      "data": [
        {
          "type": "alphas", 
          "id": "a2" 
        }
      ]
    }
    

    在后台,Hibernates执行我预期的操作,并生成以下SQL查询:

    2018-07-13 09:48:23.687 DEBUG 7964 --- [nio-9000-exec-5] org.hibernate.SQL                        : 
    Hibernate: 
        select
            alpha0_.id as id1_0_,
            alpha0_.name as name2_0_ 
        from
            alpha alpha0_ 
        where
            alpha0_.id in (
                ?
            )
    2018-07-13 09:48:23.688 TRACE 7964 --- [nio-9000-exec-5] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [a1]
    2018-07-13 09:48:23.690 DEBUG 7964 --- [nio-9000-exec-5] org.hibernate.SQL                        : 
    Hibernate: 
        select
            alphas0_.from_id as from_id2_2_0_,
            alphas0_.to_id as to_id3_2_0_,
            alpha1_.id as id1_0_1_,
            alpha1_.name as name2_0_1_ 
        from
            relationship alphas0_ 
        inner join
            alpha alpha1_ 
                on alphas0_.to_id=alpha1_.id 
        where
            alphas0_.from_id=?
    2018-07-13 09:48:23.690 TRACE 7964 --- [nio-9000-exec-5] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [a1]
    2018-07-13 09:48:23.699 DEBUG 7964 --- [nio-9000-exec-5] org.hibernate.SQL                        : 
    Hibernate: 
        select
            alpha0_.id as id1_0_,
            alpha0_.name as name2_0_ 
        from
            alpha alpha0_ 
        where
            alpha0_.id in (
                ?
            )
    2018-07-13 09:48:23.699 TRACE 7964 --- [nio-9000-exec-5] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [a2]
    2018-07-13 09:48:23.721 DEBUG 7964 --- [nio-9000-exec-5] org.hibernate.SQL                        : 
    Hibernate: 
        delete 
        from
            relationship 
        where
            from_id=? 
            and to_id=?
    2018-07-13 09:48:23.722 TRACE 7964 --- [nio-9000-exec-5] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [a1]
    2018-07-13 09:48:23.724 TRACE 7964 --- [nio-9000-exec-5] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [a2]
    

    关键是 delete from relationship where from_id=? and to_id=?

    现在问题出现在删除第二个 阿尔法 关系 A3 ,其中hibernate执行几乎完全相同的序列,除了 删除 省略了 and to_id=? 从查询中,即

    Hibernate: 
        delete 
        from
            relationship 
        where
            from_id=?
    

    这会导致删除所有其他 A1 表中的关系,即 地下一层 地下二层 .

    这就是我问题的症结所在。似乎Hibernate只看到一个相关的 阿尔法 记录并因此决定通过省略 and to_id 声明。

    我可能错过了一些非常明显的东西!

    我还应该指出,我试图在 relationship 但没有用。

    1 回复  |  直到 7 年前
        1
  •  2
  •   StuPointerException    7 年前

    Hibernate Many-to-many

    @JoinTable

    Alpha-Alpha Alpha-Bravo