skip to content

What is jOOQ's ExecuteListener, and how do you add one to Spring Boot's DSLContext without dropping Boot's exception translation?

level: middleimportance: should knowfreq 34%

answer

  1. callbacks around every statement
  2. providers, not listeners, on the Configuration
  3. one instance shared by all threads
  4. set replaces, setAppending adds
  5. Boot's translator is already in the list

basics

~20 s

An ExecuteListener receives callbacks across every jOOQ statement's lifecycle, such as executeStart, executeEnd and exception. In Spring Boot, declare it as an ExecuteListenerProvider bean so Boot adds it beside its exception-translator listener; configuration.set(listener) would replace that translator.

solid answer

~40 s

`ExecuteListener` is jOOQ's hook into each statement's lifecycle: `start`, render, prepare, bind, `executeStart`/`executeEnd`, fetch callbacks, `end`, plus `exception` and `warning`, all `default` methods receiving an `ExecuteContext` with the SQL, bind values and any `SQLException`. The `Configuration` holds `ExecuteListenerProvider`s; `DefaultExecuteListenerProvider` returns one shared instance, so per-statement state belongs in `ctx.data(...)`. Spring Boot already registers a provider for its `ExceptionTranslatorExecuteListener`, which turns `SQLException`s into Spring `DataAccessException`s, then collects every `ExecuteListenerProvider` bean into the configuration. So add yours as an `ExecuteListenerProvider` bean, or call `setAppending(...)` in a `DefaultConfigurationCustomizer`. Calling `configuration.set(listener)` replaces the whole list, and constraint violations then surface as jOOQ exceptions instead of Spring's. This describes jOOQ 3.21 with Spring Boot 4.1.

code

java · 28 lines
java
import org.jooq.ExecuteContext;
import org.jooq.ExecuteListener;
import org.jooq.ExecuteListenerProvider;
import org.jooq.impl.DefaultExecuteListenerProvider;

class SlowStatementListener implements ExecuteListener {
    private static final String STARTED = "slowStatement.startedNanos";
    private static final System.Logger LOG = System.getLogger("slow-sql");

    @Override
    public void executeStart(ExecuteContext ctx) {
        ctx.data(STARTED, System.nanoTime());
    }

    @Override
    public void executeEnd(ExecuteContext ctx) {
        if (ctx.data(STARTED) instanceof Long started
                && System.nanoTime() - started > 500_000_000L) {
            LOG.log(System.Logger.Level.WARNING, "slow statement: {0}", ctx.sql());
        }
    }
}

// in a Spring configuration class
@Bean
ExecuteListenerProvider slowStatementListenerProvider() {
    return new DefaultExecuteListenerProvider(new SlowStatementListener());
}

go deeper

for a junior

Recall that an ExecuteListener gets callbacks around each jOOQ statement and is registered on the Configuration, in Boot usually as a bean.

for a middle

Explain the callback order, ExecuteContext's sql(), params() and data(), and why set replaces the provider list while setAppending and beans keep Boot's translator.

for a senior

Diagnose a catch block for DuplicateKeyException that stopped matching after a listener change, and spot shared mutable state in a listener under concurrency.

for a principal

Decide which cross-cutting concerns a team implements as jOOQ listeners versus elsewhere, and keep their per-statement cost visible in reviews.

