Part 043 — jOOQ SQL-First Mental Model

jOOQ adalah pendekatan SQL-first untuk Java.

Jika ORM mencoba membuat database terasa seperti object graph, jOOQ melakukan hal sebaliknya:

Database schema adalah source of truth.
SQL tetap SQL.
Java mendapat type-safety, composability, dan tooling.

jOOQ tidak menyembunyikan relational model. Ia membuat relational model lebih aman dan lebih nyaman digunakan dari Java.

Part ini membahas mental model jOOQ untuk production-grade data access.

1. Core Thesis

jOOQ cocok ketika kamu ingin:

• SQL tetap eksplisit;
• query shape mudah direview;
• schema database menjadi kontrak;
• type-safety untuk table/column;
• dynamic SQL tanpa string chaos;
• akses fitur SQL modern;
• DTO/projection mapping yang jelas;
• kontrol transaction dan batching;
• minim hidden lazy loading;
• minim dirty checking surprise.

jOOQ bukan ORM object graph. jOOQ adalah SQL DSL dan code generator.

Mental model:

SQL-first + generated schema + type-safe DSL + explicit mapping

2. jOOQ vs JPA/Hibernate

jOOQ is often excellent for read/query service, reporting, bulk operations, and SQL-heavy domains.

3. SQL-First Does Not Mean No Domain

A SQL-first data layer can still have rich domain model.

Command flow:

@Transactional
public ApproveCaseResult approve(ApproveCaseCommand command) {
    CaseFile caseFile = caseRepository.loadForApproval(command.caseId())
            .orElseThrow();

    caseFile.approve(command.actorId(), command.reason());

    caseRepository.save(caseFile);
    auditRepository.append(...);
    outboxRepository.append(...);

    return ApproveCaseResult.from(caseFile);
}

Repository implementation may use jOOQ:

dsl.update(CASE_FILE)
   .set(CASE_FILE.STATUS, caseFile.status().dbCode())
   .set(CASE_FILE.VERSION, CASE_FILE.VERSION.plus(1))
   .where(CASE_FILE.ID.eq(caseFile.id().value()))
   .and(CASE_FILE.VERSION.eq(caseFile.version()))
   .execute();

jOOQ is persistence mechanism, not replacement for domain design.

4. Database-First Workflow

Typical jOOQ workflow:

1. Write database migration.
2. Apply migration to schema used for codegen.
3. jOOQ code generator reads schema.
4. Generated Java classes represent tables/columns/records.
5. Application query uses generated DSL.
6. Compile fails if column/table removed or type changed.

This aligns well with production systems where database schema is explicit and migrated.

5. Generated Schema

Generated classes conceptually look like:

import static com.example.jooq.tables.CaseFile.CASE_FILE;

CASE_FILE.ID
CASE_FILE.TENANT_ID
CASE_FILE.CASE_NUMBER
CASE_FILE.STATUS
CASE_FILE.VERSION

Query:

dsl.select(
        CASE_FILE.ID,
        CASE_FILE.CASE_NUMBER,
        CASE_FILE.STATUS
   )
   .from(CASE_FILE)
   .where(CASE_FILE.TENANT_ID.eq(tenantId.value()))
   .fetch();

If column CASE_NUMBER is renamed, compile fails.

This is a major advantage over string SQL.

6. DSLContext

DSLContext is the main entry point.

public final class JooqCaseDashboardQuery {
    private final DSLContext dsl;

    public JooqCaseDashboardQuery(DSLContext dsl) {
        this.dsl = dsl;
    }
}

It provides:

• query building;
• execution;
• transaction APIs;
• batch APIs;
• configuration/dialect;
• mapping utilities.

In Spring/Jakarta apps, DSLContext should participate in the same transaction/connection as other data access if configured correctly.

7. Basic Select

List<CaseDashboardRow> rows =
        dsl.select(
                CASE_FILE.ID,
                CASE_FILE.CASE_NUMBER,
                CASE_FILE.STATUS,
                CASE_FILE.UPDATED_AT
        )
        .from(CASE_FILE)
        .where(CASE_FILE.TENANT_ID.eq(tenantId.value()))
        .and(CASE_FILE.STATUS.eq("OPEN"))
        .orderBy(CASE_FILE.UPDATED_AT.desc(), CASE_FILE.ID.desc())
        .limit(50)
        .fetch(record -> new CaseDashboardRow(
                new CaseFileId(record.get(CASE_FILE.ID)),
                record.get(CASE_FILE.CASE_NUMBER),
                CaseStatus.fromDbCode(record.get(CASE_FILE.STATUS)),
                record.get(CASE_FILE.UPDATED_AT)
        ));

