SYS//OP
Series/Learn Java Data Access Pattern In Action
Series MapLesson 59 / 60
Final StretchOrdered learning track

Testing Data Access Layer

Learn Java Data Access Pattern In Action - Part 059

Testing data access layer untuk Java production: unit test, integration test, Testcontainers, migration test, transaction rollback test, fixture strategy, query count test, concurrency test, performance smoke test, dan production readiness validation.

11 min read2116 words
In This Lesson
1. Core Thesis2. What You Need to Prove3. Test Pyramid for Data Access
PrevNext
Lesson 5960 lesson track51–60 Final Stretch
#java#data-access#testing#integration-test+6 more

Part 059 — Testing Data Access Layer

Data access bug jarang terlihat dari unit test mock.

Banyak bug hanya muncul ketika SQL benar-benar bertemu database:

  • constraint violation;
  • wrong isolation;
  • lock timeout;
  • deadlock;
  • generated SQL berbeda;
  • N+1;
  • migration mismatch;
  • enum code unknown;
  • timestamp conversion;
  • nullable left join;
  • transaction rollback gagal;
  • read model upsert salah;
  • batch partial failure;
  • index/query plan buruk.

Testing data access layer yang production-grade harus menguji kontrak antara Java, SQL, transaction, schema, migration, dan database engine nyata.

Part ini membahas testing data access layer secara komprehensif.

1. Core Thesis

Testing data access harus berlapis:

Unit test:
  mapping/pure logic/classification.

Integration test:
  real database + migrations + repository/query/DAO.

Transaction test:
  commit/rollback/isolation/lock/concurrency.

Migration test:
  schema evolution and compatibility.

Performance smoke test:
  query count, N+1, bounded latency, realistic data shape.

Operational test:
  backfill, retry, idempotency, failure scenarios.

Mock repository tidak membuktikan data access correctness.

2. What You Need to Prove

A production-grade data access test suite should prove:

  • SQL syntax valid on real DB;
  • schema matches code;
  • migrations apply cleanly;
  • mapping handles null/enum/time/money/JSON;
  • transaction rollback works;
  • constraints are enforced;
  • errors are translated semantically;
  • optimistic/pessimistic locking works;
  • query count bounded;
  • pagination deterministic;
  • tenant/security predicate present;
  • backfill/job idempotent;
  • batch behavior correct;
  • read model upsert ignores old version;
  • performance not catastrophically bad.

3. Test Pyramid for Data Access

Many:
  pure unit tests for mapping/converters/classifiers

Enough:
  integration tests for each critical DAO/repository/query

Targeted:
  concurrency/locking tests

Selective:
  performance smoke tests

Pipeline:
  migration tests from empty and previous schema

Data access integration tests are slower than unit tests but catch bugs unit tests cannot.

Do not over-mock the database.

4. Unit Tests That Are Worth It

Useful unit tests:

  • enum DB code mapping;
  • value object validation;
  • SQL error classifier;
  • query object validation;
  • sort whitelist;
  • cursor encoding/decoding;
  • DTO/domain mapper if pure;
  • idempotency payload hash;
  • retry classifier;
  • backfill transformation;
  • constraint-name-to-domain-error mapping.

Example:

@Test
void mapsStatusFromDbCode() {
    assertThat(CaseStatus.fromDbCode("UNDER_REVIEW"))
            .isEqualTo(CaseStatus.UNDER_REVIEW);

    assertThatThrownBy(() -> CaseStatus.fromDbCode("UNKNOWN"))
            .isInstanceOf(UnknownCaseStatus.class);
}

5. Unit Test: SQL Error Classifier

@Test
void classifiesDuplicateCaseNumberConstraint() {
    SQLException sqlException = postgresUniqueViolation(
            "uq_case_file_tenant_case_number"
    );

    DataAccessException exception = wrap(sqlException);

    assertThat(classifier.isUniqueViolation(
            exception,
            "uq_case_file_tenant_case_number"
    )).isTrue();
}

But also test real DB exception in integration test if possible because wrapping differs by framework.

6. Query Object Validation Test

@Test
void rejectsTooLargeLimit() {
    assertThatThrownBy(() ->
            CaseDashboardQuery.fromRequest(limit(10_000))
    ).isInstanceOf(InvalidQuery.class);
}

