Part 045 — jOOQ Record Mapping and DTO

jOOQ membuat SQL type-safe, tetapi hasil query tetap harus diubah menjadi bentuk yang benar untuk aplikasi.

Mapping yang buruk membuat SQL-first layer rapuh:

• kolom salah alias;
• nullable field bocor;
• left join dianggap non-null;
• enum DB code salah mapping;
• BigDecimal uang dipakai tanpa currency;
• JSON payload jadi string liar;
• generated Record bocor sampai service/API;
• reflection mapping gagal diam-diam;
• DTO terlalu generik.

jOOQ yang production-grade membutuhkan mapping discipline sekuat query discipline.

Part ini membahas record mapping, DTO, converter, binding, value object mapping, dan type-safety dalam jOOQ.

1. Core Thesis

Mapping layer harus menjaga batas antara:

Database representation
jOOQ generated type
Data access row/projection
Domain object
API response

jOOQ Record adalah hasil SQL. Ia bukan otomatis domain model.

Guideline utama:

Use explicit mapping for critical paths.
Use generated Records inside data access boundary.
Map to DTO/read model/domain types before leaving boundary.
Model nullability and DB codes deliberately.

2. Mapping Surfaces in jOOQ

jOOQ menyediakan beberapa cara mapping:

• Record manual mapping;
• fetchInto(Class<T>);
• generated TableRecord;
• constructor mapping;
• RecordMapper;
• custom Converter;
• custom Binding;
• generated POJO/DAO support;
• JSON/XML mapping depending configuration/dialect;
• Kotlin/Java record mapping if configured.

Tidak semua cocok untuk production-critical query.

3. Manual Mapping

Manual mapping:

List<CaseDashboardRow> rows =
        dsl.select(
                CASE_FILE.ID,
                CASE_FILE.CASE_NUMBER,
                CASE_FILE.STATUS,
                OFFICER.DISPLAY_NAME,
                CASE_FILE.UPDATED_AT
        )
        .from(CASE_FILE)
        .leftJoin(OFFICER).on(OFFICER.ID.eq(CASE_FILE.ASSIGNED_OFFICER_ID))
        .where(CASE_FILE.TENANT_ID.eq(tenantId.value()))
        .fetch(record -> new CaseDashboardRow(
                new CaseFileId(record.get(CASE_FILE.ID)),
                record.get(CASE_FILE.CASE_NUMBER),
                CaseStatus.fromDbCode(record.get(CASE_FILE.STATUS)),
                Optional.ofNullable(record.get(OFFICER.DISPLAY_NAME)),
                record.get(CASE_FILE.UPDATED_AT).toInstant()
        ));

Pros:

• mapping explicit;
• nullability visible;
• domain conversion visible;
• no reflection magic;
• code review mudah.

Cons:

• verbose;
• perlu update saat kolom berubah.

Untuk critical query, verbosity ini biasanya worth it.

4. Typed RecordN

jOOQ can infer record arity:

Result<Record3<UUID, String, String>> result =
        dsl.select(CASE_FILE.ID, CASE_FILE.CASE_NUMBER, CASE_FILE.STATUS)
           .from(CASE_FILE)
           .fetch();

Mapping:

for (Record3<UUID, String, String> r : result) {
    UUID id = r.value1();
    String caseNumber = r.value2();
    String status = r.value3();
}

This is type-safe but positional.

For readability, field-based access is often better:

r.get(CASE_FILE.ID)

5. Field-Based Access

UUID id = record.get(CASE_FILE.ID);
String number = record.get(CASE_FILE.CASE_NUMBER);
String status = record.get(CASE_FILE.STATUS);

Benefits:

• less positional confusion;
• easier to review with selected fields;
• safer when projection grows;
• clear mapping from DB column.

For computed/aliased fields, keep Field<T> variable.

6. Alias Field Mapping

Field<Integer> activeAssignmentCount =
        DSL.count(CASE_ASSIGNMENT.ID).as("active_assignment_count");

