Stop Hiding SQL Behind Your ORM: jOOQ, JPA, and JDBC Under Production Load

Amir Golmoradi — Thu, 11 Jun 2026 11:42:02 +0000

Most persistence debates in Java are framed incorrectly.

The question is not:

Should we use JPA, jOOQ, or JDBC?

The real question is:

Is this part of the system modeling object lifecycle, relational computation, or raw database interaction?

Those are different problems. Treating them as one problem is how teams end up with bloated repositories, unreadable Criteria API trees, native SQL strings inside @Query, unstable pagination, accidental N+1 queries, and production dashboards powered by List<Object[]>.

jOOQ exists because SQL is not an implementation detail. SQL is the language of the relational engine. If your application depends on joins, projections, aggregations, window functions, recursive queries, JSON operators, upserts, locking, or vendor-specific database features, hiding SQL behind an object graph is architectural denial.

This article is not a beginner introduction to jOOQ. It is a decision framework for senior engineers designing Java persistence layers that must survive real schema evolution, high query complexity, and production load.

The Architectural Conflict

Java applications usually want one of three persistence models:

Persistence Model	Primary Concern	Best Tooling Fit	Failure Mode
Object lifecycle persistence	Aggregate identity, invariants, dirty checking, optimistic locking	JPA / Hibernate	Over-fetching, lazy-loading surprises, persistence-context bloat
Relational query modeling	Joins, projections, reports, search screens, dashboards, read models	jOOQ	Build/codegen discipline required
Low-level database access	Minimal abstraction, library code, administrative SQL, edge-case driver behavior	JDBC / JdbcTemplate	Stringly typed SQL and manual mapping

The mistake is forcing one abstraction to cover all three.

JPA is excellent when the unit of work is an aggregate root and the application wants to persist domain state changes. jOOQ is excellent when the unit of work is a query. JDBC is excellent when no abstraction should exist between the application and the driver.

A serious architecture does not pick one blindly. It assigns each tool to the layer where its runtime model is honest.

Why Raw JDBC Is Still Not Enough

JDBC is transparent, but transparency is not the same as safety.

String sql = """
    SELECT a.id, a.currency, SUM(e.amount) AS balance
    FROM account a
    JOIN ledger_entry e ON e.account_id = a.id
    WHERE a.status = ?
    GROUP BY a.id, a.currency
    HAVING SUM(e.amount) > ?
    ORDER BY balance DESC
    """;

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, "ACTIVE");
    statement.setBigDecimal(2, new BigDecimal("10000.00"));

    try (ResultSet rs = statement.executeQuery()) {
        while (rs.next()) {
            UUID accountId = rs.getObject("id", UUID.class);
            String currency = rs.getString("currency");
            BigDecimal balance = rs.getBigDecimal("balance");
        }
    }
}

This is not bad code. It is just structurally fragile.

The compiler cannot verify:

whether ledger_entry.amount exists
whether account.currency is still a VARCHAR
whether balance is aliased correctly
whether rs.getBigDecimal("balance") matches the selected expression
whether a column rename broke the query
whether the projection still matches the DTO constructor

JDBC gives full control, but it pushes schema correctness into runtime. In small scripts, that is acceptable. In a financial system, reporting engine, or high-volume backend, it is a liability.

Why JPA Breaks Down Outside Aggregate Persistence

JPA is not “bad.” That is a lazy take.

JPA is a powerful object persistence model. It tracks managed entities inside a persistence context, performs dirty checking, supports optimistic locking, maps associations, and provides a unit-of-work abstraction around aggregate state changes.

That model is valuable for this:

@Transactional
public void renameOrganization(UUID organizationId, String newName) {
    Organization organization = organizationRepository.findById(organizationId)
        .orElseThrow(() -> new OrganizationNotFoundException(organizationId));

    organization.renameTo(newName);

    // No explicit save required if the entity is managed.
    // Hibernate dirty checking will flush the changed state.
}

This is exactly where JPA belongs: domain state transition, aggregate invariant, transactional write.

But JPA becomes the wrong abstraction when the query no longer represents an entity graph.

This is where teams quietly abandon the ORM while pretending they still use it:

@Query(
    value = """
        SELECT
            a.region,
            COUNT(*) AS account_count,
            SUM(e.amount) AS total_balance
        FROM account a
        JOIN ledger_entry e ON e.account_id = a.id
        WHERE a.status = 'ACTIVE'
        GROUP BY a.region
        HAVING SUM(e.amount) > 100000
        ORDER BY total_balance DESC
        """,
    nativeQuery = true
)
List<Object[]> findRegionalBalances();

That code has the worst of both worlds:

SQL is hidden inside an annotation string.
Projection mapping is index-based or reflection-based.
Refactoring safety is weak.
The database schema is invisible to the compiler.
The repository method name pretends this is still object persistence.
The calling service now depends on implicit tuple shape.

