N+1 problem in Hibernate

Understanding N+1 Problem in Hibernate.

Hibernate N+1 problem happens when two entities have association mapping. Consider we have entities called Book.java and Story.java. Both entities are associated in OneToMany bidirectional relations.

@Entity
public class Book {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int bookId;

	@Column(name = "book_name")
	private String bookName;
	
	
	@OneToMany(mappedBy="book", cascade = CascadeType.PERSIST)
	@JsonIgnoreProperties("book")
	private List<Story> storyList = new ArrayList<>();

}

Story.java

@Entity
public class Story {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int storyId;

	@Column(name = "story_name")
	private String storyName;

	@ManyToOne
	@JoinColumn(name = "book_id", referencedColumnName = "bookId")
	@JsonIgnoreProperties("storyList")
	private Book book;

}

Also, we have 5 books in the database and each book contains 2stories.

Each book contains two stories as below.

Spring Data JPA Interview Questions and Answers
How to write a custom method in the repository in Spring Data JPA

In case of the n+1 problem, if we fetch books(i.e as of now 5 records in DB) from the database then one query will get fired for the book and additional five select queries will get fired for the story.

In this scenario –

1= Query generated for parent entity.

n = Query generated for child entity(i.e 5 queries)

Replicating N+1 using HQL.

public List<Book> findBooks() {

    Session session = entityManager.unwrap(Session.class);
    List<Book> books = session.createQuery("From Book", Book.class).getResultList();
    return books;
}

For the above code, one select query is for the book and five select queries are for the story. The query generated for the above code is below.

Hibernate: 
    select
        book0_.book_id as book_id1_0_,
        book0_.book_name as book_nam2_0_ 
    from
        book book0_
Hibernate: 
    select
        storylist0_.book_id as book_id3_1_0_,
        storylist0_.story_id as story_id1_1_0_,
        storylist0_.story_id as story_id1_1_1_,
        storylist0_.book_id as book_id3_1_1_,
        storylist0_.story_name as story_na2_1_1_ 
    from
        story storylist0_ 
    where
        storylist0_.book_id=?
Hibernate: 
    select
        storylist0_.book_id as book_id3_1_0_,
        storylist0_.story_id as story_id1_1_0_,
        storylist0_.story_id as story_id1_1_1_,
        storylist0_.book_id as book_id3_1_1_,
        storylist0_.story_name as story_na2_1_1_ 
    from
        story storylist0_ 
    where
        storylist0_.book_id=?
Hibernate: 
    select
        storylist0_.book_id as book_id3_1_0_,
        storylist0_.story_id as story_id1_1_0_,
        storylist0_.story_id as story_id1_1_1_,
        storylist0_.book_id as book_id3_1_1_,
        storylist0_.story_name as story_na2_1_1_ 
    from
        story storylist0_ 
    where
        storylist0_.book_id=?
Hibernate: 
    select
        storylist0_.book_id as book_id3_1_0_,
        storylist0_.story_id as story_id1_1_0_,
        storylist0_.story_id as story_id1_1_1_,
        storylist0_.book_id as book_id3_1_1_,
        storylist0_.story_name as story_na2_1_1_ 
    from
        story storylist0_ 
    where
        storylist0_.book_id=?
Hibernate: 
    select
        storylist0_.book_id as book_id3_1_0_,
        storylist0_.story_id as story_id1_1_0_,
        storylist0_.story_id as story_id1_1_1_,
        storylist0_.book_id as book_id3_1_1_,
        storylist0_.story_name as story_na2_1_1_ 
    from
        story storylist0_ 
    where
        storylist0_.book_id=?

Solution for N+1 problem using the HQL.

We can write a join query using HQL as below.

public List<Book> findBooks() {

    Session session = entityManager.unwrap(Session.class);
    List<Book> books = session.createQuery("From Book book JOIN fetch book.storyList", Book.class).getResultList();
    return books;
}

We don’t have five additional queries for stories.

Hibernate: 
    select
        book0_.book_id as book_id1_0_0_,
        storylist1_.story_id as story_id1_1_1_,
        book0_.book_name as book_nam2_0_0_,
        storylist1_.book_id as book_id3_1_1_,
        storylist1_.story_name as story_na2_1_1_,
        storylist1_.book_id as book_id3_1_0__,
        storylist1_.story_id as story_id1_1_0__ 
    from
        book book0_ 
    inner join
        story storylist1_ 
            on book0_.book_id=storylist1_.book_id

Replicating N+1 problem using Spring Data JPA.

See Spring Data JPA interview questions with answers.

public List<Book> findBooks() {
    return (List<Book>) bookRepository.findAll();
}

For the above code again we will have one query for the book and five queries will have for the story.

