Skip to content
CampusEduX

Database · Lesson 63 of 95

N+1 Problem

Spot and fix the N+1 problem in Spring Boot JPA. Count real queries with Hibernate statistics, then cure it with JOIN FETCH, EntityGraph or BatchSize.

9 min read

A shopkeeper sends a boy to the market with a list of ten customers. Instead of buying everything in one trip, the boy walks back and forth: one trip to check the list, then one trip for each customer's order. Eleven trips for a job that needed one or two. Your database code can behave exactly like that boy, and it does so quietly, with no error message at all.

This is the N+1 problem in Spring Boot and JPA. Let's find out what it is, how to spot it with real numbers, and three ways to fix it.

What is the N+1 problem?

Think of a hospital reception desk. You ask for the list of wards, and you get four names. Then, for each ward, you walk back to the desk to ask which patients are in it. That is one question for the list, plus one question for each of the four wards. Five trips for data that could arrive in a single trip.

The problem is not a bug in JPA. It is a side effect of lazy loading. A lazy collection loads itself the first time you touch it, and if you touch it inside a loop, it loads once per parent.

Why does it matter?

With 4 wards you will never notice it. With 500 wards, the same page sends 501 queries. Each query has a small cost, and each trip to the database has a bigger cost: network time, connection use and locking. Pages that were fast in development become slow in production, and nobody understands why, because the code looks innocent.

N+1 is one of the most common performance problems in Spring Boot projects, and interviewers love to ask about it. Knowing how to see it and fix it puts you ahead of many beginners.

How it works

Here is what happens when you load every ward and print how many patients each one has.

text
wardRepository.findAll()
    |
    v
Query 1: select all wards
    |
    v
for each ward in the list
    |
    v
ward.getPatients().size()
    |
    v
Query 2: patients of ward 1
Query 3: patients of ward 2
Query 4: patients of ward 3
Query 5: patients of ward 4

The first query returns four wards, and each ward carries a lazy placeholder for its patients. The loop touches the placeholder of each ward in turn, and every touch runs its own query. With N wards you get 1 + N queries in total.

The fix is to ask for the wards and their patients together. This is what the picture looks like after the fix.

text
wards.findAllWithPatients()
    |
    v
Query 1: select wards
         join their patients
    |
    v
Wards with patients already loaded
    |
    v
Loop reads memory only: no query

One query brings back every ward with its patients. The loop now reads objects that are already in memory, so the database is not touched again.

ApproachQueries for 4 wardsWhen to use it
Plain findAll() and a loop5Never on collections you loop over
JOIN FETCH query1One clear use case
@EntityGraph1Same, without writing JPQL
@BatchSize2Many places touch the collection

Real-Life Example

Picture a tiffin service that delivers 30 boxes a day. Version one of the plan: the manager reads the customer list, then phones the kitchen once for every customer to ask what to pack. That is 31 calls. Version two: the manager phones once and asks for the full packing sheet. One call. Same food, far less talking. JOIN FETCH is the packing sheet. Batch fetching is a middle way: the manager phones once for every ten customers, and 30 customers need only three calls.

Code Example

Let's build a small app for City Care Hospital. We create four wards, each with patients, and we count the SQL statements in each approach using Hibernate statistics. The Nurse collection shows the batch approach.

text
citycare/
├─ pom.xml
└─ src/main/
   ├─ java/com/citycare/wards/
   │  ├─ WardsApplication.java
   │  ├─ Ward.java
   │  ├─ Patient.java
   │  ├─ Nurse.java
   │  ├─ WardRepository.java
   │  └─ WardService.java
   └─ resources/
      └─ application.properties

File: pom.xml

xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>4.1.1</version>
        <relativePath/>
    </parent>

    <groupId>com.citycare</groupId>
    <artifactId>wards</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <properties>
        <java.version>21</java.version>
    </properties>

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

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

File: application.properties in src/main/resources

properties
spring.main.banner-mode=off
logging.level.root=warn
spring.jpa.properties.hibernate.generate_statistics=true

