Monday 17 June 2024

Understanding the N+1 Select Problem in Spring JPA

The N+1 select problem typically happens when an ORM (Object-Relational Mapping) frameworks, such as Hibernate in Spring JPA, executes a separate query for each associated entity.

Let me explain this with an example.

@Entity
@Table(name="AUTHOR")
public class Author {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @OneToMany(mappedBy = "author", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Book> books = new ArrayList<>();

    ......
    ......
}

@Entity
@Table(name = "BOOK")
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String title;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "author_id")
    private Author author;

    ......
    ......
}

One Author writes more than one book. It is a simple one to many relationship.



 

When the N+1 problem occur

When we fetch all authors and their books, the N+1 problem will occur in this scenario. For instance, let's say we want to fetch all authors along with their books.

List<Author> authors = authorRepository.findAll();
for (Author author : authors) {
    System.out.println(author.getBooks());
}

What happens behind the scenes?

Step 1: One Query is executed to find all the authors.

SELECT * from AUTHOR;

Step 2: For each author, another query is executed to fetch their books.

SELECT * FROM BOOK WHERE author_id = ?;

If there are 100 authors, this results in 1 query to fetch all the authors and 100 additional queries to fetch the books for each author, resulting in 101 queries total. As the number of authors increases, the number of queries increases linearly, causing performance issues.

 

Find the below working application.

 

Step 1: Create new maven project ‘n-plus-one-problem’.

 

Step 2: Update pom.xml with maven dependencies.

 

pom.xml

<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>
    <groupId>com.sample.app</groupId>
    <artifactId>n-plus-one-problem</artifactId>
    <version>0.0.1-SNAPSHOT</version>

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

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
        </dependency>

        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </dependency>

    </dependencies>

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

Step 3: Define entity classes.

 

Author.java

package com.sample.app.entity;

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;
import jakarta.persistence.Table;

@Entity
@Table(name="AUTHOR")
public class Author {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @OneToMany(mappedBy = "author", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Book> books = new ArrayList<>();

    public Author() {}

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

    // Getters and Setters
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Book> getBooks() {
        return books;
    }

    public void setBooks(List<Book> books) {
        this.books = books;
    }

    public void addBook(Book book) {
        books.add(book);
        book.setAuthor(this);
    }

    public void removeBook(Book book) {
        books.remove(book);
        book.setAuthor(null);
    }

    @Override
    public String toString() {
        return "Author [id=" + id + ", name=" + name + "]";
    }
    
    
}

Book.java

package com.sample.app.entity;

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;
import jakarta.persistence.Table;

@Entity
@Table(name = "BOOK")
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String title;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "author_id")
    private Author author;

    public Book() {
    }

    public Book(String title) {
        this.title = title;
    }

    // Getters and Setters
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public Author getAuthor() {
        return author;
    }

    public void setAuthor(Author author) {
        this.author = author;
    }

    @Override
    public String toString() {
        return "Book [id=" + id + ", title=" + title + "]";
    }

}

Step 4: Define DBConfig class.

 

DBConfig.java

package com.sample.app.config;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.io.IOException;

@Configuration
public class DBConfig {
    @Bean
    public DataSource dataSource() throws IOException {
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setDriverClassName("org.h2.Driver");
        hikariConfig.setJdbcUrl("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false");
        hikariConfig.setUsername("sa");
        hikariConfig.setPassword("");
        hikariConfig.setMaximumPoolSize(10);
        hikariConfig.setIdleTimeout(600000); // 10 minutes
        hikariConfig.setConnectionTimeout(30000); // 30 seconds
        hikariConfig.setMaxLifetime(1800000); // 30 minutes
        hikariConfig.setConnectionTestQuery("SELECT 1");
        hikariConfig.setValidationTimeout(5000); // 5 seconds
        hikariConfig.setAutoCommit(true);
        hikariConfig.setPoolName("springHikariCP");

        return new HikariDataSource(hikariConfig);
    }
}

Step 5: Define repository interfaces.

 

AuthorRepository.java

package com.sample.app.repository;

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

import com.sample.app.entity.Author;

public interface AuthorRepository extends JpaRepository<Author, Integer> {

}

BookRepository.java

package com.sample.app.repository;

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

import com.sample.app.entity.Book;

public interface BookRepository extends JpaRepository<Book, Integer> {

}

Step 6: Define AuthorService class.

 

AuthorService.java

package com.sample.app.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.sample.app.entity.Author;
import com.sample.app.entity.Book;
import com.sample.app.repository.AuthorRepository;

@Service
public class AuthorService {