This is not advanced JPA. This is a persistence layer admitting defeat.

What jOOQ Actually Provides

jOOQ is a schema-first SQL DSL and code generator.

The critical phrase is schema-first.

jOOQ does not ask you to pretend that Java classes own the relational model. It introspects the database schema and generates Java metadata for tables, columns, records, routines, enums, keys, and constraints.

A query stops being an opaque string:

String sql = "SELECT id, email FROM users WHERE status = ?";

And becomes a typed expression:

List<UserSummary> users = dsl
    .select(USER_ACCOUNT.ID, USER_ACCOUNT.EMAIL)
    .from(USER_ACCOUNT)
    .where(USER_ACCOUNT.STATUS.eq(UserStatus.ACTIVE))
    .fetch(Records.mapping(UserSummary::new));

The compiler now participates in schema correctness.

If email is renamed to email_address, regenerated jOOQ metadata removes USER_ACCOUNT.EMAIL. The application fails at compile time, not during a production request.

That is the entire value proposition.

Not “less boilerplate.”

Not “nicer SQL.”

Not “fluent syntax.”

The value is shifting schema drift from runtime failure to build-time failure.

Runtime Model: JDBC vs JPA vs jOOQ

Dimension	JDBC	JPA / Hibernate	jOOQ
Query representation	Raw string	JPQL, Criteria, derived methods, native strings	Generated DSL objects
Schema awareness	None at compile time	Entity model, not full schema	Generated database metadata
Mapping model	Manual	Entity hydration and persistence context	Records, DTOs, generated POJOs, custom mappers
Runtime overhead	Lowest abstraction overhead	Entity state tracking, proxies, dirty checking, first-level cache	Query construction plus mapping, no persistence context
SQL predictability	Exact SQL	SQL generated by provider, unless native query	Exact SQL generated from explicit DSL
Complex SQL support	Complete but stringly typed	Limited; often falls back to native SQL	Strong; models SQL directly
Write model	Manual	Excellent for aggregate writes	Excellent for explicit SQL writes
Read model	Manual mapping	Entity-centric; projections become awkward	Excellent for projections and read models
Failure timing	Runtime	Mix of startup/runtime/query execution	Build time for schema drift, runtime for data/constraint issues
Best use	Low-level access	Domain aggregate persistence	Complex queries, reporting, CQRS read side

The runtime distinction matters.

JPA maintains a persistence context. Every managed entity has identity, state, and lifecycle. That is useful when changing aggregates. It is wasteful when building a dashboard projection that should never become a managed entity.

jOOQ does not track object graphs. It builds SQL, binds parameters, executes statements, and maps results. That makes it predictable for read-heavy paths where the database is doing relational computation.

The Code Generator Is Not Optional

Using jOOQ without code generation is like using TypeScript with any everywhere. Technically possible. Architecturally pointless.

The generator is the mechanism that converts this:

CREATE TABLE account (
    id UUID PRIMARY KEY,
    owner_id UUID NOT NULL,
    currency CHAR(3) NOT NULL,
    status VARCHAR(32) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE ledger_entry (
    id UUID PRIMARY KEY,
    account_id UUID NOT NULL REFERENCES account(id),
    amount NUMERIC(19, 4) NOT NULL,
    direction VARCHAR(16) NOT NULL,
    posted_at TIMESTAMPTZ NOT NULL
);

Into typed Java metadata:

ACCOUNT.ID          // TableField<AccountRecord, UUID>
ACCOUNT.CURRENCY    // TableField<AccountRecord, String>
LEDGER_ENTRY.AMOUNT // TableField<LedgerEntryRecord, BigDecimal>
LEDGER_ENTRY.POSTED_AT

That generated model is what makes query refactoring safe.

A production-grade generation pipeline should look like this:

Flyway / Liquibase migrations
        ↓
Ephemeral database matching production dialect
        ↓
jOOQ code generation
        ↓
Java compilation
        ↓
Integration tests against the same dialect

Do not generate jOOQ classes from an H2 approximation of PostgreSQL. That is a fake safety net. If production is PostgreSQL, generate against PostgreSQL. If production is MySQL, generate against MySQL. Dialect behavior matters.

For CI, the reliable pattern is:

Start database container
Apply migrations
Generate jOOQ sources
Compile application
Run integration tests
Fail build on schema/code mismatch

Generated sources may be committed or generated during CI. Both strategies can work. What cannot work is allowing developers to generate from stale local databases while CI compiles against a different schema.

Maven Setup: Keep Versions and Credentials Out of the Plugin Body

Use a property-managed version. Do not hardcode credentials inside the plugin.

<properties>
    <java.version>21</java.version>
    <jooq.version>3.21.0</jooq.version>
    <postgres.version>42.7.5</postgres.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.jooq</groupId>
        <artifactId>jooq</artifactId>
        <version>${jooq.version}</version>
    </dependency>

    <dependency>
        <groupId>org.jooq</groupId>
        <artifactId>jooq-meta</artifactId>
        <version>${jooq.version}</version>
    </dependency>

    <dependency>
        <groupId>org.jooq</groupId>
        <artifactId>jooq-codegen</artifactId>
        <version>${jooq.version}</version>
    </dependency>

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>${postgres.version}</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

A minimal Maven codegen configuration:

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>${jooq.version}</version>

    <executions>
        <execution>
            <id>generate-jooq</id>
            <phase>generate-sources</phase>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>

    <configuration>
        <jdbc>
            <driver>org.postgresql.Driver</driver>
            <url>${env.JOOQ_CODEGEN_JDBC_URL}</url>
            <user>${env.JOOQ_CODEGEN_DB_USERNAME}</user>
            <password>${env.JOOQ_CODEGEN_DB_PASSWORD}</password>
        </jdbc>

        <generator>
            <database>
                <name>org.jooq.meta.postgres.PostgresDatabase</name>
                <inputSchema>public</inputSchema>
                <includes>.*</includes>
                <excludes>flyway_schema_history</excludes>
            </database>

            <generate>
                <records>true</records>
                <pojos>false</pojos>
                <daos>false</daos>
                <fluentSetters>true</fluentSetters>
                <javaTimeTypes>true</javaTimeTypes>
            </generate>

            <target>
                <packageName>com.acme.persistence.jooq</packageName>
                <directory>target/generated-sources/jooq</directory>
            </target>
        </generator>
    </configuration>
</plugin>

Do not enable generated DAOs by default. They encourage an anemic table-centric persistence style. In serious applications, your query objects should be designed around use cases, not around generated table wrappers.

Spring Boot Configuration

Spring Boot can auto-configure jOOQ, but production systems usually need explicit control over settings, exception translation, logging, and execution listeners.

package com.acme.persistence.config;

import org.jooq.ExecuteListenerProvider;
import org.jooq.SQLDialect;
import org.jooq.conf.RenderQuotedNames;
import org.jooq.conf.Settings;
import org.jooq.impl.DefaultConfiguration;
import org.jooq.impl.DefaultDSLContext;
import org.jooq.tools.LoggerListener;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
class JooqConfiguration {

    @Bean
    DefaultDSLContext dslContext(
            DataSource dataSource,
            ExecuteListenerProvider exceptionTranslator
    ) {
        Settings settings = new Settings()
                .withRenderQuotedNames(RenderQuotedNames.NEVER)
                .withExecuteLogging(false)
                .withRenderFormatted(false);

        DefaultConfiguration configuration = new DefaultConfiguration();
        configuration.setDataSource(dataSource);
        configuration.setSQLDialect(SQLDialect.POSTGRES);
        configuration.setSettings(settings);
        configuration.setExecuteListenerProvider(exceptionTranslator);

        return new DefaultDSLContext(configuration);
    }
}

For local debugging, use logging at the datasource/proxy layer or a jOOQ ExecuteListener. Do not enable noisy SQL logging globally in production. Query observability should be structured: operation name, bind values policy, duration, row count, failure type, and correlation ID.

Use jOOQ for Read Models, Not Generic Repositories

Do not write this:

public interface GenericRepository<T, ID> {
    Optional<T> findById(ID id);
    List<T> findAll();
    void save(T entity);
}

That abstraction is junior-level damage. It hides the actual query shape and gives every use case the same fake persistence model.

A read model should be explicit:

package com.acme.accounts.readmodel;

import java.math.BigDecimal;
import java.time.OffsetDateTime;
import java.util.UUID;

public record AccountBalanceView(
        UUID accountId,
        String currency,
        BigDecimal balance,
        OffsetDateTime lastEntryAt
) {}

The query should express the real relational operation:

package com.acme.accounts.readmodel;

import lombok.RequiredArgsConstructor;
import org.jooq.DSLContext;
import org.jooq.Field;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.OffsetDateTime;
import java.util.Optional;
import java.util.UUID;

import static com.acme.persistence.jooq.Tables.ACCOUNT;
import static com.acme.persistence.jooq.Tables.LEDGER_ENTRY;
import static org.jooq.Records.mapping;
import static org.jooq.impl.DSL.coalesce;
import static org.jooq.impl.DSL.max;
import static org.jooq.impl.DSL.sum;

@Repository
@RequiredArgsConstructor
public class AccountBalanceQuery {

    private final DSLContext dsl;

    public Optional<AccountBalanceView> findBalance(UUID accountId) {
        Field<BigDecimal> balance = coalesce(
                sum(LEDGER_ENTRY.AMOUNT),
                BigDecimal.ZERO
        ).as("balance");

        Field<OffsetDateTime> lastEntryAt = max(LEDGER_ENTRY.POSTED_AT)
                .as("last_entry_at");

        return dsl
                .select(
                        ACCOUNT.ID,
                        ACCOUNT.CURRENCY,
                        balance,
                        lastEntryAt
                )
                .from(ACCOUNT)
                .leftJoin(LEDGER_ENTRY)
                    .on(LEDGER_ENTRY.ACCOUNT_ID.eq(ACCOUNT.ID))
                .where(ACCOUNT.ID.eq(accountId))
                .groupBy(ACCOUNT.ID, ACCOUNT.CURRENCY)
                .fetchOptional(mapping(AccountBalanceView::new));
    }
}

This is a read model. It is not an entity. It should not be managed by Hibernate. It should not have lazy relationships. It should not be forced through a fake repository abstraction.

The query shape is the contract.

Avoid Reflection Mapping When the Projection Is Critical

fetchInto(SomeDto.class) is convenient, but it relies on naming conventions and reflective mapping. That may be fine for internal admin screens. It is not ideal for critical paths where constructor shape should be explicit.

Prefer constructor mapping:

return dsl
        .select(
                ACCOUNT.ID,
                ACCOUNT.CURRENCY,
                balance,
                lastEntryAt
        )
        .from(ACCOUNT)
        .leftJoin(LEDGER_ENTRY)
            .on(LEDGER_ENTRY.ACCOUNT_ID.eq(ACCOUNT.ID))
        .where(ACCOUNT.ID.eq(accountId))
        .groupBy(ACCOUNT.ID, ACCOUNT.CURRENCY)
        .fetchOptional(mapping(AccountBalanceView::new));

Now the selected fields and the DTO constructor must agree at compile time.

That is the level of safety you want in payment, ledger, compliance, and reporting code.

Keyset Pagination: Stop Shipping Offset Pagination by Default

Offset pagination is common because it is easy:

.limit(pageSize)
.offset(page * pageSize)

It is also often the wrong default for high-volume tables.

The database still has to walk past skipped rows. Under concurrent inserts, offset pagination can duplicate or miss rows between requests. For event streams, ledger entries, audit logs, and inbox-like feeds, use keyset pagination.

package com.acme.ledger.readmodel;

import org.jooq.Condition;
import org.jooq.DSLContext;
import org.springframework.stereotype.Repository;

import java.time.OffsetDateTime;
import java.util.List;
import java.util.UUID;

import static com.acme.persistence.jooq.Tables.LEDGER_ENTRY;
import static org.jooq.impl.DSL.noCondition;

@Repository
public class LedgerEntryFeedQuery {

    private final DSLContext dsl;

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

    public List<LedgerEntryRow> findPage(UUID accountId, LedgerCursor cursor, int pageSize) {
        Condition afterCursor = cursor == null
                ? noCondition()
                : LEDGER_ENTRY.POSTED_AT.lt(cursor.postedAt())
                    .or(LEDGER_ENTRY.POSTED_AT.eq(cursor.postedAt())
                    .and(LEDGER_ENTRY.ID.lt(cursor.id())));

        return dsl
                .select(
                        LEDGER_ENTRY.ID,
                        LEDGER_ENTRY.ACCOUNT_ID,
                        LEDGER_ENTRY.AMOUNT,
                        LEDGER_ENTRY.DIRECTION,
                        LEDGER_ENTRY.POSTED_AT
                )
                .from(LEDGER_ENTRY)
                .where(LEDGER_ENTRY.ACCOUNT_ID.eq(accountId))
                .and(afterCursor)
                .orderBy(
                        LEDGER_ENTRY.POSTED_AT.desc(),
                        LEDGER_ENTRY.ID.desc()
                )
                .limit(pageSize)
                .fetch(record -> new LedgerEntryRow(
                        record.get(LEDGER_ENTRY.ID),
                        record.get(LEDGER_ENTRY.ACCOUNT_ID),
                        record.get(LEDGER_ENTRY.AMOUNT),
                        record.get(LEDGER_ENTRY.DIRECTION),
                        record.get(LEDGER_ENTRY.POSTED_AT)
                ));
    }

    public record LedgerCursor(OffsetDateTime postedAt, UUID id) {}
}

The ordering is stable because it uses a deterministic tie-breaker: posted_at DESC, id DESC.

That is the difference between pagination that demos well and pagination that survives production writes.

Upserts: Use Database Semantics Directly

A frequent ORM anti-pattern:

Optional<AccountBalance> existing = repository.findByAccountId(accountId);

if (existing.isPresent()) {
    existing.get().increase(delta);
} else {
    repository.save(new AccountBalance(accountId, currency, delta));
}

Under concurrency, this becomes a race unless guarded by constraints and transaction isolation. The database already has the correct primitive: unique constraint plus upsert.

CREATE TABLE account_balance (
    account_id UUID NOT NULL,
    currency CHAR(3) NOT NULL,
    amount NUMERIC(19, 4) NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (account_id, currency)
);

Express the database operation directly:

package com.acme.accounts.write;

import org.jooq.DSLContext;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.OffsetDateTime;
import java.util.UUID;

import static com.acme.persistence.jooq.Tables.ACCOUNT_BALANCE;

@Repository
public class AccountBalanceWriter {

    private final DSLContext dsl;

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

    public void applyDelta(UUID accountId, String currency, BigDecimal delta, OffsetDateTime now) {
        dsl.insertInto(
                    ACCOUNT_BALANCE,
                    ACCOUNT_BALANCE.ACCOUNT_ID,
                    ACCOUNT_BALANCE.CURRENCY,
                    ACCOUNT_BALANCE.AMOUNT,
                    ACCOUNT_BALANCE.UPDATED_AT
                )
                .values(accountId, currency, delta, now)
                .onConflict(
                    ACCOUNT_BALANCE.ACCOUNT_ID,
                    ACCOUNT_BALANCE.CURRENCY
                )
                .doUpdate()
                .set(
                    ACCOUNT_BALANCE.AMOUNT,
                    ACCOUNT_BALANCE.AMOUNT.plus(delta)
                )
                .set(ACCOUNT_BALANCE.UPDATED_AT, now)
                .execute();
    }
}

This is not “database leakage.” This is using the database correctly.

The boundary should not hide concurrency semantics. The boundary should make them explicit.

Transactional Writes With Locking

jOOQ participates in Spring-managed transactions when it uses the same DataSource and transaction manager.

package com.acme.ledger.application;

import com.acme.ledger.domain.AccountNotPostableException;
import lombok.RequiredArgsConstructor;
import org.jooq.DSLContext;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.time.Clock;
import java.time.OffsetDateTime;
import java.util.UUID;

import static com.acme.persistence.jooq.Tables.ACCOUNT;
import static com.acme.persistence.jooq.Tables.LEDGER_ENTRY;

@Service
@RequiredArgsConstructor
public class PostLedgerEntryHandler {

    private final DSLContext dsl;
    private final Clock clock;

    @Transactional
    public UUID handle(PostLedgerEntryCommand command) {
        UUID accountId = dsl
                .select(ACCOUNT.ID)
                .from(ACCOUNT)
                .where(ACCOUNT.ID.eq(command.accountId()))
                .and(ACCOUNT.STATUS.eq("ACTIVE"))
                .forUpdate()
                .fetchOptional(ACCOUNT.ID)
                .orElseThrow(() -> new AccountNotPostableException(command.accountId()));

        UUID entryId = UUID.randomUUID();
        OffsetDateTime now = OffsetDateTime.now(clock);

        dsl.insertInto(
                    LEDGER_ENTRY,
                    LEDGER_ENTRY.ID,
                    LEDGER_ENTRY.ACCOUNT_ID,
                    LEDGER_ENTRY.AMOUNT,
                    LEDGER_ENTRY.DIRECTION,
                    LEDGER_ENTRY.POSTED_AT
                )
                .values(
                    entryId,
                    accountId,
                    command.amount(),
                    command.direction().name(),
                    now
                )
                .execute();

        return entryId;
    }
}

FOR UPDATE is not an implementation detail. It is part of the consistency model.

If two commands can mutate financial state concurrently, the transaction must say how contention is handled. Hiding that behind repository.save() does not make the race disappear.

Batch Inserts: Know What You Are Optimizing

JPA batching is provider-specific and sensitive to identifier strategy, flush size, persistence-context size, and ordering settings. With Hibernate, insert batching is limited when using identity-generated identifiers because the provider needs generated identifiers as entities become managed.

jOOQ batch execution is more direct:

List<LedgerEntryRecord> records = commands.stream()
        .map(command -> {
            LedgerEntryRecord record = dsl.newRecord(LEDGER_ENTRY);
            record.setId(UUID.randomUUID());
            record.setAccountId(command.accountId());
            record.setAmount(command.amount());
            record.setDirection(command.direction().name());
            record.setPostedAt(command.postedAt());
            return record;
        })
        .toList();

dsl.batchInsert(records).execute();

This reduces per-row statement overhead by using batched binds. It does not magically turn every workload into optimal bulk ingestion.

For very large imports, PostgreSQL COPY, database-native bulk loaders, partition-aware ingestion, or staging tables may beat both jOOQ and JPA. jOOQ is a strong general-purpose SQL construction and execution tool. It is not a replacement for every database-native ingestion mechanism.

Advanced SQL Should Not Be Second-Class Java Code

Window functions are a good example of where ORM abstractions collapse.

A risk dashboard might need the top accounts by daily outgoing transfer volume:

package com.acme.risk.readmodel;

import org.jooq.DSLContext;
import org.jooq.Field;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;

import static com.acme.persistence.jooq.Tables.TRANSFER;
import static org.jooq.impl.DSL.currentDate;
import static org.jooq.impl.DSL.date;
import static org.jooq.impl.DSL.rank;
import static org.jooq.impl.DSL.sum;

@Repository
public class RiskExposureQuery {

    private final DSLContext dsl;

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

    public List<AccountExposureRow> findTopOutgoingExposure(LocalDate businessDate, int limit) {
        Field<BigDecimal> outgoingTotal = sum(TRANSFER.AMOUNT).as("outgoing_total");

        return dsl
                .select(
                        TRANSFER.SOURCE_ACCOUNT_ID,
                        TRANSFER.CURRENCY,
                        outgoingTotal,
                        rank().over()
                                .partitionBy(TRANSFER.CURRENCY)
                                .orderBy(outgoingTotal.desc())
                                .as("currency_rank")
                )
                .from(TRANSFER)
                .where(TRANSFER.BUSINESS_DATE.eq(businessDate))
                .and(TRANSFER.STATUS.eq("SETTLED"))
                .groupBy(
                        TRANSFER.SOURCE_ACCOUNT_ID,
                        TRANSFER.CURRENCY
                )
                .orderBy(outgoingTotal.desc())
                .limit(limit)
                .fetch(record -> new AccountExposureRow(
                        record.get(TRANSFER.SOURCE_ACCOUNT_ID),
                        record.get(TRANSFER.CURRENCY),
                        record.get(outgoingTotal),
                        record.get("currency_rank", Integer.class)
                ));
    }
}

This query belongs in SQL form. The database optimizer understands it. The DSL keeps it composable and type-aware. A JPA entity graph does not improve this model; it only obscures it.

Dynamic Queries Without String Concatenation

Search endpoints often become string-concatenation traps.

With jOOQ, dynamic predicates remain typed values:

package com.acme.accounts.readmodel;

import org.jooq.Condition;
import org.jooq.DSLContext;
import org.springframework.stereotype.Repository;

import java.time.OffsetDateTime;
import java.util.List;

import static com.acme.persistence.jooq.Tables.ACCOUNT;
import static org.jooq.impl.DSL.noCondition;

@Repository
public class AccountSearchQuery {

    private final DSLContext dsl;

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

    public List<AccountSearchRow> search(AccountSearchCriteria criteria) {
        Condition condition = noCondition();

        if (criteria.status() != null) {
            condition = condition.and(ACCOUNT.STATUS.eq(criteria.status().name()));
        }

        if (criteria.currency() != null) {
            condition = condition.and(ACCOUNT.CURRENCY.eq(criteria.currency()));
        }

        if (criteria.createdAfter() != null) {
            condition = condition.and(ACCOUNT.CREATED_AT.ge(criteria.createdAfter()));
        }

        if (criteria.ownerId() != null) {
            condition = condition.and(ACCOUNT.OWNER_ID.eq(criteria.ownerId()));
        }

        return dsl
                .select(
                        ACCOUNT.ID,
                        ACCOUNT.OWNER_ID,
                        ACCOUNT.CURRENCY,
                        ACCOUNT.STATUS,
                        ACCOUNT.CREATED_AT
                )
                .from(ACCOUNT)
                .where(condition)
                .orderBy(ACCOUNT.CREATED_AT.desc(), ACCOUNT.ID.desc())
                .limit(criteria.limit())
                .fetch(record -> new AccountSearchRow(
                        record.get(ACCOUNT.ID),
                        record.get(ACCOUNT.OWNER_ID),
                        record.get(ACCOUNT.CURRENCY),
                        record.get(ACCOUNT.STATUS),
                        record.get(ACCOUNT.CREATED_AT)
                ));
    }
}

No string concatenation. No Criteria API ceremony. No hidden query derivation. No accidental SQL injection risk from manually assembled fragments.

The conditions are values.

Multi-Tenancy: Prefer Explicit Render Mapping Over Ad-Hoc SQL Rewriting

For schema-per-tenant systems, avoid casual query rewriting through listeners unless you have a very strong reason. Query listeners are powerful, but they can become invisible infrastructure magic.

Prefer deriving a tenant-specific configuration:

package com.acme.tenant;

import org.jooq.Configuration;
import org.jooq.DSLContext;
import org.jooq.conf.MappedSchema;
import org.jooq.conf.RenderMapping;
import org.jooq.conf.Settings;
import org.jooq.impl.DSL;
import org.springframework.stereotype.Component;

@Component
public class TenantDslFactory {

    private final Configuration baseConfiguration;

    public TenantDslFactory(DSLContext dsl) {
        this.baseConfiguration = dsl.configuration();
    }

    public DSLContext forTenantSchema(String tenantSchema) {
        Settings tenantSettings = baseConfiguration.settings()
                .withRenderMapping(new RenderMapping()
                        .withSchemata(new MappedSchema()
                                .withInput("public")
                                .withOutput(tenantSchema)));

        return DSL.using(baseConfiguration.derive(tenantSettings));
    }
}

This keeps tenant schema selection explicit at the boundary.

Also: validate tenantSchema against tenant metadata. Never accept raw schema names from request input and feed them into SQL rendering.

Reactive jOOQ: Useful, But Not a Free Performance Upgrade

jOOQ supports reactive execution through publisher-based query execution when configured with R2DBC. That does not automatically make a persistence layer faster.

Reactive database access is useful when:

the application stack is already reactive end-to-end
request threads must not block on I/O
the driver, connection pool, transaction manager, and execution model are all reactive
backpressure and cancellation semantics are actually understood by the team

It is not useful when a blocking Spring MVC application wraps database calls in reactive types and calls it architecture.

A reactive query shape looks similar:

Flux<AccountSearchRow> rows = Flux.from(
        reactiveDsl
                .select(
                        ACCOUNT.ID,
                        ACCOUNT.OWNER_ID,
                        ACCOUNT.CURRENCY,
                        ACCOUNT.STATUS,
                        ACCOUNT.CREATED_AT
                )
                .from(ACCOUNT)
                .where(ACCOUNT.STATUS.eq("ACTIVE"))
                .orderBy(ACCOUNT.CREATED_AT.desc())
                .limit(100)
).map(record -> new AccountSearchRow(
        record.get(ACCOUNT.ID),
        record.get(ACCOUNT.OWNER_ID),
        record.get(ACCOUNT.CURRENCY),
        record.get(ACCOUNT.STATUS),
        record.get(ACCOUNT.CREATED_AT)
));

The query DSL is not the hard part. The hard part is transaction boundaries, connection ownership, backpressure, driver maturity, and operational debugging.

Use reactive jOOQ because your system has a coherent non-blocking architecture, not because “reactive” sounds modern.

The Correct Hybrid: JPA for Aggregate Writes, jOOQ for Read Models

The strongest Java persistence architectures do not force JPA and jOOQ to compete.

They separate responsibilities.

Command side
  Application service
      ↓
  Domain aggregate
      ↓
  JPA repository
      ↓
  Transactional write model

Query side
  Query handler
      ↓
  jOOQ read model query
      ↓
  DTO / projection / API response

Example:

@Service
@RequiredArgsConstructor
public class OrganizationCommandService {

    private final OrganizationRepository organizationRepository;

    @Transactional
    public void renameOrganization(RenameOrganizationCommand command) {
        Organization organization = organizationRepository.findById(command.organizationId())
                .orElseThrow(() -> new OrganizationNotFoundException(command.organizationId()));

        organization.rename(command.newName());
    }
}

That is a good JPA use case.

Now the dashboard:

@Repository
@RequiredArgsConstructor
public class OrganizationDashboardQuery {

    private final DSLContext dsl;

    public OrganizationDashboardView getDashboard(UUID organizationId) {
        return dsl
                .select(
                        ORGANIZATION.ID,
                        ORGANIZATION.NAME,
                        ORGANIZATION.STATUS,
                        ACCOUNT.ID.count().as("account_count"),
                        coalesce(sum(ACCOUNT_BALANCE.AMOUNT), BigDecimal.ZERO).as("total_balance")
                )
                .from(ORGANIZATION)
                .leftJoin(ACCOUNT)
                    .on(ACCOUNT.ORGANIZATION_ID.eq(ORGANIZATION.ID))
                .leftJoin(ACCOUNT_BALANCE)
                    .on(ACCOUNT_BALANCE.ACCOUNT_ID.eq(ACCOUNT.ID))
                .where(ORGANIZATION.ID.eq(organizationId))
                .groupBy(
                        ORGANIZATION.ID,
                        ORGANIZATION.NAME,
                        ORGANIZATION.STATUS
                )
                .fetchOne(record -> new OrganizationDashboardView(
                        record.get(ORGANIZATION.ID),
                        record.get(ORGANIZATION.NAME),
                        record.get(ORGANIZATION.STATUS),
                        record.get("account_count", Integer.class),
                        record.get("total_balance", BigDecimal.class)
                ));
    }
}

This should not be a JPA entity graph. It is a read projection.

The Consistency Rules When Mixing JPA and jOOQ

Mixing JPA and jOOQ in one application is safe. Mixing them carelessly inside the same transaction is not.

Follow these rules:

Scenario	Risk	Required Discipline
JPA writes, jOOQ reads in same transaction	JPA changes may still be pending in persistence context	Call `entityManager.flush()` before the jOOQ query
jOOQ writes, JPA reads entity already loaded earlier	JPA first-level cache may return stale state	Avoid this flow, or call `entityManager.refresh(entity)` / `clear()` deliberately
Both JPA and jOOQ write same tables	Conflicting ownership and stale entity state	Assign write ownership per use case; do not casually interleave
jOOQ updates rows behind managed entities	Persistence context no longer reflects database state	Refresh, clear, or isolate the jOOQ write in a separate boundary
jOOQ read models over JPA-owned tables	Usually safe	Keep queries read-only and transactionally consistent

This is the part most hybrid articles under-explain.

The issue is not whether JPA and jOOQ can share a DataSource. They can.

The issue is that JPA has an identity map and jOOQ does not know it exists.

Decision Matrix

Use Case	JDBC	JPA	jOOQ	Recommendation
Simple aggregate CRUD	Possible but manual	Excellent	Possible but table-centric	Use JPA
Rich domain invariants	Poor fit	Excellent	Poor fit unless manually modeled	Use JPA
Dashboard projection	Manual and brittle	Awkward	Excellent	Use jOOQ
Reporting query	Manual and brittle	Poor fit	Excellent	Use jOOQ
Window functions	Supported but stringly typed	Poor / native SQL escape	Excellent	Use jOOQ
Bulk ingestion	Good with batching/COPY	Provider-sensitive	Good, but not always best	JDBC/native or jOOQ
Dynamic search filters	String builder risk	Criteria API complexity	Excellent	Use jOOQ
Stored procedures	Manual	Vendor awkwardness	Generated wrappers	Use jOOQ
Library with no persistence dependency	Excellent	Bad fit	Bad fit	Use JDBC
Event-sourced write store	Good	Often unnecessary	Good	JDBC or jOOQ
CQRS read side	Manual	Wrong abstraction	Excellent	Use jOOQ
CQRS command side with aggregates	Manual	Excellent	Possible but lower-level	Use JPA

Anti-Patterns

1. Generic Repository Over jOOQ

If you wrap jOOQ in a generic repository that only exposes save, findById, and findAll, you destroyed the reason to use jOOQ.

jOOQ’s strength is query shape. Do not erase it.

2. Native Queries Returning `Object[]`

This is not a production abstraction:

List<Object[]> rows = repository.findSomethingComplicated();

It is a runtime tuple with undocumented positional semantics.

Use a DTO, constructor mapping, interface projection, or jOOQ record mapper.

3. Entity Graphs for Dashboard APIs

A dashboard is not an aggregate. A dashboard is a projection.

Do not hydrate 200 entities to compute a response the database can calculate in one query.

4. Offset Pagination on Hot Tables

Offset pagination is fine for small admin tables. It is a poor default for append-heavy datasets.

Use keyset pagination for ledgers, audit logs, feeds, timelines, and transaction history.

5. Reactive Wrappers Around Blocking JDBC

Wrapping blocking JDBC calls in Mono.fromCallable() does not make the database layer reactive. It only moves blocking work somewhere else.

Recommended Architecture

For a serious Spring Boot backend using DDD, Hexagonal Architecture, or CQRS:

src/main/java/com/acme
  ├── accounts
  │   ├── domain
  │   │   └── Account.java
  │   ├── application
  │   │   ├── command
  │   │   │   └── RenameAccountHandler.java
  │   │   └── query
  │   │       └── GetAccountDashboardHandler.java
  │   ├── infrastructure
  │   │   ├── persistence
  │   │   │   ├── AccountJpaRepository.java
  │   │   │   └── AccountBalanceQuery.java
  │   │   └── web
  │   │       └── AccountController.java
  │   └── readmodel
  │       └── AccountDashboardView.java
  └── persistence
      └── jooq
          └── generated sources

Use JPA repositories for aggregate persistence.

Use jOOQ query classes for read models.

Do not put jOOQ-generated records into your domain model. They are persistence artifacts. Keep them at the infrastructure boundary.

Final Recommendation

Use JPA when the business operation is centered on an aggregate lifecycle.

Use jOOQ when the business operation is centered on relational data retrieval, projection, reporting, search, aggregation, or database-native behavior.

Use JDBC when the abstraction itself is unnecessary or harmful.

The strongest architecture is not “jOOQ everywhere” or “JPA everywhere.”

The strongest architecture is honest about the shape of the problem:

Aggregate state transition → JPA
Relational read model      → jOOQ
Driver-level operation     → JDBC

SQL is not something to hide.

SQL is part of the system’s design surface. Treat it with the same engineering discipline you apply to Java code: compile-time safety, explicit boundaries, testable behavior, stable contracts, and production observability.

That is where jOOQ earns its place.

DEV Community: Amir Golmoradi