Series/Learn Java Data Access Pattern In Action

Deepen PracticeOrdered learning track

jOOQ Query Building Patterns

Learn Java Data Access Pattern In Action - Part 044

jOOQ query building patterns untuk Java production: select, join, reusable condition, dynamic query, CTE, window function, keyset pagination, aggregation, exists, upsert, locking, plain SQL escape hatch, dan query reviewability.

[2026-07-05]9 min read1615 words

In This Lesson

1. Core Thesis 2. Basic Projection Query 3. Mapping Function

PrevNext

Lesson 4460 lesson track34–50 Deepen Practice

#java#data-access#jooq#query-building+6 more

Part 044 — jOOQ Query Building Patterns

jOOQ membuat SQL dapat dikomposisi di Java.

Tetapi composable query bisa menjadi dua hal:

sangat powerful dan reviewable;

atau spaghetti DSL yang sama buruknya dengan string SQL acak.

Kuncinya adalah pattern:

reusable condition;

typed query object;

whitelisted sort;

projection mapping;

CTE/window function secara terstruktur;

keyset pagination;

explicit join;

update count discipline;

query naming dan testing.

Part ini membahas pola membangun query jOOQ untuk production.

1. Core Thesis

jOOQ query building harus menjaga SQL tetap:

explicit,
typed,
composable,
bounded,
reviewable,
and measurable.

Jangan membuat abstraction yang menyembunyikan query shape.

Good jOOQ code reads like SQL with types.

Bad jOOQ code becomes generic query engine with too much indirection.

2. Basic Projection Query

public Slice<CaseDashboardRow> search(CaseDashboardQuery query) {
    List<CaseDashboardRow> rows =
            dsl.select(
                    CASE_FILE.ID,
                    CASE_FILE.CASE_NUMBER,
                    CASE_FILE.STATUS,
                    CASE_FILE.PRIORITY,
                    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(query.tenantId().value()))
            .and(CASE_FILE.STATUS.eq(query.status().dbCode()))
            .orderBy(CASE_FILE.UPDATED_AT.desc(), CASE_FILE.ID.desc())
            .limit(query.limitPlusOne())
            .fetch(this::mapDashboardRow);

    return Slice.fromLimitPlusOne(rows, query.limit());
}

Good properties:

selected columns explicit;
joins visible;
where clause typed;
order deterministic;
limit bounded;
mapping local.

3. Mapping Function

private CaseDashboardRow mapDashboardRow(Record6<UUID, String, String, String, String, OffsetDateTime> r) {
    return new CaseDashboardRow(
            new CaseFileId(r.get(CASE_FILE.ID)),
            r.get(CASE_FILE.CASE_NUMBER),
            CaseStatus.fromDbCode(r.get(CASE_FILE.STATUS)),
            Priority.fromDbCode(r.get(CASE_FILE.PRIORITY)),
            Optional.ofNullable(r.get(OFFICER.DISPLAY_NAME)),
            r.get(CASE_FILE.UPDATED_AT).toInstant()
    );
}

For readability, if record type becomes too verbose, use Record and named get(field) calls.

Explicit mapping is often better than auto-mapping for critical paths.

4. Reusable Conditions

Build small condition methods.

private Condition tenantCondition(TenantId tenantId) {
    return CASE_FILE.TENANT_ID.eq(tenantId.value());
}

private Condition visibleTo(UserScope scope) {
    Condition assignedToUser =
            CASE_FILE.ASSIGNED_OFFICER_ID.eq(scope.userId().value());

    Condition inUserUnits =
            CASE_FILE.ASSIGNED_UNIT_ID.in(
                    scope.unitIds().stream().map(UnitId::value).toList()
            );

    return assignedToUser.or(inUserUnits);
}

Usage:

.where(tenantCondition(query.tenantId()))
.and(visibleTo(query.scope()))

Keep condition functions domain-named and testable.

5. Optional Filters

List<Condition> conditions = new ArrayList<>();

conditions.add(tenantCondition(query.tenantId()));
conditions.add(visibleTo(query.scope()));

query.status().ifPresent(status ->
        conditions.add(CASE_FILE.STATUS.eq(status.dbCode()))
);

query.priority().ifPresent(priority ->
        conditions.add(CASE_FILE.PRIORITY.eq(priority.dbCode()))
);