var rows = dsl.select(
            CASE_FILE.ID,
            CASE_FILE.CASE_NUMBER,
            activeAssignmentCount
        )
        .from(CASE_FILE)
        .leftJoin(CASE_ASSIGNMENT)
            .on(CASE_ASSIGNMENT.CASE_ID.eq(CASE_FILE.ID))
        .groupBy(CASE_FILE.ID, CASE_FILE.CASE_NUMBER)
        .fetch(record -> new CaseAssignmentCountRow(
                new CaseFileId(record.get(CASE_FILE.ID)),
                record.get(CASE_FILE.CASE_NUMBER),
                record.get(activeAssignmentCount)
        ));

Do not use string alias if Field<T> can be preserved.

Bad:

record.get("active_assignment_count", Integer.class)

unless no better option.

7. DTO Projection Records

Java record works well for DTO:

public record CaseDashboardRow(
        CaseFileId caseId,
        String caseNumber,
        CaseStatus status,
        Priority priority,
        Optional<String> assignedOfficerName,
        Instant updatedAt
) {}

Keep DTO:

• immutable;
• use-case-specific;
• not generic;
• no behavior beyond derived formatting if needed;
• no persistence API types.

Avoid:

public record CaseDto(...)

used for dashboard, detail, export, admin, and mobile at once.

8. Generated TableRecord

Generated record:

CaseFileRecord record =
        dsl.selectFrom(CASE_FILE)
           .where(CASE_FILE.ID.eq(id.value()))
           .fetchOne();

Then:

record.getStatus();
record.setStatus("APPROVED");
record.store();

This can be useful for simple CRUD.

But in complex domain:

• generated record is persistence type;
• store() hides update semantics;
• update count/version/tenant checks may be unclear;
• domain behavior absent;
• generated type can leak across layers.

Use generated records inside data access, not as domain aggregate.

9. fetchInto

List<CaseDashboardPojo> rows =
        dsl.select(
                CASE_FILE.ID.as("caseId"),
                CASE_FILE.CASE_NUMBER.as("caseNumber"),
                CASE_FILE.STATUS.as("status")
        )
        .from(CASE_FILE)
        .fetchInto(CaseDashboardPojo.class);

Pros:

• concise;
• useful for simple POJOs;
• fast to prototype.

Cons:

• relies on naming conventions/reflection;
• alias mismatch runtime issue;
• conversion semantics less explicit;
• nullable handling hidden;
• value object mapping may need converter.

Use for simple internal DTOs. For critical paths, prefer explicit mapper.

10. Constructor Mapping

Depending jOOQ/version/config, constructor mapping can map by constructor parameters/names.

But Java parameter names may require compiler flags and config.

For production-critical code, explicit lambda mapping remains most reviewable:

.fetch(r -> new CaseDashboardRow(...))

Constructor mapping is convenient, but do not let convenience hide semantics.

11. RecordMapper Class

Reusable mapper:

public final class CaseDashboardRecordMapper implements RecordMapper<Record, CaseDashboardRow> {
    @Override
    public CaseDashboardRow map(Record record) {
        return new CaseDashboardRow(
                new CaseFileId(record.get(CASE_FILE.ID)),
                record.get(CASE_FILE.CASE_NUMBER),
                CaseStatus.fromDbCode(record.get(CASE_FILE.STATUS)),
                Priority.fromDbCode(record.get(CASE_FILE.PRIORITY)),
                Optional.ofNullable(record.get(OFFICER.DISPLAY_NAME)),
                record.get(CASE_FILE.UPDATED_AT).toInstant()
        );
    }
}

Use when same projection mapping is reused.

But avoid one mapper trying to map many different select shapes.

Mapper should match projection.

12. Projection-Specific Mapper

Good:

CaseDashboardRowMapper
CaseDetailHeaderMapper
CaseExportRowMapper
OutboxEventRowMapper

Bad:

CaseFileUniversalMapper

that accepts arbitrary record and maps optional fields depending presence.

Projection-specific mapping makes query contract clear.

13. Value Object Mapping

Database column:

case_file.id uuid

Domain ID:

public record CaseFileId(UUID value) {}

Manual mapping:

new CaseFileId(record.get(CASE_FILE.ID))

Update binding:

