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.