query.assignedOfficerId().ifPresent(officerId ->
        conditions.add(CASE_FILE.ASSIGNED_OFFICER_ID.eq(officerId.value()))
);

Condition where = DSL.and(conditions);

Then:

.where(where)

This avoids OR-heavy (:param is null or field = :param) predicates.

6. Keyword Search

query.keyword().ifPresent(keyword ->
        conditions.add(CASE_FILE.CASE_NUMBER.likeIgnoreCase("%" + escapeLike(keyword.value()) + "%"))
);

Caution:

leading wildcard may not use normal index;
escape wildcard characters;
cap keyword length;
consider full-text/search index for large data;
do not search arbitrary columns casually.

For high-scale text search, read model/search engine may be better.

7. Escaping LIKE

Implement safe escaping for %, _, and escape char.

Concept:

private static String escapeLike(String input) {
    return input
            .replace("\\", "\\\\")
            .replace("%", "\\%")
            .replace("_", "\\_");
}

Then ensure generated SQL includes escape clause if needed, depending DSL/dialect support.

Do not let user wildcard become unbounded search behavior unless intended.

8. Sorting Whitelist

public enum CaseSort {
    UPDATED_DESC,
    UPDATED_ASC,
    PRIORITY_DESC,
    CASE_NUMBER_ASC;

    public List<SortField<?>> fields() {
        return switch (this) {
            case UPDATED_DESC -> List.of(CASE_FILE.UPDATED_AT.desc(), CASE_FILE.ID.desc());
            case UPDATED_ASC -> List.of(CASE_FILE.UPDATED_AT.asc(), CASE_FILE.ID.asc());
            case PRIORITY_DESC -> List.of(CASE_FILE.PRIORITY.desc(), CASE_FILE.UPDATED_AT.desc(), CASE_FILE.ID.desc());
            case CASE_NUMBER_ASC -> List.of(CASE_FILE.CASE_NUMBER.asc(), CASE_FILE.ID.asc());
        };
    }
}

Usage:

.orderBy(query.sort().fields())

Never build sort from raw request string.

9. Offset Pagination

.limit(query.page().limitPlusOne())
.offset(query.page().offset())

Use for shallow pages.

Always deterministic order:

.orderBy(CASE_FILE.UPDATED_AT.desc(), CASE_FILE.ID.desc())

For deep navigation, use keyset.

10. Keyset Pagination

Cursor:

public record CaseCursor(Instant updatedAt, UUID id) {}

Condition for descending order:

private Condition afterCursor(Optional<CaseCursor> cursor) {
    return cursor
            .map(c -> CASE_FILE.UPDATED_AT.lt(OffsetDateTime.ofInstant(c.updatedAt(), ZoneOffset.UTC))
                    .or(CASE_FILE.UPDATED_AT.eq(OffsetDateTime.ofInstant(c.updatedAt(), ZoneOffset.UTC))
                            .and(CASE_FILE.ID.lt(c.id()))))
            .orElse(DSL.trueCondition());
}

Query:

.where(baseCondition)
.and(afterCursor(query.after()))
.orderBy(CASE_FILE.UPDATED_AT.desc(), CASE_FILE.ID.desc())
.limit(query.limitPlusOne())

Cursor fields must match order fields.

11. Seek API

jOOQ also supports seek/keyset style APIs for certain query forms.

Concept:

.orderBy(CASE_FILE.UPDATED_AT.desc(), CASE_FILE.ID.desc())
.seek(cursor.updatedAt(), cursor.id())
.limit(limit)

Use if it matches your sorting/cursor model.

Manual condition is sometimes clearer, especially with dynamic sort.

12. Join Pattern

dsl.select(...)
   .from(CASE_FILE)
   .leftJoin(OFFICER)
      .on(OFFICER.ID.eq(CASE_FILE.ASSIGNED_OFFICER_ID))
   .leftJoin(UNIT)
      .on(UNIT.ID.eq(CASE_FILE.ASSIGNED_UNIT_ID))
   .where(...)

Guidelines:

join only needed tables;
prefer explicit join condition;
be careful with one-to-many join duplication;
use exists for filtering parent by child if no child columns needed.

13. Exists Predicate

Instead of joining child table and duplicating parent rows:

