N+1 query problem in Spring Boot - Mini Project: Build & Apply

Understanding and Fixing the N+1 Query Problem in Spring Boot

📖 Scenario: You are building a simple Spring Boot application to display a list of authors and their books. Each author can have multiple books. You want to fetch authors and their books efficiently from the database.

🎯 Goal: Learn how to identify and fix the N+1 query problem by using proper JPA annotations and query techniques in Spring Boot.

📋 What You'll Learn

Create an Author entity with a list of Book entities

Configure a fetch type for the relationship

Write a repository method to fetch authors with their books efficiently

Use @EntityGraph or JOIN FETCH to avoid N+1 queries

💡 Why This Matters

🌍 Real World

Many applications display data with related entities, such as authors and books, users and orders, or posts and comments. Efficient data fetching improves performance and user experience.

💼 Career

Understanding and fixing the N+1 query problem is a key skill for backend developers working with Spring Boot and JPA to build scalable and performant applications.

1

Create Author and Book Entities

Create two JPA entities: Author and Book. In Author, add a field List books with @OneToMany(mappedBy = "author"). In Book, add a field Author author with @ManyToOne. Use fetch = FetchType.LAZY for the books field in Author.

Spring Boot

// Your code here to create Author and Book entities with the specified fields and annotations

Need a hint?

Remember to use @Entity on both classes and set fetch = FetchType.LAZY on the books list in Author.

2

Create AuthorRepository Interface

Create a Spring Data JPA repository interface called AuthorRepository that extends JpaRepository<Author, Long>. Add a method List<Author> findAll() to fetch all authors.

Spring Boot

import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;

// Your code here to create AuthorRepository interface

Need a hint?

Extend JpaRepository with Author and Long as type parameters. Declare findAll() method.

3

Fix N+1 Problem Using @EntityGraph

In AuthorRepository, add a method List<Author> findAllWithBooks() annotated with @EntityGraph(attributePaths = {"books"}) to fetch authors and their books in one query.

Spring Boot

import org.springframework.data.jpa.repository.EntityGraph;
import java.util.List;

// Your code here to add findAllWithBooks() method with @EntityGraph

Need a hint?

Use @EntityGraph on the new method to load books eagerly and avoid multiple queries.

4

Use findAllWithBooks() in Service or Controller

In your service or controller class, inject AuthorRepository and use the method findAllWithBooks() to get authors with their books efficiently.

Spring Boot

import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class AuthorService {

    private final AuthorRepository authorRepository;

    public AuthorService(AuthorRepository authorRepository) {
        this.authorRepository = authorRepository;
    }

    // Your code here to create a method getAuthorsWithBooks() that calls findAllWithBooks()
}

Need a hint?

Create a method that calls findAllWithBooks() from the repository and returns the list.