Evidence base for verification

1. The evidence base in persistence-review

When you first start digging into Hibernate, it seems that the main challenge is memorising annotations and the “correct settings”. In practice, something else is harder: agreeing with reality, because Hibernate very politely pretends that everything “just works” until, in production, you suddenly get 200 queries on a single screen. The evidence base is a way to move the argument from “opinions” to “measurements”, where the winner is not the loudest person, but the one with facts.

A red flag by itself is only a suspicion. For a finding to stop being the phrase “I do not like this code”, it needs to be pinned to facts: to SQL trace, to runtime counters, and to a test that reproduces the desired symptom.

In persistence-review, we almost always answer one of three questions: “what SQL was actually executed”, “why was it executed exactly that way”, and “how do we guarantee that tomorrow it will not get worse again”. If you do not rely on artifacts, you start arguing based on “feelings”, and as we all know, feelings do not compile. And we still have not shipped tests for feelings in JUnit (thank goodness).

Verification artifact: what counts as evidence

The word “artifact” sounds as if we are about to dig up an ancient Hibernate 3.0 amulet, but the meaning is simpler. A verification artifact is a concrete thing that can be attached to a review finding so that another developer can reproduce the conclusion. A good artifact answers the question “show me”, not “trust me”. In our course, such artifacts are most often the SQL log, numeric counters from Statistics, and an integration test that reproduces the issue.

It is very important not to confuse an “artifact” with an “interpretation”. For example, the phrase “this is N+1” is an interpretation, while “the SQL trace has 1 query for the list + 50 queries for customer by order.customer_id” is an artifact (and it almost explains the issue by itself). Likewise, “flush is extra” is an interpretation, while “the log shows UPDATE before SELECT because of overlapping query” is an artifact.

The mini-cycle “baseline → change → verify → lock-in”

If you simplify life down to a healthy minimum, any work with persistence quality is a repeatable cycle. First we capture the baseline (how it is now), then we change the solution (fetch-plan, query, transaction, bulk), then we verify that it became better, and finally we lock it in with a test so it does not regress. This is literally “repair by instruction”, not “I tapped the TV and it started working”.

Below is the same idea as a simple diagram, so the brain does not try to reinvent the wheel every time:

flowchart TD
    A[Baseline: SQL trace + statistics] --> B[Change: code-level solution]
    B --> C[Verify: repeat measurement]
    C --> D[Lock-in: regression test]
    D --> A

On one hot path, this cycle becomes a full review pass. For now, the important thing is to learn how to consistently capture a baseline and verify a change, rather than arguing blind.

2. SQL trace: Hibernate's honest witness

The SQL log is like a security camera in a store. You can argue about “who took the cookie”, you can build theories, you can refer to “I definitely remember”, but the camera calmly shows: here is the hand, here is the cookie, here is the person pretending they were just passing by. In our case, Hibernate is that “passer-by” who sometimes performs a SELECT so quietly that you only notice it when latency drops. That is why SQL trace is the first and strongest artifact.

In the course, we deliberately tie each important Hibernate mechanism to SQL: states, dirty checking, flush, lazy loading, fetching, bulk operations, locking. And the final audit must also speak the language of SQL. Not because “we love databases”, but because in the end the database executes what you sent it, not what you meant.

Enabling SQL trace: profiles and logs

SQL trace can easily turn into a “noise cannon”: you switch it on, and the console starts looking like “The Matrix”, except that instead of code, green letters print select ... from .... So the discipline is this: we enable detailed SQL with a profile, not forever. In Commerce Persistence Lab, we have the sql-trace profile for this, which makes the log detailed exactly when we are diagnosing behaviour.

A minimal but working fragment of application-sql-trace.yml looks roughly like this (the keys may vary a bit depending on the logger setup, but the idea is the same — a separate profile and explicit levels):

spring:
  jpa:
    properties:
      hibernate:
        format_sql: true # format SQL so it is actually readable

logging:
  level:
    org.hibernate.SQL: debug # print the SQL strings themselves
    org.hibernate.orm.jdbc.bind: trace # print bind parameters (values for `?`)

Here, org.hibernate.SQL shows the SQL strings themselves, while org.hibernate.orm.jdbc.bind prints bind parameters (that is, the values you actually passed into ?). Without bind parameters, the SQL log often turns into a riddle: “it is clear that it was looking for something... but what exactly — the mystery of the century”.