Condition hasTag(String tag) {
    return DSL.exists(
            DSL.selectOne()
               .from(CASE_TAG)
               .where(CASE_TAG.CASE_ID.eq(CASE_FILE.ID))
               .and(CASE_TAG.TAG.eq(tag))
    );
}

Usage:

conditions.add(hasTag("URGENT"));

exists is often better for parent filtering.

14. Aggregation Query

List<OfficerWorkloadRow> rows =
        dsl.select(
                CASE_ASSIGNMENT.OFFICER_ID,
                DSL.count().as("active_count")
        )
        .from(CASE_ASSIGNMENT)
        .where(CASE_ASSIGNMENT.ENDED_AT.isNull())
        .groupBy(CASE_ASSIGNMENT.OFFICER_ID)
        .fetch(r -> new OfficerWorkloadRow(
                new OfficerId(r.get(CASE_ASSIGNMENT.OFFICER_ID)),
                r.get("active_count", Integer.class)
        ));

For alias fields, store field variable:

Field<Integer> activeCount = DSL.count().as("active_count");

Then:

r.get(activeCount)

is safer.

15. Field Aliasing

Field<Integer> documentCount = DSL.count(DOCUMENT.ID).as("document_count");

var rows = dsl.select(
        CASE_FILE.ID,
        CASE_FILE.CASE_NUMBER,
        documentCount
)
.from(CASE_FILE)
.leftJoin(DOCUMENT).on(DOCUMENT.CASE_ID.eq(CASE_FILE.ID))
.groupBy(CASE_FILE.ID, CASE_FILE.CASE_NUMBER)
.fetch(r -> new CaseDocumentCountRow(
        new CaseFileId(r.get(CASE_FILE.ID)),
        r.get(CASE_FILE.CASE_NUMBER),
        r.get(documentCount)
));

Using Field variable avoids string alias mistakes.

16. Count Query

For page count:

int total = dsl.selectCount()
        .from(CASE_FILE)
        .where(where)
        .fetchOne(0, int.class);

But review cost.

For complex joins, prefer:

exists;
count distinct;
slice with limit+1;
read model count.

Do not automatically count every query.

17. CTE Pattern

CommonTableExpression<Record2<UUID, Integer>> activeAssignmentCount =
        DSL.name("active_assignment_count")
           .fields("case_id", "cnt")
           .as(
               DSL.select(
                       CASE_ASSIGNMENT.CASE_ID,
                       DSL.count().as("cnt")
               )
               .from(CASE_ASSIGNMENT)
               .where(CASE_ASSIGNMENT.ENDED_AT.isNull())
               .groupBy(CASE_ASSIGNMENT.CASE_ID)
           );

var aac = DSL.table(DSL.name("active_assignment_count"));
Field<UUID> aacCaseId = DSL.field(DSL.name("active_assignment_count", "case_id"), UUID.class);
Field<Integer> aacCnt = DSL.field(DSL.name("active_assignment_count", "cnt"), Integer.class);

List<CaseDashboardRow> rows =
        dsl.with(activeAssignmentCount)
           .select(
                   CASE_FILE.ID,
                   CASE_FILE.CASE_NUMBER,
                   aacCnt
           )
           .from(CASE_FILE)
           .leftJoin(aac).on(aacCaseId.eq(CASE_FILE.ID))
           .fetch(...);

CTE can organize complex queries. But avoid CTE overuse if simple join/subquery is clearer.

Generated code or aliases can reduce verbosity.

18. Window Function Pattern

Example rank cases per officer:

Field<Integer> officerRank =
        DSL.rowNumber()
           .over(partitionBy(CASE_FILE.ASSIGNED_OFFICER_ID)
           .orderBy(CASE_FILE.PRIORITY.desc(), CASE_FILE.UPDATED_AT.asc()))
           .as("officer_rank");

Query:

dsl.select(
        CASE_FILE.ID,
        CASE_FILE.ASSIGNED_OFFICER_ID,
        officerRank
)
.from(CASE_FILE)
.where(CASE_FILE.STATUS.eq("OPEN"))
.fetch();

Window functions are a reason to use jOOQ/native SQL instead of contorting JPQL.

19. Derived Table Pattern