CASE_FILE.ID.eq(caseFileId.value())

This is explicit and simple.

If you want jOOQ generated code to expose CaseFileId directly, use converter/binding/codegen configuration. That is powerful but increases setup complexity.

14. Enum Mapping

DB code:

UNDER_REVIEW
APPROVED
REJECTED

Java enum:

public enum CaseStatus {
    UNDER_REVIEW("UNDER_REVIEW"),
    APPROVED("APPROVED"),
    REJECTED("REJECTED");

    private final String dbCode;

    public String dbCode() {
        return dbCode;
    }

    public static CaseStatus fromDbCode(String code) {
        return switch (code) {
            case "UNDER_REVIEW" -> UNDER_REVIEW;
            case "APPROVED" -> APPROVED;
            case "REJECTED" -> REJECTED;
            default -> throw new UnknownCaseStatus(code);
        };
    }
}

Mapping:

CaseStatus.fromDbCode(record.get(CASE_FILE.STATUS))

Insert/update:

.set(CASE_FILE.STATUS, status.dbCode())

This prevents ordinal/name coupling.

15. jOOQ Converter

A Converter<DB, USER> maps between DB type and user type.

Concept:

public final class CaseStatusConverter implements Converter<String, CaseStatus> {
    @Override
    public CaseStatus from(String databaseObject) {
        return databaseObject == null ? null : CaseStatus.fromDbCode(databaseObject);
    }

    @Override
    public String to(CaseStatus userObject) {
        return userObject == null ? null : userObject.dbCode();
    }

    @Override
    public Class<String> fromType() {
        return String.class;
    }

    @Override
    public Class<CaseStatus> toType() {
        return CaseStatus.class;
    }
}

Configured in code generation, CASE_FILE.STATUS can become TableField<..., CaseStatus>.

Pros:

• less repeated mapping;
• type-safe enum at DSL level.

Cons:

• generator/config complexity;
• converter must handle null carefully;
• DB code evolution needs tests.

16. Converter for Value Object

For ID value object:

public final class CaseFileIdConverter implements Converter<UUID, CaseFileId> {
    @Override
    public CaseFileId from(UUID databaseObject) {
        return databaseObject == null ? null : new CaseFileId(databaseObject);
    }

    @Override
    public UUID to(CaseFileId userObject) {
        return userObject == null ? null : userObject.value();
    }

    @Override
    public Class<UUID> fromType() {
        return UUID.class;
    }

    @Override
    public Class<CaseFileId> toType() {
        return CaseFileId.class;
    }
}

Then query can be:

CASE_FILE.ID.eq(caseFileId)

if generated field type uses converter.

This is elegant but may be too much for every ID type. Choose team convention.

17. Binding vs Converter

Converter changes Java type mapping.

Binding controls how a type is rendered/bound/read at SQL/JDBC level.

Use Binding for advanced cases:

• custom database type;
• JSONB;
• arrays;
• range types;
• encrypted columns;
• vendor-specific object;
• special SQL rendering/casting.

Most simple enum/value object cases only need Converter.

18. JSON Mapping

Outbox payload:

public record OutboxEventRow(
        UUID id,
        String eventType,
        String payloadJson
) {}

Simple approach: store JSON as string.

Better typed approach:

public record CaseApprovedPayload(...) {}

Mapping:

CaseApprovedPayload payload =
        objectMapper.readValue(record.get(OUTBOX_EVENT.PAYLOAD), CaseApprovedPayload.class);

Cautions:

• JSON schema/version;
• error handling;
• unknown fields;
• payload size;
• migration;
• event compatibility.

Do not let JSON become untyped dumping ground.

19. JSON Converter

You can create converter from JSON string/JSONB type to typed object.

Pros:

• centralizes serialization;
• typed field in record.

Cons:

• converter may need generic type handling;
• schema evolution hidden;
• expensive deserialization if always loaded;
• error handling at mapping layer.

For event/outbox, explicit payload deserialization by event type may be clearer.

20. Money Mapping

Database:

amount numeric(19,2)
currency char(3)

Value object:

public record Money(BigDecimal amount, Currency currency) {}

