How to fetch all many-to-many relations in minimum count of queries?

Question

How can I fetch all many-to-many relations using a minimal number of queries? I mean without any n+1 queries. 3 - it's normal

I have an entity:

@Entity
@Table(name = "tags")
public class Tag {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;

    @Column(name = "title")
    private String title;
}

@Entity
@Table(name = "stations")
public class Station {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;

    @Column(name = "title")
    private String title;
}

@Entity
@Table(name = "songs")
public class Song {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;

    @Column(name = "title")
    private String title;

    @ManyToMany
    @JoinTable(
            name = "songs_stations",
            joinColumns = {
                    @JoinColumn(
                            name = "song_id",
                            referencedColumnName = "id"
                    )
            },
            inverseJoinColumns = {
                    @JoinColumn(
                            name = "station_id",
                            referencedColumnName = "id"
                    )
            }
    )
    private List<Station> stations;

    @ManyToMany
    @JoinTable(
            name = "songs_tags",
            joinColumns = {
                    @JoinColumn(
                            name = "song_id",
                            referencedColumnName = "id"
                    )
            },
            inverseJoinColumns = {
                    @JoinColumn(
                            name = "tag_id",
                            referencedColumnName = "id"
                    )
            }
    )
    private List<Tag> tags;
}

And a repository:

public interface SongRepository extends CrudRepository<Song, Long> {

    @Query("SELECT s FROM Song s LEFT JOIN FETCH s.tags LEFT JOIN FETCH s.stations")
    public List<Song> completeFindAllSongs();
}

• so, I can't use eager loading in completeFindAllSongs() cause of cannot simultaneously fetch multiple bags
• Can't use @NamedEntityGraph
• And I can't load data manually, because I don't have access to songs_tags table
• Please don't advise to use @LazyCollection

What should I do?

Answer 1

so, i can't use eager loading in completeFindAllSongs() cause of cannot simultaneously fetch multiple bags

This error should go away if you change your entities to use "Set" instead of "List" for OneToMany and ManyToMany relations.

Edit: So to answer to your question is: You only need 1 Query. Just use Sets and eager fetch whatever you want.

Answer 2

Keep in mind that by join fetching multiple collections you are creating full Cartesian product between the collection rows which may have a huge negative performance impact both on the database and application side.

You may want to consider using batch size to initialize the collections in batches.