    @Autowired
    private AuthorRepository authorRepository;

    @Transactional
    public void printAll() {
        List<Author> authors = authorRepository.findAll();

        for (Author author : authors) {
            System.out.println("author : " + author);
            for (Book book : author.getBooks()) {
                System.out.println("\t" + book);
            }
        }
    }
}

Step 7: Create application.yml file under src/main/sources folder.

 

application.yml

spring:
  datasource:
    communicationtimeout: 60000
    jpa:
      hibernate:
        ddl-auto: create
  jpa:
    properties:
      hibernate:
        show_sql: true
        format_sql: false

Step 8: Define main application class.

 

App.java

package com.sample.app;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.transaction.annotation.Transactional;

import com.sample.app.entity.Author;
import com.sample.app.entity.Book;
import com.sample.app.repository.AuthorRepository;
import com.sample.app.service.AuthorService;

import jakarta.annotation.PostConstruct;

@SpringBootApplication
public class App {
  @Autowired
  private AuthorRepository authorRepository;
  
  @Autowired
  private AuthorService authorService;

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

  @PostConstruct
  @Transactional
  public void demo() {
    Author hari = new Author("Hari");
    hari.addBook(new Book("Hari Book 1"));
    hari.addBook(new Book("Hari Book 2"));

    Author ram = new Author("Ram");
    ram.addBook(new Book("Ram Book 1"));
    ram.addBook(new Book("Ram Book 2"));

    Author david = new Author("David");
    david.addBook(new Book("David Book 1"));
    david.addBook(new Book("David Book 2"));

    Author chandu = new Author("Chandu");
    chandu.addBook(new Book("David Book 1"));

    Author raghu = new Author("Raghu");
    raghu.addBook(new Book("Raghu Book 1"));

    authorRepository.save(hari);
    authorRepository.save(ram);
    authorRepository.save(david);
    authorRepository.save(chandu);
    authorRepository.save(raghu);

    authorService.printAll();
  }
}

Total project structure looks like below.


 

Build the project

Go to the location, where pom.xml is located and execute below command.

mvn package

Upon successful execution of the command, you can see below jar file.

n-plus-one-problem-0.0.1-SNAPSHOT.jar

 

Run the Application

Execute below command to run the application.

java -jar ./target/n-plus-one-problem-0.0.1-SNAPSHOT.jar

You can see below messages in the console.

Hibernate: insert into author (name,id) values (?,default)
Hibernate: insert into book (author_id,title,id) values (?,?,default)
Hibernate: insert into book (author_id,title,id) values (?,?,default)
Hibernate: insert into author (name,id) values (?,default)
Hibernate: insert into book (author_id,title,id) values (?,?,default)
Hibernate: insert into book (author_id,title,id) values (?,?,default)
Hibernate: insert into author (name,id) values (?,default)
Hibernate: insert into book (author_id,title,id) values (?,?,default)
Hibernate: insert into book (author_id,title,id) values (?,?,default)
Hibernate: insert into author (name,id) values (?,default)
Hibernate: insert into book (author_id,title,id) values (?,?,default)
Hibernate: insert into author (name,id) values (?,default)
Hibernate: insert into book (author_id,title,id) values (?,?,default)
Hibernate: select a1_0.id,a1_0.name from author a1_0
author : Author [id=1, name=Hari]
Hibernate: select b1_0.author_id,b1_0.id,b1_0.title from book b1_0 where b1_0.author_id=?
  Book [id=1, title=Hari Book 1]
  Book [id=2, title=Hari Book 2]
author : Author [id=2, name=Ram]
Hibernate: select b1_0.author_id,b1_0.id,b1_0.title from book b1_0 where b1_0.author_id=?
  Book [id=3, title=Ram Book 1]
  Book [id=4, title=Ram Book 2]
author : Author [id=3, name=David]
Hibernate: select b1_0.author_id,b1_0.id,b1_0.title from book b1_0 where b1_0.author_id=?
  Book [id=5, title=David Book 1]
  Book [id=6, title=David Book 2]
author : Author [id=4, name=Chandu]
Hibernate: select b1_0.author_id,b1_0.id,b1_0.title from book b1_0 where b1_0.author_id=?
  Book [id=7, title=David Book 1]
author : Author [id=5, name=Raghu]
Hibernate: select b1_0.author_id,b1_0.id,b1_0.title from book b1_0 where b1_0.author_id=?
  Book [id=8, title=Raghu Book 1]

From the console messages, you can confirm that a separate query is fired for every user.

 

You can download this application from this link.

 

Previous                                                    Next                                                    Home

No comments:

Post a Comment