Skip to main content

Catch N+1 Queries Automatically in Hibernate 7: Statistics, datasource-proxy and a JUnit Guard

Reproduce an N+1 on Hibernate 7.4, see why Hibernate Statistics reports 1 query while 11 reach the database, compare five fixes by measured statement count, and fail the build with a JUnit 6 @MaxSelects guard.

A page that lists 10 authors and their books should need two queries, or one. If it sends eleven, nothing breaks, nothing logs a warning, and every test still passes. It is slow only once the tables are big, which is usually in production. That is the N+1 problem, and the honest way to stop it is not a code review but a test that counts the SQL. This article reproduces N+1 on a real Hibernate 7 setup, shows the two ways to count statements (and why one of them quietly gives the wrong answer), compares five fixes with their measured statement counts, and ends with a small JUnit 6 extension that fails a test when a query budget is exceeded. Everything is in one companion module you can run: hibernate-demo/n-plus-one-guard.
Versions tested. Hibernate ORM 7.4.16.Final (newest 7.x GA in maven-metadata.xml on 2026-11-17; 8.0.0.CR1 is listed after it but is a candidate, not a release), datasource-proxy 1.11.1, JUnit 6.1.3, H2 2.4.240, JDK 25 (Temurin 25.0.5+8). Plain Hibernate on in-memory H2: no Spring, no Docker. Every number below is an assertion in a test; the output blocks are copied from files the tests wrote.

N+1 means one query turned into many, without you writing any of them

Hibernate loads related objects lazily by default: when you load an Author, its books are not fetched until your code touches them. That is a good default. The trouble starts when you load a list of authors and then touch getBooks() inside a loop: Hibernate fetches the books of each author with a separate query. One query for the list, plus N for the children, is where the name comes from.
1 query: all authors select … from Author returns 10 rows, so the loop will run 10 times select … from Book where author_id=? (author 1) select … from Book where author_id=? (author 2) select … from Book where author_id=? (author 3) … and seven more, one per author The fix: 1 statement select … from Author a join Book b on a.id=b.author_id authors and books arrive together The cost is 1 + N: 11 statements for 10 authors, 101 for 100. Each one is a network round trip to the database. Nothing in the Java code looks wrong, which is why it needs a counter and not a code review.
The left side of the diagram is the problem and the right side is the fix. The point to take from it is the shape of the cost: it grows with the number of rows, not with the amount of code. The mapping is ordinary, with a lazy collection on one side and a lazy to-one on the other (see Author.java and Book.java):
@OneToMany(mappedBy = "author", cascade = CascadeType.PERSIST)
private Set<Book> books = new LinkedHashSet<>();

// Book.java
@ManyToOne(fetch = FetchType.LAZY)
private Author author;
And this is the loop that triggers it, from T01_ReproduceTest.java:
db.txv(s -> {
  List<Author> authors = s.createQuery("from Author", Author.class).getResultList();
  for (Author a : authors) {
    books[0] += a.getBooks().size();
  }
});
Run against 10 authors with 3 books each, it sends 11 statements. The block below is the top of the transcript 01-n-plus-one.txt, followed by the SQL list from the same file:
== Code: one HQL query, then a.getBooks().size() for every author
books counted by the loop        : 30
datasource-proxy SELECT count    : 11
Hibernate prepareStatementCount  : 11
Hibernate queryExecutionCount    : 1
Hibernate collectionFetchCount   : 10
Hibernate collectionLoadCount    : 10

== The statements, in order
   sql> select a1_0.id,a1_0.name from Author a1_0
   sql> select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
   sql> select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
   sql> select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
   sql> select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
   sql> select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
   sql> select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
   sql> select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
   sql> select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
   sql> select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
   sql> select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
The fingerprint of N+1. The same select ... from Book ... where author_id=? statement repeated with different parameters, once per parent row. If you see one statement shape repeated ten times in a log, this is what you are looking at.
Going deeper: the @ManyToOne version, and why it sends 10 statements for 30 books The collection case is the one everyone knows. The to-one case hides better, because the loop never touches a collection: it loads 30 books and reads book.getAuthor().getName() on each. Because Book.author is LAZY, Hibernate puts an empty proxy in each field and loads the real author on first use. There are 30 books but only 10 distinct authors, and the persistence context loads each author once, so the count is 1 + 10, not 1 + 30. The counter that matches is entityFetchCount.

