title: JPA/Hibernate: ManyToMany delete relation on Join Table
body: <p><strong>Disclaimer:</strong> This is my first Java project; learning as I go.</p>
  
  <p><strong>Background:</strong> I've inherited a legacy database on which to build a new RESTful API. We're using <a href="http://elide.io" rel="nofollow noreferrer">Elide</a> with Spring Boot to provide a <a href="http://jsonapi.org/" rel="nofollow noreferrer">JSON API</a> compliant service.</p>
  
  <p><strong>Reference:</strong> <a href="https://gitlab.com/nerdstep/elide-test" rel="nofollow noreferrer">Example source code</a></p>
  
  <p><strong>Problem:</strong> We have entities with a many-to-many relationship to each other and themselves by way of a join table. Consider the followig schema:</p>
  
  <pre><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)
  );
  </code></pre>
  
  <p>Where the resource entities are modeled as follows:</p>
  
  <pre><code>public class Alpha implements Serializable {
  
      private String id;
      private String name;
      private Set&lt;Alpha&gt; alphas = new HashSet&lt;&gt;();
      private Set&lt;Bravo&gt; bravos = new HashSet&lt;&gt;();
  
      @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&lt;Alpha&gt; getAlphas() {
          return alphas;
      }
  
      @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
      @JoinTable(
              name = "RELATIONSHIP",
              joinColumns = @JoinColumn(name = "FROM_ID"),
              inverseJoinColumns = @JoinColumn(name = "TO_ID")
      )
      public Set&lt;Bravo&gt; getBravos() {
          return bravos;
      }
  
  }
  </code></pre>
  
  <p>And the relationship table:</p>
  
  <pre><code>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;
      }
  
  }
  </code></pre>
  
  <p>Now let's say we have an <code>Alpha</code> record <code>A1</code> with relationships to <code>A2</code>, <code>A3</code>, <code>B1</code>, and <code>B2</code>. First we delete the relationship to <code>A2</code>.</p>
  
  <p>From our API this would be a <code>DELETE</code> request to <code>http://localhost:9000/api/alphas/a1/relationships/alphas</code> with BODY</p>
  
  <pre><code>{
    "data": [
      {
        "type": "alphas", 
        "id": "a2" 
      }
    ]
  }
  </code></pre>
  
  <p>Behind the scenes Hibernates does what I'm expecting and generates the following SQL queries:</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>The key piece being <code>delete from relationship where from_id=? and to_id=?</code></p>
  
  <p>Now the problem arises when trying to delete the second <code>Alpha</code> relationship <code>A3</code>, in which Hibernate does almost the exact same sequence, except for the <code>DELETE</code> query which omits the <code>and to_id=?</code> from the query, i.e.</p>
  
  <pre><code>Hibernate: 
      delete 
      from
          relationship 
      where
          from_id=?
  </code></pre>
  
  <p>Which has the unintended consequence of deleting all other <code>A1</code> relationships in the table, i.e. <code>B1</code> and <code>B2</code>.</p>
  
  <p>So that is the crux of my problem. It seems like Hibernate is only seeing one other related <code>Alpha</code> record and therefore deciding to simplify the query by omitting the <code>and to_id</code> statement.</p>
  
  <p>I'm probably missing something terribly obvious! </p>
  
  <p>I should also point out that I attempted to use a composite key on the <code>relationship</code> table but to no avail.</p>