When configuring a jOOQ runtime Configuration, you may add an explicit Settings
instance, which contains a set of useful flags that change jOOQ’s SQL generation behaviour and other things.
Example settings include:
- Object qualification (generate schema.table.column or just table.column)
- Identifier style (to quote or not to quote)
- Keyword style (UPPER, lower, or Pascal Case for the conoisseur)
- Whether to run static or prepared statements
- Whether to turn on execute logging
… and much more. Your configuration will probably include an explicit Settings instance where you have fine grained, perhaps even per-execution control over these flags. But in many cases, the default settings are applied, which include, for example, quoting all identifiers.
How to override the default
Recently, a client had trouble using jOOQ on an older Informix version, which couldn’t handle quoted identifiers 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>')
This would have worked:
select distinct trim("informix"."systables"."owner") from informix.systables where "informix"."systables"."owner" in ('<schema name>')
Luckily, the default can be overridden and we can specify not to quote any identifiers throughout jOOQ by specifying a Settings instance:
Programmatic
We can set this explicitly on a Configuration
new Settings().withRenderNameStyle(RenderNameStyle.AS_IS);
Configurative
We can put this XML file on the class path at “/jooq-settings.xml” or direct jOOQ to it via the “-Dorg.jooq.settings” system property:
<settings> <renderNameStyle>AS_IS</renderNameStyle> </settings>
The XML must implement this schema: https://www.jooq.org/xsd/jooq-runtime-3.11.2.xsd (or a newer version of it)
So, the SQL that will now be generated with such a jooq-settings.xml file on the classpath is this:
select distinct trim(informix.systables.owner) from informix.systables where informix.systables.owner in ('<schema name>')
Want to get rid of the schema as well?
<settings> <renderNameStyle>AS_IS</renderNameStyle> <renderSchema>false</renderSchema> </settings>
You’re now getting this SQL:
select distinct trim(systables.owner) from systables where systables.owner in ('<schema name>')