skip to content

DSLContext Wiring & Transactions

A DSLContext runs on a Configuration with a ConnectionProvider, and Spring Boot wires one into Spring's transactions. Interviewers ask if transactionResult and @Transactional share a connection.

on this pageshow

explore

questions

5

In jOOQ, what do a Configuration and its ConnectionProvider do, and where does a DSLContext get its JDBC connection?

level: juniorimportance: must knowfreq 50%

answer

  1. one object behind every DSLContext
  2. dialect, settings, providers, listeners
  3. acquire once per execution, release once
  4. who closes the connection afterwards
  5. Boot: a proxy that sees Spring transactions

basics

~20 s

A jOOQ Configuration bundles the SQLDialect, Settings, ConnectionProvider, TransactionProvider and listeners that a DSLContext runs on. Its ConnectionProvider hands out a JDBC Connection through acquire() once per statement execution and takes it back through release().

solid answer

~40 s

A `DSLContext` is a thin front over a `Configuration`, which bundles the `SQLDialect`, the `Settings`, a `ConnectionProvider`, a `TransactionProvider` and listener providers such as `ExecuteListenerProvider`. jOOQ calls `ConnectionProvider.acquire()` once per statement execution and `release()` exactly once afterwards, so the provider decides who owns the connection. `DSL.using(connection)` wraps it in a `DefaultConnectionProvider` whose `release()` does nothing: you commit and close it yourself. `DSL.using(dataSource, dialect)` uses a `DataSourceConnectionProvider`, which calls `getConnection()` per execution and `close()` on release, handing the connection back to the pool. Spring Boot builds one `Configuration` and one `DSLContext` bean for you, with a `DataSourceConnectionProvider` over a `TransactionAwareDataSourceProxy`, so a statement inside a Spring transaction runs on that transaction's connection.

code

java · 13 lines
java
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;
import org.jooq.impl.DSL;
import org.jooq.impl.DataSourceConnectionProvider;
import org.jooq.impl.DefaultConfiguration;

DefaultConfiguration configuration = new DefaultConfiguration();
configuration.set(SQLDialect.POSTGRES);
configuration.set(new DataSourceConnectionProvider(dataSource));
configuration.set(new Settings().withExecuteLogging(false));

DSLContext dsl = DSL.using(configuration);

go deeper

for a junior

Recall that a DSLContext sits on a Configuration holding the dialect, settings, connection provider and transaction provider, and that Spring Boot hands you a ready DSLContext bean.

for a middle

Explain acquire and release per execution, and contrast DefaultConnectionProvider, which never closes your connection, with DataSourceConnectionProvider, which returns it to the pool each time.

for a senior

Show why Boot wraps the DataSource in a TransactionAwareDataSourceProxy, and how a hand-built context on the raw pool silently escapes Spring transactions.

for a principal

Weigh one shared Configuration bean against derived copies per use, and decide which parts a team may customise through beans or customizers versus never touch at runtime.