Mapping:

Money money = new Money(
        record.get(INVOICE.AMOUNT),
        Currency.getInstance(record.get(INVOICE.CURRENCY))
);

Avoid mapping only amount.

If amount and currency must move together, use projection/embeddable-like mapper.

21. Time Mapping

jOOQ generated type depends DB/dialect/config.

Common Java types:

• LocalDate for date;
• OffsetDateTime for timestamp with timezone;
• LocalDateTime for timestamp without timezone;
• Instant in domain.

Mapping:

Instant updatedAt = record.get(CASE_FILE.UPDATED_AT).toInstant();

Be consistent. Document timezone semantics.

Do not mix DB local timestamp and domain instant casually.

22. Nullability From Schema

Generated types may not fully prevent null at compile time.

Even if column is NOT NULL, left join can produce null.

Example:

OFFICER.DISPLAY_NAME // not null in officer table

but:

leftJoin(OFFICER)

means record.get(OFFICER.DISPLAY_NAME) may be null.

Mapping must follow query semantics, not only schema metadata.

23. Required Field Helper

public static <T> T required(Record record, Field<T> field) {
    T value = record.get(field);
    if (value == null) {
        throw new DataMappingException("Required field is null: " + field.getName());
    }
    return value;
}

Use for required projection fields.

This catches data/mapping bugs early.

24. Optional Field Helper

public static <T> Optional<T> optional(Record record, Field<T> field) {
    return Optional.ofNullable(record.get(field));
}

Use when left join/nullable column is intentional.

Avoid raw nullable leaking into domain where optional semantics matter.

25. Mapping Invariant Violation

If query expects one row but multiple returned:

Optional<CaseFileRow> row =
        dsl.select(...)
           .from(CASE_FILE)
           .where(...)
           .fetchOptional(this::mapRow);

If uniqueness not enforced, fetchOptional may throw too-many-rows style exception depending API.

Better ensure database unique constraint.

Cardinality is mapping contract.

26. fetchOne vs fetchOptional

Use:

fetchOptional()

for optional unique.

Use:

fetchOne()

when required by SQL but handle null/exception.

Repository should translate:

• not found;
• non-unique;
• data access error.

Do not let jOOQ cardinality exception leak everywhere.

27. Mapping Domain Aggregate

Rows:

CaseFileRow caseRow = caseDao.findById(...).orElseThrow();
List<CaseAssignmentRow> assignments = assignmentDao.findActiveByCaseId(...);

CaseFile aggregate = mapper.toDomain(caseRow, assignments);

jOOQ query maps to row DTO first, then domain mapper builds aggregate.

This keeps SQL/mapping separated from domain construction.

28. Row DTO vs Domain Object

Row DTO:

public record CaseFileRow(
        CaseFileId id,
        TenantId tenantId,
        CaseNumber caseNumber,
        CaseStatus status,
        long version,
        Instant updatedAt
) {}

Domain:

public final class CaseFile {
    public void approve(...) { ... }
}

Don't put business behavior in row DTO.

Don't put jOOQ Record in domain.

29. Mapping Hierarchical Result

If one query joins parent and child:

Result<Record> records = dsl.select(...)
        .from(CASE_FILE)
        .leftJoin(CASE_ASSIGNMENT).on(...)
        .where(CASE_FILE.ID.eq(id.value()))
        .fetch();

Need group:

Map<UUID, List<Record>> byCase =
        records.stream().collect(groupingBy(r -> r.get(CASE_FILE.ID)));

Then map parent once and child list.

Caution:

• duplicate parent rows;
• child null row from left join;
• multiple child collections cause cartesian explosion.

For complex detail, separate queries often clearer.

30. Mapping One-to-Many Safely

private CaseDetailView mapDetail(Result<Record> records) {
    if (records.isEmpty()) {
        throw new CaseNotFound(...);
    }

    Record first = records.get(0);

    CaseDetailHeader header = mapHeader(first);

    List<AssignmentView> assignments = records.stream()
            .filter(r -> r.get(CASE_ASSIGNMENT.ID) != null)
            .map(this::mapAssignment)
            .distinct()
            .toList();

    return new CaseDetailView(header, assignments);
}

