Versions tested. Hibernate ORM7.4.16.Final(newest 7.x GA inmaven-metadata.xmlon 2026-11-17;8.0.0.CR1is listed after it but is a candidate, not a release), datasource-proxy1.11.1, JUnit6.1.3, H22.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 anAuthor, 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.
@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 readsbook.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
- Earlier on ankurm.com: the N+1 fixes for plain
@OneToManymappings, with@BatchSize, in the one-to-many module. - Lazy vs eager defaults in the Hibernate 7.4 User Guide.
- Top 40 Hibernate and JPA interview questions, which has the short version of this problem.
Counting statements: Hibernate Statistics and datasource-proxy disagree
There are two obvious ways to count. Hibernate can count for you if you sethibernate.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.
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.Wire it in by passing the wrapped DataSource to Hibernate, as Db.java does with thequeryExecutionCountis 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 ongetQueryExecutionCount(), an N+1 regression sails through.getPrepareStatementCount()and the proxy both say 11, which is the number you want.
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 aregetPrepareStatementCount() (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
- The wiring: Db.java and QueryGuard.java.
- Statistics API: org.hibernate.stat.Statistics Javadoc.
- datasource-proxy artifact: repo1.maven.org/maven2/net/ttddyy/datasource-proxy/.
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 thejoin fetchstatement usesjoin Bookwhile the entity graph usesleft 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 combinejoin 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 usessession.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
- The comparison test: T02_FixesTest.java.
- Projections without entities: Spring Data JPA Projections: Interface, Record and Dynamic.
- Batch inserts and generated ids: Hibernate ID Generation.
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 inBeforeTestExecutionCallback 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 aDataSource 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
- All guard sources: QueryGuard.java, MaxSelects.java, QueryGuardExtension.java.
- Extension callbacks: JUnit user guide, lifecycle callbacks.
- More on JUnit 6: Top 30 JUnit 6 and Mockito interview questions.
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
- Companion module: hibernate-demo/n-plus-one-guard, with its README.
- Soft Delete in Hibernate 7, where hidden extra queries are also worth counting.
- Optimistic vs Pessimistic Locking in Hibernate 7.
- Hibernate ORM 7.4 User Guide
- JUnit User Guide
No Comments yet!