One mapping detail matters here: the JPA default for @ManyToOne is EAGER, and the module sets LAZY explicitly. I did not run the EAGER variant here, so I make no claim about the SQL it produces. Transcript: 02-n-plus-one-to-one.txt.
== Code: one HQL query for 30 books, then book.getAuthor().getName() for every book
datasource-proxy SELECT count    : 11
Hibernate entityFetchCount       : 10

Going deeper in this section

Counting statements: Hibernate Statistics and datasource-proxy disagree

There are two obvious ways to count. Hibernate can count for you if you set hibernate.generate_statistics=true, and a JDBC proxy such as datasource-proxy can count whatever reaches the database driver. They sound interchangeable. They are not, and the difference is the most useful thing in this article.
Your code Hibernate Session datasource-proxy H2 driver Hibernate Statistics queryExecutionCount = 1 collectionFetchCount = 10 QueryCount SELECT = 11 Statistics counts the HQL query you wrote; the ten lazy loads are not queries to it. The proxy counts what reached the driver. For a regression guard you want the second number.
Hibernate Statistics sits inside the session and counts events Hibernate defines: queries you executed, entities loaded, collections fetched. datasource-proxy wraps the DataSource, so it sees every statement the driver is asked to run, whoever caused it. Here is the wrapping code from QueryGuard.java:
public static DataSource wrap(DataSource real) {
  return ProxyDataSourceBuilder.create(real)
      .name(DATASOURCE_NAME)
      .countQuery()
      .listener(new QueryExecutionListener() {
        @Override
        public void beforeQuery(net.ttddyy.dsproxy.ExecutionInfo execInfo, List<QueryInfo> queryInfoList) {
        }

        @Override
        public void afterQuery(net.ttddyy.dsproxy.ExecutionInfo execInfo, List<QueryInfo> queryInfoList) {
          for (QueryInfo q : queryInfoList) {
            SQL.get().add(q.getQuery().replaceAll("\\s+", " ").trim());
          }
        }
      })
      .build();
}
and the dependency it needs:
<dependency>
  <groupId>net.ttddyy</groupId>
  <artifactId>datasource-proxy</artifactId>
  <version>1.11.1</version>
</dependency>
Both counters ran on the same N+1 loop. The first block of 01-n-plus-one.txt shows what each one reported:
== Code: one HQL query, then a.getBooks().size() for every author
books counted by the loop        : 30
datasource-proxy SELECT count    : 11
Hibernate prepareStatementCount  : 11
Hibernate queryExecutionCount    : 1
Hibernate collectionFetchCount   : 10
Hibernate collectionLoadCount    : 10
The trap. queryExecutionCount is 1. Hibernate counts the one HQL query you wrote; the ten lazy collection loads are not “queries” in its vocabulary, they are collection fetches. If your guard asserts on getQueryExecutionCount(), an N+1 regression sails through. getPrepareStatementCount() and the proxy both say 11, which is the number you want.
Wire it in by passing the wrapped DataSource to Hibernate, as Db.java does with the jakarta.persistence.nonJtaDataSource setting. That is the whole integration: no agent, no bytecode, no Hibernate-specific hook.
Going deeper: what each Statistics counter means, and a thread-safety catch The three Hibernate counters worth knowing are getPrepareStatementCount() (JDBC statements prepared, the closest match to what the database sees), getCollectionFetchCount() (lazy collection loads, which is your N in a collection N+1) and getEntityFetchCount() (lazy entity loads, your N in a to-one N+1). Call getStatistics().clear() after seeding data, otherwise the inserts are counted too; Db.seed() does this.

The catch with datasource-proxy: its QueryCountHolder stores counters in a ThreadLocal (I confirmed it with javap on the 1.11.1 jar: the field is a ThreadLocal<ConcurrentMap<String, QueryCount>>). Queries run on another thread, for example inside an @Async method or a parallel stream, are counted on that thread and invisible to a guard asserting on the test thread. The SQL list in QueryGuard is thread-local for the same reason, and I did not test the multi-threaded case.