File: Ward.java in package com.citycare.wards

java
package com.citycare.wards;

import java.util.HashSet;
import java.util.Set;

import jakarta.persistence.CascadeType;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;

import org.hibernate.annotations.BatchSize;

@Entity
public class Ward {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @OneToMany(mappedBy = "ward", cascade = CascadeType.PERSIST)
    private Set<Patient> patients = new HashSet<>();

    @OneToMany(mappedBy = "ward", cascade = CascadeType.PERSIST)
    @BatchSize(size = 10)
    private Set<Nurse> nurses = new HashSet<>();

    protected Ward() {
    }

    public Ward(String name) {
        this.name = name;
    }

    public Set<Patient> getPatients() {
        return patients;
    }

    public Set<Nurse> getNurses() {
        return nurses;
    }
}

File: Patient.java in package com.citycare.wards

java
package com.citycare.wards;

import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;

@Entity
public class Patient {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "ward_id")
    private Ward ward;

    protected Patient() {
    }

    public Patient(String name, Ward ward) {
        this.name = name;
        this.ward = ward;
    }
}

File: Nurse.java in package com.citycare.wards

java
package com.citycare.wards;

import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;

@Entity
public class Nurse {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "ward_id")
    private Ward ward;

    protected Nurse() {
    }

    public Nurse(String name, Ward ward) {
        this.name = name;
        this.ward = ward;
    }
}

File: WardRepository.java in package com.citycare.wards

java
package com.citycare.wards;

import java.util.List;

import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface WardRepository extends JpaRepository<Ward, Long> {

    @Query("select distinct w from Ward w join fetch w.patients")
    List<Ward> findAllWithPatients();

    @EntityGraph(attributePaths = "patients")
    List<Ward> findAllBy();
}

File: WardService.java in package com.citycare.wards

java
package com.citycare.wards;

import java.util.List;

import jakarta.persistence.EntityManagerFactory;

import org.hibernate.SessionFactory;
import org.hibernate.stat.Statistics;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class WardService {

    private final WardRepository wards;
    private final Statistics stats;

    public WardService(WardRepository wards, EntityManagerFactory emf) {
        this.wards = wards;
        this.stats = emf.unwrap(SessionFactory.class).getStatistics();
    }

    @Transactional
    public void seed() {
        String[] names = {"Cardiology", "Pediatrics", "Orthopedics", "Maternity"};
        for (String name : names) {
            Ward ward = new Ward(name);
            ward.getPatients().add(new Patient(name + " patient A", ward));
            ward.getPatients().add(new Patient(name + " patient B", ward));
            ward.getNurses().add(new Nurse(name + " nurse", ward));
            wards.save(ward);
        }
    }

    @Transactional(readOnly = true)
    public void naive() {
        stats.clear();
        int patients = 0;
        for (Ward ward : wards.findAll()) {
            patients += ward.getPatients().size();
        }
        report("Plain loop", patients);
    }

    @Transactional(readOnly = true)
    public void joinFetch() {
        stats.clear();
        report("Join fetch", count(wards.findAllWithPatients()));
    }

    @Transactional(readOnly = true)
    public void entityGraph() {
        stats.clear();
        report("Entity graph", count(wards.findAllBy()));
    }

    @Transactional(readOnly = true)
    public void batch() {
        stats.clear();
        int nurses = 0;
        for (Ward ward : wards.findAll()) {
            nurses += ward.getNurses().size();
        }
        System.out.println("Batch size 10, nurses: " + nurses);
        System.out.println("  queries: " + stats.getPrepareStatementCount());
    }

    private int count(List<Ward> list) {
        return list.stream().mapToInt(w -> w.getPatients().size()).sum();
    }

    private void report(String label, int patients) {
        System.out.println(label + ", patients: " + patients);
        System.out.println("  queries: " + stats.getPrepareStatementCount());
    }
}

File: WardsApplication.java in package com.citycare.wards

java
package com.citycare.wards;

import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

@SpringBootApplication
public class WardsApplication {