The SQL shape is visible in Java.

8. jOOQ Records

jOOQ can return Record types.

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

For application code, map to DTO/domain:

record -> new CaseDashboardRow(...)

Avoid leaking jOOQ Record outside data access boundary unless internal module deliberately uses it.

9. TableRecord vs DTO

Generated table record:

CaseFileRecord record = dsl.fetchOne(CASE_FILE, CASE_FILE.ID.eq(id));

Can be useful for simple CRUD, but beware:

• can resemble Active Record if misused;
• persistence-specific type can leak;
• domain behavior absent;
• update/store semantics need discipline.

For complex domain, map record to domain object or DTO.

10. Query Ownership

jOOQ queries should live in data access components:

infrastructure/jooq/
  JooqCaseFileRepository
  JooqCaseDashboardQuery
  JooqOutboxDao

Avoid scattering DSL queries across controllers/services.

SQL-first does not mean SQL-everywhere.

Keep:

• query naming;
• mapping;
• transaction contract;
• error translation;
• metrics;

inside data access layer.

11. jOOQ and DAO Pattern

DAO method:

public Optional<CaseFileRow> findById(TenantId tenantId, CaseFileId id) {
    return dsl.select(
                CASE_FILE.ID,
                CASE_FILE.TENANT_ID,
                CASE_FILE.CASE_NUMBER,
                CASE_FILE.STATUS,
                CASE_FILE.VERSION
            )
            .from(CASE_FILE)
            .where(CASE_FILE.TENANT_ID.eq(tenantId.value()))
            .and(CASE_FILE.ID.eq(id.value()))
            .fetchOptional(this::mapRow);
}

This is explicit SQL wrapped in a precise contract.

12. jOOQ and Repository Pattern

Repository can compose jOOQ queries.

public Optional<CaseFile> loadForApproval(CaseFileId id) {
    Optional<CaseFileRow> row = caseFileDao.findById(currentTenant.required(), id);

    if (row.isEmpty()) {
        return Optional.empty();
    }

    List<CaseActionRow> actions =
            actionDao.findRelevantForApproval(currentTenant.required(), id);

    return Optional.of(mapper.toDomainForApproval(row.get(), actions));
}

jOOQ does not force repository-less architecture.

13. No Hidden Lazy Loading

jOOQ does not lazy-load associations automatically.

If query selects officer name, SQL includes join.

If query does not select officer name, no hidden query later.

This makes performance more predictable.

But it also means you must design query shape intentionally.

14. No Dirty Checking by Default

With jOOQ, changing a Java object does not auto-update DB.

CaseFile caseFile = repository.load(...);
caseFile.approve(...);

repository.save(caseFile);

save must execute SQL.

Pros:

• writes visible;
• no accidental update in read path;
• no flush surprise.

Cons:

• more explicit persistence code;
• no automatic unit of work;
• update counts must be checked manually.

15. Conditional Update Is Natural

jOOQ makes conditional update explicit.

int updated = dsl.update(CASE_FILE)
        .set(CASE_FILE.STATUS, "APPROVED")
        .set(CASE_FILE.APPROVED_AT, now)
        .set(CASE_FILE.VERSION, CASE_FILE.VERSION.plus(1))
        .where(CASE_FILE.TENANT_ID.eq(tenantId.value()))
        .and(CASE_FILE.ID.eq(caseId.value()))
        .and(CASE_FILE.STATUS.eq("UNDER_REVIEW"))
        .and(CASE_FILE.VERSION.eq(expectedVersion))
        .execute();

if (updated == 0) {
    throw new OptimisticConflict(caseId, expectedVersion);
}

This is excellent for atomic state transitions.

16. Returning Updated Rows

On databases that support RETURNING, jOOQ can express returning.

Concept:

