How to Detect N plus 1 Queries in Tests Automatically
Detect N plus 1 queries in Java tests: count statements with Hibernate statistics or datasource-proxy and fail the build when a budget is exceeded.

A lazy association read inside a loop returns the right data and a slow response, so the usual tests pass. The page renders, the JSON is correct, the assertions are green, and in production a list of fifty authors triggers fifty-one queries. Functional tests cannot see the problem because it is not a wrong answer, it is a wasteful one. The fix is to treat the number of queries as something a test can assert. This post shows two ways to count them, with Hibernate statistics and with a datasource proxy, and a helper that turns the check into one line. It uses Hibernate 7, Spring Boot 4 and JUnit 5.
Seed enough rows
Before any counting, fix the test data. With one parent row, N plus 1 is two queries and looks perfectly fine. Seed at least five or ten parents, each with several children, so a per-row query is plainly more than a constant. A realistic seed also protects you from the opposite mistake: asserting a count that happens to be right only because the tables were nearly empty.
Count with Hibernate statistics
Turn statistics on for the test profile:
# src/test/resources/application.properties
spring.jpa.properties.hibernate.generate_statistics=trueThen read the counter around the call you care about:
@DataJpaTest
class AuthorQueryTest {
@Autowired
AuthorService service;
@Autowired
EntityManagerFactory emf;
@Test
void listingAuthorsUsesAtMostTwoQueries() {
Statistics stats = emf.unwrap(SessionFactory.class).getStatistics();
stats.clear();
service.listWithBookTitles();
assertThat(stats.getPrepareStatementCount()).isLessThanOrEqualTo(2);
}
}getPrepareStatementCount counts statements prepared, which is the number you want, because it counts what was sent to the database. Call stats.clear() immediately before the code under test. Without it, the inserts that built the seed data are counted, and the number is meaningless.
The assertion says "at most two", and that is a deliberate choice. The test describes a budget, not a fingerprint. If a later change fetches the books with a join and needs one query, the test still passes, and if someone adds a loop that issues a query per author, it fails with the count in the message.
Count at the JDBC layer
A proxy around the data source counts statements whatever issued them, which works for code that does not go through Hibernate at all.
<dependency>
<groupId>net.ttddyy</groupId>
<artifactId>datasource-proxy</artifactId>
<version>1.10</version>
<scope>test</scope>
</dependency>ProxyDataSourceBuilder.create(dataSource)
.countQuery(queryCountHolder)
.build();After the call, QueryCountHolder.getGrandTotal().getSelect() gives the number of selects, and the same holder reports inserts, updates and deletes separately. That split is useful: a test can assert that a read-only endpoint performed zero writes. The hypersistence-utils library packages the same idea as SQLStatementCountValidator, with assertion methods and a readable failure that lists the statements, which is worth using if you do not want to build the helper yourself.
One line in each test
Put the pattern in a helper so no test repeats the clearing and the assertion.
final class QueryBudget {
static void assertAtMost(EntityManagerFactory emf, int max, Runnable action) {
Statistics s = emf.unwrap(SessionFactory.class).getStatistics();
s.clear();
action.run();
assertThat(s.getPrepareStatementCount())
.as("SQL statements")
.isLessThanOrEqualTo(max);
}
}A test then reads assertAtMost(emf, 2, () -> service.listWithBookTitles()). The as(...) text makes a failure explain itself: the report says the SQL statement count was higher than the budget and shows both numbers. The assertion style is covered in AssertJ fluent assertions.
Choosing the budget number
How many queries is acceptable? Work it out from the code, not from the current behavior. A list of authors with their book titles needs one query if you join, or two if you load authors and then their books in one batch. Anything that grows with the row count is a bug. Write the number you derived with a comment on why, for example "authors, then one batch for books", and then check the current code against it. If the code issues six queries for a reason you cannot explain, you have found the next thing to look at.
For endpoints with pagination, remember the count query. A page of results usually costs one query for the rows and one for the total, so the budget is two even for a perfect implementation. Include that in the comment, or the next reader will lower the budget to one and the test will fail for the wrong reason.
Keep it fast and run it everywhere
A check that runs only on one developer's machine does not protect anything. Put these tests in the normal suite so every pull request runs them. The test slices from Spring Boot test slices keep them quick, since @DataJpaTest starts the persistence layer and nothing else. Add one budget test for each endpoint or service method that lists things, not for every method.
What to do when it fails
Read the SQL the test printed, find the loop that triggers the extra statements, and fix it at the source: a join fetch in the query, an entity graph on the repository method, or batch fetching when a join would multiply rows. Then lower the budget to the new, smaller number. A budget that stays at the old ceiling lets the problem return unnoticed, and tightening it is the step people forget.
Pitfalls that fool the test
- Statistics count across the whole session factory, so tests running in parallel can add each other's statements. Run query-count tests serially, or give each test class its own context.
- A second-level cache or a query cache can hide queries, since a cached read issues nothing. Switch both off in tests that count, or the number reflects the cache, not the code.
- Do not assert exact counts for large flows. Use a ceiling, because incidental changes, such as a new audit insert, would otherwise break unrelated tests.
- A transaction opened by the test itself can keep entities in the persistence context, so a lazy association loads once and never again. Clear the entity manager before the call under test, or the second access looks free and the N plus 1 disappears from the test while remaining in production.
A reusable annotation so the check is one line
Repeating the statistics setup in every test invites mistakes. Wrap it in a JUnit extension and a small annotation, so a test states its budget and nothing else:
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@ExtendWith(QueryBudgetExtension.class)
public @interface MaxQueries {
int value();
}public class QueryBudgetExtension implements BeforeEachCallback, AfterEachCallback {
@Override
public void beforeEach(ExtensionContext context) {
statistics(context).clear();
statistics(context).setStatisticsEnabled(true);
}
@Override
public void afterEach(ExtensionContext context) {
MaxQueries budget = context.getRequiredTestMethod().getAnnotation(MaxQueries.class);
if (budget == null) return;
long actual = statistics(context).getPrepareStatementCount();
if (actual > budget.value()) {
throw new AssertionFailedError("Expected at most " + budget.value()
+ " statements but " + actual + " were prepared");
}
}
private Statistics statistics(ExtensionContext context) {
EntityManagerFactory emf = SpringExtension.getApplicationContext(context).getBean(EntityManagerFactory.class);
return emf.unwrap(SessionFactory.class).getStatistics();
}
}@DataJpaTest
class OrderListQueriesTest {
@Autowired OrderQueries queries;
@Test
@MaxQueries(2)
void listsOrdersWithCustomersInTwoStatements() {
List<OrderRow> rows = queries.latest(50);
assertEquals(50, rows.size());
}
}The failure message gives the number that was prepared, which is the number a reviewer wants to see in the pull request. Keep the budget tight, not loose: a budget of 20 for a list that needs 2 hides the regression that takes it to 12. Attach the annotation to every endpoint that returns a list, so a new lazy access in a mapper fails in the build, not in the graph that a person reads after the release.
When budgets are overkill
A prototype with tiny tables and no users does not need query budgets. Add them when a list endpoint reaches real traffic. For visibility in production, where no test can help, see Hibernate statistics and slow query logging. The references are the Hibernate Statistics API and the datasource-proxy project.


