Can I call repository.save()?

Can I prove that this data boundary behaves correctly under real schema, real constraints,
real isolation, real connection pooling, and realistic failure modes?

Many small mapper/unit tests
Several repository integration tests with real DB
Focused transaction/concurrency tests
Focused pool/timeout/failure tests
Few end-to-end tests

Everything is mocked
Everything important is only tested through end-to-end
Production discovers the real behavior

when(dataSource.getConnection()).thenReturn(connection);
when(connection.prepareStatement(anyString())).thenReturn(statement);
when(statement.executeUpdate()).thenReturn(1);

repository.renameUser(42L, "alice");

verify(statement).setString(1, "alice");
verify(statement).setLong(2, 42L);

Repository is fully tested with mocks, therefore JDBC is safe.

Repository mock tests are interaction tests. They do not replace integration tests.

@Test
void mapsUserRow() {
    jdbc.update("""
        insert into app_user(id, email, status, created_at)
        values (?, ?, ?, ?)
        """,
        1L,
        "a@example.com",
        "ACTIVE",
        OffsetDateTime.parse("2026-06-27T10:15:30Z")
    );

    User user = repository.findById(1L).orElseThrow();

    assertThat(user.id()).isEqualTo(1L);
    assertThat(user.email()).isEqualTo("a@example.com");
    assertThat(user.status()).isEqualTo(UserStatus.ACTIVE);
}

interface RowView {
    long getLong(String column);
    String getString(String column);
    OffsetDateTime getOffsetDateTime(String column);
    boolean isNull(String column);
}

final class UserMapper {
    User map(RowView row) {
        return new User(
            row.getLong("id"),
            row.getString("email"),
            UserStatus.valueOf(row.getString("status")),
            row.getOffsetDateTime("created_at")
        );
    }
}

src/test/java
  com.example.user
    UserRepositoryIT.java
    UserTransactionIT.java
    UserConcurrencyIT.java
src/test/resources
  db/migration
    V001__create_user.sql
    V002__create_outbox.sql

public final class UserRepository {
    private final JdbcTemplate jdbc;

    public UserRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    public long create(String email) {
        KeyHolder keys = new GeneratedKeyHolder();

        jdbc.update(connection -> {
            PreparedStatement ps = connection.prepareStatement("""
                insert into app_user(email, status, created_at)
                values (?, 'ACTIVE', now())
                """, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, email);
            return ps;
        }, keys);

        Number id = keys.getKey();
        if (id == null) {
            throw new IllegalStateException("Expected generated key");
        }
        return id.longValue();
    }

    public Optional<User> findByEmail(String email) {
        return jdbc.query("""
            select id, email, status, created_at
            from app_user
            where email = ?
            """, rs -> {
            if (!rs.next()) return Optional.empty();
            return Optional.of(new User(
                rs.getLong("id"),
                rs.getString("email"),
                UserStatus.valueOf(rs.getString("status")),
                rs.getObject("created_at", OffsetDateTime.class)
            ));
        }, email);
    }
}

@Test
void createAndFindByEmail() {
    long id = repository.create("a@example.com");

    User user = repository.findByEmail("a@example.com").orElseThrow();

    assertThat(user.id()).isEqualTo(id);
    assertThat(user.email()).isEqualTo("a@example.com");
    assertThat(user.status()).isEqualTo(UserStatus.ACTIVE);
}

@Testcontainers
class UserRepositoryIT {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16-alpine")
        .withDatabaseName("app")
        .withUsername("app")
        .withPassword("secret");

    private HikariDataSource dataSource;
    private JdbcTemplate jdbc;
    private UserRepository repository;

    @BeforeEach
    void setUp() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(postgres.getJdbcUrl());
        config.setUsername(postgres.getUsername());
        config.setPassword(postgres.getPassword());
        config.setMaximumPoolSize(4);
        config.setConnectionTimeout(1_000);
        config.setPoolName("user-repository-test");

        dataSource = new HikariDataSource(config);
        jdbc = new JdbcTemplate(dataSource);

        migrate(dataSource);
        cleanDatabase(jdbc);

        repository = new UserRepository(jdbc);
    }

    @AfterEach
    void tearDown() {
        dataSource.close();
    }
}

static void migrate(DataSource dataSource) {
    Flyway.configure()
        .dataSource(dataSource)
        .locations("classpath:db/migration")
        .cleanDisabled(false)
        .load()
        .migrate();
}