Going deeper in this section

Five ways to fix it, with the statement count of each

The fix is always the same idea: tell Hibernate up front which related data you need, so it can fetch it together or in a few big batches. The module runs the same job five ways against the same data and counts the SELECTs. The code is from T02_FixesTest.java:
run(db, t, counts, "naive loop (lazy collection per author)", s -> total(s.createQuery("from Author", Author.class).getResultList()));
run(db, t, counts, "join fetch", s -> total(s.createQuery("select a from Author a join fetch a.books", Author.class).getResultList()));
run(db, t, counts, "entity graph (Author.withBooks)", s -> total(s.createQuery("from Author", Author.class)
    .setHint("jakarta.persistence.fetchgraph", s.getEntityGraph("Author.withBooks")).getResultList()));
run(db, t, counts, "batch fetching, size 5 (session.setFetchBatchSize)", s -> {
  s.setFetchBatchSize(5);
  return total(s.createQuery("from Author", Author.class).getResultList());
});
run(db, t, counts, "DTO projection (no entities)", s -> s.createQuery(
    "select count(b) from Book b group by b.author.id", Long.class).getResultList().stream().mapToInt(Long::intValue).sum());
Result, copied from 03-fixes.txt:
== SELECT statements for 10 authors x 3 books, same result every time (30 books)
naive loop (lazy collection per author)              11
join fetch                                           1
entity graph (Author.withBooks)                      1
batch fetching, size 5 (session.setFetchBatchSize)   3
DTO projection (no entities)                         1
Read the right-hand column as a menu. Join fetch and entity graph load everything in one statement. Batch fetching keeps the original two-step shape but replaces N queries with N divided by the batch size, so 10 authors in batches of 5 is 2 book queries plus the author query, 3 in all. A DTO projection avoids entities altogether and is the right choice when you only need numbers or a few columns.
Join fetch and entity graph are not the same SQL. In the transcript the join fetch statement uses join Book while the entity graph uses left join Book. An inner join drops authors that have no books; a left join keeps them. If you switch from one to the other, check what happens to empty parents. The module seeds every author with 3 books, so it does not exercise that case, and I have only checked the generated SQL here.
Going deeper: join fetch with setMaxResults, which behaves better in 7.4 than its reputation The long-standing advice was never to combine join fetch on a collection with setMaxResults(), because Hibernate used to apply the limit in memory after loading every row and logged a warning about it (HHH90003004, as I remember it; I did not re-check the older versions). I expected to demonstrate that failure and wrote the test for it. It failed: on 7.4.16 the generated SQL applies the limit to the owning side, inside a subquery, and still sends one statement. The test now pins the current behaviour instead, from 04-join-fetch-pagination.txt:
authors returned: 3, books loaded: 9
SELECT statements: 1
   sql> select a1_0.id,b1_0.author_id,b1_0.id,b1_0.title,a1_0.name from (select a1_0.id,a1_0.name from Author a1_0 where exists(select 1 from Book b1_0 where a1_0.id=b1_0.author_id) order by a1_0.id offset ? rows fetch first ? rows only) a1_0(id,name) join Book b1_0 on a1_0.id=b1_0.author_id order by a1_0.id
Three authors and nine books came back from one statement. Two cautions: I only tested a plain order by on the id, and the subquery contains exists(select 1 from Book ...), so parents without children are excluded, as with any inner join fetch.
Going deeper: @BatchSize, subselect and the other fetch strategies This module uses session.setFetchBatchSize(5) to get batch fetching for one session, and the transcript shows the resulting where b1_0.author_id in (?,?,?,?,?) statements, two of them. The annotation and global-setting forms (@BatchSize, hibernate.default_batch_fetch_size) are the same mechanism applied wider; the earlier one-to-many module runs @BatchSize(size = 20) on a collection. I did not test FetchMode.SUBSELECT in this module, so I make no numeric claim about it.
   sql> select a1_0.id,a1_0.name from Author a1_0
   sql> select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id in (?,?,?,?,?)
   sql> select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id in (?,?,?,?,?)

Going deeper in this section