@Test
void sortMustBeWhitelisted() {
    assertThatThrownBy(() ->
            CaseDashboardQuery.fromRequest(sort("updated_at desc; drop table case_file"))
    ).isInstanceOf(InvalidQuery.class);
}

Prevent unsafe query before SQL builder/mapper.

7. Integration Test Must Use Real DB

Use same database family as production.

Why H2/in-memory is not enough:

  • dialect differences;
  • locking/isolation differences;
  • constraint behavior;
  • JSON/array functions;
  • timestamp semantics;
  • generated keys;
  • index syntax;
  • partial/concurrent index;
  • SQL planner;
  • transaction DDL behavior.

Use Testcontainers or equivalent real DB.

8. Testcontainers Pattern

Conceptual JUnit setup:

@Testcontainers
class CaseFileRepositoryIT {
    @Container
    static PostgreSQLContainer<?> postgres =
            new PostgreSQLContainer<>("postgres:16");

    @BeforeAll
    static void migrate() {
        Flyway.configure()
              .dataSource(postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword())
              .locations("classpath:db/migration")
              .load()
              .migrate();
    }
}

Use actual migrations, not hand-created schema.

9. Spring Integration Test

@SpringBootTest
@Testcontainers
class CaseFileRepositoryIT {
    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16");

    @DynamicPropertySource
    static void properties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
    }
}

Spring Boot can run Flyway/Liquibase automatically in tests.

Ensure test DB user and schema are realistic.

10. Migration Test From Empty DB

Test:

empty database -> apply all migrations -> app context starts -> data access tests pass

This proves full schema can be built.

It catches:

  • migration syntax error;
  • missing object;
  • wrong order;
  • repeatable migration failure;
  • jOOQ codegen mismatch if included;
  • MyBatis XML referencing missing columns.

11. Migration Test From Previous Version

More valuable for production:

previous release schema/data -> apply current migrations -> smoke tests

Catches:

  • dirty data constraint failure;
  • not-null failure;
  • duplicate unique violation;
  • incompatible data transformation;
  • long migration behavior in staging;
  • old data mapping issues.

Use anonymized/sampled production data if possible.

12. Test Migration Preconditions

If migration adds not-null after backfill, test precondition failure.

Example:

Given priority null rows exist
When constraint migration runs
Then migration halts before adding not-null

This proves migration fails safely rather than corrupting/locking.

13. Repository Integration Test

@Test
void loadForApprovalReturnsCaseWithExpectedState() {
    fixture.caseFile(caseId, status(UNDER_REVIEW), version(7));

    Optional<CaseFile> result = repository.loadForApproval(caseId);

    assertThat(result).isPresent();
    assertThat(result.get().status()).isEqualTo(UNDER_REVIEW);
    assertThat(result.get().version()).isEqualTo(7);
}

This tests mapping and query contract.

14. Query Service Integration Test

@Test
void dashboardSearchFiltersByTenantAndStatus() {
    fixture.caseFile(tenantA, status(OPEN));
    fixture.caseFile(tenantA, status(CLOSED));
    fixture.caseFile(tenantB, status(OPEN));

    Slice<CaseDashboardRow> result =
            dashboardQuery.search(query(tenantA, status(OPEN)));

    assertThat(result.items()).hasSize(1);
    assertThat(result.items().get(0).tenantId()).isEqualTo(tenantA);
}

Test tenant isolation explicitly.

15. Mapping Nullability Test

@Test
void dashboardMapsUnassignedOfficerAsEmptyOptional() {
    fixture.caseFile(caseId, assignedOfficer(null));

    CaseDashboardRow row = dashboardQuery.get(caseId);

    assertThat(row.assignedOfficerName()).isEmpty();
}

Catches left join null handling.

16. Unknown Enum Test

If DB can contain unknown code due older/newer version or bad data:

@Test
void unknownStatusFailsWithDataMappingException() {
    fixture.rawSql("""
        insert into case_file(id, tenant_id, status, version)
        values (?, ?, 'ALIEN_STATUS', 0)
        """, caseId, tenantId);

    assertThatThrownBy(() -> repository.load(caseId))
            .isInstanceOf(DataMappingException.class);
}

Or if forward compatibility requires unknown handling, assert fallback behavior.

17. Timestamp Mapping Test