Bind parameters: what is lost without them

A naive SQL log without parameters looks prettier, but is often less useful. It will show the shape of the query, but not the real selectivity that can make the query behave differently. The same select ... where status = ? can be cheap if status=ACTIVE is rare, and heavy if status=ACTIVE matches almost every row. For performance auditing, this is critical.

Another reason to love bind parameters is correctness diagnostics. A very unpleasant class of bugs: you think you are filtering out “not deleted” products, but in fact you are comparing deleted = null or you did not add the condition at all. When you can see the parameters, you catch that with your eyes in a second, without deep philosophical reasoning about “possible causes”.

Reading SQL logs: count, shape, flush trace

Reading SQL trace is not “read 500 lines and get tired”. It is discipline: first you look for structure, then for details. I usually start with three questions: how many queries are in the scenario, which queries repeat with the same shape, and where UPDATE/INSERT/DELETE suddenly appear where you thought “we are just reading”.

An example of a small log fragment (fictional, but very similar to what you have already seen in fetching labs):

-- 1) Load the order by id (root entity)
select o.id, o.order_number, o.customer_id
from purchase_order o
where o.id = ?;

-- 2) Then lazily load the customer by FK (if the fetch-plan did not pull it in earlier)
select c.id, c.email
from customer c
where c.id = ?;

If this is the detail use case “load the order and show the customer email”, then two queries may be normal (depending on the fetch-plan). But if you see such a pair 50 times in a row in one order list query, that is almost a textbook N+1. In that sense, SQL trace is honest: it does not argue, it just shows what you actually did.

Another typical signal in SQL trace is an unexpected flush. Imagine that in a service you change an object (dirty checking) and then run a query to “check a condition”. Hibernate may flush changes to the database before executing SELECT to ensure correctness. In the log this looks like “why do I have an UPDATE in the middle of the method?”. And this is where SQL trace is the only way not to guess.

3. Hibernate Statistics: runtime counters

Statistics is not a replacement for the SQL log, but a “dashboard counter”. It does not show the details of each query, but it gives numbers: how many statements were prepared, how many entities were loaded, how many collections were initialised, how many flushes happened. This is convenient when you need to compare “it was 51 queries, now it is 1” without reading a wall of logs. But there is a nuance: Statistics counts beautifully, but it does not explain, so you need to interpret it together with the use case and the SQL.

Just do not overvalue these numbers: prepareStatementCount is a good quick counter of SQL activity, but not a substitute for the SQL trace itself. We still look at the query shape, the order of SELECT/UPDATE, and flush side effects in the log.

In the final audit, Statistics is especially useful as a quick smoke detector. If one product list query gives you prepareStatementCount=200, that is a reason to enable SQL trace and find where you created an SQL chat. And if prepareStatementCount=1, that does not guarantee the query is good, but it at least says you most likely did not create N+1.

Enabling statistics and reading them in code

For Statistics to count anything at all, it must be enabled. Usually this is done with the Hibernate setting hibernate.generate_statistics=true (in our project we have a separate stats profile for this). It is important to remember that keeping statistics always on in production is often unnecessary: this is a diagnostics and testing tool, not an essential part of the business logic.

Then a practical question arises: how do you get a Statistics object in code? In a Spring Boot application, you usually have an EntityManagerFactory, which can be “unwrapped” into Hibernate SessionFactory, and from there you can get Statistics. A tiny fragment that you will use both in tests and in lab support:

import jakarta.persistence.EntityManagerFactory;
import org.hibernate.SessionFactory;
import org.hibernate.stat.Statistics;

// Extract the native Hibernate API from the JPA wrapper (we do this intentionally for a deep dive)
Statistics stats = entityManagerFactory
        .unwrap(SessionFactory.class) // turn EntityManagerFactory into SessionFactory
        .getStatistics(); // get runtime counters

The key word here is unwrap: we honestly admit that under the JPA abstraction we have a concrete provider — Hibernate — and we deliberately use its API because we are doing a deep-dive course. This is not “bad”, it is a tool.

What to look at in Statistics: key metrics

If you open the Statistics interface, there will be many methods, and it is easy to start measuring everything like a beginner who buys a smartwatch and tries to analyse... the phase of the moon. For our final audit, the most “down-to-earth” things matter: how many SQL operations, how many entity loads, how many collections were initialised, how many flush cycles happened. These metrics fit the audit axes well: performance and correctness.