## What a Configuration is In jOOQ, every query you build and run through a **`DSLContext`** executes against a **`Configuration`**: the object that holds everything the query needs apart from its own SQL. The `DSLContext` is little more than a convenient front over it; `dsl.configuration()` returns it, and `DSL.using(configuration)` wraps one in a new `DSLContext`. The parts you meet most often: - **`SQLDialect`**: which database's SQL to render, such as `SQLDialect.POSTGRES` or `SQLDialect.H2`. - **`Settings`**: rendering and execution options, such as bind versus inlined values, name quoting and logging. - **`ConnectionProvider`**: where JDBC connections come from and where they go back to. - **`TransactionProvider`**: what `transactionResult(...)` and `transaction(...)` actually do. - **`ExecuteListenerProvider`s**: hooks that observe or alter every statement's lifecycle. - **`RecordMapperProvider`** and its relatives: how rows are mapped into your own types. ## The ConnectionProvider contract `ConnectionProvider` is a two-method interface: 1. `acquire()` returns a `java.sql.Connection`. jOOQ calls it **exactly once per execution lifecycle**, that is once per `ExecuteContext`, which in practice means once per statement run. 2. `release(Connection)` hands it back. jOOQ guarantees that every acquired connection is released **exactly once**. The interface leaves the policy open on purpose: an implementation may return the same connection every time, the same one per thread, the same one per transaction, or a fresh one per call. If `acquire()` returns `null`, as jOOQ's `NoConnectionProvider` does when a context was created without a connection or data source, the context can still render SQL, but executing a statement fails with a `DetachedException`. ## The providers you will actually use | How the context is built | Provider | Who owns the connection | |---|---|---| | `DSL.using(connection)` | `DefaultConnectionProvider` | You: its `release()` is empty, so you commit and close | | `DSL.using(dataSource, dialect)` | `DataSourceConnectionProvider` | The pool: `getConnection()` per execution, `close()` on release | | Spring Boot's auto-configured bean | `DataSourceConnectionProvider` over a `TransactionAwareDataSourceProxy` | Spring while a transaction is active, the pool otherwise | The middle row has a consequence people miss: with a plain `DataSourceConnectionProvider`, two statements in a row can run on **two different pooled connections**, so they are not one transaction unless something binds them together. jOOQ's own `transactionResult` does that by acquiring one connection for the whole block; Spring does it by binding a connection to the current thread. ## How Spring Boot wires it With the jOOQ starter on the classpath and a `DataSource` bean present, Spring Boot's jOOQ auto-configuration: 1. Creates a `DataSourceConnectionProvider` around a **`TransactionAwareDataSourceProxy`** of your `DataSource`. The proxy asks Spring's `DataSourceUtils` for the connection, so inside an active Spring transaction jOOQ receives that transaction's connection, and jOOQ's `close()` on release does not end it. 2. Creates a `SpringTransactionProvider` when a `PlatformTransactionManager` bean exists, so `transactionResult` goes through Spring's transaction manager. 3. Registers an execute listener that translates `SQLException`s into Spring's `DataAccessException` hierarchy. 4. Builds a `DefaultConfiguration` with the dialect from `spring.jooq.sql-dialect` (detected from the `DataSource` when the property is unset), the providers above and any `Settings` bean, then applies every `DefaultConfigurationCustomizer` bean. 5. Exposes a `DefaultDSLContext` bean that you inject as `DSLContext`. The connection provider, transaction provider, `Configuration` and `DSLContext` beans each back off when you declare your own bean of that type, so a custom one replaces Boot's rather than competing with it. ## Sharing a Configuration safely A `Configuration` may be long-lived and shared across threads, as Boot's singleton is, provided nobody calls its `set(...)` methods after start-up and nobody mutates its `Settings`. For a local variation, such as another dialect for one report or an extra listener for one batch job, call one of the `derive(...)` methods: each returns a modified copy and leaves the shared configuration untouched. ## Where people go wrong - Building a second context with `DSL.using(dataSource, dialect)` inside a Boot application: it talks to the raw pool, skips the transaction-aware proxy and the Spring transaction provider, and so runs outside `@Transactional` work. - Passing a connection to `DSL.using(connection)` and assuming jOOQ will close or commit it. - Calling `set(...)` on Boot's shared configuration at runtime to change one query, instead of deriving a copy. ## What to say in an interview - The `DSLContext` is the API; the `Configuration` is the state behind it. - The `ConnectionProvider` is asked for a connection per execution and decides who closes it. - Under Spring Boot, the connection comes through a transaction-aware proxy, which is why jOOQ statements join `@Transactional` work without extra code.

  • What happens if you build a jOOQ DSLContext with only a SQLDialect and try to execute a query?
    It can still render SQL, but it has no connection: the configuration falls back to jOOQ's `NoConnectionProvider`, whose `acquire()` returns `null`, and executing throws a `DetachedException`. Such a context is useful for producing SQL text that other code will run.
  • Why is Boot's DataSourceConnectionProvider wrapped in a TransactionAwareDataSourceProxy instead of the raw DataSource?
    A raw pool hands out any free connection, so jOOQ would run outside the connection Spring bound to the thread for `@Transactional`. The proxy delegates to Spring's `DataSourceUtils`, returning the transaction's connection while one is active and making `close()` harmless to it.

  • The DSLContext opens one JDBC connection at creation and keeps it for its lifetime.
  • DSL.using(connection) closes the connection you passed in after each query.
  • Spring Boot's DSLContext talks to the raw DataSource, so it cannot join @Transactional work.
  • A Configuration is only the SQL dialect; connections are configured elsewhere.
  • Calling set() on a shared Configuration at runtime is a safe way to tweak one query.
open as a page

With Spring Boot's jOOQ auto-configuration, an @Transactional order method writes rows and then calls dsl.transactionResult; do they share a connection, and what commits when?

level: seniorimportance: must knowfreq 52%

basics

~10 s

Yes, one connection: Boot's TransactionAwareDataSourceProxy hands jOOQ the connection Spring bound to the thread. transactionResult runs on SpringTransactionProvider with PROPAGATION_NESTED, so it becomes a savepoint, and only the outer @Transactional method commits.

open as a page

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%

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.

open as a page

Outside Spring, how does jOOQ's DSLContext.transactionResult commit, roll back and nest, and why must the lambda use its Configuration argument?

level: middleimportance: should knowfreq 42%

basics

~20 s

Without Spring, transactionResult runs on jOOQ's DefaultTransactionProvider: one connection, auto-commit off, commit when the lambda returns, rollback on any Throwable, savepoints for nested calls. Statements must use the lambda's Configuration, or they run on a different connection.

open as a page

As the lead of a Spring Boot service using jOOQ, would you standardise on @Transactional or on dsl.transactionResult, and how would you enforce it?

level: principalimportance: should knowfreq 38%

basics

~20 s

Pick one model per codebase. In a Spring Boot service, @Transactional usually owns boundaries and transactionResult, which Boot turns into a nested savepoint, is allowed only as a deliberate savepoint step; enforce it with an architecture test and failure-path integration tests.

open as a page