@Test
void mapsUpdatedAtConsistently() {
    Instant updatedAt = Instant.parse("2026-07-05T10:15:30Z");
    fixture.caseFile(caseId, updatedAt(updatedAt));

    CaseFileRow row = query.find(caseId).orElseThrow();

    assertThat(row.updatedAt()).isEqualTo(updatedAt);
}

Timezone bugs are common. Test them.

18. Constraint Test

@Test
void duplicateCaseNumberRejected() {
    fixture.caseFile(tenantId, caseNumber("CASE-001"));

    assertThatThrownBy(() ->
            repository.create(caseNumber("CASE-001"))
    ).isInstanceOf(DuplicateCaseNumber.class);
}

This proves:

  • DB constraint exists;
  • constraint named/mapped;
  • exception translation works.

19. Foreign Key Test

@Test
void assignmentToMissingCaseFailsSemantically() {
    assertThatThrownBy(() ->
            assignmentRepository.assign(missingCaseId, officerId)
    ).isInstanceOf(CaseNotFoundOrInvalidReference.class);
}

Whether FK violation maps to user-facing error or internal invariant depends contract.

Test it.

20. Transaction Rollback Test

@Test
void rollbackWhenOutboxInsertFails() {
    fixture.caseFile(caseId, status(UNDER_REVIEW), version(7));

    assertThatThrownBy(() ->
            useCase.approveWithBrokenOutbox(command)
    ).isInstanceOf(DataAccessException.class);

    CaseFileRow row = caseQuery.find(caseId).orElseThrow();

    assertThat(row.status()).isEqualTo(UNDER_REVIEW);
    assertThat(auditQuery.findByCase(caseId)).isEmpty();
    assertThat(outboxQuery.findByAggregate(caseId)).isEmpty();
}

This proves transaction integration across DAOs/repositories.

21. Commit Test

@Test
void approveCommitsCaseAuditAndOutboxAtomically() {
    ApproveCaseResult result = useCase.approve(command);

    assertThat(caseQuery.status(caseId)).isEqualTo(APPROVED);
    assertThat(auditQuery.findByCommand(command.commandId())).hasSize(1);
    assertThat(outboxQuery.findByEventKey("case-approved:" + command.commandId())).hasSize(1);
}

Commit path and rollback path both needed.

22. @Transactional Test Caveat

In Spring tests, test method may run inside transaction and rollback after test.

This is convenient for cleanup but can hide commit behavior.

For commit/rollback semantics, run use case in real transaction and verify from separate transaction/context.

Be deliberate.

23. Test Data Cleanup

Options:

  • transaction rollback per test;
  • truncate tables after test;
  • recreate container/database per test class;
  • use unique tenant/test IDs;
  • schema per test.

Trade-offs:

StrategyProsCons
rollback per testfasthides commit, not for async
truncaterealistic commitslower, FK order
container per classisolatedslower startup
unique IDsparallel-friendlyleftover data
schema per testisolationsetup complexity

Choose intentionally.

24. Fixture Strategy

Good fixtures:

  • use domain builders;
  • hide irrelevant columns;
  • allow explicit edge cases;
  • use real repository for setup where useful;
  • use raw SQL for corrupt/edge data;
  • create tenant/security scope;
  • include timestamps/version.

Example:

fixture.caseFile()
        .tenant(tenantA)
        .status(UNDER_REVIEW)
        .version(7)
        .assignedOfficer(officerId)
        .insert();

Avoid giant fixture blobs.

25. Fixture Builder

public final class CaseFileFixtureBuilder {
    private TenantId tenantId = TenantId.random();
    private CaseStatus status = CaseStatus.UNDER_REVIEW;
    private long version = 0;
    private Instant updatedAt = Instant.parse("2026-07-05T00:00:00Z");

    public CaseFileId insert() {
        CaseFileId id = CaseFileId.newId();

        jdbc.update("""
            insert into case_file(id, tenant_id, status, version, updated_at)
            values (?, ?, ?, ?, ?)
            """,
            id.value(),
            tenantId.value(),
            status.dbCode(),
            version,
            updatedAt
        );

        return id;
    }
}

Fixtures are test infrastructure. Keep them correct and clear.

26. Query Count Test

N+1 prevention:

@Test
void dashboardDoesNotHaveNPlusOne() {
    fixture.createOpenCases(50);

    sqlCounter.reset();

    dashboardUseCase.search(query(limit(50)));

    assertThat(sqlCounter.totalSelects()).isLessThanOrEqualTo(2);
}

