skip to content

With jOOQ's code generator, how do you use forcedTypes to map a JSONB column and a varchar status column to your own types?

level: middleimportance: should knowfreq 45%

answer

  1. a userType plus a converter
  2. one regex for names, one for types
  3. first match wins, by priority
  4. enums by name, numbers by ordinal
  5. unknown values read as null

basics

~20 s

A forcedType names your userType plus a converter or binding, and regexes that select columns (includeExpression) and database types (includeTypes). The generator then types the column as your class, so records and queries carry it end to end.

solid answer

~30 s

For the JSONB column, a `<forcedType>` sets `<userType>` to your `PaymentDetails` class, `<converter>` to a `Converter<JSONB, PaymentDetails>` you write, `<includeExpression>payment\.details</includeExpression>` and optionally `<includeTypes>jsonb</includeTypes>`; when both regexes are given, both must match. For the status column, `<enumConverter>true</enumConverter>` with your enum as the user type makes the generator use jOOQ's `EnumConverter`, which maps strings by `name()` and numbers by ordinal. The generated field becomes `TableField<PaymentRecord, PaymentStatus>`. Only the first matching forced type applies, ordered by `<priority>` (higher first) and then declaration order. A forced type that matched nothing is logged as unused, and fails the run when `onUnused` is `FAIL`.

code

xml · 13 lines
xml
<forcedTypes>
  <forcedType>
    <userType>com.example.payments.PaymentDetails</userType>
    <converter>com.example.payments.PaymentDetailsConverter</converter>
    <includeExpression>payment\.details</includeExpression>
    <includeTypes>jsonb</includeTypes>
  </forcedType>
  <forcedType>
    <userType>com.example.payments.PaymentStatus</userType>
    <enumConverter>true</enumConverter>
    <includeExpression>payment\.status</includeExpression>
  </forcedType>
</forcedTypes>

go deeper

for a junior

Recall that forced types live in the generator configuration and pair a userType with a converter, matched to columns by a regex.

for a middle

Explain includeExpression versus includeTypes, the first-match rule ordered by priority, and how EnumConverter maps strings, literals and ordinals.

for a senior

Catch the silent failures: an unused forced type leaving the default type, an unknown enum value reading as null, an ordinal mapping broken by reordering.

for a principal

Set a team rule that unused forced types fail the build and that enums stored as text map by name, so schema and code drift surface early.