But if there are multiple child sets, avoid one join.

31. Avoid Cartesian Mapping

This query:

case + assignments + documents + actions

creates multiplicative rows.

Mapping becomes complex and expensive.

Prefer:

Header header = headerQuery.get(id);
List<AssignmentView> assignments = assignmentQuery.active(id);
List<DocumentView> documents = documentQuery.list(id);
List<ActionView> actions = actionQuery.recent(id, 20);

jOOQ makes separate projections cheap and clear.

32. Auto-Mapping Risk With Joins

If two tables have same column names:

id
created_at
status

auto-mapping can be ambiguous unless aliases used.

Explicit aliases:

CASE_FILE.ID.as("caseId")
OFFICER.ID.as("officerId")

For manual mapping, field references disambiguate.

33. Aliasing Same Table Twice

var requester = USER.as("requester");
var approver = USER.as("approver");

dsl.select(
        CASE_FILE.ID,
        requester.DISPLAY_NAME.as("requester_name"),
        approver.DISPLAY_NAME.as("approver_name")
)
.from(CASE_FILE)
.leftJoin(requester).on(requester.ID.eq(CASE_FILE.REQUESTED_BY))
.leftJoin(approver).on(approver.ID.eq(CASE_FILE.APPROVED_BY))
.fetch(record -> new ApprovalRow(
        record.get(CASE_FILE.ID),
        record.get(requester.DISPLAY_NAME),
        record.get(approver.DISPLAY_NAME)
));

When using aliases, use fields from alias object.

34. Generated POJOs

jOOQ can generate POJOs corresponding to tables.

Useful for:

• simple transfer inside data access;
• codegen-driven CRUD;
• internal tools.

But table POJOs mirror schema. They are not API/domain objects by default.

For complex systems, prefer explicit row/domain/DTO types.

35. Generated DAOs

jOOQ can generate DAO classes for simple CRUD.

Good for:

• admin tools;
• reference tables;
• simple modules.

Limitations:

• generic CRUD semantics;
• less domain-specific contract;
• transaction/idempotency/error translation still needed;
• not ideal for complex aggregate mutation.

Use generated DAO selectively.

36. Mapping to Java Sealed Result

For command/update result:

public sealed interface ReserveCapacityResult {
    record Reserved(int newCount) implements ReserveCapacityResult {}
    record CapacityExceeded() implements ReserveCapacityResult {}
    record OfficerNotFound() implements ReserveCapacityResult {}
}

jOOQ query can return result based on affected rows/returning.

This is clearer than boolean.

37. Mapping Update Returning

Record2<UUID, Integer> updated =
        dsl.update(OFFICER_WORKLOAD)
           .set(OFFICER_WORKLOAD.ACTIVE_CASE_COUNT,
                   OFFICER_WORKLOAD.ACTIVE_CASE_COUNT.plus(1))
           .where(OFFICER_WORKLOAD.OFFICER_ID.eq(officerId.value()))
           .and(OFFICER_WORKLOAD.ACTIVE_CASE_COUNT.lt(OFFICER_WORKLOAD.MAX_ACTIVE_CASES))
           .returning(OFFICER_WORKLOAD.OFFICER_ID, OFFICER_WORKLOAD.ACTIVE_CASE_COUNT)
           .fetchOne();

if (updated == null) {
    return new CapacityExceeded();
}

return new Reserved(updated.get(OFFICER_WORKLOAD.ACTIVE_CASE_COUNT));

Dialect support matters.

38. Error Mapping vs Result Mapping

Expected branch:

• no work claimed;
• duplicate same idempotency key;
• old projection event ignored;
• capacity exceeded maybe.

Use result type.

Unexpected/data violation:

• unknown enum code;
• missing required column;
• unique violation where impossible;
• multiple rows for unique lookup.

Throw semantic/data exception.

39. Mapping Tests

Test mapping with real DB:

• not-null fields;
• nullable left join;
• enum code;
• unknown enum code if possible;
• timestamp conversion;
• money/currency;
• JSON payload;
• one-to-many grouping;
• alias correctness;
• generated converter behavior;
• fetchOptional cardinality.

