Spring Boot JPA Batch Fetching: Optimize Large Queries & Fix N+1 Problem

When working with Spring Data JPA, performance is one of the most critical aspects of handling large datasets efficiently. A common issue developers face is the N+1 query problem, where Hibernate executes too many queries when fetching related entities.

To solve this, we use Batch Fetching to optimize database queries and improve application speed. This guide covers everything from scratch, step by step, with real code examples and a REST API to test each case.

---

1. What is Batch Fetching and Why It Matters?

The N+1 Query Problem

Imagine we have a Department entity, each with multiple Employee records. When you fetch a list of departments, Hibernate loads them first. But when you access department.getEmployees(), Hibernate fires one query per department to fetch employees.

If you have 10 departments, this results in 1 + 10 = 11 queries, which is inefficient and can slow down performance.

Example of the Problem:

List<Department> departments = departmentRepository.findAll();
for (Department department : departments) {
    System.out.println(department.getEmployees().size()); // Triggers extra queries
}

Generated SQL:

SELECT * FROM department;
SELECT * FROM employee WHERE department_id = 1;
SELECT * FROM employee WHERE department_id = 2;
SELECT * FROM employee WHERE department_id = 3;
...

💥 More queries = More database load = Slow performance.

---

2. Setting Up a Spring Boot Project

Required Dependencies

Add the following dependencies in pom.xml:

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>

Database Configuration (H2 In-Memory)

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

---

3. Defining Entities

Department Entity

import jakarta.persistence.*;
import java.util.List;

@Entity
public class Department {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

    @OneToMany(mappedBy = "department", fetch = FetchType.LAZY)
    private List<Employee> employees;

    // Getters and Setters
}

Employee Entity

import jakarta.persistence.*;

@Entity
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "department_id")
    private Department department;

    // Getters and Setters
}

---

4. Fixing the N+1 Problem with Batch Fetching

To enable batch fetching in Hibernate, we can configure it in application.properties:

spring.jpa.properties.hibernate.default_batch_fetch_size=10

🔹 Now, instead of multiple queries, Hibernate fetches employees in batches of 10.
Generated SQL:

SELECT * FROM department;
SELECT * FROM employee WHERE department_id IN (1,2,3,4,5,6,7,8,9,10);

🔥 Fewer queries = Faster performance.

---

5. Alternative: Using @BatchSize Annotation

Instead of configuring batch size globally, we can do it at the entity level:

import org.hibernate.annotations.BatchSize;

@Entity
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "department_id")
    @BatchSize(size = 10)  // Batch fetch employees in chunks of 10
    private Department department;
}

---

6. Optimizing Further with JOIN FETCH

Another way to solve the N+1 problem is to fetch related entities in one query using JOIN FETCH.
Modify the repository like this:

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface DepartmentRepository extends JpaRepository<Department, Long> {

    @Query("SELECT d FROM Department d JOIN FETCH d.employees WHERE d.id = :id")
    Department findDepartmentWithEmployees(@Param("id") Long id);
}

Generated SQL:

SELECT d.*, e.* FROM department d
JOIN employee e ON d.id = e.department_id
WHERE d.id = ?;

✔ One query, all data loaded efficiently.

---

7. Adding a REST API for Testing

To test all scenarios, let's expose REST endpoints:

import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/departments")
public class DepartmentController {

    private final DepartmentRepository departmentRepository;

    public DepartmentController(DepartmentRepository departmentRepository) {
        this.departmentRepository = departmentRepository;
    }

    // Normal fetching (Lazy, may cause N+1 problem)
    @GetMapping
    public List<Department> getAllDepartments() {
        return departmentRepository.findAll();
    }

    // Batch fetching (Optimized)
    @GetMapping("/{id}/batch")
    public Department getDepartmentWithBatchFetching(@PathVariable Long id) {
        return departmentRepository.findById(id).orElse(null);
    }

    // JOIN FETCH (Optimized)
    @GetMapping("/{id}/join-fetch")
    public Department getDepartmentWithEmployees(@PathVariable Long id) {
        return departmentRepository.findDepartmentWithEmployees(id);
    }
}

Testing Endpoints

1️⃣ Normal Fetching (N+1 Problem)

GET http://localhost:8080/departments

2️⃣ Optimized with Batch Fetching

GET http://localhost:8080/departments/1/batch

3️⃣ Optimized with JOIN FETCH

GET http://localhost:8080/departments/1/join-fetch

---

8. Conclusion

Use batch fetching for large datasets. Use JOIN FETCH for immediate needs.