Table<?> ranked = dsl.select(
            CASE_FILE.ID.as("case_id"),
            CASE_FILE.ASSIGNED_OFFICER_ID.as("officer_id"),
            officerRank
        )
        .from(CASE_FILE)
        .where(CASE_FILE.STATUS.eq("OPEN"))
        .asTable("ranked");

Field<UUID> rankedCaseId = ranked.field("case_id", UUID.class);
Field<Integer> rankedRank = ranked.field("officer_rank", Integer.class);

dsl.select(rankedCaseId)
   .from(ranked)
   .where(rankedRank.le(10))
   .fetch();

Derived tables are powerful but can become verbose. Encapsulate carefully.

20. Upsert Pattern

For PostgreSQL-like ON CONFLICT if supported:

dsl.insertInto(INBOX_MESSAGE)
   .set(INBOX_MESSAGE.MESSAGE_ID, messageId)
   .set(INBOX_MESSAGE.STATUS, "PROCESSING")
   .set(INBOX_MESSAGE.RECEIVED_AT, now)
   .onConflict(INBOX_MESSAGE.MESSAGE_ID)
   .doNothing()
   .execute();

If result count 1: inserted. If 0: duplicate.

This is excellent for idempotency.

Dialect support varies.

21. Idempotent Inbox Start

public InboxStartResult tryStart(MessageId messageId, String payloadHash, Instant now) {
    int inserted = dsl.insertInto(INBOX_MESSAGE)
            .set(INBOX_MESSAGE.MESSAGE_ID, messageId.value())
            .set(INBOX_MESSAGE.PAYLOAD_HASH, payloadHash)
            .set(INBOX_MESSAGE.STATUS, "PROCESSING")
            .set(INBOX_MESSAGE.RECEIVED_AT, now)
            .onConflict(INBOX_MESSAGE.MESSAGE_ID)
            .doNothing()
            .execute();

    if (inserted == 1) {
        return InboxStartResult.started();
    }

    String existingHash = dsl.select(INBOX_MESSAGE.PAYLOAD_HASH)
            .from(INBOX_MESSAGE)
            .where(INBOX_MESSAGE.MESSAGE_ID.eq(messageId.value()))
            .fetchOne(INBOX_MESSAGE.PAYLOAD_HASH);

    if (!Objects.equals(existingHash, payloadHash)) {
        throw new IdempotencyKeyConflict(messageId);
    }

    return InboxStartResult.duplicate();
}

Semantics are explicit.

22. Conditional Update Pattern

public void transitionStatus(
        CaseFileId id,
        CaseStatus expected,
        CaseStatus next,
        long expectedVersion,
        Instant now
) {
    int updated = dsl.update(CASE_FILE)
            .set(CASE_FILE.STATUS, next.dbCode())
            .set(CASE_FILE.VERSION, CASE_FILE.VERSION.plus(1))
            .set(CASE_FILE.UPDATED_AT, now)
            .where(CASE_FILE.ID.eq(id.value()))
            .and(CASE_FILE.STATUS.eq(expected.dbCode()))
            .and(CASE_FILE.VERSION.eq(expectedVersion))
            .execute();

    if (updated == 0) {
        throw new CaseTransitionConflict(id);
    }
}

This pattern is race-safe and easy to review.

23. Atomic Counter Update

int 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))
        .execute();

if (updated == 0) {
    throw new OfficerCapacityExceeded(officerId);
}

No pre-check race.

24. Claim Work With Skip Locked

Dialect-specific concept:

List<OutboxEventRow> events =
        dsl.selectFrom(OUTBOX_EVENT)
           .where(OUTBOX_EVENT.PUBLISHED_AT.isNull())
           .orderBy(OUTBOX_EVENT.CREATED_AT.asc(), OUTBOX_EVENT.ID.asc())
           .limit(limit)
           .forUpdate()
           .skipLocked()
           .fetch(this::mapOutbox);

Then mark claimed in same transaction, or use update-returning pattern if supported.

Check generated SQL for target DB.

25. Update Returning Claim Pattern

On supporting dialects:

