Your team keeps re-introducing code paths that fire one SQL statement per row, and it is only noticed in production. How would you turn the number of SQL statements a code path executes into an automatically enforced, failing assertion in the test suite?

## Why this needs automation Per-row fetching is invisible in code review and invisible in a test that asserts only on returned values. It is also invisible in a fixture with two rows: two extra statements cost nothing. It becomes an incident when the same code meets a thousand rows. The only durable defence is to make the statement count a first-class, asserted property of a code path, so any change that multiplies it fails the build. ## Instrumentation option 1: Hibernate Statistics With `hibernate.generate_statistics=true`, the pattern is clear-run-assert: ``` stats.clear(); service.loadDashboard(userId); assertEquals(3, stats.getPrepareStatementCount()); ``` Strengths: no dependency, and you can assert on richer ORM-level facts at the same time — `getEntityLoadCount()`, `getCollectionFetchCount()` (collections fetched by a separate statement rather than a join), `getQueryExecutionCount()`. Limits: counters are factory-wide, so the test must be single-threaded and must not share a factory with concurrent tests; only Hibernate's traffic is counted; and the failure message is just a number — you still have to turn SQL logging on to see which statements they were. ## Instrumentation option 2: a JDBC proxy `datasource-proxy` wraps the `DataSource` and hands every execution to a listener, so you can build a per-test recorder. `p6spy` does the same at driver level. Advantages that matter in practice: - **Everything is counted**, including native JDBC, Flyway/Liquibase-style plumbing, and statements from a second ORM. - **Categorised assertions**: select/insert/update/delete counts separately, so a test can pin 'exactly one UPDATE' without caring about reads. - **Good failure messages**: the recorder can print the captured SQL, which turns a red build into a diagnosis without a rerun. - **Batch awareness**: proxies report batch executions and their sizes, which lets you assert that batching is actually happening rather than assuming it. ## Making the assertion meaningful **Assert equality, not `<=`.** An upper bound silently tolerates the whole budget being consumed and hides improvements. An exact count forces every change in query shape to be acknowledged in the diff, which is the point. **Control the persistence context.** If the entity you are about to read is already managed, no statement is issued and the count is a lie about production behaviour. Flush and clear (or use a fresh session) before the measured block. Similarly, clear the second-level cache between runs, or the second execution of the same test measures a warm cache. **Use realistic data.** The fixture must contain enough child rows that a per-row pattern shows up as a different number than a joined fetch. Two parents with three children each is usually enough to separate 1 statement from 3. **Use the real database.** An in-memory database with a different dialect can generate different SQL, support different batching, and behave differently for identity generation — and identity generation in particular disables JDBC insert batching, so a count that passes on one dialect can fail on another for reasons unrelated to the code under test. Containerised real databases remove that whole class of false signal. **Keep it single-threaded and isolated.** Both instrumentation strategies aggregate globally. Parallel test execution against a shared factory or DataSource will produce flaky counts. ## Where to put the assertions Do not sprinkle them everywhere: a count assertion on every test makes the suite brittle, because every legitimate query change touches dozens of tests. Put them on the handful of paths where fan-out actually hurts — list endpoints, batch jobs, report builders, anything that iterates a collection of aggregates. A small dedicated set of 'query budget' tests is more maintainable and more honest than a global rule. ## Complementary signals Count assertions catch regressions in code you have tests for. Pair them with: `hibernate.use_sql_comments` so production statement logs name the source query; a slow-query threshold so pathological single statements surface; and database-side aggregation (statement-level statistics views) so you can see, in production, which statement text dominates total execution time. Counting in tests is prevention; the production signals are detection for everything the tests do not cover.