Solution for n+1 problem using Spring Data JPA.

Writing JPQL using Spring Data JPA.

@Repository
public interface BookRepository extends CrudRepository<Book, Serializable> {
	
	@Query("SELECT book FROM Book book LEFT JOIN FETCH book.storyList") 
	public List<Book> findBooks();
}

The query generated for the above code.

Hibernate: 
    select
        book0_.book_id as book_id1_0_0_,
        storylist1_.story_id as story_id1_1_1_,
        book0_.book_name as book_nam2_0_0_,
        storylist1_.book_id as book_id3_1_1_,
        storylist1_.story_name as story_na2_1_1_,
        storylist1_.book_id as book_id3_1_0__,
        storylist1_.story_id as story_id1_1_0__ 
    from
        book book0_ 
    left outer join
        story storylist1_ 
            on book0_.book_id=storylist1_.book_id

Hibernate N+1 problem example using spring boot and Spring Data JPA.

Open eclipse and create maven project, Don’t forget to check ‘Create a simple project (skip)’ and click on next. Fill all details(GroupId – nplusonespringbootexample, ArtifactId – nplusonespringbootexample and name – nplusonespringbootexample) and click on finish. Keep packaging as the jar.

The directory structure of the example.

Define pom.xml file.

<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>hibernategetvsload</groupId>
	<artifactId>hibernategetvsload</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>hibernategetvsload</name>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.2.RELEASE</version>
	</parent>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
	</dependencies>
</project>

Define Entity and other classes/interfaces.

Book.java

package com.javatute.entity;

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

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

@Entity
public class Book {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int bookId;

	@Column(name = "book_name")
	private String bookName;

	@OneToMany(mappedBy = "book", cascade = CascadeType.PERSIST)
	@JsonIgnoreProperties("book")
	private List<Story> storyList = new ArrayList<>();

	public int getBookId() {
		return bookId;
	}

	public void setBookId(int bookId) {
		this.bookId = bookId;
	}

	public String getBookName() {
		return bookName;
	}

	public void setBookName(String bookName) {
		this.bookName = bookName;
	}

	public List<Story> getStoryList() {
		return storyList;
	}

	public void setStoryList(List<Story> storyList) {
		this.storyList = storyList;
	}

}

Story.java

package com.javatute.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

@Entity
public class Story {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int storyId;

	@Column(name = "story_name")
	private String storyName;

	@ManyToOne
	@JoinColumn(name = "book_id", referencedColumnName = "bookId")
	@JsonIgnoreProperties("storyList")
	private Book book;

	public int getStoryId() {
		return storyId;
	}

	public void setStoryId(int storyId) {
		this.storyId = storyId;
	}

	public String getStoryName() {
		return storyName;
	}

	public void setStoryName(String storyName) {
		this.storyName = storyName;
	}

	public Book getBook() {
		return book;
	}

	public void setBook(Book book) {
		this.book = book;
	}

	
}

BookService.java

package com.javatute.service;

import java.util.List;

import org.springframework.stereotype.Component;

import com.javatute.entity.Book;

@Component
public interface BookService {
	public Book saveBook(Book book);

	public List<Book> findBooks();
}

BookRepository.java

package com.javatute.repository;

import java.io.Serializable;
import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.javatute.entity.Book;

@Repository
public interface BookRepository extends CrudRepository<Book, Serializable> {

	// Solution using JPQL
	/*
	 * @Query("SELECT book FROM Book book LEFT JOIN FETCH book.storyList")
	 * public List<Book> findBooks();
	 */
}

Define serviceimpl class.

BookServiceImpl.java

package com.javatute.impl;

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

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.javatute.entity.Book;
import com.javatute.entity.Story;
import com.javatute.repository.BookRepository;
import com.javatute.service.BookService;

@Service("bookServiceImpl")
public class BookServiceImpl implements BookService {

	@Autowired
	private BookRepository bookRepository;

	@PersistenceContext
	private EntityManager entityManager;

	public Book saveBook(Book book) {

		List<Story> storyList = new ArrayList<>();

		// create first story
		Story story1 = new Story();
		story1.setStoryName("Push Ki Rat");

		// create second story
		Story story2 = new Story();
		story2.setStoryName("Idgah");

		// add all story into storyList. Till here we have prepared data for
		// OneToMany
		storyList.add(story1);
		storyList.add(story2);

		// Prepare data for ManyToOne
		story1.setBook(book);
		story2.setBook(book);

		book.setStoryList(storyList);
		book = bookRepository.save(book);

		return book;

	}

