Hibernate is a popular ORM (Object Relational Mapping) framework in the Java programming language ecosystem. Being a common framework does not mean there are no difficult elements to use.
ORMs are familiar to programmers and many programmers have used them to quickly launch applications and manipulate databases without having to write database query statements. But it can lead to a lot of problems if programmers are not familiar with the basic functions of the framework. And the problem of n + 1 query is such a problem.
This is also a question I was asked in an interview. There is a great article on how to solve this problem and I would like to share it again with you.
1. Issue n + 1 queries
If you’ve used Hibernate (or any other ORM framework), chances are you’ve encountered the notorious n + 1 problem over and over again.
N + 1 query problem occurs when while you load data in FetchType.LAZY mode in One-to-Many in parent-child relationship: 1 query to retrieve n parent objects
It takes n more queries (one for each lookup object) to retrieve sub objects
2. Illustrative example
Environment is: Spring Boot, JPA (Hibernate), H2 database. We created a database with 5 authors and 25 books. Each author assigns with 5 books
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | @Entity public class Author { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; private String firstName; private String lastName; private String address; @OneToMany(fetch=FetchType.LAZY, mappedBy="author") private List<Book> books; } . . . @Entity public class Book { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; private String title; private String isbn; @ManyToOne private Author author; } . . . public interface BookDataService extends JpaRepository<Book, Long>{ } . . . public interface AuthorDataService extends JpaRepository<Book, Long>{ } . . . List<Author> authors = authorDataService.findAll(); for (Author author : authors) { System.out.printf("Author: %s %s has %d books.%n", author.getFirstName(), author.getLastName(), author.getBooks().size()); } |
It is part of the code to print out the author’s name and the number of related books. Here we will have logs:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 | 13-01-2019 20:22:53.182 [restartedMain] INFO org.hibernate.hql.internal.QueryTranslatorFactoryInitiator.initiateService - HHH000397: Using ASTQueryTranslatorFactory Hibernate: select author0_.id as id1_0_, author0_.address as address2_0_, author0_.first_name as first_na3_0_, author0_.last_name as last_nam4_0_ from author author0_ Hibernate: select books0_.author_id as author_i4_1_0_, books0_.id as id1_1_0_, books0_.id as id1_1_1_, books0_.author_id as author_i4_1_1_, books0_.isbn as isbn2_1_1_, books0_.title as title3_1_1_ from book books0_ where books0_.author_id=? Author: Lucille Ordelt has 5 books. Hibernate: select books0_.author_id as author_i4_1_0_, books0_.id as id1_1_0_, books0_.id as id1_1_1_, books0_.author_id as author_i4_1_1_, books0_.isbn as isbn2_1_1_, books0_.title as title3_1_1_ from book books0_ where books0_.author_id=? Author: Slade Gerwood has 5 books. Hibernate: select books0_.author_id as author_i4_1_0_, books0_.id as id1_1_0_, books0_.id as id1_1_1_, books0_.author_id as author_i4_1_1_, books0_.isbn as isbn2_1_1_, books0_.title as title3_1_1_ from book books0_ where books0_.author_id=? Author: Wallis Croall has 5 books. Hibernate: select books0_.author_id as author_i4_1_0_, books0_.id as id1_1_0_, books0_.id as id1_1_1_, books0_.author_id as author_i4_1_1_, books0_.isbn as isbn2_1_1_, books0_.title as title3_1_1_ from book books0_ where books0_.author_id=? Author: Alena Hall has 5 books. Hibernate: select books0_.author_id as author_i4_1_0_, books0_.id as id1_1_0_, books0_.id as id1_1_1_, books0_.author_id as author_i4_1_1_, books0_.isbn as isbn2_1_1_, books0_.title as title3_1_1_ from book books0_ where books0_.author_id=? Author: Marcello Szymanski has 5 books. 13-01-2019 20:22:53.197 [restartedMain] INFO org.hibernate.engine.internal.StatisticalLoggingSessionEventListener.end - Session Metrics { 32411 nanoseconds spent acquiring 1 JDBC connections; 0 nanoseconds spent releasing 0 JDBC connections; 811912 nanoseconds spent preparing 6 JDBC statements; 993417 nanoseconds spent executing 6 JDBC statements; 0 nanoseconds spent executing 0 JDBC batches; 0 nanoseconds spent performing 0 L2C puts; 0 nanoseconds spent performing 0 L2C hits; 0 nanoseconds spent performing 0 L2C misses; 928594 nanoseconds spent executing 1 flushes (flushing a total of 30 entities and 5 collections); 9723 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections) } |
As you can see there is a query that is executed to retrieve the author list and then each individual query is executed to retrieve books for each author, resulting in an additional 6 queries being executed. . This is the default behavior in Hibernate, in my opinion it is fine as long as you give the book to an author.
But now consider the impact of this problem in an actual application, when the data is not on the same server and there are thousands of records. It will greatly improve your performance when you do it.
3. Use Fetch Mode
In Hibernate provide annotations (anotation) Fetch (…) can be used to determine how to fetch a set of related objects (such as loading book objects for author objects in the above example)
FetchMode.SUBSELECT
- “use a subquery to load additional collections” * – SUBSELECT
Using Fetch (FetchMode.SUBSELECT) will only give an additional query to retrieve books.
Modify the Author.java class as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 | @Entity public class Author { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; private String firstName; private String lastName; private String address; @OneToMany(fetch=FetchType.LAZY, mappedBy="author") @Fetch(FetchMode.SUBSELECT) private List<Book> books; } |
And now we will have the log:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | 13-01-2019 21:45:55.231 [restartedMain] INFO org.hibernate.hql.internal.QueryTranslatorFactoryInitiator.initiateService - HHH000397: Using ASTQueryTranslatorFactory Hibernate: select author0_.id as id1_0_, author0_.address as address2_0_, author0_.first_name as first_na3_0_, author0_.last_name as last_nam4_0_ from author author0_ Hibernate: select books0_.author_id as author_i4_1_1_, books0_.id as id1_1_1_, books0_.id as id1_1_0_, books0_.author_id as author_i4_1_0_, books0_.isbn as isbn2_1_0_, books0_.title as title3_1_0_ from book books0_ where books0_.author_id in ( select author0_.id from author author0_ ) Author: Lucille Ordelt has 5 books. Author: Slade Gerwood has 5 books. Author: Wallis Croall has 5 books. Author: Alena Hall has 5 books. Author: Marcello Szymanski has 5 books. 13-01-2019 21:45:55.262 [restartedMain] INFO org.hibernate.engine.internal.StatisticalLoggingSessionEventListener.end - Session Metrics { 43756 nanoseconds spent acquiring 1 JDBC connections; 0 nanoseconds spent releasing 0 JDBC connections; 2011683 nanoseconds spent preparing 2 JDBC statements; 1827476 nanoseconds spent executing 2 JDBC statements; 0 nanoseconds spent executing 0 JDBC batches; 0 nanoseconds spent performing 0 L2C puts; 0 nanoseconds spent performing 0 L2C hits; 0 nanoseconds spent performing 0 L2C misses; 771397 nanoseconds spent executing 1 flushes (flushing a total of 30 entities and 5 collections); 9724 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections) } |
We can see only two queries being executed, one for retrieving the author list, the other for retrieving books related to these authors. JOIN FETCH What if we wanted to fetch all authors and their books in a single query?
If I wanted to write a query for this purpose, I would join the tables together.
Do it in a way ORM is familiar to developers using the Query annotation.
Re-use the AuthorDataService.java class as follows:
1 2 3 4 5 | public interface AuthorDataService extends JpaRepository<Author, Long>{ @Query("select a from Author a join fetch a.books") List<Author> findAll(); } |
Now we can log as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | Hibernate: select author0_.id as id1_0_0_, books1_.id as id1_1_1_, author0_.address as address2_0_0_, author0_.first_name as first_na3_0_0_, author0_.last_name as last_nam4_0_0_, books1_.author_id as author_i4_1_1_, books1_.isbn as isbn2_1_1_, books1_.title as title3_1_1_, books1_.author_id as author_i4_1_0__, books1_.id as id1_1_0__ from author author0_ inner join book books1_ on author0_.id=books1_.author_id Author: Lucille Ordelt has 5 books. Author: Lucille Ordelt has 5 books. Author: Lucille Ordelt has 5 books. Author: Lucille Ordelt has 5 books. Author: Lucille Ordelt has 5 books. Author: Slade Gerwood has 5 books. Author: Slade Gerwood has 5 books. Author: Slade Gerwood has 5 books. Author: Slade Gerwood has 5 books. Author: Slade Gerwood has 5 books. Author: Wallis Croall has 5 books. Author: Wallis Croall has 5 books. Author: Wallis Croall has 5 books. Author: Wallis Croall has 5 books. Author: Wallis Croall has 5 books. Author: Alena Hall has 5 books. Author: Alena Hall has 5 books. Author: Alena Hall has 5 books. Author: Alena Hall has 5 books. Author: Alena Hall has 5 books. Author: Marcello Szymanski has 5 books. Author: Marcello Szymanski has 5 books. Author: Marcello Szymanski has 5 books. Author: Marcello Szymanski has 5 books. Author: Marcello Szymanski has 5 books. 13-01-2019 22:19:06.954 [restartedMain] INFO org.hibernate.engine.internal.StatisticalLoggingSessionEventListener.end - Session Metrics { 37273 nanoseconds spent acquiring 1 JDBC connections; 0 nanoseconds spent releasing 0 JDBC connections; 426213 nanoseconds spent preparing 1 JDBC statements; 324117 nanoseconds spent executing 1 JDBC statements; 0 nanoseconds spent executing 0 JDBC batches; 0 nanoseconds spent performing 0 L2C puts; 0 nanoseconds spent performing 0 L2C hits; 0 nanoseconds spent performing 0 L2C misses; 616901 nanoseconds spent executing 1 flushes (flushing a total of 30 entities and 5 collections); 9723 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections) } |
The problem has been solved! Mostly, using ** JOIN FETCH ** solves the problem of multiple queries using joins but it returns duplicate records for each Author. But that is what joins are supposed to do
1 2 3 4 | select * from author author0_ inner join book books1_ on author0_.id=books1_.author_id |
Performing the above query produces the following result, where each author is duplicated with their data
The problem with repeating the record is to add DISTINCT to the query:
1 2 3 4 5 | public interface AuthorDataService extends JpaRepository<Author, Long>{ @Query("select distinct a from Author a join fetch a.books") List<Author> findAll(); } |
Logs:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | Hibernate: select distinct author0_.id as id1_0_0_, books1_.id as id1_1_1_, author0_.address as address2_0_0_, author0_.first_name as first_na3_0_0_, author0_.last_name as last_nam4_0_0_, books1_.author_id as author_i4_1_1_, books1_.isbn as isbn2_1_1_, books1_.title as title3_1_1_, books1_.author_id as author_i4_1_0__, books1_.id as id1_1_0__ from author author0_ inner join book books1_ on author0_.id=books1_.author_id Author: Slade Gerwood has 5 books. Author: Lucille Ordelt has 5 books. Author: Alena Hall has 5 books. Author: Wallis Croall has 5 books. Author: Marcello Szymanski has 5 books. 13-01-2019 22:20:00.730 [restartedMain] INFO org.hibernate.engine.internal.StatisticalLoggingSessionEventListener.end - Session Metrics { 38353 nanoseconds spent acquiring 1 JDBC connections; 0 nanoseconds spent releasing 0 JDBC connections; 494818 nanoseconds spent preparing 1 JDBC statements; 979372 nanoseconds spent executing 1 JDBC statements; 0 nanoseconds spent executing 0 JDBC batches; 0 nanoseconds spent performing 0 L2C puts; 0 nanoseconds spent performing 0 L2C hits; 0 nanoseconds spent performing 0 L2C misses; 681185 nanoseconds spent executing 1 flushes (flushing a total of 30 entities and 5 collections); 10264 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections) } |
4. Conclusion
So through this article I have presented to you query n + 1 query as well as the solution in Hibernate. Thank you for taking the time to read and hopefully it will help you! Reference: https://medium.com/@mansoor_ali/hibernate-n-1-queries-problem-8a926b69f618