You signed in with another tab or window.Reload to refresh your session.You signed out in another tab or window.Reload to refresh your session.You switched accounts on another tab or window.Reload to refresh your session.Dismiss alert
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 TDA[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 )
Loading
In this example we will useHibernate Entity Manager to solve N+1 problem.
graph TDA[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)
Loading
DB Design
Without Entity Graph
EntityManagerentityManager =getEntityManager();Authorauthor =entityManager.find(Author.class,1);System.out.println(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books.");Set<Book>books =author.getBooks();for(Bookbook: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
Named Entity Graph
Entity Class (declaring Named Entity Graph as below)
In this example we are using@NamedEntityGraph name attributegraph.author.books to get entity graph api.It will load only Book objects but not publisher associate with books
EntityManagerentityManager =getEntityManager();Stringhql ="SELECT a FROM Author a WHERE a.id = 1";EntityGraphgraph =entityManager.getEntityGraph("graph.author.books");TypedQuery<Author>query =entityManager.createQuery(hql,Author.class);query.setHint("javax.persistence.loadgraph",graph);Authorauthor =query.getSingleResult();System.out.println(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books.");Set<Book>books =author.getBooks();for(Bookbook: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=1Joshua 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 attributegraph.author.books.publisher to get entity graph api.It will load Book objects as well as publisher associate with books
EntityManagerentityManager =getEntityManager();Stringhql ="SELECT a FROM Author a WHERE a.id = 1";EntityGraphgraph =entityManager.getEntityGraph("graph.author.books.publisher");TypedQuery<Author>query =entityManager.createQuery(hql,Author.class);query.setHint("javax.persistence.loadgraph",graph);Authorauthor =query.getSingleResult();System.out.println(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books.");Set<Book>books =author.getBooks();for(Bookbook: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=1Joshua Bloch wrote 1 books.Publisher name: Addison-Wesley Professional
In this example we can set attributes dynamically. Graph API hasaddAttributeNodes(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
StringHQL ="SELECT a FROM Author a WHERE a.id = 1";EntityManagerentityManager =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);Authorauthor =query.getSingleResult();System.out.println(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books.");Set<Book>books =author.getBooks();for(Bookbook: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=1Joshua 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 usingaddSubgraph(property name) andaddAttributeNodes(property name)Author class has property namebooks and Book class has property namepublisherIt will load Book objects as well as publisher associate with books
EntityManagerentityManager =getEntityManager();StringHQL ="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);Authorauthor =query.getSingleResult();System.out.println(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books.");Set<Book>books =author.getBooks();for(Bookbook: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=1Joshua Bloch wrote 1 books.Publisher name: Addison-Wesley Professional
In this example we need to createRootGraph object and pass all properties to load.
In this example, it will load only Book objects but not publisher associate with books
StringHQL ="SELECT a FROM Author a WHERE a.id = 1";RootGraph<Author>graph =GraphParser.parse(Author.class,"books",entityManager);Map<String,Object>properties =newHashMap<String,Object>();properties.put("javax.persistence.loadgraph",graph);TypedQuery<Author>query =entityManager.createQuery(HQL,Author.class);Authorauthor =query.getSingleResult();System.out.println(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books.");Set<Book>books =author.getBooks();for(Bookbook: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 createRootGraph object and pass all properties to load. Author class has property namebooks and Book class has property namepublisher
In this example, it will load Book objects as well as publisher associate with books
StringHQL ="SELECT a FROM Author a WHERE a.id = 1";RootGraph<Author>graph =GraphParser.parse(Author.class,"books(publisher)",entityManager);Map<String,Object>properties =newHashMap<String,Object>();properties.put("javax.persistence.loadgraph",graph);TypedQuery<Author>query =entityManager.createQuery(HQL,Author.class);Authorauthor =query.getSingleResult();System.out.println(author.getFirstName()+" "+author.getLastName()+" wrote "+author.getBooks().size()+" books.");Set<Book>books =author.getBooks();for(Bookbook: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