Result<OutboxEventRecord> claimed =
        dsl.update(OUTBOX_EVENT)
           .set(OUTBOX_EVENT.CLAIMED_BY, workerId)
           .set(OUTBOX_EVENT.CLAIMED_AT, now)
           .where(OUTBOX_EVENT.ID.in(
                   dsl.select(OUTBOX_EVENT.ID)
                      .from(OUTBOX_EVENT)
                      .where(OUTBOX_EVENT.PUBLISHED_AT.isNull())
                      .and(OUTBOX_EVENT.CLAIMED_AT.isNull()
                           .or(OUTBOX_EVENT.CLAIMED_AT.lt(staleBefore)))
                      .orderBy(OUTBOX_EVENT.CREATED_AT.asc(), OUTBOX_EVENT.ID.asc())
                      .limit(limit)
           ))
           .returning()
           .fetch();

Some DBs require different syntax for update + limit + skip locked.

jOOQ helps but dialect still matters.

26. Plain SQL for Vendor-Specific Claim

If DSL becomes contorted, plain SQL may be clearer:

Result<Record> result = dsl.fetch("""
    /* query=OutboxDao.claimNextBatch */
    update outbox_event
    set claimed_by = ?, claimed_at = ?
    where id in (
        select id
        from outbox_event
        where published_at is null
          and (claimed_at is null or claimed_at < ?)
        order by created_at, id
        for update skip locked
        limit ?
    )
    returning *
    """, workerId, now, staleBefore, limit);

Rules:

bind parameters;
isolate method;
test on target DB;
document dialect.

SQL-first means using the best expression of SQL.

27. Reusable Select Field Lists

For consistency:

private static final List<SelectFieldOrAsterisk> CASE_DASHBOARD_FIELDS = List.of(
        CASE_FILE.ID,
        CASE_FILE.CASE_NUMBER,
        CASE_FILE.STATUS,
        CASE_FILE.PRIORITY,
        CASE_FILE.UPDATED_AT
);

But avoid over-abstracting. Sometimes explicit inline fields are more readable.

Use constants for truly reused projection field sets.

28. Query Object Integration

public CursorSlice<CaseDashboardRow, CaseCursor> search(CaseDashboardQuery query) {
    Condition condition = DSL.and(
            tenantCondition(query.tenantId()),
            visibleTo(query.scope()),
            statusCondition(query.status()),
            priorityCondition(query.priority()),
            afterCursor(query.cursor())
    );

    List<CaseDashboardRow> rows = dsl.select(...)
            .from(...)
            .where(condition)
            .orderBy(query.sort().fields())
            .limit(query.limitPlusOne())
            .fetch(this::mapDashboardRow);

    return CursorSlice.from(rows, query.limit(), this::cursorOf);
}

Query object validates inputs before data access.

29. Condition Helper Should Not Hide Everything

Good:

visibleTo(scope)

Bad:

applyAllBusinessRulesAndSecurityAndFilters(query)

If helper hides too much, query review becomes hard.

Keep high-level structure visible.

30. Dynamic Table/Column Caution

Avoid user-driven table/column selection.

If admin/report needs configurable fields, create whitelist mapping:

enum ExportColumn {
    CASE_NUMBER(CASE_FILE.CASE_NUMBER),
    STATUS(CASE_FILE.STATUS),
    UPDATED_AT(CASE_FILE.UPDATED_AT);

    final Field<?> field;
}

Never accept raw column names from request.

31. JSON Field Query

jOOQ can express JSON functions depending dialect.

Use for outbox payload/search sparingly.

Cautions:

indexing JSON paths;
schema validation;
migration;
portability;
type conversion.

For core queryable fields, prefer relational columns.

32. Array/List Parameter

For IN:

CASE_FILE.ID.in(ids)

Handle empty list:

if (ids.isEmpty()) {
    return List.of();
}

Cap size.

For large ID set:

temp table;
staging table;
join;
batch chunks.

33. Multi-Row Insert

InsertValuesStepN<?> insert = dsl.insertInto(
        CASE_AUDIT,
        CASE_AUDIT.ID,
        CASE_AUDIT.CASE_ID,
        CASE_AUDIT.ACTION,
        CASE_AUDIT.CREATED_AT
);

for (AuditRow row : rows) {
    insert.values(row.id(), row.caseId(), row.action(), row.createdAt());
}

insert.execute();

For many rows, batch may be better depending dialect/size.

Avoid building huge single SQL beyond limits.

34. Batch Bind Pattern