A small table (without overdoing it) will help keep the focus:

Yes, this is an oversimplification, but we need a working tool, not a museum of metrics. In the final review, you should answer questions quickly, not write a dissertation on why prepareStatementCount increased by 2.

A mini-helper for lab support: a before/after statistics snapshot

A very convenient technique for labs and tests is to take a “snapshot” of statistics at the start and end of a scenario. Then you do not argue about “a lot/little”, but say “it was 0, now it is 51”. In Java, this is conveniently expressed with a small record (and yes, record is right on point here: a simple DTO with no extra logic).

import org.hibernate.stat.Statistics;

// Mini DTO: save what we care about comparing "before/after"
public record StatsSnapshot(long statements, long entityLoads) {

    // Create a snapshot from the current Statistics state
    public static StatsSnapshot of(Statistics stats) {
        return new StatsSnapshot(
                stats.getPrepareStatementCount(), // how many prepared statements Hibernate prepared
                stats.getEntityLoadCount() // how many entities were actually loaded
        );
    }
}

Then in a test or lab code, you can call: stats.clear(), run the use case, take StatsSnapshot.of(stats), and compare. This does not replace SQL, but it works very well as a “digital receipt” for a review finding.

4. ORM regression tests: lock in the behaviour

Tests in the persistence layer are not so much about “did the data save correctly” as they are about “does the system behave correctly”. ORM bugs are tricky because the code remains “logically correct”, but becomes expensive or unpredictable. That is why regression tests for ORM are insurance against old problems coming back after an “innocent” refactor, a new mapper, or a fetch-plan change.

And there is an important psychological point here: a good ORM test is one where you verify a symptom that really happens in production. For example, N+1, stale persistence context after bulk, extra flush, optimistic conflict. Testing “what SQL Hibernate does” is pointless. Testing “that we do not make 51 prepared statements for the list” is absolutely worthwhile.

Integration context instead of unit tests

A unit test is a test of logic in a vacuum. ORM, however, does not live in a vacuum, but in a combination: transaction, persistence context, JDBC, database, isolation, execution plans. So a repository unit test with mocks is usually like testing an airplane in a room: you can verify that the “landing gear” button calls lowerLandingGear(), but you cannot verify that the plane actually lands.

That does not mean unit tests are bad. It means that for ORM behaviour you need an integration context: at least @DataJpaTest with a real database (in our course — a PostgreSQL-oriented approach). And it is in this context that you can measure SQL, Statistics, and catch bugs that simply do not exist in mocks.

N+1 test template: measure, do not trust

A test for N+1 most often looks like this: we run a specific read use case, clear statistics, execute the query, and assert that the prepared statement counter matches expectations. It is not a perfect metric, but it works very well as regression insurance: if tomorrow someone removes EntityGraph or rewrites the query to findAll(), the test starts shouting.

A tiny fragment from a test (inside @DataJpaTest or @SpringBootTest — depending on which piece you are verifying):

import org.hibernate.SessionFactory;
import org.hibernate.stat.Statistics;

// Given: get Statistics from Hibernate (in a test this is legal and useful)
Statistics stats = entityManager.getEntityManagerFactory()
        .unwrap(SessionFactory.class)
        .getStatistics();

stats.clear(); // Given: reset counters so we measure only this scenario

// When: execute the "detailed" use case
orderRepository.findDetailedById(orderId).orElseThrow();

// Then: lock in the regression by the number of prepared statements in this scenario
assertThat(stats.getPrepareStatementCount()).isEqualTo(1);

Pay attention to the stats.clear() ritual. If you forget it, you will measure not the scenario, but the entire history of the test process. As a result, the test becomes unstable and starts behaving like a fortune teller: “today 3, tomorrow 7”.

And one more point: this test should verify a specific method intended for the detail use case (for example, findDetailedById). If you verify the “regular” findById, then the expectation of “1 query” may be incorrect — you simply have a different contract.

Bulk test: stale state and clear()

Bulk update/delete is a separate “school of pain”, because bulk changes data in the database, but does not synchronise already loaded managed objects in the persistence context. Therefore, the correct regression test here usually records not only the bulk operation, but also the post-operation protocol: clear() or a re-read.

A mini idea for such a test using Product:

