Java.Hibernate.Middle.How can you avoid the N+1 select problem?

✅ 1. Use JOIN FETCH in JPQL/HQL

This is the most direct way:

List<Author> authors = em.createQuery(
    "SELECT DISTINCT a FROM Author a JOIN FETCH a.books", Author.class
).getResultList();

This forces Hibernate to fetch authors and their books in one SQL query.

✅ 2. Use EntityGraph (JPA 2.1+)

If you want to avoid hard-coding fetch joins into your queries, JPA’s EntityGraph allows you to dynamically specify what to eagerly load:

EntityGraph<?> graph = em.createEntityGraph(Author.class);
graph.addAttributeNodes("books");

List<Author> authors = em.createQuery("FROM Author", Author.class)
    .setHint("javax.persistence.fetchgraph", graph)
    .getResultList();

✅ 3. Batch fetching (Hibernate-specific)

If you don’t want to fetch everything eagerly but want to reduce the number of queries, Hibernate’s @BatchSize lets you fetch collections in batches:

@Entity
public class Author {
    @OneToMany(mappedBy = "author")
    @BatchSize(size = 10) // fetch 10 authors’ collections at once
    private List<Book> books;
}

This reduces N queries to ~N/10 queries.

✅ 4. Set fetch mode to FetchType.EAGER carefully

In your entity mappings, you can switch collections to EAGER, but be careful:

@OneToMany(fetch = FetchType.EAGER)
private List<Book> books;

EAGER fetching can help avoid N+1 but might cause performance issues or Cartesian product explosions if not used wisely — so prefer JOIN FETCH or EntityGraph over global eager fetching.

✅ 5. Profile with Hibernate statistics or SQL logs

Before optimizing blindly, check if N+1 happens in your actual queries:

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.generate_statistics=true

This way, you can see how many queries are executed.

🚨 Important note:

• Using JOIN FETCH on a collection may cause duplicate parent rows in the result set, so always add DISTINCT to avoid getting the same parent entity multiple times.

🔎 Summary:

• Use fetch joins or EntityGraph for single-query loading.
• Consider batch fetching if you want to keep lazy loading but reduce query count.
• Avoid eager fetching as a blanket solution; it can hurt performance in other scenarios.