Mapping tests catch drift.

40. Converter Tests

@Test
void caseStatusConverterRoundTrips() {
    CaseStatusConverter converter = new CaseStatusConverter();

    assertThat(converter.to(CaseStatus.UNDER_REVIEW)).isEqualTo("UNDER_REVIEW");
    assertThat(converter.from("UNDER_REVIEW")).isEqualTo(CaseStatus.UNDER_REVIEW);
}

Also test unknown DB code throws.

41. Integration Test for Left Join Null

@Test
void dashboardMapsUnassignedOfficerAsEmptyOptional() {
    fixture.caseWithoutOfficer(caseId);

    CaseDashboardRow row = dashboardQuery.get(caseId);

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

Prevents null pointer later.

42. Integration Test for Required Null

If required column unexpectedly null due migration/data corruption:

assertThatThrownBy(() -> dashboardQuery.get(corruptCaseId))
        .isInstanceOf(DataMappingException.class);

This is better than returning invalid domain object.

43. Mapping Review Checklist

• jOOQ Record does not leak outside data access boundary.
• DTO is use-case-specific.
• Critical mapping explicit.
• Value objects mapped deliberately.
• Enum DB codes mapped through converter/factory.
• Left join nullability handled.
• Required fields checked.
• Aliased fields stored as Field<T> where possible.
• Auto-mapping only used for simple tested DTO.
• Generated table records not used as domain aggregates.
• JSON mapping handles version/schema.
• Timezone conversion consistent.
• Money includes currency.
• Mapping tests exist.

44. Anti-Pattern: Returning Record From Service

This leaks persistence/query shape.

Map to DTO/domain.

45. Anti-Pattern: fetchInto for Complex Projection Without Tests

Alias/null/converter bugs appear at runtime.

46. Anti-Pattern: DB String Codes Everywhere

Map to enums/value objects near boundary.

47. Anti-Pattern: Treating Left Join Column as Non-Null

Left join can null any right-side field.

48. Anti-Pattern: Generated Record as Domain Model

Generated code is persistence representation.

49. Anti-Pattern: Universal Mapper

One mapper for all query shapes becomes full of conditionals and hidden assumptions.

50. Mini Lab

Design mapping for:

Case detail projection:
- case id;
- case number;
- status;
- priority;
- assigned officer name nullable;
- active assignment count;
- last action summary nullable;
- source version;
- updated at;
- SLA breach derived flag.

Tasks:

1. Define DTO record.
2. Define selected fields.
3. Define aliased computed fields.
4. Map status/priority enums.
5. Handle nullable officer/action.
6. Convert timestamp to Instant.
7. Decide if SLA breach computed in SQL or Java.
8. Add required field helper.
9. Add mapping tests.
10. Decide if converter/codegen should map status.

51. Summary

jOOQ mapping is where SQL correctness becomes application correctness.

You must master:

• manual record mapping;
• field-based access;
• alias field variables;
• DTO records;
• generated table records;
• fetchInto trade-offs;
• projection-specific mappers;
• value object mapping;
• enum converter;
• binding vs converter;
• JSON mapping;
• money/time mapping;
• nullability and left joins;
• required field checks;
• one-to-many grouping;
• generated POJOs/DAOs limits;
• result vs exception mapping;
• mapping tests and review checklist.

Part berikutnya membahas jOOQ Transactions and Batching: transaction, batch, bulk insert, generated keys, upsert, conflict handling, rollback testing, and performance trade-offs.

52. References

• jOOQ Fetching: https://www.jooq.org/doc/latest/manual/sql-execution/fetching/
• jOOQ RecordMapper: https://www.jooq.org/doc/latest/manual/sql-execution/fetching/recordmapper/
• jOOQ Converter: https://www.jooq.org/doc/latest/manual/sql-building/data-types/converted-data-types/
• jOOQ Binding: https://www.jooq.org/doc/latest/manual/sql-building/data-types/custom-bindings/
• jOOQ Code Generation: https://www.jooq.org/doc/latest/manual/code-generation/