Use datasource proxy or ORM statistics.

Do not assert exact SQL count for every small test. Use budgets on critical endpoints.

27. Serialization Query Test

If using ORM entities:

@Test
void serializingResponseDoesNotHitDatabase() throws Exception {
    CaseDetailResponse response = detailUseCase.getDetail(caseId);

    sqlCounter.reset();

    objectMapper.writeValueAsString(response);

    assertThat(sqlCounter.total()).isZero();
}

Catches lazy entity/proxy leaking to response.

28. No-Write Read Test

Read endpoint should not dirty-check update.

@Test
void dashboardReadDoesNotUpdateAnything() {
    fixture.caseFile(caseId, updatedAt(oldTime));

    sqlCounter.reset();

    dashboardUseCase.search(query());

    assertThat(sqlCounter.updateCount()).isZero();
}

Useful for ORM read path where mapper may mutate entity accidentally.

29. Pagination Test

Offset/slice:

@Test
void dashboardUsesDeterministicOrder() {
    Instant same = Instant.parse("2026-07-05T00:00:00Z");

    CaseFileId a = fixture.caseFile(updatedAt(same)).insert();
    CaseFileId b = fixture.caseFile(updatedAt(same)).insert();

    Slice<CaseDashboardRow> page = dashboardQuery.search(sort(UPDATED_DESC));

    assertThat(page.items()).extracting(CaseDashboardRow::id)
            .containsSubsequence(b, a); // according to tiebreaker
}

Test tiebreaker order to avoid duplicate/skip under pagination.

30. Keyset Cursor Test

@Test
void keysetCursorReturnsNextPageWithoutOverlap() {
    fixture.createCases(30);

    CursorSlice<Row, CaseCursor> first = query.search(firstPage(limit(10)));
    CursorSlice<Row, CaseCursor> second = query.search(after(first.nextCursor(), limit(10)));

    assertThat(ids(first)).doesNotContainAnyElementsOf(ids(second));
}

Also test cursor belongs to same sort/filter if encoded with metadata.

31. Empty IN Test

@Test
void findByIdsWithEmptyListReturnsEmptyWithoutSqlError() {
    List<CaseFileRow> rows = repository.findByIds(List.of());

    assertThat(rows).isEmpty();
}

Many DBs do not accept in ().

32. Batch Test

@Test
void insertAuditBatchRollsBackOnDuplicate() {
    List<AuditRow> rows = List.of(
            audit("a"),
            audit("b"),
            audit("a") // duplicate ID
    );

    assertThatThrownBy(() -> auditDao.insertBatch(rows))
            .isInstanceOf(DuplicateAudit.class);

    assertThat(auditQuery.count()).isZero();
}

Proves batch transaction and failure behavior.

33. Upsert Idempotency Test

@Test
void projectionIgnoresOlderVersion() {
    projectionDao.apply(snapshot(caseId, version(8), status(APPROVED)));
    projectionDao.apply(snapshot(caseId, version(7), status(UNDER_REVIEW)));

    ProjectionRow row = projectionDao.find(caseId).orElseThrow();

    assertThat(row.version()).isEqualTo(8);
    assertThat(row.status()).isEqualTo(APPROVED);
}

Critical for read model/event processing.

34. Optimistic Lock Test

Use separate transactions/entity managers.

@Test
void staleExpectedVersionFails() {
    fixture.caseFile(caseId, status(UNDER_REVIEW), version(8));

    ApproveCaseCommand stale =
            commandBuilder.caseId(caseId).expectedVersion(7).build();

    assertThatThrownBy(() -> useCase.approve(stale))
            .isInstanceOf(OptimisticConflict.class);

    assertThat(caseQuery.status(caseId)).isEqualTo(UNDER_REVIEW);
}

This tests expected version conflict.

35. True Concurrent Optimistic Test

@Test
void concurrentApprovalsOnlyOneSucceeds() throws Exception {
    fixture.caseFile(caseId, status(UNDER_REVIEW), version(0));

    ExecutorService executor = Executors.newFixedThreadPool(2);
    CountDownLatch start = new CountDownLatch(1);

    Future<Result> first = executor.submit(() -> {
        start.await();
        return attemptApprove(commandA);
    });

    Future<Result> second = executor.submit(() -> {
        start.await();
        return attemptApprove(commandB);
    });

    start.countDown();

    List<Result> results = List.of(first.get(), second.get());

    assertThat(results).filteredOn(Result::isSuccess).hasSize(1);
    assertThat(results).filteredOn(Result::isConflict).hasSize(1);
}