BatchBindStep batch = dsl.batch(
        dsl.insertInto(CASE_AUDIT)
           .columns(CASE_AUDIT.ID, CASE_AUDIT.CASE_ID, CASE_AUDIT.ACTION, CASE_AUDIT.CREATED_AT)
           .values((UUID) null, null, null, null)
);

for (AuditRow row : rows) {
    batch.bind(row.id(), row.caseId(), row.action(), row.createdAt());
}

int[] counts = batch.execute();

Check counts and partial failure behavior.

35. Transaction Pattern

dsl.transaction(configuration -> {
    DSLContext tx = DSL.using(configuration);

    int updated = tx.update(CASE_FILE)
            .set(...)
            .where(...)
            .execute();

    if (updated != 1) {
        throw new OptimisticConflict(...);
    }

    tx.insertInto(CASE_AUDIT).set(...).execute();
    tx.insertInto(OUTBOX_EVENT).set(...).execute();
});

In Spring, prefer @Transactional if that is team convention.

Do not nest transaction models accidentally.

36. Common Table Aliases

For self-join:

var parent = CASE_FILE.as("parent");
var child = CASE_FILE.as("child");

dsl.select(parent.ID, child.ID)
   .from(parent)
   .join(child).on(child.PARENT_CASE_ID.eq(parent.ID))
   .fetch();

Use aliases clearly.

Avoid cryptic aliases in complex query.

37. Left Join Null Mapping

.leftJoin(OFFICER).on(...)

Fields from OFFICER can be null.

Even if OFFICER.DISPLAY_NAME is not null in table, left join makes it nullable.

Mapping:

Optional.ofNullable(record.get(OFFICER.DISPLAY_NAME))

Do not assume generated column nullability handles join nullability.

38. Distinct

Use distinct only when needed.

If join duplicates parent rows, ask:

should query use exists instead?
should aggregation/grouping be used?
is relationship cardinality wrong?
is DTO supposed to include child row?

distinct can hide modeling issue and add sort/hash cost.

39. Query Timeout

jOOQ can set query timeout depending API/config.

At minimum, use datasource/statement timeout or transaction timeout.

For interactive queries, set bounded execution.

Timeout is protection, not optimization.

40. Query Metrics Wrapper

Wrap critical query:

return metrics.time("CaseDashboardQuery.search", () ->
        dsl.select(...)
           .fetch(this::map)
);

Tags:

query name;
success/failure;
row count maybe;
not user/tenant IDs as high-cardinality tags.

41. Rendered SQL for Debugging

jOOQ can render SQL with placeholders for debugging depending config/API.

Use in development/test to inspect generated SQL.

In production, avoid logging sensitive bind values.

For critical query, include generated SQL sample in code review/design doc.

42. Testing jOOQ Query

Integration test:

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

    CaseDashboardQuery query = new CaseDashboardQuery(tenantA, Optional.of(OPEN), ...);

    var result = dashboardQuery.search(query);

    assertThat(result.items()).hasSize(1);
}

Use real database and migrations.

43. Testing Sort Stability

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

    CaseId a = fixture.caseUpdatedAt(same);
    CaseId b = fixture.caseUpdatedAt(same);

    var rows = dashboardQuery.search(sort(UPDATED_DESC));

    assertThat(rows.items()).extracting(CaseDashboardRow::id)
            .containsExactly(b, a); // according to ID desc fixture
}

Stable order prevents pagination duplicates/skips.

44. Testing Conditional Update Conflict

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

    assertThatThrownBy(() ->
            caseDao.approve(caseId, expectedVersion(7), now)
    ).isInstanceOf(OptimisticConflict.class);

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

Check row unchanged.

45. Testing Upsert Idempotency

@Test
void duplicateInboxMessageReturnsDuplicate() {
    InboxStartResult first = inboxDao.tryStart(messageId, hash, now);
    InboxStartResult second = inboxDao.tryStart(messageId, hash, now);

    assertThat(first).isEqualTo(STARTED);
    assertThat(second).isEqualTo(DUPLICATE);
}

Also test same key different hash conflict.

46. Query Review Checklist

47. Anti-Pattern: Generic jOOQ Query Builder for Everything

A universal builder can hide query shape and bypass security/index constraints.

Use typed query objects and specific query components.

