How to prevent retrieval of all entries from bidirectional ManyToMany collection while adding new entry

Question

I would like to know how to prevent retrieval of all entries from bidirectional ManyToMany collection while adding new entry into that collection. I have User entity, that has bidirectional relationship with UserGroup entity. When I add group in User entity it add User to group as well as the UserGroup is parent. The problem is that if I add a lot of groups (let say 20) to user, then user is being added to each group as well and all users (100+ per group) are retrieved from each group. It makes transaction to take a lot of time and shows significant lag while I only want to insert UserGroup into User's userGroups collection and have no need of selecting other users from those groups.

User.java

@ManyToMany(mappedBy = "users")
private Set<UserGroup> userGroups;

public void addGroup(UserGroup userGroup){
this.userGroups.add(userGroup);
userGroup.addUser(this);
}

UserGroup.java

@ManyToMany
@JoinTable(name = "group_users", joinColumns = @JoinColumn(name = "group_id"),
        inverseJoinColumns = @JoinColumn(name = "user_id"))
private Set<User> users;

public void addUser(User user) {
   this.users.add(user);
}

Method used

@Transactional
public void addGroupsToUser(UserAddGroupsCommand userAddGroupsCommand, String username) {
    User user = userRepository.findByUsername(username);
    if (user != null) {
        List<UserGroup> userGroupList = userGroupRepository.findAllById(userAddGroupsCommand.getId());
        for (UserGroup userGroup : userGroupList) {
            user.addGroup(userGroup);
        }
        userRepository.save(user);
    }
}

So I can see in console, that all join table entries are selected for the group I am iterating at

EDITED: this is the code that execuded and I am not sure why.

select users0_.group_id as group_id1_0_0_, users0_.user_id as user_id2_0_0_, user1_.id as id1_7_1_ 
from group_users users0_ 
inner join User user1_ on users0_.user_id=user1_.id 
where users0_.group_id=?

Peter Šály · Answer 1 · 2019-02-27T00:45:56.323

1

If you don't need to know which users are in a group (displaying it in application), then do unidirectional mapping. Remove Set<User> users from UserGroup class.

In User class use @JoinTable instead mappedBy:

@ManyToMany
@JoinTable(name = "group_users", 
    joinColumns = @JoinColumn(name = "user_id"),
    inverseJoinColumns = @JoinColumn(name = "group_id"))
private Set<UserGroup> userGroups;

public void addGroup(UserGroup userGroup){
    this.userGroups.add(userGroup);
}

edited Feb 27 '19 at 00:45

answered Feb 27 '19 at 00:38

Peter Šály

2,848
2
12
26

Dear Peter. Actually I need. I have an option in front-end to add groups to user. And also I have option to access group and add users to group. So I guess bidirectional is needed. – Andyally Feb 27 '19 at 01:07
You can always replace the reverse direction by a query method in a repository. – Jens Schauder Feb 27 '19 at 07:06

Peter Šály · Accepted Answer · 2019-02-27T10:15:58.553

0

Try custom query to gain better performance

from UserGroup g left join fetch g.users where g.id in :IDS

This way users in each group will be loaded in one query and not in N+1 queries.

Changing @ManyToMany(FetchType.EAGER) is not efficient, because users will be always loaded with UserGroup, also when not used.

Using @EntityGraph is same as join fetch query.

edited Feb 27 '19 at 10:15

answered Feb 27 '19 at 10:08

Peter Šály

2,848
2
12
26

I just edited the last code snippet. I actually don't want this part to be executed after user gets added to group. – Andyally Feb 27 '19 at 13:22
it will not execute for each group if you fetch users on the beginning when calling `userGroupRepository.findAllById`. Replace that with custom query `from UserGroup g left join fetch g.users where g.id in :IDS` – Peter Šály Feb 27 '19 at 14:30

How to prevent retrieval of all entries from bidirectional ManyToMany collection while adding new entry

2 Answers2