    public static void main(String[] args) {
        SpringApplication.run(WardsApplication.class, args);
    }

    @Bean
    CommandLineRunner demo(WardService service) {
        return args -> {
            service.seed();
            service.naive();
            service.joinFetch();
            service.entityGraph();
            service.batch();
        };
    }
}

Run it:

bash
mvn spring-boot:run

Output:

text
Plain loop, patients: 8
  queries: 5
Join fetch, patients: 8
  queries: 1
Entity graph, patients: 8
  queries: 1
Batch size 10, nurses: 4
  queries: 2

The plain loop sends five statements: one for the four wards, and one for each ward's patients. Both the JOIN FETCH query and the entity graph need only one. The batch version loads the nurses of all four wards with a single extra query, because they fit in one batch of ten.

Code Explained

  • naive() is the N+1 shape: findAll() runs first, and touching getPatients() inside the loop runs one query per ward.
  • findAllWithPatients() uses join fetch in JPQL. The word distinct removes duplicate wards created by the join.
  • @EntityGraph(attributePaths = "patients") gives the same result without writing a query. Spring Data builds it from the method name.
  • @BatchSize(size = 10) tells Hibernate to load the collections of up to ten wards in one query, using an in list.
  • getPrepareStatementCount() from Hibernate statistics is the number you watch. It is a good habit to check it before you trust a data-heavy page.

Common Mistakes

  • Not looking at the queries. N+1 never throws an error. Check the SQL in development or count statements in tests.
  • Fetching two collections at once. A JOIN FETCH on two List collections in one query can cause a MultipleBagFetchException or a huge result. Fetch one, and use @BatchSize for the other.
  • Combining `JOIN FETCH` with paging. Hibernate then pages in memory. For paged lists, use @BatchSize or fetch the ids first.
  • Forgetting `distinct`. With older Hibernate habits, people worry about duplicates. Keep distinct for clarity.
  • Returning entities to the web layer. Jackson touches every getter and triggers a query per row. Map to DTOs and load exactly what the DTO needs.

Interview Questions

What is the N+1 problem?

Ans:One query loads N parent rows, and then N more queries load the children, one per parent. It usually comes from lazy loading inside a loop.

How do you detect it?

Ans:Turn on SQL logging or Hibernate statistics and look for the same query repeated with different ids. A test that asserts the statement count also catches it.

How do you fix it?

Ans:Fetch the children together with the parents using JOIN FETCH or @EntityGraph, or reduce the number of queries with @BatchSize or a global batch fetch size.

Why is switching to EAGER not a real fix?

Ans:With a query such as findAll(), Hibernate still loads the children with extra queries, and it now does so everywhere, even when the data is not needed.

Key Points to Remember

  • N+1 means one query for the list plus one query for each row's children.
  • It comes from lazy loading inside a loop, and it never shows an error.
  • Count the statements to see it. Do not guess.
  • Fix it with JOIN FETCH, @EntityGraph or @BatchSize.
  • Do not fix it with FetchType.EAGER.

Frequently Asked Questions

How do I find the N+1 problem in my Spring Boot project?

Set spring.jpa.show-sql=true while developing and read the console. If one select for the list is followed by many similar selects that differ only by an id, you have N+1. Hibernate statistics give you an exact count.

Does N+1 happen with @ManyToOne too?

Yes. Loading 100 dishes whose restaurant is eager can load 100 restaurants one by one. Mark the link lazy and fetch the restaurant only where you need it.

Is JOIN FETCH always the best fix for the N+1 problem?

It is the simplest for one collection and one use case. For several collections, or for paged lists, batch fetching is often safer.

Can I set a batch size for the whole application?

Yes. Add this line to application.properties:

properties
spring.jpa.properties.hibernate.default_batch_fetch_size=10

It applies a batch size to every lazy association, which is a cheap way to reduce the N+1 problem across a project.

Related Topics

Practice Problems

Try each problem on your own first. Both use the H2 in-memory database, so nothing needs to be installed.

