Testing the persistence layer

Key points

• @DataJpaTest starts the JPA slice only, wraps each test method in a transaction and rolls it back on the way out.
• That rollback is why tests do not leak into one another, and why you must never assert on rows a previous test left behind.
• The first level cache hides bugs. flush() then clear() before asserting, so the read really hits the database.
• Test against the real engine with Testcontainers. H2 accepts SQL that Postgres rejects, and the dialects differ on locking and sequences.
• Add @AutoConfigureTestDatabase(replace = Replace.NONE) or Spring quietly swaps your container for an in-memory database.
• Assert the query count next to the result. Correctness and the number of round trips are two different bugs with two different fixes.

Example

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@Testcontainers
class BookRepositoryTest {

    @Container
    @ServiceConnection                       // wires the datasource for you
    static PostgreSQLContainer<?> db = new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired TestEntityManager em;
    @Autowired BookRepository books;

    @Test
    void findsByIsbnFromTheDatabaseNotTheCache() {
        em.persist(new Book("978-0134685991", "Effective Java"));
        em.flush();
        em.clear();                          // without this the L1 cache answers

        Optional<Book> found = books.findByIsbn("978-0134685991");

        assertThat(found).isPresent();
        assertThat(found.get().getTitle()).isEqualTo("Effective Java");
    }
    // the transaction rolls back here, so the next test sees an empty table
}

Flush, clear, and use the real database - or you are testing the cache.