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 Hibernate Entity Manager to solve N+1 problem.
graph TD A[Hibernate Entity Manager] --> | Using Named Entity Graph API | B(NamedEntityGraph) A[Hibernate Entity Manager] --> | Using Dynamically pass graph attribute nodes | C(Dynamic Entity Graph API) A[Hibernate Entity Manager] --> | Using Root Entity Graph API | D(RootGraph API) 
EntityManager entityManager = getEntityManager(); Author author = entityManager.find(Author.class, 1); System.out.println(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books."); Set<Book> books = author.getBooks(); for(Book book: books) { System.out.println(book.getPublisher()); } We can notice when I tried to access author, books and publisher, it issues 3 queries to fetch the data.
19:22:24,828 DEBUG [org.hibernate.SQL] - select author0_.id as id1_0_0_, author0_.first_name as first_na2_0_0_, author0_.last_name as last_nam3_0_0_, author0_.version as version4_0_0_ from author author0_ where author0_.id=? 19:22:24,867 DEBUG [org.hibernate.SQL] - 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=? Joshua Bloch wrote 1 books. 19:22:24,887 DEBUG [org.hibernate.SQL] - 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=? Publisher name: Addison-Wesley Professional @Entity @Table(name = "author") @NamedEntityGraph(name = "graph.author.books.publisher", attributeNodes = @NamedAttributeNode(value = "books", subgraph = "books"), subgraphs = @NamedSubgraph(name = "books", attributeNodes = @NamedAttributeNode("publisher")) ) @NamedEntityGraph(name = "graph.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 this example we are using @NamedEntityGraph name attribute graph.author.books to get entity graph api. It will load only Book objects but not publisher associate with books
EntityManager entityManager = getEntityManager(); String hql = "SELECT a FROM Author a WHERE a.id = 1"; EntityGraph graph = entityManager.getEntityGraph("graph.author.books"); TypedQuery<Author> query = entityManager.createQuery(hql, Author.class); query.setHint("javax.persistence.loadgraph", graph); Author author = query.getSingleResult(); System.out.println(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books."); Set<Book> books = author.getBooks(); for(Book book: books) { System.out.println(book.getPublisher()); }we can notice that it issued only 2 queries to fetch data. This is because we included books as part of graph.
19:27:38,472 DEBUG [org.hibernate.SQL] - 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=1 Joshua Bloch wrote 1 books. 19:27:38,534 DEBUG [org.hibernate.SQL] - 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=? Publisher name: Addison-Wesley Professional In this example we are using @NamedEntityGraph name attribute graph.author.books.publisher to get entity graph api. It will load Book objects as well as publisher associate with books
EntityManager entityManager = getEntityManager(); String hql = "SELECT a FROM Author a WHERE a.id = 1"; EntityGraph graph = entityManager.getEntityGraph("graph.author.books.publisher"); TypedQuery<Author> query = entityManager.createQuery(hql, Author.class); query.setHint("javax.persistence.loadgraph", graph); Author author = query.getSingleResult(); System.out.println(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books."); Set<Book> books = author.getBooks(); for(Book book: books) { System.out.println(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.
19:31:18,884 DEBUG [org.hibernate.SQL] - 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=1 Joshua Bloch wrote 1 books. Publisher name: Addison-Wesley Professional @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 this example we can set attributes dynamically. Graph API has addAttributeNodes(property name) method to set property names. We can set as many as attributesnodes to graph api. Here Author class has property name books. So we setting books attribute to graph. It will load only Book objects but not publisher associate with books
String HQL ="SELECT a FROM Author a WHERE a.id = 1"; EntityManager entityManager = getEntityManager(); EntityGraph<Author> graph = entityManager.createEntityGraph(Author.class); graph.addAttributeNodes("books"); TypedQuery<Author> query = entityManager.createQuery(HQL, Author.class); query.setHint("javax.persistence.loadgraph", graph); Author author = query.getSingleResult(); System.out.println(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books."); Set<Book> books = author.getBooks(); for(Book book: books) { System.out.println(book.getPublisher()); } 20:02:56,111 DEBUG [org.hibernate.SQL] - 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=1 Joshua Bloch wrote 1 books. 20:02:56,178 DEBUG [org.hibernate.SQL] - 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=? Publisher name: Addison-Wesley Professional Graph API provide to add sub graph and its properties also, by using addSubgraph(property name) and addAttributeNodes(property name) 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
EntityManager entityManager = getEntityManager(); String HQL ="SELECT a FROM Author a WHERE a.id = 1"; EntityGraph<Author> graph = entityManager.createEntityGraph(Author.class); graph.addSubgraph("books").addAttributeNodes("publisher"); TypedQuery<Author> query = entityManager.createQuery(HQL, Author.class); query.setHint("javax.persistence.loadgraph", graph); Author author = query.getSingleResult(); System.out.println(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books."); Set<Book> books = author.getBooks(); for(Book book: books) { System.out.println(book.getPublisher()); }20:03:52,435 DEBUG [org.hibernate.SQL] - 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=1 Joshua Bloch wrote 1 books. Publisher name: Addison-Wesley Professional @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 this example we need to create RootGraph object and pass all properties to load.
In this example, it will load only Book objects but not publisher associate with books
String HQL ="SELECT a FROM Author a WHERE a.id = 1"; RootGraph<Author> graph = GraphParser.parse(Author.class, "books", entityManager); Map<String, Object> properties = new HashMap<String, Object>(); properties.put("javax.persistence.loadgraph", graph); TypedQuery<Author> query = entityManager.createQuery(HQL, Author.class); Author author = query.getSingleResult(); System.out.println(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books."); Set<Book> books = author.getBooks(); for(Book book: books) { System.out.println(book.getPublisher()); }20:04:50,371 DEBUG [org.hibernate.SQL] - select author0_.id as id1_0_0_, author0_.first_name as first_na2_0_0_, author0_.last_name as last_nam3_0_0_, author0_.version as version4_0_0_, books1_.author_id as author_i2_2_1_, book2_.id as book_id1_2_1_, book2_.id as id1_1_2_, book2_.publisher_id as publishe5_1_2_, book2_.publishing_date as publishi2_1_2_, book2_.title as title3_1_2_, book2_.version as version4_1_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 where author0_.id=? Joshua Bloch wrote 1 books. 20:04:50,424 DEBUG [org.hibernate.SQL] - 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=? Publisher name: Addison-Wesley Professional In this example we need to create RootGraph object and pass all properties to load. Author class has property name books and Book class has property name publisher
In this example, it will load Book objects as well as publisher associate with books
String HQL ="SELECT a FROM Author a WHERE a.id = 1"; RootGraph<Author> graph = GraphParser.parse(Author.class, "books(publisher)", entityManager); Map<String, Object> properties = new HashMap<String, Object>(); properties.put("javax.persistence.loadgraph", graph); TypedQuery<Author> query = entityManager.createQuery(HQL, Author.class); Author author = query.getSingleResult(); System.out.println(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books."); Set<Book> books = author.getBooks(); for(Book book: books) { System.out.println(book.getPublisher()); }20:05:40,676 DEBUG [org.hibernate.SQL] - select author0_.id as id1_0_0_, author0_.first_name as first_na2_0_0_, author0_.last_name as last_nam3_0_0_, author0_.version as version4_0_0_, books1_.author_id as author_i2_2_1_, book2_.id as book_id1_2_1_, book2_.id as id1_1_2_, book2_.publisher_id as publishe5_1_2_, book2_.publishing_date as publishi2_1_2_, book2_.title as title3_1_2_, book2_.version as version4_1_2_, publisher3_.id as id1_3_3_, publisher3_.name as name2_3_3_, publisher3_.version as version3_3_3_ 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=? Joshua Bloch wrote 1 books. Publisher name: Addison-Wesley Professional