Easy: Cinema Halls and Shows

CineGo has halls, and every hall has several shows. A page prints the number of shows in each hall. Build the app with a plain findAll() loop first, count the SQL statements, then add an @EntityGraph method and count again.

Show answer
The loop with three halls sends four statements, one for the list and one for each hall. The entity graph brings the shows with the halls, so it sends one.

File: pom.xml

xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>4.1.1</version>
        <relativePath/>
    </parent>

    <groupId>com.cinego</groupId>
    <artifactId>halls</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <properties>
        <java.version>21</java.version>
    </properties>

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

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

File: application.properties in src/main/resources

properties
spring.main.banner-mode=off
logging.level.root=warn
spring.jpa.properties.hibernate.generate_statistics=true

File: Hall.java in package com.cinego.halls

java
package com.cinego.halls;

import java.util.ArrayList;
import java.util.List;

import jakarta.persistence.CascadeType;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;

@Entity
public class Hall {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @OneToMany(mappedBy = "hall", cascade = CascadeType.PERSIST)
    private List<Show> shows = new ArrayList<>();

    protected Hall() {
    }

    public Hall(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public List<Show> getShows() {
        return shows;
    }
}

File: Show.java in package com.cinego.halls

java
package com.cinego.halls;

import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;

@Entity
public class Show {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String movie;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "hall_id")
    private Hall hall;

    protected Show() {
    }

    public Show(String movie, Hall hall) {
        this.movie = movie;
        this.hall = hall;
    }
}

File: HallRepository.java in package com.cinego.halls

java
package com.cinego.halls;

import java.util.List;

import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;

public interface HallRepository extends JpaRepository<Hall, Long> {

    @EntityGraph(attributePaths = "shows")
    List<Hall> findAllBy();
}

File: HallService.java in package com.cinego.halls

java
package com.cinego.halls;

import java.util.List;

import jakarta.persistence.EntityManagerFactory;

import org.hibernate.SessionFactory;
import org.hibernate.stat.Statistics;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class HallService {

    private final HallRepository halls;
    private final Statistics stats;

    public HallService(HallRepository halls, EntityManagerFactory emf) {
        this.halls = halls;
        this.stats = emf.unwrap(SessionFactory.class).getStatistics();
    }

    @Transactional
    public void seed() {
        for (String name : List.of("Hall A", "Hall B", "Hall C")) {
            Hall hall = new Hall(name);
            hall.getShows().add(new Show("The Silent Orbit", hall));
            hall.getShows().add(new Show("Monsoon Express", hall));
            halls.save(hall);
        }
    }

    @Transactional(readOnly = true)
    public void compare() {
        stats.clear();
        int total = 0;
        for (Hall hall : halls.findAll()) {
            total += hall.getShows().size();
        }
        System.out.println("Loop: " + total + " shows");
        System.out.println("  queries: " + stats.getPrepareStatementCount());

        stats.clear();
        total = 0;
        for (Hall hall : halls.findAllBy()) {
            total += hall.getShows().size();
        }
        System.out.println("Graph: " + total + " shows");
        System.out.println("  queries: " + stats.getPrepareStatementCount());
    }
}

File: HallsApplication.java in package com.cinego.halls

java
package com.cinego.halls;

import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

@SpringBootApplication
public class HallsApplication {

    public static void main(String[] args) {
        SpringApplication.run(HallsApplication.class, args);
    }

    @Bean
    CommandLineRunner demo(HallService service) {
        return args -> {
            service.seed();
            service.compare();
        };
    }
}

Running the app prints:

text
Loop: 6 shows
  queries: 4
Graph: 6 shows
  queries: 1

Medium: Clinic Doctor Summary

A clinic wants a list that shows each doctor's name and the number of appointments, and nothing else. Loading every appointment just to count them is wasteful. Write one JPQL query that returns a small record with the doctor name and the count, and prove with statistics that it runs one statement.

Show answer
A projection query never loads the appointment entities, so there is nothing lazy to trigger. One statement returns all the rows.