Use real DB. Avoid flaky timing by using barriers/latches.

36. Pessimistic Lock Timeout Test

Two transactions:

T1 locks row and waits.
T2 attempts lock with short timeout.
T2 fails with lock timeout.
T1 releases.

Pseudo:

@Test
void lockTimeoutMapsToCaseBusy() {
    CountDownLatch locked = new CountDownLatch(1);
    CountDownLatch release = new CountDownLatch(1);

    Future<?> t1 = executor.submit(() ->
            tx.execute(() -> {
                repository.loadForUpdate(caseId);
                locked.countDown();
                release.await();
                return null;
            })
    );

    locked.await();

    assertThatThrownBy(() ->
            tx.execute(() -> repository.loadForUpdateWithTimeout(caseId))
    ).isInstanceOf(CaseBusy.class);

    release.countDown();
}

Use timeouts so test cannot hang forever.

37. Deadlock Retry Test

Deadlock tests can be flaky, but for critical workflows you can simulate or provoke.

Alternative: test retry wrapper with classified exception.

Integration test for real deadlock may be valuable but should be isolated.

Most important:

  • retry whole transaction;
  • idempotency prevents duplicate effects.

38. Isolation Test

Test anomaly only if isolation level matters.

Example serializable retry:

  • two transactions attempt conflicting predicate update;
  • one should fail serialization;
  • retry wrapper handles.

These tests are DB-specific and can be slow. Use targeted coverage.

39. Outbox Atomicity Test

@Test
void outboxInsertedInSameTransactionAsStateChange() {
    useCase.approve(command);

    assertThat(caseQuery.status(caseId)).isEqualTo(APPROVED);
    assertThat(outboxQuery.findByAggregate(caseId))
            .singleElement()
            .extracting(OutboxEvent::eventType)
            .isEqualTo("CaseApproved");
}

Rollback counterpart should exist.

40. Inbox Idempotency Test

@Test
void duplicateMessageProcessedOnce() {
    Message message = fixture.caseApprovedMessage(eventId);

    inboxConsumer.handle(message);
    inboxConsumer.handle(message);

    assertThat(readModelQuery.applyCount(eventId)).isEqualTo(1);
    assertThat(inboxQuery.status(eventId)).isEqualTo(PROCESSED);
}

Also test same message ID different payload hash -> conflict.

41. Cache Safety Test

@Test
void commandDoesNotTrustStaleCache() {
    caseDetailCache.put(caseId, detail(status(UNDER_REVIEW), version(7)));

    caseDao.directUpdateStatus(caseId, CLOSED, version(8));

    assertThatThrownBy(() -> approveUseCase.approve(expectedVersion(7)))
            .isInstanceOf(OptimisticConflict.class);
}

Proves command validates source DB, not cache.

42. Migration Compatibility Test

For expand-contract:

expanded schema + old code smoke
expanded schema + new code smoke
partial backfill + new code smoke
contracted schema + new code smoke

In practice you may run release-level compatibility tests.

At minimum, integration tests should cover partial backfill state.

43. Partial Backfill Test

@Test
void newCodeReadsFallbackWhenNewColumnNull() {
    fixture.caseFile(status("APPROVED"), caseStatus(null));

    CaseFile caseFile = repository.load(caseId).orElseThrow();

    assertThat(caseFile.status()).isEqualTo(APPROVED);
}

This is essential for expand-contract.

44. Dual Write Test

@Test
void newCodeWritesOldAndNewColumns() {
    useCase.changeStatus(command(next(APPROVED)));

    RawRow row = rawQuery.caseFile(caseId);

    assertThat(row.status()).isEqualTo("APPROVED");
    assertThat(row.caseStatus()).isEqualTo("APPROVED");
}

Prevents hidden writer bug.

45. Backfill Job Test

Test:

  • dry run no write;
  • chunk updates rows;
  • progress advances;
  • rerun idempotent;
  • pause respected;
  • error recorded;
  • cursor not advanced on rollback.

Example:

@Test
void backfillIsIdempotent() {
    fixture.caseFile(status("APPROVED"), caseStatus(null));

    job.runOnce(chunkSize(100));
    job.runOnce(chunkSize(100));

    assertThat(rawQuery.caseStatus(caseId)).isEqualTo("APPROVED");
    assertThat(progress.rowsUpdated()).isEqualTo(1);
}

46. Progress Transaction Test

@Test
void progressNotAdvancedWhenChunkFails() {
    fixture.rowsNeedingBackfill(10);
    job.injectFailureAfterUpdates();

    assertThatThrownBy(() -> job.runOneChunk())
            .isInstanceOf(RuntimeException.class);

    assertThat(progress.cursor()).isEqualTo(initialCursor);
}

Prevents skipped rows.

47. Performance Smoke Test

Not full benchmark.

Goal: catch catastrophic regressions.

Example:

@Test
void dashboardSearchUnderBudgetWithRepresentativeData() {
    fixture.createCases(10_000, tenantA);

    long elapsed = time(() -> dashboardQuery.search(query(limit(50))));

    assertThat(elapsed).isLessThan(500);
    assertThat(sqlCounter.totalSelects()).isLessThanOrEqualTo(2);
}

Keep thresholds generous to avoid flaky CI.

Run heavier tests nightly if needed.

48. Explain Plan Test

Automated plan assertion is brittle.

Better:

  • capture SQL;
  • run explain in performance test environment;
  • review manually for critical query;
  • store expected index usage in docs;
  • alert on slow query in staging/prod.

For mission-critical queries, plan regression tests may be justified but require care.

49. Realistic Data Fixture

Performance tests need:

  • many tenants;
  • hot tenant;
  • status distribution;
  • child rows;
  • old archived data;
  • realistic indexes;
  • realistic page size;
  • text selectivity;
  • skew.

Small uniform fixture misleads.

50. Contract Test for Repository

Repository contract test examples:

  • not found returns Optional.empty;
  • get required throws NotFound;
  • duplicate maps to domain exception;
  • optimistic conflict maps conflict;
  • delete/update affected rows checked;
  • stream must close resources;
  • method does not start transaction if caller owns it? depends contract.

Write tests around contract, not framework internals.

51. Test SQL Injection Defense

@Test
void rawSortInjectionRejectedBeforeMapper() {
    assertThatThrownBy(() -> requestToQuery(sort("id desc; drop table officer")))
            .isInstanceOf(InvalidSort.class);
}

For MyBatis ${sortSql}, test mapper receives enum-derived SQL only.

52. Security Predicate Test

@Test
void userCannotSeeCaseOutsideVisibleUnit() {
    fixture.caseFile(tenantA, unitA);
    fixture.caseFile(tenantA, unitB);

    UserScope scope = scope(visibleUnits(unitA));

    List<Row> rows = dashboardQuery.search(query(tenantA, scope));

    assertThat(rows).extracting(Row::unitId).containsOnly(unitA);
}

Data leaks often happen in query filters.

53. Multi-Tenant Cache Key Test

@Test
void cachedDashboardIsTenantScoped() {
    fixture.caseFile(tenantA, caseNumber("A"));
    fixture.caseFile(tenantB, caseNumber("B"));

    List<Row> a = dashboardService.search(tenantA, sameFilter);
    List<Row> b = dashboardService.search(tenantB, sameFilter);

    assertThat(a).extracting(Row::tenantId).containsOnly(tenantA);
    assertThat(b).extracting(Row::tenantId).containsOnly(tenantB);
}

If cache key missing tenant, this test catches it.

54. Testing Reactive Data Access

Use StepVerifier.

@Test
void approveRollsBackOnOutboxFailure() {
    StepVerifier.create(useCase.approveWithBrokenOutbox(command))
            .expectError()
            .verify();

    StepVerifier.create(caseQuery.find(caseId))
            .assertNext(row -> assertThat(row.status()).isEqualTo(UNDER_REVIEW))
            .verifyComplete();
}

Also use BlockHound to catch blocking calls in reactive stack.

55. Testing Virtual Thread Data Access

Virtual thread data access mostly uses normal integration tests.

Add load/concurrency tests for:

  • pool wait;
  • many virtual threads;
  • timeouts;
  • pinning diagnostics if needed;
  • bounded concurrency.

Do not create unbounded virtual threads in tests that overload CI database.

56. Flaky Test Prevention

Concurrency/time tests can be flaky.

