title: strange sql behavior when do deleting relation for many-to-many in JPA
body: <p>This is a question about Hibernate's generated sql about deleting one relationship under many-to-many mapping, not 'cascade' problem.</p>
  
  <p>I use JPA 2 and hibernate as its implementation.</p>
  
  <p>I have two models,  User and Role. One user can have many role, and one role can have many users, so they are many-to-many mapping: </p>
  
  <pre><code>@Entity
  class User{
      @Id Long id;
  
      @ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.REFRESH)
      @JoinTable(name = "user_role", inverseJoinColumns = @JoinColumn(name = "role_id"),
                  joinColumns = @JoinColumn(name = "user_id"))
      private List&lt;Role&gt; roles;
   }
  
  @Entity
  class Role{
      @Id Long id;
  
      @ManyToMany(fetch = FetchType.LAZY,cascade = CascadeType.REFRESH, mappedBy = "roles")
      private List&lt;User&gt; users; 
   }
  </code></pre>
  
  <p>and the mapping works well , hibernate auto create three tables for this mapping</p>
  
  <pre><code>table user
  table role
  table user_role
  </code></pre>
  
  <p>Now here is the problem, what I want is just remove one role from one user (not remove a user or a role, just one relation between one user and one role, means only need remove one record from the table <code>user_role</code>). Here is the code I tried:</p>
  
  <pre><code>public void removeOneRoleFromUser(long userId, long roleId){
      User user = userService.getById(userId);
      Role role = roleService.getById(roleId);
      user.getRoles().remove(role);     //here
      userService.update(user);
  }
  </code></pre>
  
  <p>when I execute this code it work, the role was removed the from the user indeed. But when I check the sql which hibernate generated for it, it's not what I expected, The hibernate generated sql is:</p>
  
  <pre><code>delete from user_role where user_id = {userId}
  insert into user_role values({user_id}, {role_id_not_removed}) 
  ...
  insert into user_role values({user_id}, {another_role_id_not_removed}) 
  </code></pre>
  
  <p><strong>So for deleting one role from one user, hibernate first delete all roles from the user, then add those role which should not be removed back to the user one by one.</strong></p>
  
  <p>And what I expect is just one sql sentence archive it:</p>
  
  <pre><code>delete from user_role where user_id = {userId} and role_id = {role_id}
  </code></pre>
  
  <p>I know there is some other ways I can archive this like introducing another  entity <code>UserRoleMapping</code> which mapping to the table <code>user_role</code>, then directly remove one UserRoleMapping instance will remove one role from one user; but I want to know is there any solution I can get the expect with the current solution.</p>