How to statically override defaults in jOOQ – Java, SQL and jOOQ.

When configuring a Configuration of the jOOQ runtime environment, you can add an explicit Settings instance, which contains a set of useful flags that change the SQL build behavior of jOOQ and other things.

Examples of parameters:

… and much more. Your setup will likely include an explicit parameter instance where you will have fine control, perhaps even by runtime, over these flags. But in many cases the default settings are applied, which include, for example, quoting all IDs.

How to override the default

Recently, a customer had difficulty using jOOQ on an older version of Informix, which could not handle identifiers in quotes in the FROM clause. The code generator produced this problematic SQL statement:

select distinct trim("informix"."systables"."owner")
from "informix"."systables"
where "informix"."systables"."owner" in ('<schema name>')

It would have worked:

select distinct trim("informix"."systables"."owner")
from informix.systables
where "informix"."systables"."owner" in ('<schema name>')

Fortunately, the default can be overridden and we can specify not to quote any identifier in jOOQ by specifying an instance of parameters:

Programmatic

We can set this explicitly on a configuration

new Settings().withRenderNameStyle(RenderNameStyle.AS_IS);

Configurative

We can put this XML file in the classpath in “/jooq-settings.xml” or direct jOOQ there via the system property “-Dorg.jooq.settings”:

<settings>
  <renderNameStyle>AS_IS</renderNameStyle>
</settings>

XML must implement this schema: https://www.jooq.org/xsd/jooq-runtime-3.11.2.xsd (or a newer version)

So the SQL which will now be generated with such a jooq-settings.xml file on the classpath is as follows:

select distinct trim(informix.systables.owner)
from informix.systables
where informix.systables.owner in ('<schema name>')

Do you also want to get rid of the schema?

<settings>
  <renderNameStyle>AS_IS</renderNameStyle>
  <renderSchema>false</renderSchema>
</settings>

You now get this SQL:

select distinct trim(systables.owner)
from systables
where systables.owner in ('<schema name>')