48. Anti-Pattern: Raw Sort/Column from Request

Always whitelist.

49. Anti-Pattern: `distinct` as Default Fix

Understand duplication cause.

50. Anti-Pattern: Huge `IN` List

Cap and use staging/temp table/chunks.

51. Anti-Pattern: CTE for Every Subquery

CTE can improve clarity but can also add verbosity or optimization effects depending DB.

Use deliberately.

52. Anti-Pattern: Plain SQL Without Tests

If using raw SQL escape hatch, test on target DB.

53. Mini Lab

Build jOOQ query for:

Supervisor dashboard:
- tenant scoped;
- visible to supervisor units;
- optional status;
- optional priority;
- optional keyword on case number;
- active assignment count;
- assigned officer name;
- order by updated desc or priority desc;
- keyset cursor;
- page limit max 100.

Tasks:

Define query object.
Define reusable conditions.
Define sort enum.
Decide join vs exists.
Decide aggregation vs read model for assignment count.
Define cursor condition.
Map left join officer name.
Define indexes.
Add integration tests.
Add query count/performance budget.

54. Summary

jOOQ query building is powerful when disciplined.

You must master:

projection query;
explicit mapping;
reusable conditions;
optional filters;
sort whitelist;
offset/keyset pagination;
joins;
exists;
aggregation;
alias fields;
CTE;
derived table;
window functions;
upsert;
conditional update;
atomic counter;
skip locked claim;
plain SQL escape hatch;
batch bind;
transaction pattern;
null mapping;
testing;
query review checklist.

Part berikutnya membahas jOOQ Record Mapping and DTO: record mapping, POJO mapping, constructor mapping, converter, binding, type safety, and how to keep mapping layer robust.

55. References

jOOQ SQL Building: https://www.jooq.org/doc/latest/manual/sql-building/
jOOQ SELECT Statement: https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/
jOOQ Dynamic SQL: https://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql/
jOOQ Transactions: https://www.jooq.org/doc/latest/manual/sql-execution/transaction-management/
jOOQ Batch Execution: https://www.jooq.org/doc/latest/manual/sql-execution/batch-execution/

Lesson Recap

You just completed lesson 44 in deepen practice. 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 Series Next 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 43

jOOQ SQL-First Mental Model

Next Lesson

Lesson 45

jOOQ Record Mapping and DTO

jOOQ Query Building Patterns

Part 044 — jOOQ Query Building Patterns

1. Core Thesis

2. Basic Projection Query

3. Mapping Function

4. Reusable Conditions

5. Optional Filters

6. Keyword Search

7. Escaping LIKE

8. Sorting Whitelist

9. Offset Pagination

10. Keyset Pagination

11. Seek API

12. Join Pattern

13. Exists Predicate

14. Aggregation Query

15. Field Aliasing

16. Count Query

17. CTE Pattern

18. Window Function Pattern

19. Derived Table Pattern

20. Upsert Pattern

21. Idempotent Inbox Start

22. Conditional Update Pattern

23. Atomic Counter Update

24. Claim Work With Skip Locked

25. Update Returning Claim Pattern

26. Plain SQL for Vendor-Specific Claim

27. Reusable Select Field Lists

28. Query Object Integration

29. Condition Helper Should Not Hide Everything

30. Dynamic Table/Column Caution

31. JSON Field Query

32. Array/List Parameter

33. Multi-Row Insert

34. Batch Bind Pattern

35. Transaction Pattern

36. Common Table Aliases

37. Left Join Null Mapping

38. Distinct

39. Query Timeout

40. Query Metrics Wrapper

41. Rendered SQL for Debugging

42. Testing jOOQ Query

43. Testing Sort Stability

44. Testing Conditional Update Conflict

45. Testing Upsert Idempotency

46. Query Review Checklist

47. Anti-Pattern: Generic jOOQ Query Builder for Everything

48. Anti-Pattern: Raw Sort/Column from Request

49. Anti-Pattern: distinct as Default Fix

50. Anti-Pattern: Huge IN List

51. Anti-Pattern: CTE for Every Subquery

52. Anti-Pattern: Plain SQL Without Tests

53. Mini Lab

54. Summary

55. References

49. Anti-Pattern: `distinct` as Default Fix

50. Anti-Pattern: Huge `IN` List