Use:

  • latches/barriers;
  • explicit lock timeout;
  • generous deadlines;
  • real DB container per class;
  • avoid sleeping as synchronization;
  • clean resource shutdown;
  • deterministic IDs/timestamps;
  • retry test only if infrastructure flake known, not to hide bugs.

57. Test Naming

Name tests by behavior:

approveWithStaleVersionReturnsConflict()
dashboardDoesNotExecuteNPlusOneQueries()
backfillDoesNotAdvanceCursorWhenChunkRollsBack()
readModelIgnoresOlderSourceVersion()

Good test names become documentation.

58. CI Strategy

CI stages:

unit tests
data access integration tests
migration tests
contract/security tests
query count tests
selective performance smoke

Nightly/staging:

previous-release upgrade
large fixture performance
concurrency/lock tests
backfill dry run

Not all expensive tests must run on every commit, but critical correctness should.

59. Local Developer Workflow

Make data access tests easy to run:

  • Docker/Testcontainers;
  • deterministic fixtures;
  • clear error messages;
  • no manual DB setup;
  • fast enough subset;
  • docs for running specific test.

If tests are painful, team stops running them.

60. Production Readiness Test Checklist

  • Migrations apply from empty DB.
  • Migrations apply from previous release state.
  • Critical repositories tested with real DB.
  • Query services test tenant/security predicates.
  • Constraint exceptions translated.
  • Transaction rollback tested.
  • Outbox/inbox idempotency tested.
  • Lock/optimistic conflict tested.
  • Query count budgets tested.
  • Pagination/cursor tested.
  • Batch failure rollback tested.
  • Backfill dry run/idempotency/progress tested.
  • Partial migration/backfill compatibility tested.
  • Performance smoke test exists for hot queries.
  • Cache stale safety tested.
  • Reactive/virtual thread specifics tested if used.

61. Anti-Pattern: Mocking Repository and Calling It Data Access Test

Mocks test caller behavior, not database contract.

62. Anti-Pattern: H2 for PostgreSQL-Specific SQL

Use real DB for integration.

63. Anti-Pattern: Only Testing Happy Path

Data access bugs often happen in constraint/error/rollback path.

64. Anti-Pattern: Tests With Tiny Data Only

Performance and pagination bugs hide.

65. Anti-Pattern: No Query Count Budget

N+1 returns silently.

66. Anti-Pattern: Backfill Untested Until Production

Backfill is production code.

67. Mini Lab

Build test plan for:

Approve case use case:
- expected version;
- status transition;
- audit insert;
- outbox insert;
- idempotent command replay;
- duplicate command with different payload rejected;
- optimistic conflict;
- rollback on outbox failure;
- tenant isolation;
- dashboard read model updated asynchronously.

Tasks:

  1. Unit tests.
  2. Integration tests.
  3. Transaction rollback tests.
  4. Concurrency tests.
  5. Outbox/inbox tests.
  6. Read model upsert tests.
  7. Query count tests.
  8. Migration compatibility tests.
  9. Performance smoke tests.
  10. Production readiness checklist.

68. Summary

Testing data access layer means testing the real contract between Java and database.

You must master:

  • unit vs integration boundaries;
  • real DB tests;
  • Testcontainers;
  • migration tests;
  • previous release upgrade;
  • mapping null/enum/time tests;
  • constraint/error translation;
  • transaction rollback;
  • fixture strategy;
  • query count/N+1 tests;
  • pagination/cursor tests;
  • batch/upsert tests;
  • concurrency/locking tests;
  • outbox/inbox tests;
  • cache safety;
  • expand-contract compatibility;
  • backfill/repair tests;
  • performance smoke;
  • security predicate tests;
  • reactive/virtual thread specifics;
  • CI strategy;
  • anti-patterns.

Part berikutnya adalah final: Data Access Engineering Playbook — decision matrix, checklist, failure model, review rubric, production readiness, and interview-level synthesis.

69. References

Lesson Recap

You just completed lesson 59 in final stretch. Use the series map if you want to review the broader track, or continue directly into the next lesson while the context is still warm.

Back To SeriesNext Lesson
Continue The Track

Keep the momentum while the lesson is still fresh. Move backward for review or continue forward into the next concept.

Previous Lesson
Lesson 58
Data Backfill and Repair Job
Next Lesson
Lesson 60
Data Access Engineering Playbook