Search code examples
sqljpaspring-data-jpa

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

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. enter image description here

Solution

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