import jakarta.persistence.EntityManager;

// Given: loaded a managed entity into the persistence context
Product p = em.find(Product.class, productId);

// When: do a bulk update (it bypasses the persistence context and writes directly to the DB)
productRepository.markDeleted(productId);

// Then: make sure we synchronise with the DB, otherwise we will keep reading a stale object
em.clear(); // removed stale objects from the persistence context

Product reloaded = em.find(Product.class, productId); // read the current state from the DB
assertThat(reloaded.isDeleted()).isTrue();

This fragment is good because it is very “human”: you literally show that after bulk you are not allowed to trust the object p, and you take the correct synchronisation step. Such a test will protect you in the future from an “optimiser” who decides to remove clear() “because it does not seem necessary”.

Test readability: Given/When/Then in 10 lines

The problem with ORM tests is not only writing them, but also understanding what you measured a month later. That is why even a short test is useful when structured as “Given/When/Then”, at least in comments. This is not a religion, but a way to protect against the “riddle test”.

An example of a micro-structure directly in code:

stats.clear(); // Given: clean baseline, previous SQL/flush are not interesting to us

orderQuery.findOrderPage(); // When: execute the use case (this is what we measure)

long sql = stats.getPrepareStatementCount(); // Then: get a measurable fact about SQL activity, not a "feeling"
assertThat(sql).isLessThanOrEqualTo(2); // Then: lock in the contract (and catch regressions)

Yes, this looks almost comically simple. But it is precisely such simple comments that make persistence tests educational and maintainable, rather than “magic numbers without context”.

SQL + statistics + tests as one language

A strong engineering position in persistence-review looks like this: “I see the issue, I can show it in SQL, I can confirm it with a number, and I can lock the fix with a test.” These are three levels of the same truth. SQL shows the details, statistics gives quick numeric signals, and the regression test guarantees you will not forget the lesson in a week.

To make this approach become “muscle memory”, it helps to keep a simple matrix at hand. Not as a list of 50 items, but as “if they ask X — prove Y like this”.

The point of the table is that it forces you to think not “which tool do I like”, but “what statement am I making and how do I support it”. That is evidence-based review.

When hot paths, red flags, and evidence sit next to each other, the review stops being theory and turns into a single pass: service → query → SQL → finding → priority.

5. Typical mistakes when gathering evidence

There are a couple of traps in the evidence base that especially attract beginners. They sound logical “in words”, but they lead to audit turning into either noise or ritual. Let us talk about them so you can recognise them by smell, the way a programmer recognises a memory leak: “it seems to work... but the fan is screaming like a jet engine”.

Mistake #1: leaving SQL trace on forever and stopping reading it.
If the SQL log is always on, you very quickly stop noticing it. It is like living near a railway: the first two days you wake up from every train, on the third day the brain gives up. In the end, you have “observability enabled”, but it does not work. It is better to keep detailed SQL in a separate profile and enable it intentionally for the scenario.

Mistake #2: measuring Statistics without isolating the scenario.
A very common situation: you looked at prepareStatementCount, saw the number 37, and concluded “terrible, N+1”. Then it turns out those 37 came from three different tests, because statistics were not cleared. stats.clear() before the scenario is not an “extra line”, it is the boundary of the experiment. Without it, numbers become guesswork.

Mistake #3: thinking that “1 SQL” automatically means “everything is fine”.
One query can be wonderful, or it can be a monster with 12 joins, duplicate rows, and a gigantic result width. Statistics will say “1”, but performance may still be bad. So “1 SQL” is only an answer to the question “is there no N+1”, not a quality stamp for the whole selection. For quality, you still need to look at the SQL shape and understand the use case.

Mistake #4: writing ORM tests as a “check of Hibernate”, not as a check of our contract.
The test “Hibernate flushes on commit” is pointless: it will either always be green or break during a version upgrade. The test “our findDetailedById method should not make more than 1 prepared statement” is useful, because that is your contract. ORM regression tests should protect exactly your expectations from the persistence layer, not universal ORM rules.

Mistake #5: turning artifacts into a religion: “SQL is sacred, tests will solve everything”.
SQL trace and tests are not the goal, but tools. If you start “proving” every tiny thing in three different ways, you will spend more time measuring than fixing. Keep the balance: first the signal (Statistics), then the explanation (SQL), then the lock-in (test) — exactly where there is regression risk and real value.