How to delete a @ManyToMany association (joined link) without deleting the actual target entity

Question

I am working on a Spring-MVC application and I have many-to-many mapping in it. There are 3 tables, one is GroupAccount, memberjunction, GroupMembers. Now, when I delete a groupMember, I only want to remove the entries from GroupMembers and memberjunction corresponding to the memberId. I tried to use Cascade.Remove, it caused deletion in the GroupAccount's as well. I am posting my sql code, and what I have till now.

SQL code :

CREATE TABLE groupaccount
(
  groupid numeric NOT NULL,
  groupname character varying,
  adminusername character varying,
  CONSTRAINT groupid PRIMARY KEY (groupid)
)
CREATE TABLE memberjunction
(
  memberid integer NOT NULL,
  groupid numeric NOT NULL,
  CONSTRAINT membergroupid PRIMARY KEY (memberid, groupid),
  CONSTRAINT groupaccount_memberjunction_fk FOREIGN KEY (groupid)
      REFERENCES groupaccount (groupid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT groupmembers_memberjunction_fk FOREIGN KEY (memberid)
      REFERENCES groupmembers (memberid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
CREATE TABLE groupmembers
(
  memberid integer NOT NULL,
  musername character varying,
  accesslevel boolean DEFAULT true,
  CONSTRAINT memberid PRIMARY KEY (memberid)
)

GroupMembersDAOImpl deletion method :

@Override
public boolean removeGroupMember(int memberId) {
    session = sessionFactory.getCurrentSession();
    GroupMembers groupMembers = (GroupMembers) session.get(GroupMembers.class, memberId);
    if(!(groupMembers == null)){
        groupMembers.getGroupAccounts().clear();
        session.flush();
        session.delete(groupMembers);
        return true;
    } else {
        return false;
    }
}

GroupAccount entity :

@Entity
@Table(name="groupaccount")
public class GroupAccount {
 @Id
    @Column(name="groupid")
    @GeneratedValue(strategy = GenerationType.SEQUENCE,generator = "groupaccount_seq_gen")
    @SequenceGenerator(name = "groupaccount_seq_gen",sequenceName = "groupaccount_seq")
    private Long groupId;
 @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "memberjunction", joinColumns = {@JoinColumn(name = "groupid")},
                inverseJoinColumns = {@JoinColumn(name = "memberid")})
    private Set<GroupMembers> groupMembersSet = new HashSet<>();

    public void setGroupMembersSet(Set<GroupMembers> groupMembersSet){
        this.groupMembersSet = groupMembersSet;
    }

    public Set<GroupMembers> getGroupMembersSet(){
        return this.groupMembersSet;
    }
}

GroupMembers Entity :

@Entity
@Table(name="groupmembers")
public class GroupMembers {

 @Id
    @Column(name="memberid")
    @GeneratedValue(strategy = GenerationType.SEQUENCE,generator = "groupmembers_seq_gen")
    @SequenceGenerator(name = "groupmembers_seq_gen",sequenceName = "groupmembers_seq")
    private int memberid;
 @ManyToMany(mappedBy = "groupMembersSet")
    private Set<GroupAccount> groupAccounts = new HashSet<>();

    public void setGroupAccounts(Set<GroupAccount> groupAccounts){
        this.groupAccounts = groupAccounts;
    }

    public Set<GroupAccount> getGroupAccounts(){
        return this.groupAccounts;
    }
}

Latest Error Log :

org.postgresql.util.PSQLException: ERROR: update or delete on table "groupmembers" violates foreign key constraint "groupmembers_memberjunction_fk" on table "memberjunction"
  Detail: Key (memberid)=(2501) is still referenced from table "memberjunction".
    org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
    org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)

The above method fails to work, as it says, there is a reference to memberJunction. How can I query the memberJunction too and remove entries corresponding to the memberId. Any help would be nice. Thanks a lot.. :-)

Answer 1

You need to do the following:

for(GroupAccount groupAccount : groupMembers.getGroupAccounts()) {
    groupAccount.getGroupMembersSet().remove(this);
}
groupMembers.getGroupAccounts().clear();
session.flush();
session.delete(groupMembers);

Because you have a @ManyToMany association, the association table is not available at runtime. For this you can either:

1. Clear the GroupAccounts from GroupMembers prior to deleting the GroupMembers. make sure you flush so that you make sure the delete action doesn't prevent an update against on the deletable entity state.

2. You can replace the @ManyToMany with a @OneToMany association to a MemberJunction entity (whose PK/@Id is build from both the GroupMembers and GroupAccount). This way you can cascade the delete from GroupMembers to the MemberJunction mappedBy Set, because it will delete the association table rows, and not the actual GroupAccount entities.