<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Mansoor Ali on Medium]]></title>
        <description><![CDATA[Stories by Mansoor Ali on Medium]]></description>
        <link>https://medium.com/@mansoor_ali?source=rss-852a7e60daa1------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*w2w9_VD4FOTktmYP</url>
            <title>Stories by Mansoor Ali on Medium</title>
            <link>https://medium.com/@mansoor_ali?source=rss-852a7e60daa1------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 00:30:42 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@mansoor_ali/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Hibernate N+1 Queries Problem]]></title>
            <link>https://medium.com/@mansoor_ali/hibernate-n-1-queries-problem-8a926b69f618?source=rss-852a7e60daa1------2</link>
            <guid isPermaLink="false">https://medium.com/p/8a926b69f618</guid>
            <category><![CDATA[java]]></category>
            <category><![CDATA[hibernate]]></category>
            <category><![CDATA[jpa]]></category>
            <dc:creator><![CDATA[Mansoor Ali]]></dc:creator>
            <pubDate>Sun, 13 Jan 2019 17:57:47 GMT</pubDate>
            <atom:updated>2019-01-13T17:57:47.217Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*189yme4dxx8BE3E7__4SXA.png" /></figure><p>Hibernate is a very popular ORM framework in Java ecosystem. But being a popular framework doesn’t mean that it is free from all predicaments. ORMs are developer friendly and many developers use them to quickly bootstrap their applications and perform database operations without writing database queries — but it can lead to many problems if the developers are not familiar with the underlying functionality of the framework. The n+1 queries problem is one such problem.</p><h4><strong>N+1 Queries Problem</strong></h4><p>If you have been using Hibernate(or any other ORM for that matter), chances are you have faced the infamous <strong>n+1 queries problem</strong> one time or another.<br>The <strong>n+1 </strong>queries problem occurs while fetching lazy loaded <strong>One-to-Many</strong> parent-child relationships; <br><strong>1</strong> query to fetch <strong>n </strong>entities — parent<br><strong>n </strong>queries(1 for each entity) to fetch the lazy loaded child entities</p><h4><strong>Consider the following example</strong></h4><p>Environment: Spring Boot, JPA(Hibernate), H2 database.<br><em>I have initialized the database with 5 authors and 25 books; and assigned 5 books to each author.</em></p><pre>@Entity<br>public class Author {</pre><pre>@Id<br>@GeneratedValue(strategy = GenerationType.IDENTITY)<br>private Long id;<br>private String firstName;<br>private String lastName;<br>private String address;</pre><pre>@OneToMany(fetch=FetchType.LAZY, mappedBy=&quot;author&quot;)<br>private List&lt;Book&gt; books;</pre><pre>}</pre><pre>. . . </pre><pre>@Entity<br>public class Book {</pre><pre>@Id<br>@GeneratedValue(strategy = GenerationType.IDENTITY)<br>private Long id;<br>private String title;<br>private String isbn;</pre><pre>@ManyToOne<br>private Author author;</pre><pre>}</pre><pre>. . . </pre><pre>public interface BookDataService extends JpaRepository&lt;Book, Long&gt;{</pre><pre>}</pre><pre>. . .</pre><pre>public interface AuthorDataService extends JpaRepository&lt;Book, Long&gt;{</pre><pre>}</pre><pre>. . .</pre><pre>List&lt;Author&gt; authors = authorDataService.findAll();<br>for (Author author : authors) {<br>    System.out.printf(&quot;Author: %s %s has %d books.%n&quot;,<br>        author.getFirstName(), author.getLastName(),<br>        author.getBooks().size());<br>}</pre><p>The above piece of code prints the full name of the author and the number of books associated with each author.</p><p><strong>Logs:</strong></p><pre>13-01-2019 20:22:53.182 [restartedMain] INFO  org.hibernate.hql.internal.QueryTranslatorFactoryInitiator.initiateService - HHH000397: Using ASTQueryTranslatorFactory<br>Hibernate: <br>    select<br>        author0_.id as id1_0_,<br>        author0_.address as address2_0_,<br>        author0_.first_name as first_na3_0_,<br>        author0_.last_name as last_nam4_0_ <br>    from<br>        author author0_<br>Hibernate: <br>    select<br>        books0_.author_id as author_i4_1_0_,<br>        books0_.id as id1_1_0_,<br>        books0_.id as id1_1_1_,<br>        books0_.author_id as author_i4_1_1_,<br>        books0_.isbn as isbn2_1_1_,<br>        books0_.title as title3_1_1_ <br>    from<br>        book books0_ <br>    where<br>        books0_.author_id=?<br>Author: Lucille Ordelt has 5 books.<br>Hibernate: <br>    select<br>        books0_.author_id as author_i4_1_0_,<br>        books0_.id as id1_1_0_,<br>        books0_.id as id1_1_1_,<br>        books0_.author_id as author_i4_1_1_,<br>        books0_.isbn as isbn2_1_1_,<br>        books0_.title as title3_1_1_ <br>    from<br>        book books0_ <br>    where<br>        books0_.author_id=?<br>Author: Slade Gerwood has 5 books.<br>Hibernate: <br>    select<br>        books0_.author_id as author_i4_1_0_,<br>        books0_.id as id1_1_0_,<br>        books0_.id as id1_1_1_,<br>        books0_.author_id as author_i4_1_1_,<br>        books0_.isbn as isbn2_1_1_,<br>        books0_.title as title3_1_1_ <br>    from<br>        book books0_ <br>    where<br>        books0_.author_id=?<br>Author: Wallis Croall has 5 books.<br>Hibernate: <br>    select<br>        books0_.author_id as author_i4_1_0_,<br>        books0_.id as id1_1_0_,<br>        books0_.id as id1_1_1_,<br>        books0_.author_id as author_i4_1_1_,<br>        books0_.isbn as isbn2_1_1_,<br>        books0_.title as title3_1_1_ <br>    from<br>        book books0_ <br>    where<br>        books0_.author_id=?<br>Author: Alena Hall has 5 books.<br>Hibernate: <br>    select<br>        books0_.author_id as author_i4_1_0_,<br>        books0_.id as id1_1_0_,<br>        books0_.id as id1_1_1_,<br>        books0_.author_id as author_i4_1_1_,<br>        books0_.isbn as isbn2_1_1_,<br>        books0_.title as title3_1_1_ <br>    from<br>        book books0_ <br>    where<br>        books0_.author_id=?<br>Author: Marcello Szymanski has 5 books.<br>13-01-2019 20:22:53.197 [restartedMain] INFO  org.hibernate.engine.internal.StatisticalLoggingSessionEventListener.end - Session Metrics {<br>    32411 nanoseconds spent acquiring 1 JDBC connections;<br>    0 nanoseconds spent releasing 0 JDBC connections;<br><strong>    811912 nanoseconds spent preparing 6 JDBC statements;</strong><br><strong>    993417 nanoseconds spent executing 6 JDBC statements;</strong><br>    0 nanoseconds spent executing 0 JDBC batches;<br>    0 nanoseconds spent performing 0 L2C puts;<br>    0 nanoseconds spent performing 0 L2C hits;<br>    0 nanoseconds spent performing 0 L2C misses;<br><strong>    928594 nanoseconds spent executing 1 flushes (flushing a total of 30 entities and 5 collections);<br></strong>    9723 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)<br>}</pre><p>As you can see 1 query is executed to get all authors and then a separate query is executed to get the books for each author — resulting in 6 queries being executed. Now consider the impact of this problem in a real world application where database doesn’t essentially exist on the same server as your application and has thousands of records.</p><p>This is the default behavior of Hibernate and in my opinion it is fine as long as you have to fetch the books of only a single author.</p><h4>Fetch Mode</h4><p>Hibernate provides an annotation @Fetch(...) which can be used to specify how the associated collection is fetched.</p><p><strong>FetchMode.SUBSELECT<br></strong><em>“use a subselect query to load the additional collections” — </em><a href="https://docs.jboss.org/hibernate/annotations/3.5/api/org/hibernate/annotations/FetchMode.html#SUBSELECT">SUBSELECT</a><br>Using @Fetch(FetchMode.SUBSELECT) will issue just 1 additional query to fetch the books.</p><p>Modify the class Author.java as follows:</p><pre>. . . </pre><pre>@Entity<br>public class Author {</pre><pre>@Id<br>@GeneratedValue(strategy = GenerationType.IDENTITY)<br>private Long id;<br>private String firstName;<br>private String lastName;<br>private String address;</pre><pre>@OneToMany(fetch=FetchType.LAZY, mappedBy=&quot;author&quot;)<br><strong>@Fetch(FetchMode.SUBSELECT)</strong><br>private List&lt;Book&gt; books;</pre><pre>}</pre><pre>. . .</pre><p>And take a look at the logs:</p><pre>13-01-2019 21:45:55.231 [restartedMain] INFO  org.hibernate.hql.internal.QueryTranslatorFactoryInitiator.initiateService - HHH000397: Using ASTQueryTranslatorFactory<br>Hibernate: <br>    select<br>        author0_.id as id1_0_,<br>        author0_.address as address2_0_,<br>        author0_.first_name as first_na3_0_,<br>        author0_.last_name as last_nam4_0_ <br>    from<br>        author author0_<br>Hibernate: <br>    select<br>        books0_.author_id as author_i4_1_1_,<br>        books0_.id as id1_1_1_,<br>        books0_.id as id1_1_0_,<br>        books0_.author_id as author_i4_1_0_,<br>        books0_.isbn as isbn2_1_0_,<br>        books0_.title as title3_1_0_ <br>    from<br>        book books0_ <br>    where<br>        books0_.author_id in (<br>            select<br>                author0_.id <br>            from<br>                author author0_<br>        )<br>Author: Lucille Ordelt has 5 books.<br>Author: Slade Gerwood has 5 books.<br>Author: Wallis Croall has 5 books.<br>Author: Alena Hall has 5 books.<br>Author: Marcello Szymanski has 5 books.<br>13-01-2019 21:45:55.262 [restartedMain] INFO  org.hibernate.engine.internal.StatisticalLoggingSessionEventListener.end - Session Metrics {<br>    43756 nanoseconds spent acquiring 1 JDBC connections;<br>    0 nanoseconds spent releasing 0 JDBC connections;<br><strong>    2011683 nanoseconds spent preparing 2 JDBC statements;<br>    1827476 nanoseconds spent executing 2 JDBC statements;</strong><br>    0 nanoseconds spent executing 0 JDBC batches;<br>    0 nanoseconds spent performing 0 L2C puts;<br>    0 nanoseconds spent performing 0 L2C hits;<br>    0 nanoseconds spent performing 0 L2C misses;<br><strong>    771397 nanoseconds spent executing 1 flushes (flushing a total of 30 entities and 5 collections);</strong><br>    9724 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)<br>}</pre><p>We can see that only 2 queries have been executed; one to fetch the authors, and the other to fetch the books associated with those authors.</p><p><strong>FetchMode.JOIN<br></strong>According to Hibernate Docs:<br>“use an outer join to load the related entities, collections or joins” — <a href="https://docs.jboss.org/hibernate/annotations/3.5/api/org/hibernate/annotations/FetchMode.html#JOIN">JOIN</a><br>Using @Fetch(FetchMode.JOIN) should essentially fetch the associated collection using an outer join, but in my experience it doesn’t work on collections.</p><h4><strong>JOIN FETCH</strong></h4><p>What if we want to fetch all authors and their books in a single query? <br>If I were to write an SQL query for this purpose I would take a join of these tables.<br>Let’s do that in a developer friendly ORM way by using JPA’s @Query annotation.</p><p>Modify AuthorDataService.java as follows:</p><pre>public interface AuthorDataService extends JpaRepository&lt;Author, Long&gt;{</pre><pre>@Query(&quot;select a from Author a join fetch a.books&quot;)<br>List&lt;Author&gt; findAll();</pre><pre>}</pre><p>Let’s take a look at the logs now:</p><pre>Hibernate: <br>    select<br>        author0_.id as id1_0_0_,<br>        books1_.id as id1_1_1_,<br>        author0_.address as address2_0_0_,<br>        author0_.first_name as first_na3_0_0_,<br>        author0_.last_name as last_nam4_0_0_,<br>        books1_.author_id as author_i4_1_1_,<br>        books1_.isbn as isbn2_1_1_,<br>        books1_.title as title3_1_1_,<br>        books1_.author_id as author_i4_1_0__,<br>        books1_.id as id1_1_0__ <br>    from<br>        author author0_ <br>    inner join<br>        book books1_ <br>            on author0_.id=books1_.author_id</pre><pre><strong>Author: Lucille Ordelt has 5 books.<br>Author: Lucille Ordelt has 5 books.<br>Author: Lucille Ordelt has 5 books.<br>Author: Lucille Ordelt has 5 books.<br>Author: Lucille Ordelt has 5 books.<br>Author: Slade Gerwood has 5 books.<br>Author: Slade Gerwood has 5 books.<br>Author: Slade Gerwood has 5 books.<br>Author: Slade Gerwood has 5 books.<br>Author: Slade Gerwood has 5 books.<br>Author: Wallis Croall has 5 books.<br>Author: Wallis Croall has 5 books.<br>Author: Wallis Croall has 5 books.<br>Author: Wallis Croall has 5 books.<br>Author: Wallis Croall has 5 books.<br>Author: Alena Hall has 5 books.<br>Author: Alena Hall has 5 books.<br>Author: Alena Hall has 5 books.<br>Author: Alena Hall has 5 books.<br>Author: Alena Hall has 5 books.<br>Author: Marcello Szymanski has 5 books.<br>Author: Marcello Szymanski has 5 books.<br>Author: Marcello Szymanski has 5 books.<br>Author: Marcello Szymanski has 5 books.<br>Author: Marcello Szymanski has 5 books.</strong></pre><pre>13-01-2019 22:19:06.954 [restartedMain] INFO  org.hibernate.engine.internal.StatisticalLoggingSessionEventListener.end - Session Metrics {<br>    37273 nanoseconds spent acquiring 1 JDBC connections;<br>    0 nanoseconds spent releasing 0 JDBC connections;<br><strong>    426213 nanoseconds spent preparing 1 JDBC statements;<br>    324117 nanoseconds spent executing 1 JDBC statements;</strong><br>    0 nanoseconds spent executing 0 JDBC batches;<br>    0 nanoseconds spent performing 0 L2C puts;<br>    0 nanoseconds spent performing 0 L2C hits;<br>    0 nanoseconds spent performing 0 L2C misses;<br>    616901 nanoseconds spent executing 1 flushes (flushing a total of 30 entities and 5 collections);<br>    9723 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)<br>}</pre><p>Problem solved! … almost — Using <strong>JOIN FETCH </strong>solves the problem of multiple queries by using a join but it returns duplicate records for Author.<br>But that is what a join is supposed to do.</p><pre>select * from author author0_ <br>inner join book books1_ <br>on author0_.id=books1_.author_id</pre><p>Executing the above query gives the following result in which each author’s data is duplicated with their books.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/802/1*pe2hQovL5INGvkD1T5N83Q.png" /></figure><p>The solution to duplicate records problem is adding DISTINCT to the query.</p><pre>public interface AuthorDataService extends JpaRepository&lt;Author, Long&gt;{</pre><pre>@Query(&quot;select <strong>distinct</strong> a from Author a join fetch a.books&quot;)<br>List&lt;Author&gt; findAll();</pre><pre>}</pre><p>Logs:</p><pre>Hibernate: <br>    select<br>        distinct author0_.id as id1_0_0_,<br>        books1_.id as id1_1_1_,<br>        author0_.address as address2_0_0_,<br>        author0_.first_name as first_na3_0_0_,<br>        author0_.last_name as last_nam4_0_0_,<br>        books1_.author_id as author_i4_1_1_,<br>        books1_.isbn as isbn2_1_1_,<br>        books1_.title as title3_1_1_,<br>        books1_.author_id as author_i4_1_0__,<br>        books1_.id as id1_1_0__ <br>    from<br>        author author0_ <br>    inner join<br>        book books1_ <br>            on author0_.id=books1_.author_id<br><strong>Author: Slade Gerwood has 5 books.<br>Author: Lucille Ordelt has 5 books.<br>Author: Alena Hall has 5 books.<br>Author: Wallis Croall has 5 books.<br>Author: Marcello Szymanski has 5 books.</strong><br>13-01-2019 22:20:00.730 [restartedMain] INFO  org.hibernate.engine.internal.StatisticalLoggingSessionEventListener.end - Session Metrics {<br>    38353 nanoseconds spent acquiring 1 JDBC connections;<br>    0 nanoseconds spent releasing 0 JDBC connections;<br><strong>    494818 nanoseconds spent preparing 1 JDBC statements;</strong><br><strong>    979372 nanoseconds spent executing 1 JDBC statements;</strong><br>    0 nanoseconds spent executing 0 JDBC batches;<br>    0 nanoseconds spent performing 0 L2C puts;<br>    0 nanoseconds spent performing 0 L2C hits;<br>    0 nanoseconds spent performing 0 L2C misses;<br>    681185 nanoseconds spent executing 1 flushes (flushing a total of 30 entities and 5 collections);<br>    10264 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)<br>}</pre><p><em>Note: All of these operations can also be done using criteria query.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8a926b69f618" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>