## Why forced types exist By default jOOQ's code generator maps each column to the Java type that matches its database type: `VARCHAR` to `String`, a PostgreSQL `jsonb` column to jOOQ's `org.jooq.JSONB` wrapper, `TIMESTAMP` to `LocalDateTime`. Services usually want their own types instead: a domain object for a JSON payload, a domain enum for a status column. A **forced type** tells the generator to declare a matched column with a different type, so the generated `TableField`, the record getter and setter, and every query using the field carry your type at compile time. Forced types live in `<database><forcedTypes>` as a list of `<forcedType>` entries. They are applied **while generating**, so changing one means regenerating. ## The parts of a `<forcedType>` A forced type has two halves: **what to become** and **what to match**. What to become: - `<userType>`: the fully qualified class the column should have. When it is set, `<name>` is ignored and a converter or binding is required. - `<converter>`: a class implementing jOOQ's `Converter<T, U>`, where `T` is the type jOOQ already reads and `U` is your user type. - `<enumConverter>true</enumConverter>`: use jOOQ's built-in `EnumConverter` for an enum user type. - `<lambdaConverter>` with `<from>` and `<to>` expressions: an inline converter, built with `Converter.ofNullable` by default. - `<binding>`: a class implementing `Binding`, for when jOOQ must change how the value is rendered and bound, not just what type it becomes. - `<name>`: alternatively, a built-in `SQLDataType` name to force, with no user type. What to match: - `<includeExpression>`: a regex over column (or parameter, attribute) names, whole-name match, tried against partially qualified names. - `<includeTypes>`: a regex over the database type name. - `<excludeExpression>` and `<excludeTypes>`: exclusions, tested before inclusions. - `<nullability>` and `<objectType>`: restrict to nullable or non-null columns, or to one kind of object, such as columns or routine parameters. If both `includeExpression` and `includeTypes` are present, **both must match**. ## Mapping the two payment columns ```xml <forcedTypes> <forcedType> <userType>com.example.payments.PaymentDetails</userType> <converter>com.example.payments.PaymentDetailsConverter</converter> <includeExpression>payment\.details</includeExpression> <includeTypes>jsonb</includeTypes> </forcedType> <forcedType> <userType>com.example.payments.PaymentStatus</userType> <enumConverter>true</enumConverter> <includeExpression>payment\.status</includeExpression> </forcedType> </forcedTypes> ``` The generated table class then declares `TableField<PaymentRecord, PaymentDetails> DETAILS` and `TableField<PaymentRecord, PaymentStatus> STATUS`. ## How `EnumConverter` behaves `EnumConverter` builds a lookup map from each enum constant to a database value: 1. For a numeric column it uses the constant's **ordinal**. 2. For an enum that implements jOOQ's `EnumType` it uses `getLiteral()`. 3. Otherwise it uses `name()`. Reading looks the value up in that map and returns **`null` for a value with no entry**, with no exception. Two consequences follow: a status another service added reads back as `null`, and reordering constants silently remaps a numeric column. A native database enum type is different again: jOOQ generates its own Java enum for it in an `enums` package, and a forced type is needed only to swap in your domain enum. ## Which forced type wins `AbstractDatabase.getConfiguredForcedType` walks the list and returns the **first** entry that matches; the code comment reads "Only the first matching <forcedType/> is applied". The list is sorted by `<priority>` (default `0`, higher first), and entries of equal priority keep declaration order. Overlapping entries do not combine. Forced types that matched nothing are reported after the run as unused. `onUnused` defaults to `LOG`; set it to `FAIL` to break the build, or set `ignoreUnused` on an entry that is expected to match nothing. ## Converter or binding | Need | Use | |---|---| | jOOQ already reads the database type; you want a different Java type | `converter`, `enumConverter` or `lambdaConverter` | | jOOQ must render a cast or call JDBC differently for the value | `binding` | A `Binding` has `sql`, `register`, `set` and `get` methods and supplies its own `converter()`. jOOQ itself registers bindings as forced types for some PostgreSQL extension types when its PostgreSQL extensions module is on the classpath (`forcedTypesForBuiltinDataTypeExtensions`, default `true`). The 3.21 schema also offers a `jsonConverter` flag that picks one of jOOQ's JSON converter extensions from the classpath. ## Checks before you rely on it - Confirm the generated field type, not just a green build: a forced type that matched nothing leaves the column as `JSONB` or `String`. - Read the unused-forced-type warning after every run; it is the quickest sign a regex is wrong. - Keep the converter null-safe: jOOQ's `Converter` contract says an implementation must be able to handle `null` values.

  • What does jOOQ's EnumConverter return when the status column holds a value the enum lacks?
    `null`. `EnumConverter` precomputes a map from each constant's `name()`, literal or ordinal to the constant, and `from` simply looks the value up, so an unknown value finds no entry and no exception is thrown. Add the constant before any writer can store the new value, or name your own converter that throws on an unknown value.
  • When does a jOOQ forced type need a Binding rather than a Converter?
    When the Java type is not the only problem. A `Converter` sits on top of a database type jOOQ already binds, such as `JSONB`. A `Binding` takes over SQL rendering and JDBC calls through its `sql`, `set` and `get` methods, which you need for a vendor type that requires a cast or a JDBC call jOOQ does not make on its own.

  • Forced types are applied by DSLContext at runtime, so no regeneration is needed.
  • Every matching forcedType applies in turn, each refining the one before.
  • EnumConverter throws an exception when the column holds an unknown value.
  • includeExpression matches any column whose name merely contains the pattern.
  • A Binding is just another name for a Converter.

Assumes jOOQ 3.21's code generator configuration schema, whose forcedType element offers enumConverter, lambdaConverter and jsonConverter beside converter and binding.