how to avoid n+1 problem in many to many relationshipp

Question

I have many-to-many relationship in spring data jpa. The group Entity has many clients. And, the client belongs to many groups. In the group entity. I have this code.

GroupEntity {
    @ManyToMany
    @JoinTable(
            name = "rel_group_client",
            joinColumns = @JoinColumn(name = "group_id"),
            inverseJoinColumns = @JoinColumn(name = "client_id")
    )
    @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
    @JsonIgnore
    private Set<ClientEntity> clients = new HashSet<>();
}

The ClientEntity does not have any field related to GroupEntity. I use findAll() to get all groups. But at the same time I do a lot of queries for clientEntity. I don't want to query the clients. How can I do this?
This is where I call findAll() method.

    @Override
    @Transactional(readOnly = true)
    public Page<GroupDTO> findAll(Pageable pageable) {
        return groupRepository.findAll(pageable).map(groupMapper::toDto);
    }

I have set fetch = FetchType.LAZY and add @JsonIgnore.They all do not work. I use show-sql:true to see the SQL. I can get a lot of SQL like this. I want to avoid them.

not an expert on jpa, but found this topic : https://stackoverflow.com/questions/46810929/how-to-use-fetchtype-lazy-with-manytomany don't you need a method with @Transactional for this ? EDIT: fetchtype LAZY should work, please provide your code using it by editing your post — titouanbou, Jun 20 '23 at 08:25
Could you please provide the implementation of your `groupMapper`. Does your `groupMapper` access the `clients`getter and therefore invokes a call to the db to fetch the clients? — Daniel Rafael Wosch, Jun 20 '23 at 09:10
Fetchtype LAZY is the default type for ManyToMany and it works. No doubts. The question is what are you doing with the result of findAll? If the mapper access the field `clients` or any of its fields then it will cause Hibernate to additionally fire the select with join. — Mar-Z, Jun 20 '23 at 09:10
Thanks for your help. I generate other sql because I add `map(groupMapper::toDto)`.When I remove this.I achieve my goal.Thanks a lot.`fetchtype LAZY` works for me now! — Smile, Jun 20 '23 at 09:49
This fixes the symptom but not your problem. You should check the mapper implementation and remove the access to the lazy fetched properties. — Daniel Rafael Wosch, Jun 21 '23 at 12:16

score 1 · Accepted Answer · answered Jun 21 '23 at 12:08

1

It looks like you are accessing GroupEntity.clients() in your GroupMapper::toDto.

answered Jun 21 '23 at 12:08

Sergey Tsypanov

3,265
3
8
34

how to avoid n+1 problem in many to many relationshipp

1 Answers1