In Hibernate/JPA we face N+1 Problem. First we will see what is the N+1 Problem.
In order to find, what is N+1 Problem Please go through the below stackoverflow link
We can fix N+1 select problem by using Entity Graphs provided by JPA 2.1. we can achieve it in the following ways.
- Using Entity Manager
- Using JPA Repository
- Using 3rd Party Entity Graph Utility
graph TD A[Entity Graph] -->|Using Entity Manager Class | B(Hibernate Entity Manager ) A[Entity Graph] -->|Using JPA Repository Class | C(JPA Repository ) A[Entity Graph] -->|Using 3rd Party Entity Graph | D(3rd Party Entity Graph Utility ) In this example we will use 3rd Party Entity Graph Utility to solve N+1 problem.
graph TD A[3rd Party Entity Graph Utility] --> | Using Named Entity Graph API | B(NamedEntityGraph) A[3rd Party Entity Graph Utility] --> | Using EntityGraph API | C(EntityGraph) 
<dependency> <groupId>com.cosium.spring.data</groupId> <artifactId>spring-data-jpa-entity-graph</artifactId> <version>2.3.1</version> </dependency>@Repository public interface AuthorRepository extends EntityGraphJpaRepository<Author, Integer> { @Query("SELECT author FROM Author author WHERE author.id = :id ") Author findAuthor(@Param("id") Integer id); @Query("SELECT author FROM Author author WHERE author.id = :id ") Author findBooks(@Param("id") Integer id,EntityGraph entityGraph); }@Test public void selectAuthor() { log.info("... selectAuthor ..."); Author author = authorRepository.findAuthor(1); displayAuthor(author); } protected void displayAuthor(Author author) { log.info(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books."); Set<Book> books = author.getBooks(); for(Book book: books) { log.info(book.getPublisher()); } } We can notice when I tried to access author, books and publisher, it issues 3 queries to fetch the data.
Hibernate: select author0_.`id` as id1_0_, author0_.`first_name` as first_na2_0_, author0_.`last_name` as last_nam3_0_, author0_.`version` as version4_0_ from `author` author0_ where author0_.`id`=? Hibernate: select books0_.`author_id` as author_i2_2_0_, books0_.`book_id` as book_id1_2_0_, book1_.`id` as id1_1_1_, book1_.`publisher_id` as publishe5_1_1_, book1_.`publishing_date` as publishi2_1_1_, book1_.`title` as title3_1_1_, book1_.`version` as version4_1_1_ from `book_author` books0_ inner join `book` book1_ on books0_.`book_id`=book1_.`id` where books0_.`author_id`=? 21:05:06.725 INFO 11284 ---[main] .t.m.r.TestJPARepositoryNamedEntityGraph : Joshua Bloch wrote 1 books. Hibernate: select publisher0_.`id` as id1_3_0_, publisher0_.`name` as name2_3_0_, publisher0_.`version` as version3_3_0_ from `publisher` publisher0_ where publisher0_.`id`=? 21:05:06.737 INFO 11284 ---[main] .t.m.r.TestJPARepositoryNamedEntityGraph : Publisher name: Addison-Wesley Professional @Entity @Table(name = "author") @NamedEntityGraph(name = "3rdpartygraph.author.books.publisher", attributeNodes = @NamedAttributeNode(value = "books", subgraph = "books"), subgraphs = @NamedSubgraph(name = "books", attributeNodes = @NamedAttributeNode("publisher")) ) @NamedEntityGraph(name = "3rdpartygraph.author.books", attributeNodes = @NamedAttributeNode(value = "books")) public class Author { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; @Version private Integer version; @Column(name = "first_name") private String firstName; @Column(name = "last_name") private String lastName; @ManyToMany(mappedBy="authors",fetch = FetchType.LAZY) private Set<Book> books = new HashSet<Book>(); } @Entity @Table(name = "book") public class Book { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; @Version private Integer version; private String title; @Temporal(TemporalType.DATE) @Column(name = "publishing_date") private Date publishingDate; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name="publisher_id") private Publisher publisher; @ManyToMany @JoinTable( name="book_author", joinColumns={@JoinColumn(name="book_id", referencedColumnName="id")}, inverseJoinColumns={@JoinColumn(name="author_id", referencedColumnName="id")}) private Set<Author> authors = new HashSet<Author>(); } @Entity @Table(name = "publisher") public class Publisher { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; @Version private Integer version; private String name; @OneToMany(mappedBy = "publisher") private Set<Book> books = new HashSet<Book>(); }In below example we are using EntityGraph pass as parameter in find methods. It will load only Book objects but not publisher associate with books
@Repository public interface AuthorRepository extends EntityGraphJpaRepository<Author, Integer> { @Query("SELECT author FROM Author author WHERE author.id = :id ") Author findBooks(@Param("id") Integer id,EntityGraph entityGraph); }@Test public void selectWithBooksUsingNamedEntityGraph() { log.info("... selectWithBooksUsingNamedEntityGraph ..."); Author author = authorRepository.findBooks(new Integer(1), EntityGraphUtils.fromName("3rdpartygraph.author.books",true)); displayAuthor(author); } protected void displayAuthor(Author author) { log.info(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books."); Set<Book> books = author.getBooks(); for(Book book: books) { log.info(book.getPublisher()); } } we can notice that it issued only 2 queries to fetch data. This is because we included books as part of graph.
Hibernate: select author0_.`id` as id1_0_0_, book2_.`id` as id1_1_1_, author0_.`first_name` as first_na2_0_0_, author0_.`last_name` as last_nam3_0_0_, author0_.`version` as version4_0_0_, book2_.`publisher_id` as publishe5_1_1_, book2_.`publishing_date` as publishi2_1_1_, book2_.`title` as title3_1_1_, book2_.`version` as version4_1_1_, books1_.`author_id` as author_i2_2_0__, books1_.`book_id` as book_id1_2_0__ from `author` author0_ left outer join `book_author` books1_ on author0_.`id`=books1_.`author_id` left outer join `book` book2_ on books1_.`book_id`=book2_.`id` where author0_.`id`=? 21:10:23.108 INFO 9004 --- [main] .t.m.r.TestJPARepositoryNamedEntityGraph : Joshua Bloch wrote 1 books. Hibernate: select publisher0_.`id` as id1_3_0_, publisher0_.`name` as name2_3_0_, publisher0_.`version` as version3_3_0_ from `publisher` publisher0_ where publisher0_.`id`=? 21:10:23.118 INFO 9004 ---[main] .t.m.r.TestJPARepositoryNamedEntityGraph : Publisher name: Addison-Wesley Professional In below example we are using EntityGraph pass as parameter in find methods.
It will load Book objects as well as publisher associate with books
@Repository public interface AuthorRepository extends EntityGraphJpaRepository<Author, Integer> { @Query("SELECT author FROM Author author WHERE author.id = :id ") Author findBooks(@Param("id") Integer id,EntityGraph entityGraph); }@Test public void selectWithBooksPublisherUsingNamedEntityGraph() { log.info("... selectWithBooksPublisherUsingNamedEntityGraph..."); Author author = authorRepository.findBooks(new Integer(1), EntityGraphUtils.fromName("3rdpartygraph.author.books.publisher",true)); displayAuthor(author); } protected void displayAuthor(Author author) { log.info(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books."); Set<Book> books = author.getBooks(); for(Book book: books) { log.info(book.getPublisher()); } } we can notice that it issued only one query to fetch all data. This is because we included books and publisher as part of graph.
Hibernate: select author0_.`id` as id1_0_0_, book2_.`id` as id1_1_1_, publisher3_.`id` as id1_3_2_, author0_.`first_name` as first_na2_0_0_, author0_.`last_name` as last_nam3_0_0_, author0_.`version` as version4_0_0_, book2_.`publisher_id` as publishe5_1_1_, book2_.`publishing_date` as publishi2_1_1_, book2_.`title` as title3_1_1_, book2_.`version` as version4_1_1_, books1_.`author_id` as author_i2_2_0__, books1_.`book_id` as book_id1_2_0__, publisher3_.`name` as name2_3_2_, publisher3_.`version` as version3_3_2_ from `author` author0_ left outer join `book_author` books1_ on author0_.`id`=books1_.`author_id` left outer join `book` book2_ on books1_.`book_id`=book2_.`id` left outer join `publisher` publisher3_ on book2_.`publisher_id`=publisher3_.`id` where author0_.`id`=? 21:12:23.046 INFO 3464 ---[main] .t.m.r.TestJPARepositoryNamedEntityGraph : Joshua Bloch wrote 1 books. @Entity public class Author { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; @Version private Integer version; @Column(name = "first_name") private String firstName; @Column(name = "last_name") private String lastName; @ManyToMany(mappedBy="authors",fetch = FetchType.LAZY) private Set<Book> books = new HashSet<Book>(); } @Entity @Table(name = "book") public class Book { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; @Version private Integer version; private String title; @Temporal(TemporalType.DATE) @Column(name = "publishing_date") private Date publishingDate; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name="publisher_id") private Publisher publisher; @ManyToMany @JoinTable( name="book_author", joinColumns={@JoinColumn(name="book_id", referencedColumnName="id")}, inverseJoinColumns={@JoinColumn(name="author_id", referencedColumnName="id")}) private Set<Author> authors = new HashSet<Author>(); } @Entity @Table(name = "publisher") public class Publisher { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; @Version private Integer version; private String name; @OneToMany(mappedBy = "publisher") private Set<Book> books = new HashSet<Book>(); }In below example we can set attributes paths. EntityGraphUtils.fromAttributePaths("books") It will load only Book objects but not publisher associate with books
@Repository public interface AuthorRepository extends EntityGraphJpaRepository<Author, Integer> { @Query("SELECT author FROM Author author WHERE author.id = :id ") Author findBooks(@Param("id") Integer id,EntityGraph entityGraph); }@Test public void selectWithBooks() { log.info("... selectWithBooksPublisher ..."); Author author = authorRepository.findBooks(new Integer(1), EntityGraphUtils.fromAttributePaths("books")); displayAuthor(author); } protected void displayAuthor(Author author) { log.info(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books."); Set<Book> books = author.getBooks(); for(Book book: books) { log.info(book.getPublisher()); } } Hibernate: select author0_.`id` as id1_0_0_, book2_.`id` as id1_1_1_, author0_.`first_name` as first_na2_0_0_, author0_.`last_name` as last_nam3_0_0_, author0_.`version` as version4_0_0_, book2_.`publisher_id` as publishe5_1_1_, book2_.`publishing_date` as publishi2_1_1_, book2_.`title` as title3_1_1_, book2_.`version` as version4_1_1_, books1_.`author_id` as author_i2_2_0__, books1_.`book_id` as book_id1_2_0__ from `author` author0_ left outer join `book_author` books1_ on author0_.`id`=books1_.`author_id` left outer join `book` book2_ on books1_.`book_id`=book2_.`id` where author0_.`id`=? 21:19:15.763 INFO 14156 --- [main] c.t.m.r.TestJPARepositoryEntityGraph : Joshua Bloch wrote 1 books. Hibernate: select publisher0_.`id` as id1_3_0_, publisher0_.`name` as name2_3_0_, publisher0_.`version` as version3_3_0_ from `publisher` publisher0_ where publisher0_.`id`=? 21:19:15.774 INFO 14156 --- [main] c.t.m.r.TestJPARepositoryEntityGraph : Publisher name: Addison-Wesley Professional In below example we can set attributes paths. EntityGraphUtils.fromAttributePaths("books","books.publisher") Author class has property name books and Book class has property name publisher It will load Book objects as well as publisher associate with books
@Repository public interface AuthorRepository extends JpaRepository<Author, Integer> { @Query("SELECT author FROM Author author WHERE author.id = :id ") Author findBooks(@Param("id") Integer id,EntityGraph entityGraph); }@Test public void selectWithBooksPublisher() { log.info("... selectWithBooksPublisher..."); Author author = authorRepository.findBooks(new Integer(1), EntityGraphUtils.fromAttributePaths("books","books.publisher")); displayAuthor(author); } protected void displayAuthor(Author author) { log.info(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books."); Set<Book> books = author.getBooks(); for(Book book: books) { log.info(book.getPublisher()); } } Hibernate: select author0_.`id` as id1_0_0_, book2_.`id` as id1_1_1_, publisher3_.`id` as id1_3_2_, author0_.`first_name` as first_na2_0_0_, author0_.`last_name` as last_nam3_0_0_, author0_.`version` as version4_0_0_, book2_.`publisher_id` as publishe5_1_1_, book2_.`publishing_date` as publishi2_1_1_, book2_.`title` as title3_1_1_, book2_.`version` as version4_1_1_, books1_.`author_id` as author_i2_2_0__, books1_.`book_id` as book_id1_2_0__, publisher3_.`name` as name2_3_2_, publisher3_.`version` as version3_3_2_ from `author` author0_ left outer join `book_author` books1_ on author0_.`id`=books1_.`author_id` left outer join `book` book2_ on books1_.`book_id`=book2_.`id` left outer join `publisher` publisher3_ on book2_.`publisher_id`=publisher3_.`id` where author0_.`id`=? 21:21:43.496 INFO 6476 ---[main] c.t.m.r.TestJPARepositoryEntityGraph : Joshua Bloch wrote 1 books. 21:21:43.497 INFO 6476 ---[main] c.t.m.r.TestJPARepositoryEntityGraph : Publisher name: Addison-Wesley Professional