## What an ExecuteListener is `org.jooq.ExecuteListener` is jOOQ's hook into the lifecycle of **every statement** a `DSLContext` runs. All of its methods are `default` no-ops, so you override only the ones you need. Each receives an **`ExecuteContext`**, which exposes the rendered SQL (`sql()`), the bind values (`params()`), the `Connection`, the `SQLException` if one occurred (`sqlException()`), and a `data(key, value)` map that lives for that one execution. The callbacks fire in a fixed order around a statement: 1. `start`, before anything else happens. 2. `renderStart` and `renderEnd`, around turning the query into SQL text. 3. `prepareStart` / `prepareEnd` and `bindStart` / `bindEnd`, around creating the JDBC statement and binding values. 4. `executeStart` and `executeEnd`, around the JDBC execute call (with `outStart` / `outEnd` for procedure OUT parameters). 5. `fetchStart`, `resultStart`, `recordStart` / `recordEnd`, `resultEnd` and `fetchEnd`, while rows are read. 6. `end`, when the execution lifecycle finishes. On top of these, `exception` fires on a failure at any moment, and the listener may replace the exception that will be thrown by calling `ctx.exception(...)`; `warning` reports JDBC warnings. Typical uses are slow-statement logging, timing and metrics, auditing, and exception translation. ## How listeners are registered A `Configuration` does not hold listeners directly; it holds **`ExecuteListenerProvider`s**, and jOOQ calls each provider's `provide()` once per execution. `DefaultExecuteListenerProvider` wraps one listener instance and returns it every time, so that instance is **shared by every thread** using the configuration. Keep per-statement state, such as a start time, in `ctx.data(...)` rather than in fields, or register a provider lambda that returns a new listener for each execution. On the `Configuration` itself: - `set(ExecuteListener...)` and `set(ExecuteListenerProvider...)` **replace** the whole list; - `setAppending(...)` adds to the existing list; - `derive(...)` and `deriveAppending(...)` do the same on a copy, leaving the original alone. ## What Spring Boot already registers Spring Boot's jOOQ auto-configuration registers a `DefaultExecuteListenerProvider` with `@Order(0)` wrapping an **`ExceptionTranslatorExecuteListener`**. Its `exception` callback runs the `SQLException` through one of Spring's `SQLExceptionTranslator`s, error-code based for the dialects Spring knows by name (such as PostgreSQL, MySQL and MariaDB, H2, HSQLDB and Derby) and subclass based otherwise, and sets the translated exception as the one to throw. That is why a unique violation in a Boot application arrives as Spring's `DuplicateKeyException`, a `DataAccessException`, rather than as jOOQ's own `IntegrityConstraintViolationException`. Boot then builds the `Configuration` by collecting **every `ExecuteListenerProvider` bean**, in order, and only afterwards runs your `DefaultConfigurationCustomizer` beans against it. ## Adding your own without losing translation | Approach | Effect on Boot's translator | |---|---| | Declare an `ExecuteListenerProvider` bean, such as `new DefaultExecuteListenerProvider(listener)` | kept: Boot adds yours to the list | | `DefaultConfigurationCustomizer` calling `configuration.setAppending(listener)` | kept: yours is appended after Boot's | | `DefaultConfigurationCustomizer` calling `configuration.set(listener)` | **lost**: the list is replaced | | Declare your own `ExceptionTranslatorExecuteListener` bean | Boot's default backs off and yours is used instead, deliberately | The third row is the classic regression. A team adds a timing listener in a customizer with `set(...)`, every happy-path test passes, and weeks later the handler that catches `DuplicateKeyException` to report "coupon already used" stops matching, because the database error now surfaces as jOOQ's `IntegrityConstraintViolationException`. Nothing fails at start-up; only the error path changed. ## Picking the right jOOQ hook `ExecuteListener` is one of several listener types a `Configuration` can carry, and choosing the wrong one is a common design slip: - `ExecuteListener`: one statement's execution, from rendering to the end of fetching. - `TransactionListener`: `beginStart`, `commitEnd`, `rollbackStart` and their siblings around `transactionResult` blocks. - `RecordListener`: the store, insert, update, delete, load and refresh lifecycle of records. - `VisitListener`: query rendering, where a query can be transformed, for example to add a tenant filter. ## Practical rules - Keep listeners stateless, or store per-statement state in `ctx.data(...)`. - Keep callbacks cheap: they run on every statement the application executes. - Register through beans or `setAppending(...)`, never `set(...)`, in a Boot application. - jOOQ already adds its own debug `LoggerListener` when `Settings.executeLogging` is not switched off and that logger is at DEBUG, so a listener written only to print SQL is rarely needed.

  • How would you replace Spring Boot's exception translation for jOOQ with your own rules?
    Declare your own `ExceptionTranslatorExecuteListener` bean, for example from `ExceptionTranslatorExecuteListener.of(...)` with a function that picks an `SQLExceptionTranslator` per `ExecuteContext`. Boot's default is conditional on no such bean existing, so yours takes its place inside the provider Boot registers.
  • Why can a stateful ExecuteListener field produce wrong timings under load?
    `DefaultExecuteListenerProvider` returns the same listener for every execution, so concurrent statements on different threads overwrite each other's fields. Storing the start time in `ctx.data(...)` keeps it per execution, or a provider lambda can return a new listener each time.

  • configuration.set(listener) in a customizer adds a listener next to the existing ones.
  • Each statement gets its own listener instance, so instance fields are safe for timing.
  • Spring Boot leaves jOOQ exceptions untranslated unless you add a listener yourself.
  • ExecuteListener only fires for SELECT statements, not for inserts or updates.
  • Every ExecuteListener method must be implemented, so listeners are verbose.

Assumes jOOQ 3.21 with Spring Boot 4.1's jOOQ module, which registers ExceptionTranslatorExecuteListener and collects ExecuteListenerProvider beans.