Skip to content
javahacks.dev
Hibernate

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.

6 min read
Summarize in AI
Flat illustration of an entity class mapped to a database table, with the Hibernate, Liquibase, Spring Boot and MySQL logos.

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:

properties
# src/test/resources/application.properties
spring.jpa.properties.hibernate.generate_statistics=true

Then read the counter around the call you care about:

java
@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.

XML
<dependency>
  <groupId>net.ttddyy</groupId>
  <artifactId>datasource-proxy</artifactId>
  <version>1.10</version>
  <scope>test</scope>
</dependency>
java
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.

java
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:

java
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@ExtendWith(QueryBudgetExtension.class)
public @interface MaxQueries {
    int value();
}
java
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();
    }
}
java
@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.

Also inTesting
Taggedhow to
Share

Keep reading

Get new posts by email

We send one email when a new post is published. No spam.

Unsubscribe in one click. No spam, ever.