        @Transactional
	public List<Book> findBooks() {

		Session session = entityManager.unwrap(Session.class);
		// Below code will cause n+1 problem
		/*
		 * List<Book> books = session.createQuery("From Book",
		 * Book.class).getResultList(); return books;
		 */

		// HQL solution
		List<Book> books = session.createQuery("From Book book JOIN fetch book.storyList", Book.class).getResultList();
		return books;

		// return (List<Book>)bookRepository.findBooks();
	}

}

See more details about how to get a session from entityManager in the Spring Boot application. Also, see a depth tutorial about @Transactional here.

BookController.java

package com.javatute.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import com.javatute.entity.Book;
import com.javatute.service.BookService;


@RestController
@RequestMapping(value = "/book")
public class BookController {

	@Autowired
	private BookService bookService;

	@RequestMapping(value = "/savebook", method = RequestMethod.POST)
	@ResponseBody
	public Book saveBook(@RequestBody Book book) {
		Book bookResponse = bookService.saveBook(book);
		return bookResponse;
	}

	@RequestMapping(value = "/books", method = RequestMethod.GET)
	@ResponseBody
	public List<Book> getBookDetails() {
		List<Book> books= bookService.findBooks();

		return books;
	}

}

@RestController – This annotation is a combined form of @Controller and @ResponseBody. See more detail here.

Note – See here more about @Component, @Controller, @Service and @Repository annotations here.

JpaConfig.java

package com.javatute.config;

import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@Configuration
@EnableJpaRepositories(basePackages = "com.javatute.repository")
public class JpaConfig {

}

@Configuration – This annotation is used for configuration purpose. See more details about @Configuration annotation here.

application.properties file

spring.datasource.url=jdbc:mysql://localhost:3306/springbootcrudexample
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
server.port = 9091

SpringMain.java

package com.javatute.main;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication
@ComponentScan(basePackages="com.javatute.*")
@EntityScan("com.javatute.*")
public class SpringMain {
	public static void main(String[] args) {

        SpringApplication.run(SpringMain.class, args);
    }

}

Let’s run and deploy the example.

Url for testing purpose –

http://localhost:9091/book/books

Use the above URL and see the console. We should be able to see the generated query.

That’s all about the N+1 problem in Hibernate.

Download source code from Github.

See N+1 problem docs here.

You may like other Hibernate tutorials.

• @Version Annotation Example In Hibernate.
• Dirty Checking in Hibernate
• Hibernate Query Language example
• Difference between save() and persist() in Hibernate
• Hibernate Validator Constraints Example Using Spring Boot.
• @Temporal Annotation Example In Hibernate/Jpa Using Spring Boot.
• Hibernate Table Per Concrete Class Spring Boot.
• Hibernate Table Per Subclass Inheritance Spring Boot.
• Hibernate Single Table Inheritance using Spring Boot.
• One To One Mapping Annotation Example in Hibernate/JPA using Spring Boot and Oracle.
• One To One Bidirectional Mapping Example In Hibernate/JPA Using Spring Boot and Oracle.
• One To Many Mapping Annotation Example In Hibernate/JPA Using Spring Boot And Oracle.
• Many To One Unidirectional Mapping In Hibernate/JPA Annotation Example Using Spring Boot and Oracle.
• One To Many Bidirectional Mapping In Hibernate/JPA Annotation Example Using Spring Boot and Oracle.
• Many To Many Mapping Annotation Example In Hibernate/JPA Using Spring Boot And

Spring Data JPA tutorial.

• Spring Data JPA greater than Example
• Spring Data JPA less than Example
• Spring Data JPA IsNull Example Using Spring Boot
• Spring Data findById() Vs getOne()
• Spring Data JPA CrudRepository findById()
• Spring Data JPA JpaRepository getOne()
• Spring Data CrudRepository saveAll() and findAll().
• Spring Data CrudRepository existsById()
• Spring Data JPA delete() vs deleteInBatch()
• Spring Data JPA deleteAll() Vs deleteAllInBatch()
• Spring Data JPA JpaRepository deleteAllInBatch()
• Spring Data JPA deleteInBatch() Example
• Spring Data JPA JpaRepository saveAndFlush() Example
• Spring Data JPA CrudRepository count() Example
• Spring Data JPA CrudRepository delete() and deleteAll()
• Spring Data JPA CrudRepository deleteById() Example
• CrudRepository findAllById() Example Using Spring Boot
• Spring Data CrudRepository save() Method.
• Sorting in Spring Data JPA using Spring Boot.
• Spring Data JPA example using spring boot.
• Spring Data JPA and its benefit.

Summary – If two entities are associated in one to many bidirectional mapping, N+1 problem can occur while fetching the parent entity. In hibernate N+1 problem 1 query will get generated for parent entity and n additional query will get generated for child entity. We can resolve N+1 problem using join.