CODEWITHSUNDEEP
javahibernatejpaspring-bootspring-data
sata
sata

Reputation: 288

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

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();
}

What should I do?

Upvotes: 1

Views: 1468

Answers (2)

Dragan Bozanovic
Dragan Bozanovic

Reputation: 23562

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.

Upvotes: 1

user6073886
user6073886

Reputation:

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.

Upvotes: 2

Related Questions