CaseFileRecord record =
        dsl.update(CASE_FILE)
           .set(CASE_FILE.STATUS, "APPROVED")
           .set(CASE_FILE.VERSION, CASE_FILE.VERSION.plus(1))
           .where(CASE_FILE.ID.eq(caseId.value()))
           .and(CASE_FILE.VERSION.eq(expectedVersion))
           .returning(CASE_FILE.ID, CASE_FILE.STATUS, CASE_FILE.VERSION)
           .fetchOne();

If unsupported, jOOQ may emulate or not depending dialect.

Returning is useful for:

• new version;
• generated values;
• update result;
• avoiding second select.

Always know dialect behavior.

17. Dialect Awareness

jOOQ is dialect-aware. It can generate SQL for configured database.

This is powerful but does not mean all SQL features are portable.

Examples:

• RETURNING;
• ON CONFLICT;
• MERGE;
• SKIP LOCKED;
• window functions;
• JSON functions;
• array operators.

If you depend on vendor feature, document it.

SQL-first engineering accepts that database dialect matters.

18. jOOQ and Migrations

jOOQ works best when schema migration is disciplined.

Workflow:

migration -> codegen -> compile -> tests

If migration removes column but code still uses it, compile fails after regeneration.

This catches errors earlier than runtime string SQL.

Use Flyway/Liquibase/other migration tool as source for codegen schema.

19. Generated Code in Build

Common strategy:

• generate jOOQ classes during build;
• commit generated sources or generate in CI;
• use migration-applied test database for codegen;
• keep generator config versioned.

Trade-offs:

Commit generated code

Pros:

• IDE works without DB;
• diff shows schema API change.

Cons:

• generated code noise.

Generate in build

Pros:

• source clean.

Cons:

• build requires DB/codegen setup;
• CI complexity.

Choose team workflow.

20. Type Safety Limits

jOOQ type-safety catches:

• table/column names;
• basic Java types;
• many SQL construction errors.

It does not automatically catch:

• wrong business predicate;
• missing tenant filter;
• wrong join cardinality;
• missing index;
• bad transaction boundary;
• stale read model;
• incorrect mapping semantic;
• SQL plan regression.

You still need review/tests.

21. Mapping Discipline

jOOQ mapping can be concise:

.fetchInto(CaseDashboardRow.class)

But reflection-based auto-mapping can hide mismatch.

For critical code, explicit mapping is safer:

.fetch(record -> new CaseDashboardRow(
        new CaseFileId(record.get(CASE_FILE.ID)),
        record.get(CASE_FILE.CASE_NUMBER),
        CaseStatus.fromDbCode(record.get(CASE_FILE.STATUS)),
        record.get(CASE_FILE.UPDATED_AT)
));

Use auto-mapping for simple internal DTOs if tested.

22. Null Semantics

Generated column type may be nullable at runtime even if Java type does not fully express nullability depending configuration.

Be explicit in mapping:

String officerName = record.get(OFFICER.DISPLAY_NAME); // nullable due left join
Optional<String> name = Optional.ofNullable(officerName);

Left join creates nulls even for non-null target column.

Mapping must reflect query shape.

23. jOOQ and Transactions

jOOQ has transaction API:

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

    tx.update(...).execute();
    tx.insertInto(...).execute();
});

In Spring, you often use @Transactional and injected DSLContext participates in transaction if configured.

Choose one transaction model consistently.

Do not accidentally create separate connection/transaction for jOOQ inside a larger use case.

24. Spring Transaction Integration

Typical:

@Transactional
public void approve(...) {
    caseDao.update(...);   // jOOQ uses tx-bound connection
    auditDao.insert(...);
    outboxDao.insert(...);
}

Requirement:

• jOOQ configured with transaction-aware datasource/connection provider;
• same transaction manager as other JDBC/JPA if mixing;
• tests prove rollback across jOOQ operations.

25. Mixing jOOQ and JPA

Possible but requires discipline.

Risks:

• JPA persistence context stale after jOOQ update;
• JPA flush before jOOQ query;
• two transaction managers misconfigured;
• cache inconsistency.

If mixing in same transaction:

• flush JPA before jOOQ reads if needed;
• clear/refresh JPA after jOOQ writes if entity already managed;
• keep boundaries clear.

Often better:

Use jOOQ for query/read/bulk.
Use JPA for aggregate entity command.
Avoid modifying same tables with both in same transaction unless necessary.

26. jOOQ and Error Translation

