In jOOQ, what do a Configuration and its ConnectionProvider do, and where does a DSLContext get its JDBC connection?
answer
- one object behind every DSLContext
- dialect, settings, providers, listeners
- acquire once per execution, release once
- who closes the connection afterwards
- Boot: a proxy that sees Spring transactions
basics
~20 sA 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 sA `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 linesimport 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
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.
Explain acquire and release per execution, and contrast DefaultConnectionProvider, which never closes your connection, with DataSourceConnectionProvider, which returns it to the pool each time.
Show why Boot wraps the DataSource in a TransactionAwareDataSourceProxy, and how a hand-built context on the raw pool silently escapes Spring transactions.
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.