Careeroza

Asserting Query Counts

Catch N+1 in a Test

An N+1 problem is invisible in a normal assertion: the result is correct, it is just slow. Use Hibernate statistics in the test to assert how many statements ran, so a future change that brings N+1 back fails the build.

@DataJpaTest(properties = "spring.jpa.properties.hibernate.generate_statistics=true")
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@Import(PostgresTestConfig.class)
class OrderQueryCountTest {

    @Autowired OrderRepository orders;
    @Autowired EntityManagerFactory emf;
    @Autowired TestEntityManager tem;

    @Test
    void loadingOrdersWithItemsUsesOneQuery() {
        TestData.ordersWithItems(tem, 10, 3);      // 10 orders x 3 items
        tem.flush(); tem.clear();

        Statistics stats = emf.unwrap(SessionFactory.class).getStatistics();
        stats.clear();

        List<Order> result = orders.findAllWithItems();          // join fetch
        result.forEach(o -> o.getItems().size());                // touch the lazy collection

        assertThat(stats.getPrepareStatementCount()).isEqualTo(1);
    }
}

Tip: The open-source library datasource-proxy (or db-util by Vlad Mihalcea) can count SELECT/INSERT/UPDATE separately, if you need finer assertions.

Exercise

Exercise: Your test asserts 1 statement, but it passes even though the repository method has an N+1 problem. The test did not call tem.clear() after creating the data. Why does it pass?

Show answer
The orders and items created in the test are still managed in the first-level cache.
findAllWithItems() runs 1 query, but each order's items collection is already initialised
in memory (it is the same object the test created), so no lazy load SQL is sent.
After tem.flush(); tem.clear(); the entities are loaded fresh and the N+1 shows up.