This is continuation to my previous post ‘Understanding the N+1 SelectProblem’. In this post, I am going to show, how can we address N+1 select problem using @EntityGraph annotation.
@EntityGraph is a JPA annotation used to define which related entities should be fetched from the database when a query is executed. This is particularly useful to avoid the N+1 select problem and optimize performance by controlling the loading strategy.
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;
}
public interface AuthorRepository extends JpaRepository<Author, Integer> {
@EntityGraph(attributePaths = {"books"})
List<Author> findAll();
}
When we call the method ‘findAll’, following query is generated by spring.
select author.id,book.AUTHOR_id,book.id,book.title,author.name from AUTHOR author left join BOOK book on author.id=book.author_id
1. @EntityGraph
The @EntityGraph annotation is used to define a JPA entity graph. An entity graph allows you to specify which associations (related entities) should be fetched along with the entity you are querying. This helps in optimizing performance by reducing the number of SQL queries executed, thereby avoiding the N+1 select problem.
2. attributePaths = {"books"}
The attributePaths attribute of the @EntityGraph annotation specifies which attributes (typically relationships) of the entity should be eagerly fetched. In this case, "books" refers to the association between the Author entity and the Book entity.
For example, if the Author entity has a @OneToMany or @ManyToMany relationship with the Book entity, specifying "books" means that when fetching Author entities, the associated Book entities should be fetched in the same query.
Find the below working application.
Step 1: Create new maven project ‘avoid-n-plus-one-problem-using-entitygraph’.
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>avoid-n-plus-one-problem-using-entitygraph</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 repository interfaces.
AuthorRepository.java
package com.sample.app.repository;
import java.util.List;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
import com.sample.app.entity.Author;
public interface AuthorRepository extends JpaRepository<Author, Integer> {
@EntityGraph(attributePaths = {"books"})
List<Author> findAll();
}
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 5: 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 6: 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 7: Create application.yml file under src/main/resources 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.javapackage 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();
}
}
Build the Artifact
Go to the folder where pom.xml is located and execute below command to generate the Artifact.mvn package
Upon successful execution of the command, you can see a jar file ‘avoid-n-plus-one-problem-using-entitygraph-0.0.1-SNAPSHOT.jar’ in the target folder.
Run the Application
Execute below command to run the application.java -jar ./target/avoid-n-plus-one-problem-using-entitygraph-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,b1_0.author_id,b1_0.id,b1_0.title,a1_0.name from author a1_0 left join book b1_0 on a1_0.id=b1_0.author_id author : Author [id=1, name=Hari] Book [id=1, title=Hari Book 1] Book [id=2, title=Hari Book 2] author : Author [id=2, name=Ram] Book [id=3, title=Ram Book 1] Book [id=4, title=Ram Book 2] author : Author [id=3, name=David] Book [id=5, title=David Book 1] Book [id=6, title=David Book 2] author : Author [id=4, name=Chandu] Book [id=7, title=David Book 1] author : Author [id=5, name=Raghu] Book [id=8, title=Raghu Book 1]
You can download this application from this link.
No comments:
Post a Comment