skip to content

As the lead on a jOOQ codebase, what rule would you set for plain SQL templates such as DSL.field(String, Object...), and how would you enforce it?

level: principalimportance: should knowfreq 35%

answer

  1. the escape hatch has an annotation
  2. ? binds, {0} takes query parts
  3. constant templates only
  4. a typed DataType on every template
  5. a checker with an allow scope

basics

~10 s

Allow jOOQ plain SQL only as constant templates with bound or {n} arguments and an explicit DataType, inside a small reviewed module, and enforce it with jOOQ-checker's PlainSQLChecker plus @Allow.PlainSQL where it is permitted.

solid answer

~40 s

Plain SQL is every jOOQ API marked `@PlainSQL`: `DSL.field`, `DSL.condition`, `DSL.table` and `DSL.sql` with a `String`, `DSLContext.fetch(String, Object...)` and `resultQuery`, and the `String` overload of `DSL.function`. Templates take `?` with `Object...` bindings, which are bound, and `{0}`-style placeholders filled with query parts such as fields or `DSL.val(x)`. The risk is a template string built at runtime, which reopens injection and loses type, rename and dialect safety. My rule: plain SQL lives in one reviewed package of typed wrappers; templates are compile-time constants; every argument goes through a placeholder; identifiers come from `DSL.name` over an allow-list; every template gets a `DataType`. Prefer non-plain alternatives first, such as `DSL.function(Name, Class, Field...)`. Enforce with `PlainSQLChecker` from jOOQ-checker, which fails compilation outside scopes annotated `@Allow.PlainSQL`. A total ban tends to push people to raw JDBC, which is worse.

code

java · 15 lines
java
package com.example.search.sql;

import org.jooq.Allow;
import org.jooq.Field;
import org.jooq.impl.DSL;
import org.jooq.impl.SQLDataType;

@Allow.PlainSQL
public final class VendorFunctions {
    private static final String SIMILARITY = "similarity({0}, {1})";

    public static Field<Double> similarity(Field<String> column, String term) {
        return DSL.field(SIMILARITY, SQLDataType.DOUBLE, column, DSL.val(term));
    }
}

go deeper

for a junior

Recall that DSL.field(String, ...) and similar methods embed raw SQL text, and that values must go through ? or DSL.val, never concatenation.

for a middle

Explain ? bindings versus {n} query-part placeholders, and why a template needs an explicit DataType.

for a senior

Spot runtime-built templates, string-named functions and missing DataTypes in review, and replace them with typed alternatives.

for a principal

Choose between a ban, a checked allow-scope and review-only, and defend the boundary with jOOQ-checker, ownership and rendering tests.