File: pom.xml

xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>4.1.1</version>
        <relativePath/>
    </parent>

    <groupId>com.wellclinic</groupId>
    <artifactId>doctors</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <properties>
        <java.version>21</java.version>
    </properties>

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

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

File: application.properties in src/main/resources

properties
spring.main.banner-mode=off
logging.level.root=warn
spring.jpa.properties.hibernate.generate_statistics=true

File: Doctor.java in package com.wellclinic.doctors

java
package com.wellclinic.doctors;

import java.util.ArrayList;
import java.util.List;

import jakarta.persistence.CascadeType;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;

@Entity
public class Doctor {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @OneToMany(mappedBy = "doctor", cascade = CascadeType.PERSIST)
    private List<Appointment> appointments = new ArrayList<>();

    protected Doctor() {
    }

    public Doctor(String name) {
        this.name = name;
    }

    public List<Appointment> getAppointments() {
        return appointments;
    }
}

File: Appointment.java in package com.wellclinic.doctors

java
package com.wellclinic.doctors;

import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;

@Entity
public class Appointment {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String slot;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "doctor_id")
    private Doctor doctor;

    protected Appointment() {
    }

    public Appointment(String slot, Doctor doctor) {
        this.slot = slot;
        this.doctor = doctor;
    }
}

File: DoctorSummary.java in package com.wellclinic.doctors

java
package com.wellclinic.doctors;

public record DoctorSummary(String name, Long appointments) {
}

File: DoctorRepository.java in package com.wellclinic.doctors

java
package com.wellclinic.doctors;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface DoctorRepository extends JpaRepository<Doctor, Long> {

    @Query("""
            select new com.wellclinic.doctors.DoctorSummary(d.name, count(a))
            from Doctor d left join d.appointments a
            group by d.name order by d.name
            """)
    List<DoctorSummary> summaries();
}

File: DoctorService.java in package com.wellclinic.doctors

java
package com.wellclinic.doctors;

import jakarta.persistence.EntityManagerFactory;

import org.hibernate.SessionFactory;
import org.hibernate.stat.Statistics;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class DoctorService {

    private final DoctorRepository doctors;
    private final Statistics stats;

    public DoctorService(DoctorRepository doctors, EntityManagerFactory emf) {
        this.doctors = doctors;
        this.stats = emf.unwrap(SessionFactory.class).getStatistics();
    }

    @Transactional
    public void seed() {
        Doctor rao = new Doctor("Dr. Rao");
        rao.getAppointments().add(new Appointment("09:00", rao));
        rao.getAppointments().add(new Appointment("09:30", rao));
        rao.getAppointments().add(new Appointment("10:00", rao));
        Doctor iyer = new Doctor("Dr. Iyer");
        iyer.getAppointments().add(new Appointment("11:00", iyer));
        Doctor bose = new Doctor("Dr. Bose");
        doctors.save(rao);
        doctors.save(iyer);
        doctors.save(bose);
    }

    @Transactional(readOnly = true)
    public void print() {
        stats.clear();
        doctors.summaries().forEach(s ->
                System.out.println(s.name() + ": " + s.appointments()));
        System.out.println("queries: " + stats.getPrepareStatementCount());
    }
}

File: DoctorsApplication.java in package com.wellclinic.doctors

java
package com.wellclinic.doctors;

import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

@SpringBootApplication
public class DoctorsApplication {

    public static void main(String[] args) {
        SpringApplication.run(DoctorsApplication.class, args);
    }

    @Bean
    CommandLineRunner demo(DoctorService service) {
        return args -> {
            service.seed();
            service.print();
        };
    }
}

Running the app prints:

text
Dr. Bose: 0
Dr. Iyer: 1
Dr. Rao: 3
queries: 1

Dr. Bose has no appointments, so the left join keeps the row with a count of zero.

Mock Test

  • N+1 Problem - Quick Test

    5 questions to check what you learned in N+1 Problem.

    5 questions · 5 min · Medium
    Start Mock Test

Related lessons