Make the build fail when the query count grows

Fixing N+1 once is easy; keeping it fixed is the hard part, because the next person to add a field to the loop will not see the cost. A query budget turns the number into a test. The module ships a small JUnit extension: annotate a test with @MaxSelects(n) and it fails if the test sends more than n SELECTs. A passing example from T03_GuardTest.java:
@Test
@MaxSelects(1)
void joinFetchStaysWithinBudget() {
  db.txv(s -> s.createQuery("select a from Author a join fetch a.books", Author.class).getResultList()
      .forEach(a -> a.getBooks().size()));
}
To see what a failure looks like, the module contains a deliberately regressed test, BrokenFixture, that is not run by the normal build. T03_GuardTest runs it through the JUnit Platform test kit and records the failure. The fixture is the N+1 loop with a budget of 2:
@Test
@MaxSelects(2)
void listAuthorsWithBookCount() {
  db.txv(s -> {
    for (Author a : s.createQuery("from Author", Author.class).getResultList()) {
      a.getBooks().size();
    }
  });
}
and this is the failure message, copied from 05-guard-failure.txt:
listAuthorsWithBookCount(): expected at most 2 SELECT statement(s) but 11 were sent.
  1) select a1_0.id,a1_0.name from Author a1_0
  2) select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
  3) select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
  4) select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
  5) select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
  6) select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
  7) select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
  8) select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
  9) select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
  10) select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
  11) select b1_0.author_id,b1_0.id,b1_0.title from Book b1_0 where b1_0.author_id=?
The message lists every statement, so whoever breaks the budget sees the repeated shape immediately and does not need to attach a debugger. The extension itself is short enough to copy whole, from QueryGuardExtension.java:
public class QueryGuardExtension implements BeforeTestExecutionCallback, AfterTestExecutionCallback {

  @Override
  public void beforeTestExecution(ExtensionContext ctx) {
    QueryGuard.reset();
  }

  @Override
  public void afterTestExecution(ExtensionContext ctx) {
    MaxSelects budget = AnnotationSupport.findAnnotation(ctx.getTestMethod(), MaxSelects.class)
        .or(() -> AnnotationSupport.findAnnotation(ctx.getTestClass(), MaxSelects.class))
        .orElse(null);
    if (budget != null) {
      QueryGuard.assertMaxSelects(budget.value(), ctx.getDisplayName());
    }
  }
}
Set the budget to the fixed number, not to the current number. Use the count you want (1 for the join fetch), not whatever the code does today. Also keep at least two parent rows in the test data: with a single author, the naive loop sends 1 + 1 = 2 statements and a budget of 2 would never catch it.
Going deeper: when the counters reset and what that means for @BeforeEach The extension resets counters in BeforeTestExecutionCallback and checks in AfterTestExecutionCallback. JUnit documents these callbacks as wrapping only the test method itself, after @BeforeEach methods and before @AfterEach methods (see the lifecycle callbacks link below), so setup and cleanup are outside the budget. In this module the same effect comes from Db.seed() clearing the counters itself, so I have not isolated the extension ordering with a separate test. Budgets are checked per test method; a @MaxSelects on the class applies to every test in it unless a method has its own.
Going deeper: using it in a Spring Boot test The guard needs a DataSource to wrap. In a Spring Boot test that means wrapping the DataSource bean, for example in a BeanPostProcessor, and letting @MaxSelects read the same thread-local counters. I did not wire that up in this module, which is plain Hibernate on H2, so treat it as the obvious next step rather than a tested recipe. The @DataJpaTest with Testcontainers article covers the test slice you would put it in.

Going deeper in this section

Should you add this to every project?

Add it to the tests that guard your hot paths, not to all of them. A budget on a list endpoint or a report query is cheap and catches the regression that matters. A budget on every test is noise: counts change for legitimate reasons and people start raising the numbers to make the build pass, which defeats the point. Also remember it runs against test data: it shows the count does not grow with the number of rows only if the data has more than one parent row. It says nothing about how slow each statement is; for that you still need EXPLAIN and a realistic dataset.

Further reading

No Comments yet!

This site uses Akismet to reduce spam. Learn how your comment data is processed.