@BeforeAll
static void migrateOnce() {
    migrate(dataSource);
}

@BeforeEach
void clean() {
    jdbc.update("truncate table outbox_event, app_user restart identity cascade");
}

@BeforeEach
void resetSchema() {
    Flyway flyway = Flyway.configure()
        .dataSource(dataSource)
        .cleanDisabled(false)
        .load();
    flyway.clean();
    flyway.migrate();
}

final class UserDbFixture {
    private final JdbcTemplate jdbc;

    UserDbFixture(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    long activeUser(String email) {
        return jdbc.queryForObject("""
            insert into app_user(email, status, created_at)
            values (?, 'ACTIVE', now())
            returning id
            """, Long.class, email);
    }

    long suspendedUser(String email) {
        return jdbc.queryForObject("""
            insert into app_user(email, status, created_at)
            values (?, 'SUSPENDED', now())
            returning id
            """, Long.class, email);
    }
}

@Test
void suspendedUserCannotBeRenamed() {
    long userId = fixture.suspendedUser("old@example.com");

    assertThatThrownBy(() -> service.renameUser(userId, "new@example.com"))
        .isInstanceOf(InvalidUserStateException.class);

    assertThat(repository.findById(userId).orElseThrow().email())
        .isEqualTo("old@example.com");
}

create table app_user (
    id bigserial primary key,
    email text not null,
    status text not null,
    created_at timestamptz not null,
    constraint uq_app_user_email unique(email),
    constraint ck_app_user_status check (status in ('ACTIVE', 'SUSPENDED'))
);

@Test
void emailMustBeUnique() {
    repository.create("a@example.com");

    assertThatThrownBy(() -> repository.create("a@example.com"))
        .isInstanceOf(DuplicateKeyException.class);
}

if (repository.existsByEmail(email)) {
    throw new DuplicateEmailException(email);
}

@Transactional
public void registerUserAndOutbox(String email) {
    long userId = userRepository.create(email);
    outboxRepository.append("UserRegistered", userId);
}

@Test
void rollsBackUserIfOutboxInsertFails() {
    outboxRepository.forceFailureForTest();

    assertThatThrownBy(() -> service.registerUserAndOutbox("a@example.com"))
        .isInstanceOf(OutboxWriteException.class);

    assertThat(count("app_user")).isZero();
    assertThat(count("outbox_event")).isZero();
}

public void transfer(long from, long to, BigDecimal amount) {
    txRunner.inTransaction(connection -> {
        accountRepository.debit(connection, from, amount);
        accountRepository.credit(connection, to, amount);
        ledgerRepository.append(connection, from, to, amount);
        return null;
    });
}

@Test
void transferIsAtomic() {
    long a = fixture.account("A", new BigDecimal("100.00"));
    long b = fixture.account("B", new BigDecimal("10.00"));

    ledgerRepository.failNextAppendForTest();

    assertThatThrownBy(() -> service.transfer(a, b, new BigDecimal("20.00")))
        .isInstanceOf(LedgerException.class);

    assertThat(balance(a)).isEqualByComparingTo("100.00");
    assertThat(balance(b)).isEqualByComparingTo("10.00");
    assertThat(count("ledger_entry")).isZero();
}

After failure, database state must be equivalent to before operation.

public boolean updateEmail(long id, long expectedVersion, String email) {
    int updated = jdbc.update("""
        update app_user
        set email = ?, version = version + 1
        where id = ? and version = ?
        """, email, id, expectedVersion);

    return updated == 1;
}

@Test
void staleVersionDoesNotUpdateRow() {
    long id = fixture.user("a@example.com", 3L);

    boolean updated = repository.updateEmail(id, 2L, "b@example.com");

    assertThat(updated).isFalse();
    assertThat(repository.findById(id).orElseThrow().email())
        .isEqualTo("a@example.com");
}

jdbc.update(sql, args);
return true;

int updated = jdbc.update(sql, args);
if (updated != 1) {
    throw new ConcurrentModificationException(...);
}

@Test
void createReturnsGeneratedIdThatCanBeLoaded() {
    long id = repository.create("a@example.com");

    assertThat(id).isPositive();
    assertThat(repository.findById(id)).isPresent();
}

@Test
void createFailsIfGeneratedKeyMissing() {
    // Usually tested with a small fake KeyHolder or special repository variant,
    // because forcing a real driver to omit generated keys is awkward.
}

@Test
void preservesMoneyScaleAndPrecision() {
    long id = invoiceRepository.create(new BigDecimal("1234567890.1234"));

    Invoice invoice = invoiceRepository.findById(id).orElseThrow();

    assertThat(invoice.amount()).isEqualByComparingTo("1234567890.1234");
}

@Test
void mapsNullableBooleanWithoutPrimitiveWasNullBug() {
    long id = jdbc.queryForObject("""
        insert into consent(user_id, marketing_allowed)
        values (42, null)
        returning id
        """, Long.class);

    Consent consent = repository.findConsent(id).orElseThrow();

    assertThat(consent.marketingAllowed()).isEmpty();
}

@Test
void storesEventInstantAsUtcInstant() {
    Instant occurredAt = Instant.parse("2026-06-27T10:15:30.123456Z");

    long id = eventRepository.append("UserRegistered", occurredAt);

    Event event = eventRepository.findById(id).orElseThrow();
    assertThat(event.occurredAt()).isEqualTo(occurredAt);
}

@Test
void findsEventsUsingHalfOpenInterval() {
    fixture.eventAt("2026-06-27T00:00:00Z");
    fixture.eventAt("2026-06-27T23:59:59Z");
    fixture.eventAt("2026-06-28T00:00:00Z");

    List<Event> events = repository.findBetween(
        Instant.parse("2026-06-27T00:00:00Z"),
        Instant.parse("2026-06-28T00:00:00Z")
    );

    assertThat(events).hasSize(2);
}

where date(created_at) = ?

where created_at >= ?
  and created_at < ?

A user may have at most one ACTIVE subscription.

create unique index uq_active_subscription_user
on subscription(user_id)
where status = 'ACTIVE';

@Test
void onlyOneActiveSubscriptionCanBeCreatedConcurrently() throws Exception {
    long userId = fixture.user("a@example.com");

    ExecutorService executor = Executors.newFixedThreadPool(2);
    CyclicBarrier barrier = new CyclicBarrier(2);

    Callable<Boolean> task = () -> {
        barrier.await();
        try {
            service.activateSubscription(userId);
            return true;
        } catch (DuplicateActiveSubscriptionException e) {
            return false;
        }
    };

    Future<Boolean> r1 = executor.submit(task);
    Future<Boolean> r2 = executor.submit(task);

    List<Boolean> results = List.of(r1.get(), r2.get());

    assertThat(results).containsExactlyInAnyOrder(true, false);
    assertThat(countActiveSubscriptions(userId)).isEqualTo(1);
}

@Test
void deadlockIsTranslatedToRetryableFailure() throws Exception {
    long a = fixture.account("A");
    long b = fixture.account("B");

    ExecutorService executor = Executors.newFixedThreadPool(2);
    CyclicBarrier barrier = new CyclicBarrier(2);

    Callable<Result> t1 = () -> transferWithManualLockOrder(a, b, barrier);
    Callable<Result> t2 = () -> transferWithManualLockOrder(b, a, barrier);

    List<Result> results = List.of(
        executor.submit(t1).get(),
        executor.submit(t2).get()
    );

    assertThat(results)
        .anyMatch(Result::isSuccess)
        .anyMatch(Result::isRetryableDeadlock);
}

@Test
void lockTimeoutIsTranslated() throws Exception {
    long id = fixture.user("a@example.com");

    Connection c1 = dataSource.getConnection();
    c1.setAutoCommit(false);
    jdbcWith(c1).update("select * from app_user where id = ? for update", id);

    try {
        assertThatThrownBy(() -> txRunner.inTransaction(c2 -> {
            try (Statement s = c2.createStatement()) {
                s.execute("set local lock_timeout = '200ms'");
            }
            userRepository.rename(c2, id, "b@example.com");
            return null;
        })).isInstanceOf(LockTimeoutException.class);
    } finally {
        c1.rollback();
        c1.close();
    }
}

@Test
void failsFastWhenPoolIsExhausted() throws Exception {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl(postgres.getJdbcUrl());
    config.setUsername(postgres.getUsername());
    config.setPassword(postgres.getPassword());
    config.setMaximumPoolSize(1);
    config.setConnectionTimeout(200);

    try (HikariDataSource ds = new HikariDataSource(config)) {
        Connection held = ds.getConnection();
        try {
            assertThatThrownBy(ds::getConnection)
                .isInstanceOf(SQLException.class)
                .hasMessageContaining("Connection is not available");
        } finally {
            held.close();
        }
    }
}

@Test
void repositoryDoesNotLeakConnections() throws Exception {
    HikariConfig config = testPoolConfig();
    config.setMaximumPoolSize(2);
    config.setConnectionTimeout(300);

    try (HikariDataSource ds = new HikariDataSource(config)) {
        UserRepository repo = new UserRepository(new JdbcTemplate(ds));

        for (int i = 0; i < 50; i++) {
            repo.findByEmail("missing-" + i + "@example.com");
        }

        HikariPoolMXBean mx = ds.getHikariPoolMXBean();
        assertThat(mx.getActiveConnections()).isZero();
        assertThat(mx.getThreadsAwaitingConnection()).isZero();
    }
}

config.setLeakDetectionThreshold(2_000);

try (Connection connection = dataSource.getConnection();
     PreparedStatement ps = connection.prepareStatement(sql)) {
    // use ps
}

try (Connection connection = dataSource.getConnection()) {
    connection.setAutoCommit(false);
    try {
        work.execute(connection);
        connection.commit();
    } catch (Throwable t) {
        safeRollback(connection, t);
        throw t;
    }
}

@Test
void returnsConnectionWhenMapperThrows() {
    assertThatThrownBy(() -> repository.queryWithFailingMapper())
        .isInstanceOf(MappingException.class);

    assertThat(pool().getActiveConnections()).isZero();
}

ResultSet rs = ps.executeQuery();
return mapper.map(rs); // ResultSet/Statement not closed on mapper failure

try (PreparedStatement ps = connection.prepareStatement(sql);
     ResultSet rs = ps.executeQuery()) {
    return mapper.map(rs);
}

@Test
void searchByEmailUsesIndex() {
    String plan = jdbc.queryForObject("""
        explain select id, email
        from app_user
        where email = 'a@example.com'
        """, String.class);

    assertThat(plan).contains("Index");
}

select * from case_file limit 50 offset 100

@Test
void paginationIsStableByCreatedAtAndId() {
    fixture.caseFile("2026-06-27T10:00:00Z", 1L);
    fixture.caseFile("2026-06-27T10:00:00Z", 2L);
    fixture.caseFile("2026-06-27T10:01:00Z", 3L);

    Page<CaseFile> page = repository.findPageAfter(null, 2);

    assertThat(page.items())
        .extracting(CaseFile::id)
        .containsExactly(1L, 2L);

    Page<CaseFile> next = repository.findPageAfter(page.nextCursor(), 2);

    assertThat(next.items())
        .extracting(CaseFile::id)
        .containsExactly(3L);
}

@Test
void bulkImportCanRestartAfterFailedChunk() {
    ImportJob job = fixture.importJob(List.of(
        row("a@example.com"),
        row("b@example.com"),
        row("bad-email"),
        row("c@example.com")
    ));

    ImportResult first = importer.run(job.id(), 2);

    assertThat(first.successfulRows()).isEqualTo(2);
    assertThat(first.failedRows()).isEqualTo(1);

    ImportResult second = importer.run(job.id(), 2);

    assertThat(second.successfulRows()).isEqualTo(1);
    assertThat(countUsers()).isEqualTo(3);
}

A rerun after failure must not duplicate committed rows.

@Test
void userRegistrationAppendsOutboxAtomically() {
    service.registerUser("a@example.com");

    assertThat(count("app_user")).isEqualTo(1);
    assertThat(outboxEvents())
        .singleElement()
        .satisfies(event -> {
            assertThat(event.type()).isEqualTo("UserRegistered");
            assertThat(event.status()).isEqualTo("PENDING");
        });
}

@Test
void rollbackRemovesUserIfOutboxCannotBeWritten() {
    fixture.breakOutboxConstraintForTest();

    assertThatThrownBy(() -> service.registerUser("a@example.com"))
        .isInstanceOf(DataAccessException.class);

    assertThat(count("app_user")).isZero();
    assertThat(count("outbox_event")).isZero();
}

@Test
void outboxPublisherMarksEventPublishedAfterSuccessfulSend() {
    long eventId = fixture.pendingOutboxEvent("UserRegistered");

    fakeBroker.succeed();
    publisher.publishPendingBatch();

    assertThat(outboxStatus(eventId)).isEqualTo("PUBLISHED");
}

@Test
void outboxPublisherDoesNotMarkPublishedWhenSendFails() {
    long eventId = fixture.pendingOutboxEvent("UserRegistered");

    fakeBroker.fail();
    publisher.publishPendingBatch();

    assertThat(outboxStatus(eventId)).isEqualTo("PENDING");
}

@Test
void readOnlyServiceDoesNotWrite() {
    assertThatThrownBy(() -> readOnlyService.accidentallyWrites())
        .isInstanceOf(DataAccessException.class);
}

grant usage on schema public to app_read;
grant select on all tables in schema public to app_read;
revoke insert, update, delete on all tables in schema public from app_read;

@Test
void searchTreatsInputAsValueNotSql() {
    fixture.user("normal@example.com");

    List<User> users = repository.searchByEmail("' OR 1=1 --");

    assertThat(users).isEmpty();
}

@Test
void rejectsUnknownSortColumn() {
    assertThatThrownBy(() -> repository.search(new SearchRequest(
        "alice",
        "email; drop table app_user; --",
        SortDirection.ASC
    ))).isInstanceOf(InvalidSortColumnException.class);
}

Use real DB for database semantics.
Use controlled fakes for external dependencies and rare infrastructure faults.

@Test
void logsCorrelationIdOnDatabaseFailure() {
    withCorrelationId("req-123", () -> {
        assertThatThrownBy(() -> repository.createDuplicateEmail())
            .isInstanceOf(DuplicateKeyException.class);
    });

    assertThat(logs())
        .anyMatch(log -> log.contains("req-123")
            && log.contains("duplicate")
            && log.contains("app_user"));
}

@Test
void incrementsRepositoryFailureMetric() {
    assertThatThrownBy(() -> repository.createDuplicateEmail())
        .isInstanceOf(DuplicateKeyException.class);

    assertThat(meterRegistry.counter("jdbc.repository.errors",
        "repository", "UserRepository",
        "operation", "create",
        "kind", "duplicate_key"
    ).count()).isEqualTo(1.0);
}

@Test
void checkedExceptionRollsBackWhenConfigured() {
    assertThatThrownBy(() -> service.operationThatThrowsCheckedException())
        .isInstanceOf(BusinessCheckedException.class);

    assertThat(count("app_user")).isZero();
}

@Transactional(rollbackFor = BusinessCheckedException.class)

@Test
void internalCallDoesNotAccidentallyBypassRequiredTransaction() {
    assertThatThrownBy(() -> service.outerCallsInnerTransactionalDirectly())
        .isInstanceOf(TransactionRequiredException.class);
}

@SpringBootTest
@Transactional
class UserServiceTest { ... }

String schema = "test_" + UUID.randomUUID().toString().replace("-", "");
jdbc.execute("create schema " + schema);
jdbc.execute("set search_path to " + schema);

<profile>
    <id>integration-test</id>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-failsafe-plugin</artifactId>
                <version>3.5.0</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>integration-test</goal>
                            <goal>verify</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</profile>

*Test.java  -> fast unit tests
*IT.java    -> integration tests with DB
*CT.java    -> concurrency tests, if separated

Tests verify calls, not data behavior.

Use real DB integration tests for repository semantics.

H2 behavior differs from production database.

Use same database family as production for critical tests.

Test A depends on row created by Test B.

Each test owns its data setup and cleanup.

assertThrows(Exception.class, () -> service.doWork());

assertThat(countAffectedRows()).isZero();

Test sleeps for production timeout duration.

Use small test-specific timeout and deterministic blocking condition.

Service validation is tested, database guardrail is not.

Test unique/check/foreign-key behavior directly.

@SpringBootTest @Transactional makes every test rollback.

Use non-transactional integration tests for commit/outbox/publisher behavior.

Take one repository that currently only has mock tests.
Add a Testcontainers integration test using the same DB family as production.
Prove insert, query, duplicate key, rollback, and generated key behavior.

Create a race test for a business invariant.
First make it fail without a database constraint.
Then add the constraint or locking strategy.
Make the test pass deterministically.

Configure HikariCP maximumPoolSize=1 and connectionTimeout=200ms in a test.
Hold one connection open.
Prove second acquisition fails fast and produces the expected translated exception/metric.

Create a SQL injection regression test for a dynamic search endpoint.
Prove values are bound and identifiers are allowlisted.

Pick one incident scenario from your system.
Convert it into a failure-injection integration test.
The test should fail before the fix and pass after the fix.

Mock tests prove interactions.
Real database tests prove behavior.
Failure tests prove resilience.
Concurrency tests prove invariants.

What exact production failure mode does this test prevent?