In jOOQ, what is a DSLContext, and what does the SQLDialect you configure on it decide?
answer
- entry point for build and run
- a Configuration underneath
- connection source, dialect, Settings
- one query, different SQL text
- open-source edition: families only
basics
~20 sDSLContext is jOOQ's entry point: it wraps a Configuration holding the connection source, the SQLDialect and the Settings, then builds and executes queries. The SQLDialect decides how each query renders as SQL for the target database.
solid answer
~40 s`DSLContext` is what `DSL.using(dataSource, SQLDialect.POSTGRES)` returns: a facade over a `Configuration` that holds the connection source, the `SQLDialect` and the `Settings`. Queries built from it are attached to that configuration, so `fetch()` or `execute()` runs them on its connection. `DSL.using(SQLDialect.POSTGRES)` with no connection can only render SQL; executing a query from it throws `DetachedException`. The dialect drives rendering: identifier quoting, paging syntax, and emulation of supported constructs the database lacks natively, so one query object can produce different SQL for PostgreSQL and MySQL. `SQLDialect.DEFAULT` is a fallback, not meant for a real database. In jOOQ 3.21 the open-source edition ships only dialect families for open-source databases; versioned dialects and commercial databases need a commercial distribution.
code
java · 12 linesimport org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import static com.example.db.Tables.CUSTOMER;
DSLContext pg = DSL.using(SQLDialect.POSTGRES);
DSLContext my = DSL.using(SQLDialect.MYSQL);
var byEmail = CUSTOMER.EMAIL.eq("[email protected]");
String forPostgres = pg.selectFrom(CUSTOMER).where(byEmail).limit(10).getSQL();
String forMysql = my.selectFrom(CUSTOMER).where(byEmail).limit(10).getSQL();go deeper
Recall that DSLContext comes from DSL.using and carries a connection source, a SQLDialect and Settings, and that queries built from it can execute directly.
Explain that the dialect changes the rendered SQL text, quoting, paging and emulations, and that a render-only context throws DetachedException on execution.
Show you would catch a missing or wrong dialect early, know why a dialect seems absent from the enum (edition on the classpath), and derive Settings per code path.
Weigh the open-source families against a commercial distribution when the team needs versioned dialects or a commercial database, and fix one dialect per deployment.
## What a DSLContext is In jOOQ almost every query begins at a **`DSLContext`**. It is a facade over a **`Configuration`**, the object that carries everything jOOQ needs to turn a query into SQL and run it: - a **connection source**: a JDBC `Connection`, a `DataSource`, a `ConnectionProvider`, or an R2DBC `ConnectionFactory`; - an **`SQLDialect`**: which database the SQL is rendered for; - a **`Settings`** object: rendering and execution options; - optional extras such as execute listeners. Queries you build from a `DSLContext` (`ctx.select(...)`, `ctx.selectFrom(...)`, `ctx.update(...)`) are **attached** to its configuration. That is why you can call `fetch()` or `execute()` on them directly: the query already knows which connection to use and which dialect to render. ## Creating one The static factory `DSL.using(...)` has overloads for each connection source, each optionally taking `Settings`: ```java import org.jooq.DSLContext; import org.jooq.SQLDialect; import org.jooq.conf.Settings; import org.jooq.impl.DSL; DSLContext ctx = DSL.using(dataSource, SQLDialect.POSTGRES, new Settings().withRenderFormatted(true)); // Render-only context: no connection at all DSLContext renderer = DSL.using(SQLDialect.MYSQL); String sql = renderer.select(DSL.inline(1)).getSQL(); ``` A context created with only a dialect is **detached**. It renders SQL perfectly well, which is handy in tests or tooling, but executing a query from it throws `DetachedException` ("Cannot execute query. No JDBC Connection configured"). jOOQ never guesses a connection. ## What SQLDialect controls jOOQ builds an in-memory model of the query and renders it per dialect. The `SQLDialect` decides, among other things: 1. **Identifier quoting and casing**: how table and column names are quoted for that database. 2. **Clause syntax**: for example how paging (`limit` / `offset`) is spelled. 3. **Emulation**: when a construct jOOQ supports is missing natively on the target, jOOQ may render an equivalent form instead. 4. **Bind variable handling**: details such as how many bind values a statement may carry. Each DSL method carries a `@Support` annotation listing the dialects it works on, so you can see in the IDE whether a construct is available for your database. `SQLDialect.DEFAULT` is chosen when nothing else is configured; its Javadoc says it is not intended for any actual database, because it may mix dialect-specific behaviour. ## Dialect families and editions The enum is not the same in every distribution of jOOQ. | Distribution | What `SQLDialect` contains | |---|---| | Open-source edition (3.21) | Dialect **families** for open-source databases only, such as `POSTGRES`, `MYSQL`, `MARIADB`, `SQLITE`, `H2`, `DUCKDB` | | Commercial distributions | The families plus **versioned** dialects (such as `POSTGRES_14`) and commercial databases | In the open-source edition a family behaves like the **latest** supported version of that database; in 3.21 `POSTGRES` behaves like the versioned PostgreSQL 18 dialect. If a dialect you read about is missing from your enum, the usual cause is the wrong jOOQ edition on the classpath, often pulled in transitively by a framework's dependency management. ## How a query becomes SQL A jOOQ query is not a string while you build it. It is a tree of objects: a table, the fields you selected, the conditions, the ordering. Only when the query runs, or when you ask for its SQL, does jOOQ walk that tree with a rendering context configured for the dialect. Three `DSLContext` methods expose the steps directly: - `ctx.render(query)` returns the SQL text with placeholders for bind values; - `ctx.extractBindValues(query)` returns the values that will be bound; - `ctx.renderInlined(query)` returns the SQL with values written in as literals, for reading or logging. Because rendering happens late, the same query object can be rendered for different dialects, and a `Settings` change on a derived configuration changes the SQL without touching the code that built the query. In a typical service the application creates one `DSLContext` per data source and passes it to its data-access code, rather than calling `DSL.using` for each query. ## Settings: the other half of rendering The dialect says *which* SQL; `Settings` says *how*. It holds options such as `statementType` (bind values or inlined values), `renderFormatted` (pretty-printed SQL, off by default), `fetchSize`, `executeLogging` and `renderQuotedNames`. You pass `Settings` to `DSL.using`, or derive a variant with `Configuration.derive(Settings)` for one code path without touching the shared context. ## Mistakes worth avoiding - Leaving the dialect unset and rendering with `DEFAULT` against a real database. - Expecting a render-only context to execute. - Assuming the open-source enum has a versioned constant like `POSTGRES_14`. - Treating the dialect as a connection setting: it changes the SQL text, not where it runs.
- In jOOQ, can you build a query without a DSLContext and run it later?Yes. Static `DSL.select(...)` builds an unattached query. You can execute it with `ctx.fetch(query)` or `ctx.execute(query)`, or attach it to a configuration first. The dialect used for rendering is the one of the context that finally executes it, so the same query object can be rendered for different databases.
- In jOOQ, how do you change one setting for a single code path without changing the shared DSLContext?Derive a new configuration: `ctx.configuration().derive(newSettings).dsl()` returns a context with the same connection source and dialect but different `Settings`. The shared context stays untouched, so an export path can, for example, carry its own `fetchSize` without affecting the request path.
- A DSLContext is a database connection you must close after each query
- The SQLDialect only matters for codegen, not for runtime SQL
- DSL.using(SQLDialect) alone can execute queries using a default connection
- SQLDialect.DEFAULT is a safe portable choice for production
- The open-source jOOQ enum includes versioned dialects like POSTGRES_14
Assumes jOOQ 3.21: the open-source SQLDialect enum holds only open-source dialect families, and POSTGRES behaves like the PostgreSQL 18 dialect.