jOOQ throws data access exceptions wrapping SQL errors.

Application should still translate:

• unique constraint -> duplicate semantic error;
• FK violation -> invalid reference or data bug;
• deadlock/serialization -> retryable transaction failure;
• timeout -> data access timeout;
• mapping error -> invariant bug.

Constraint names still matter.

27. Update Count Discipline

jOOQ .execute() returns affected row count.

Always use it.

int updated = dsl.update(...).execute();

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

For idempotent cleanup, 0 may be valid. Document contract.

28. Insert and Constraint Handling

try {
    dsl.insertInto(CASE_FILE)
       .set(CASE_FILE.ID, row.id())
       .set(CASE_FILE.CASE_NUMBER, row.caseNumber())
       .execute();
} catch (DataAccessException e) {
    if (sqlErrors.isUniqueViolation(e, "uq_case_file_case_number")) {
        throw new DuplicateCaseNumber(row.caseNumber(), e);
    }
    throw e;
}

Use explicit DB constraint names.

29. jOOQ for Outbox

Outbox insert:

dsl.insertInto(OUTBOX_EVENT)
   .set(OUTBOX_EVENT.ID, event.id())
   .set(OUTBOX_EVENT.EVENT_KEY, event.eventKey())
   .set(OUTBOX_EVENT.AGGREGATE_TYPE, event.aggregateType())
   .set(OUTBOX_EVENT.AGGREGATE_ID, event.aggregateId())
   .set(OUTBOX_EVENT.EVENT_TYPE, event.eventType())
   .set(OUTBOX_EVENT.PAYLOAD, event.payloadJson())
   .set(OUTBOX_EVENT.CREATED_AT, event.createdAt())
   .execute();

Outbox claim with SKIP LOCKED may use dialect-specific DSL or plain SQL.

jOOQ is strong for outbox/inbox patterns because SQL is explicit.

30. jOOQ for Read Models

Read model upsert:

dsl.insertInto(CASE_DASHBOARD_READ_MODEL)
   .set(CASE_DASHBOARD_READ_MODEL.CASE_ID, snapshot.caseId())
   .set(CASE_DASHBOARD_READ_MODEL.STATUS, snapshot.status())
   .set(CASE_DASHBOARD_READ_MODEL.SOURCE_VERSION, snapshot.version())
   .onConflict(CASE_DASHBOARD_READ_MODEL.CASE_ID)
   .doUpdate()
   .set(CASE_DASHBOARD_READ_MODEL.STATUS, snapshot.status())
   .set(CASE_DASHBOARD_READ_MODEL.SOURCE_VERSION, snapshot.version())
   .where(CASE_DASHBOARD_READ_MODEL.SOURCE_VERSION.lt(snapshot.version()))
   .execute();

Dialect support varies.

This style makes idempotent projection explicit.

31. jOOQ for Reporting

jOOQ excels at:

• joins;
• CTEs;
• window functions;
• group by;
• aggregation;
• database-specific functions;
• keyset pagination;
• projection.

For reporting query where JPA becomes awkward, jOOQ is often a better fit.

32. SQL Review Culture

Even with jOOQ, code review should inspect SQL intent.

Review:

• selected columns;
• joins;
• predicates;
• tenant scope;
• ordering;
• limit;
• update count;
• constraints;
• indexes;
• transaction context;
• mapping nullability;
• generated SQL for dialect if critical.

Type-safe DSL is not a substitute for SQL thinking.

33. Query Naming

jOOQ supports comments through plain SQL fragments or settings, but at minimum data access method should emit metric:

query="CaseDashboardQuery.search"

For raw SQL/native fragments:

/* query=OutboxDao.claimNextBatch */

Observability remains important.

34. Plain SQL Escape Hatch

jOOQ allows plain SQL fragments.

Use sparingly.

DSL.condition("some_vendor_function(?) = true", value)

Rules:

• never concatenate user input;
• bind values;
• isolate in method;
• document dialect;
• test generated SQL;
• prefer DSL when available.

35. jOOQ Is Not Magic Against Bad SQL

You can still write:

dsl.select()
   .from(CASE_FILE)
   .fetch();

unbounded table scan.

You can still forget tenant predicate.

You can still create cartesian join.

You can still run slow count.

jOOQ makes SQL safer to construct, not automatically correct.

