How to remove entity with ManyToMany relationship in JPA (and corresponding join table rows)?

Question

Let's say I have two entities: Group and User. Every user can be member of many groups and every group can have many users.

@Entity
public class User {
    @ManyToMany
    Set<Group> groups;
    //...
}

@Entity
public class Group {
    @ManyToMany(mappedBy="groups")
    Set<User> users;
    //...
}

Now I want to remove a group (let's say it has many members).

Problem is that when I call EntityManager.remove() on some Group, JPA provider (in my case Hibernate) does not remove rows from join table and delete operation fails due to foreign key constrains. Calling remove() on User works fine (I guess this has something to do with owning side of relationship).

So how can I remove a group in this case?

Only way I could come up with is to load all users in the group, then for every user remove current group from his groups and update user. But it seems ridiculous to me to call update() on every user from the group just to be able to delete this group.

score 105 · Accepted Answer · answered Jul 06 '09 at 20:15

105

The ownership of the relation is determined by where you place the 'mappedBy' attribute to the annotation. The entity you put 'mappedBy' is the one which is NOT the owner. There's no chance for both sides to be owners. If you don't have a 'delete user' use-case you could simply move the ownership to the Group entity, as currently the User is the owner.
On the other hand, you haven't been asking about it, but one thing worth to know. The groups and users are not combined with each other. I mean, after deleting User1 instance from Group1.users, the User1.groups collections is not changed automatically (which is quite surprising for me),
All in all, I would suggest you decide who is the owner. Let say the User is the owner. Then when deleting a user the relation user-group will be updated automatically. But when deleting a group you have to take care of deleting the relation yourself like this:

entityManager.remove(group)
for (User user : group.users) {
     user.groups.remove(group);
}
...
// then merge() and flush()

answered Jul 06 '09 at 20:15

Grzegorz Oledzki

23,614
16
68
106

1

Thnx! I had the same problem and your solution solved it. But I must know if there is another way to solve this problem. It produces a horrible code. Why can't it be _em.remove(entity)_ and that's it? – Royi Freifeld Apr 16 '13 at 18:31
2

Is this optimized behind the scenes ? cuz I don't wanna query the whole dataset. – Ced May 01 '16 at 02:04
2

This is really bad approach, what if you have several thousands users in that group? – Sergiy Sokolenko Sep 20 '17 at 07:54
3

This does not update the relation table, in the relation table the rows about this relation still remains. I didn't test but That could potentially cause problems. – Saygın Doğu Dec 11 '17 at 14:10
@SergiySokolenko no database queries are executed in that for loop. All of them are batched after the Transactional function is left. – Kilves Mar 20 '19 at 12:13
I wouldn't exactly count on implementation details. Just do a bulk delete? – Martin Andersson May 27 '21 at 12:22

score 52 · Answer 2 · answered Feb 16 '13 at 15:30

52

The following works for me. Add the following method to the entity that is not the owner of the relationship (Group)

@PreRemove
private void removeGroupsFromUsers() {
    for (User u : users) {
        u.getGroups().remove(this);
    }
}

Keep in mind that for this to work, the Group must have an updated list of Users (which is not done automatically). so everytime you add a Group to the group list in User entity, you should also add a User to the user list in the Group entity.

answered Feb 16 '13 at 15:30

damian

4,024
5
35
53

2

cascade = CascadeType.ALL should not be set when you want to use this solution. Otherwise it works perfect! – ltsstar Nov 22 '16 at 15:32
@damian Hi i have used your solution, but i have got an error concurrentModficationException i think as you pointed out the reason could be the Group must have an updated list of Users. Because if i add more than one group to the user i get this exception... – Adnan Abdul Khaliq Aug 22 '19 at 14:49
@Damian Keep in mind that for this to work, the Group must have an updated list of Users (which is not done automatically). so everytime you add a Group to the group list in User entity, you should also add a User to the user list in the Group entity. Could you please elaborate on this, give me an example,, thnks – Adnan Abdul Khaliq Aug 22 '19 at 15:57

score 28 · Answer 3 · edited May 23 '17 at 12:10

I found a possible solution, but... I don't know if it's a good solution.

@Entity
public class Role extends Identifiable {

    @ManyToMany(cascade ={CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH})
    @JoinTable(name="Role_Permission",
            joinColumns=@JoinColumn(name="Role_id"),
            inverseJoinColumns=@JoinColumn(name="Permission_id")
        )
    public List<Permission> getPermissions() {
        return permissions;
    }

    public void setPermissions(List<Permission> permissions) {
        this.permissions = permissions;
    }
}

@Entity
public class Permission extends Identifiable {

    @ManyToMany(cascade = {CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH})
    @JoinTable(name="Role_Permission",
            joinColumns=@JoinColumn(name="Permission_id"),
            inverseJoinColumns=@JoinColumn(name="Role_id")
        )
    public List<Role> getRoles() {
        return roles;
    }

    public void setRoles(List<Role> roles) {
        this.roles = roles;
    }

I have tried this and it works. When you delete Role, also the relations are deleted (but not the Permission entities) and when you delete Permission, the relations with Role are deleted too (but not the Role instance). But we are mapping a unidirectional relation two times and both entities are the owner of the relation. Could this cause some problems to Hibernate? Which type of problems?

Thanks!

The code above is from another post related.

This is not correct. Bidirectional ManyToMany should have one and only one owner side of the relationship. This solution is making both sides as owners and will eventually result in duplicate records. To avoid duplicate records, Sets must be used instead of Lists. But, using Set is just a workaround for doing something that is not recommended. — L. Holanda, Feb 16 '15 at 22:02

score 13 · Answer 4 · edited Dec 20 '22 at 20:29

As an alternative to JPA/Hibernate solutions : you could use a CASCADE DELETE clause in the database definition of your foreign key on your join table, such as (Oracle syntax) :

CONSTRAINT fk_to_group
     FOREIGN KEY (group_id)
     REFERENCES group (id)
     ON DELETE CASCADE

That way the DBMS itself automatically deletes the row that points to the group when you delete the group. It works whether the delete is made from Hibernate/JPA, JDBC, manually in the DB, or any other way.

the cascade delete feature is supported by all major DBMS (Oracle, MySQL, SQL Server, PostgreSQL).

Mehul Katpara · Answer 5 · 2018-03-06T21:16:23.380

5

This works for me:

@Transactional
public void remove(Integer groupId) {
    Group group = groupRepository.findOne(groupId);
    group.getUsers().removeAll(group.getUsers());

    // Other business logic

    groupRepository.delete(group);
}

Also, mark the method @Transactional (org.springframework.transaction.annotation.Transactional), this will do whole process in one session, saves some time.

edited Mar 06 '18 at 21:16

answered Mar 06 '18 at 21:11

Mehul Katpara

1,701
1
13
8

score 3 · Answer 6 · answered Jun 02 '12 at 03:21

For what its worth, I am using EclipseLink 2.3.2.v20111125-r10461 and if I have a @ManyToMany unidirectional relationship I observe the problem that you describe. However, if I change it to be a bi-directional @ManyToMany relationship I am able to delete an entity from the non-owning side and the JOIN table is updated appropriately. This is all without the use of any cascade attributes.

score 3 · Answer 7 · answered Sep 18 '20 at 10:15

3

This is what I ended up doing. Hopefully someone might find it useful.

@Transactional
public void deleteGroup(Long groupId) {
    Group group = groupRepository.findById(groupId).orElseThrow();
    group.getUsers().forEach(u -> u.getGroups().remove(group));
    userRepository.saveAll(group.getUsers());
    groupRepository.delete(group);
}

answered Sep 18 '20 at 10:15

Stephen Paul

37,253
15
92
74

1

If a group has 100 users, `deleteGroup` will trigger 1 (select) + 100 (delete) + 100 (update) + 1 (delete) queries. – Yves Calaci Dec 25 '20 at 17:22

score 2 · Answer 8 · edited Jul 09 '17 at 02:18

2

This is a good solution. The best part is on the SQL side – fine tuning to any level is easy.

I used MySql and MySql Workbench to Cascade on delete for the Required Foreign KEY.

ALTER TABLE schema.joined_table 
ADD CONSTRAINT UniqueKey
FOREIGN KEY (key2)
REFERENCES schema.table1 (id)
ON DELETE CASCADE;

edited Jul 09 '17 at 02:18

petezurich

9,280
9
43
57

answered Jul 08 '17 at 20:09

user1776955

41
4

Welcome to SO. Please take a moment and look into this to improve your formatting and proof reading: https://stackoverflow.com/help/how-to-ask – petezurich Jul 08 '17 at 20:32

score 2 · Answer 9 · edited May 28 '20 at 07:49

2

This works for me on a similar issue where I failed to delete the user due to the reference. Thank you

@ManyToMany(cascade = {CascadeType.MERGE, CascadeType.PERSIST,CascadeType.REFRESH})

edited May 28 '20 at 07:49

Shalu T D

3,921
2
26
37

answered May 28 '20 at 04:11

Me Me

21
1

score 0 · Answer 10 · answered Sep 26 '21 at 04:25

If you are using Spring Data Jpa, then simply create a repository interface for the owner class Group.class, then use their deleteById(Long id) method extended from JpaRepository.class. Then, when you delete a Group, the related rows(containing the same group id as you specify) in the join table will also be removed. Be aware of the CascadeType, avoid CascadeType.All, otherwise it will attempt to delete the user from the User table, which would cause the foreign key constraint runtime error again.

How to remove entity with ManyToMany relationship in JPA (and corresponding join table rows)?

10 Answers10

Linked

Related