jooq-best-practices

name	jooq-best-practices
description	Comprehensive jOOQ DSL best practices for Kotlin/Java. Use when writing jOOQ queries, mapping results, handling transactions, or designing repository layers.

jOOQ Best Practices

Expert knowledge base for jOOQ 3.20 DSL usage, SQL patterns, and Kotlin integration. Built from official docs + 783 blog articles (progressively updated).

How to use

1. Reference the knowledge files below for patterns, best practices, and real-world examples
2. If you hit a compile error or conflict with a knowledge entry, use the jOOQ MCP server to check current syntax

Knowledge base

• anti-patterns.md — 20 "don't do this" rules: schema, SQL, jOOQ API, H2 compatibility modes; .eq() vs .equals(); noCondition() for dynamic SQL; always call .execute(); don't map relational→objects→JSON when SQL can produce JSON directly
• multiset.md — Nested collections with MULTISET, JSON emulation
• fetching-mapping.md — RecordMapper, fetchMap, fetchGroups, collect() with JDK Collectors, ad-hoc converters, dot-notation alias for nested class mapping, fetchSize() for JDBC fetch size
• array-operations.md — Array lambda functions (filter, map, match) with PostgreSQL emulation
• merge-upsert.md — SQL MERGE as RIGHT JOIN mental model, full sync with FULL JOIN in USING clause, AND conditions in WHEN clauses, Oracle/SQL Server dialect variants, ORA-38104 workarounds
• implicit-joins.md — Implicit/explicit path joins, join elimination, path correlation, to-many joins, ON-clause paths, synthetic FKs
• code-generator.md — Code generator configuration: visibility modifiers, custom strategies, TVP wrappers, version compatibility, forced types (incl. MySQL TINYINT(1)→Boolean), embedded types, schema mapping, Testcontainers code generation, LiquibaseDatabase, Meta.migrateTo() schema diffing
• hierarchical-queries.md — Records.intoHierarchy() collector, recursive CTEs for tree structures
• derived-tables.md — Derived table declaration, type-safe column refs, simplification, LATERAL as local variables, asterisk().except()
• aggregate-functions.md — FILTER vs CASE in aggregates, COUNT(*) vs COUNT(1), functional dependencies in GROUP BY, table-level grouping, REDUCE simulation via recursive CTE, PRODUCT() emulation via EXP/LN (handles zeros/negatives, works as window function), weighted averages to fix join-multiplication distortion, PERCENTILE_DISC for skew detection, PERCENTILE_DISC emulation via PERCENT_RANK+FIRST_VALUE, custom user-defined aggregates (PostgreSQL CREATE AGGREGATE, Oracle ODCI interface)
• logging.md — LoggingConnection for SQL logging, DiagnosticsConnection for N+1 detection, ExecuteListener/CallbackExecuteListener for lifecycle hooks, DefaultConnection as JDBC proxy
• native-sql-strategy.md — When to use jOOQ DSL vs views/functions for complex static SQL
• stored-procedures.md — Calling procedures with default/named parameters; vendor-agnostic anonymous blocks, while_/for_ loops, and cross-dialect procedural logic
• type-system.md — jOOQ type hierarchy: DSLContext vs DSL, Result vs Cursor, Converter vs Binding, AlwaysInlineBinding for skewed enums, Condition as Field; Oracle BINARY_DOUBLE vs NUMBER performance
• dml-returning.md — RETURNING data from INSERT/UPDATE/DELETE across dialects, data change delta tables, set-based vs row-by-row DML performance, Oracle aggregate RETURNING
• computed-columns.md — Virtual client-side computed columns as reusable expressions, context-aware projections; DB-side GENERATED ALWAYS AS columns for constant FK values
• transactions.md — Explicit programmatic transactions, NESTED savepoints, R2DBC reactive transactions
• sequences.md — Fetching single/batch sequence values across dialects
• qom-traverser.md — Query Object Model inspection, expression tree traversal, SQL transformation with $replace()
• spring-boot-config.md — DefaultConfigurationCustomizer for Spring Boot jOOQ config; MySQL allowMultiQueries flag; switching to commercial editions; static classpath jooq-settings.xml override
• parser.md — Parser ignore comments, parsing connection for dialect translation, IN list padding, Oracle implicit-join-to-ANSI-JOIN transformation, online SQL translation tool
• result-formatting.md — ASCII chart output, formatChart() for console visualization
• sql-json.md — jsonObject/jsonArrayAgg nesting pattern (3.14, superseded by MULTISET 3.15+); dialect pitfalls: MySQL type degradation, Oracle CLOB, GROUP_CONCAT truncation, NULL semantics
• r2dbc-reactive.md — R2DBC setup, reactive query execution with Flux/Mono, automatic connection lifecycle management
• pagination.md — Single-query pagination metadata (total rows, page number, last-page flag) via COUNT(*) OVER() in nested derived tables; deterministic ordering requirement
• set-operations.md — Table diff with EXCEPT/UNION vs FULL JOIN USING; NULL-safe comparison with IS NOT DISTINCT FROM; row value expression NULL semantics; INTERSECT precedence over UNION/EXCEPT; multi-column row() IN predicates
• plain-sql-templating.md — val() vs inline() (bind vs literal), plain SQL field/table/condition templates with SQL injection safety, name() for identifiers, keyword() for consistent rendering, text blocks for multi-line plain SQL, custom DSL wrapper helpers to encapsulate plain SQL templates
• dynamic-sql.md — noCondition/trueCondition/falseCondition for optional predicates, conditional SELECT fields, conditional JOINs, conditional UNION branches
• naming-conventions.md — Table aliasing algorithm, semantic type prefixes, singular/plural consistency
• window-functions.md — DISTINCT ON emulation with FIRST_VALUE; LAST_VALUE IGNORE NULLS for gap-filling; cumulative percentage with nested SUM aggregates
• temporal-tables.md — Delta-to-snapshot reconstruction with ROW_NUMBER; SQL:2011 temporal tables (SQL Server, Oracle, MariaDB)
• predicates.md — Quantified LIKE ANY / LIKE ALL predicates replacing verbose OR chains
• join-patterns.md — ON clause vs WHERE clause semantics in outer joins; predicate placement rules
• updatable-record.md — UpdatableRecord CRUD: changed() delta tracking, DB DEFAULT support, avoid POJO round-trips

Core rules (always apply)

• Target version: jOOQ 3.20 on PostgreSQL
• Use jOOQ DSL in repository classes, never raw SQL in controllers/services
• Map results to Kotlin data classes
• Use record.FIELD not record.get(field) for type safety
• Use jOOQ-generated enums, don't replicate DB enums in Kotlin
• Prefer fetchMap(KEY, VALUE) with coerceTo() for aggregates
• Wrap multi-step DB operations in @Transactional
• Use DSL.name("cte").as(select...) for CTEs
• Use EXISTS() not COUNT(*) > 0 for existence checks
• Prefer COUNT(*) over COUNT(1) — identical semantics but ~10% faster on PostgreSQL; use count() (no argument) in jOOQ DSL
• Use NOT EXISTS instead of NOT IN with nullable columns
• Prefer UNION ALL over UNION unless dedup is needed
• Prefer FILTER (WHERE ...) over CASE in aggregates — more readable and faster on PostgreSQL
• Always execute jOOQ queries through jOOQ — don't extract SQL for JDBC/JPA (loses MULTISET emulation, type-safe mapping, R2DBC)
• Test against the real target database (via Testcontainers), not H2 with compatibility modes
• Always call .execute() on DML/DDL — jOOQ's fluent API builds silently without it; use IntelliJ's @CheckReturnValue inspection to catch this