36. When jOOQ Is a Strong Fit

Use jOOQ when:

• SQL is central to business;
• reporting/dashboard/query-heavy service;
• database-first schema;
• complex joins/CTE/window functions;
• high need for generated type-safe schema;
• desire to avoid ORM hidden behavior;
• stored procedure/native SQL integration;
• performance-critical query review;
• Java team comfortable with SQL.

37. When JPA May Be Better

Use JPA/Hibernate when:

• object graph aggregate persistence is central;
• domain entity lifecycle maps well to ORM;
• dirty checking/unit-of-work saves boilerplate;
• association graph moderate;
• team disciplined with fetch plans;
• complex SQL not dominant.

Many systems use both:

JPA for command aggregate
jOOQ for read/query/bulk/report

with clear boundaries.

38. When Plain JDBC May Be Better

Use JDBC when:

• very small dependency footprint;
• simple direct SQL;
• maximum control;
• specialized streaming;
• minimal DSL needed;
• library/tool code;
• you want zero codegen.

jOOQ adds codegen/build complexity. Worth it when schema/type safety/query DSL benefits matter.

39. jOOQ Anti-Pattern: DSL Everywhere

Do not put jOOQ query in controller:

@GetMapping
public List<?> search() {
    return dsl.select(...).fetch();
}

Keep architecture:

Controller -> Query Use Case -> Query DAO/jOOQ component

40. jOOQ Anti-Pattern: Generated Record as Domain

Generated table records are persistence objects.

Do not add business behavior to generated code.

Map to domain objects or use separate wrappers.

41. jOOQ Anti-Pattern: Ignoring Transactions

Multiple jOOQ calls must be atomic when business requires.

update case
insert audit
insert outbox

must be same transaction.

Test rollback.

42. jOOQ Anti-Pattern: Auto-Mapping Everything

fetchInto can be convenient, but if field names/types drift, errors may be subtle.

For critical projections, map explicitly.

43. jOOQ Anti-Pattern: Overusing Plain SQL

If everything is raw string inside jOOQ, you lose type-safety.

Use DSL/generator where possible.

44. Production Checklist

• Schema codegen integrated with migrations.
• Generated sources reproducible.
• DSLContext transaction integration tested.
• Query code lives in DAO/query/repository layer.
• Tenant/scope predicate explicit.
• Update count checked.
• Constraint errors translated.
• DTO/domain mapping explicit for critical paths.
• Nullability from joins handled.
• Dialect-specific features documented.
• Generated SQL/plan reviewed for critical queries.
• No raw user input in SQL fragments.
• Rollback across multiple jOOQ operations tested.
• jOOQ/JPA mixing rules documented if both used.

45. Mini Lab

Design jOOQ data access for:

Approve case:
- update case status if expected version and status match;
- insert audit;
- insert outbox;
- return new version;
- idempotency command table exists.

Questions:

1. Which tables are generated?
2. What conditional update SQL is needed?
3. Do you use RETURNING?
4. How is update count handled?
5. Where is transaction boundary?
6. How is duplicate command detected?
7. How is audit inserted?
8. How is outbox event key unique?
9. What constraint errors are translated?
10. What rollback test proves atomicity?

46. Summary

jOOQ gives Java a SQL-first, type-safe data access model.

You must master:

• database-first workflow;
• generated schema;
• DSLContext;
• explicit select/update/insert;
• mapping records to DTO/domain;
• update count discipline;
• transaction integration;
• constraint error translation;
• dialect awareness;
• migration/codegen workflow;
• jOOQ with repository/DAO/query service;
• no hidden lazy loading/dirty checking;
• jOOQ + JPA mixing caveats;
• SQL review culture;
• anti-patterns.

Part berikutnya membahas jOOQ Query Building Patterns: select, join, CTE, window function, pagination, dynamic query, reusable conditions, and how to build complex SQL without losing readability.

47. References

• jOOQ Manual: https://www.jooq.org/doc/latest/manual/
• jOOQ Code Generation: https://www.jooq.org/doc/latest/manual/code-generation/
• jOOQ DSLContext: https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/DSLContext.html
• jOOQ Transactions: https://www.jooq.org/doc/latest/manual/sql-execution/transaction-management/
• jOOQ SQL Building: https://www.jooq.org/doc/latest/manual/sql-building/