## What counts as plain SQL in jOOQ jOOQ's typed DSL cannot express every vendor function or syntax, so it offers an escape hatch: **plain SQL**, SQL text you write as a string and embed in a typed query. These methods are annotated **`@PlainSQL`**, and their Javadoc repeats one warning: when inserting plain SQL into jOOQ objects, you must guarantee syntax integrity and may create the possibility of SQL injection. The surface is wider than `DSL.field`: - `DSL.field(String, ...)`, `DSL.condition(String, ...)`, `DSL.table(String, ...)`, `DSL.sql(String, ...)` - `DSLContext.fetch(String, Object...)`, `resultQuery(String, Object...)`, `execute(String, Object...)`, `fetchLazy(String, Object...)` - `DSL.function(String, Class, Field...)`: the overload taking a function **name as a string** is plain SQL too ## How templates take arguments | Placeholder | Arguments | Rendered as | |---|---|---| | `?` | `Object... bindings` such as `1`, `"DE"` | Bind values | | `{0}`, `{1}` | `QueryPart... parts` such as `CUSTOMER.NAME`, `DSL.val(term)`, `DSL.name("x")` | Each part rendered in place, binds staying binds | ```java import org.jooq.Field; import org.jooq.impl.DSL; import org.jooq.impl.SQLDataType; import static com.example.db.Tables.CUSTOMER; static Field<Double> similarity(Field<String> column, String term) { return DSL.field("similarity({0}, {1})", SQLDataType.DOUBLE, column, DSL.val(term)); } ``` The template is a constant; the column is a generated field; the term is a bind value. Nothing from a request becomes SQL text. ## What goes wrong without a rule 1. **Injection**: someone concatenates a request value into the template string. 2. **Lost type information**: `DSL.field(String, Object...)` returns `Field<Object>`; the Javadoc notes that without a `DataType` some drivers struggle to bind nulls and you lose compile-time type safety. 3. **Lost rename safety**: a column named inside a string is invisible to the generated classes, so renaming it breaks at runtime, not at compile time. 4. **Lost portability**: the dialect cannot rewrite text it does not understand. ## The rule I would set - **Try the typed API first.** A vendor function rarely needs plain SQL: `DSL.function(DSL.name("similarity"), Double.class, CUSTOMER.NAME, DSL.val(term))` is not annotated `@PlainSQL`. - **Constant templates only.** A template is a `static final` string or literal, never built from variables. - **Every argument through a placeholder.** Values through `?` or `DSL.val`, columns as generated fields, identifiers through `DSL.name` or `DSL.quotedName` after an allow-list check. - **A `DataType` on every template**, from `SQLDataType` or the generated field's type. - **One home.** Plain SQL lives in a small package of typed wrapper methods with named code owners, each with a test that renders its SQL. ## Enforcing it The **jOOQ-checker** module ships `PlainSQLChecker`, a checker that disallows use of `@PlainSQL` API except where allowed explicitly. Scopes are opened with **`@Allow.PlainSQL`** on a method, type or package. Put that annotation on the wrapper package only, and any plain SQL call elsewhere fails the build. Pair it with ownership on that package and a review checklist: constant template, placeholders, `DataType`, a rendering test. ## Rolling the rule out on an existing codebase A rule announced on a codebase with hundreds of plain SQL calls only works if it starts from an inventory. 1. Run the checker once to list every current call site. 2. Mark each legacy site with `@Allow.PlainSQL` at **method** scope, so the build passes and every exception is visible and searchable. 3. Classify the sites: replaceable by the typed DSL, worth a wrapper in the shared package, or genuinely one-off. 4. Shrink the list on a schedule, and reject any new `@Allow.PlainSQL` outside the wrapper package in review. The count of remaining annotations becomes a measurable number the team can watch fall. ## The trade-offs a lead weighs | Option | Gain | Cost | |---|---|---| | Total ban | Simplest to audit | Pushes vendor features into raw JDBC, outside jOOQ entirely | | Checked allow-scope (above) | Escape hatch with a reviewed boundary | Annotation and ownership upkeep | | Review-only, no tooling | No build changes | Drifts as the team grows | The checked allow-scope usually wins: it keeps the escape hatch inside jOOQ, where bind values, dialect rendering and execute listeners still apply, while making each use visible. Revisit the wrapper package periodically; a wrapper that a newer typed API can replace should be deleted.

  • In a jOOQ plain SQL template, how do you insert a table or column name chosen at runtime safely?
    Check the requested name against an allow-list first, then render it with `DSL.name(...)` or `DSL.quotedName(...)` passed into a `{n}` placeholder, never concatenated into the template. Better still, map the allowed key to a generated `Table` or `Field` and pass that query part instead.
  • In jOOQ, what does a plain SQL template lose compared with the typed DSL, even when it is injection-safe?
    Without a `DataType` it yields `Field<Object>`, with weaker null binding and no compile-time type checks. Columns named inside the string escape the generated classes, so renames fail at runtime. And the dialect cannot rewrite text it does not parse, so the template is tied to one database's syntax.

  • Plain SQL is safe as long as it goes through DSL.field
  • DSL.function with a string name is not plain SQL
  • {0} placeholders inline argument values as literals
  • Banning plain SQL outright has no downside
  • A Field<Object> from a template is as type-safe as a generated field