How to Statically Override the Default Settings in jOOQ

Spread the love
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

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 (”)

This would have worked:

select distinct trim(“informix”.”systables”.”owner”)
from informix.systables
where “informix”.”systables”.”owner” in (”)

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:

AS_IS

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 (”)

Want to get rid of the schema as well?

AS_IS
false

You’re now getting this SQL:

select distinct trim(systables.owner)
from systables
where systables.owner in (”)

X ITM Cloud News

Emily

Next Post

The Difference Between SQL’s JOIN .. ON Clause and the Where Clause

Sun Nov 24 , 2019
Spread the love          A question that is frequently occurring among my SQL training‘s participants is: What’s the difference between putting a predicate in the JOIN .. ON clause and the WHERE clause? I can definitely see how that’s confusing some people, as there seems to be no difference at first sight, […]
X- ITM

Cloud Computing – Consultancy – Development – Hosting – APIs – Legacy Systems

X-ITM Technology helps our customers across the entire enterprise technology stack with differentiated industry solutions. We modernize IT, optimize data architectures, and make everything secure, scalable and orchestrated across public, private and hybrid clouds.

This image has an empty alt attribute; its file name is x-itmdc.jpg

The enterprise technology stack includes ITO; Cloud and Security Services; Applications and Industry IP; Data, Analytics and Engineering Services; and Advisory.

Watch an animation of  X-ITM‘s Enterprise Technology Stack

We combine years of experience running mission-critical systems with the latest digital innovations to deliver better business outcomes and new levels of performance, competitiveness and experiences for our customers and their stakeholders.

X-ITM invests in three key drivers of growth: People, Customers and Operational Execution.

The company’s global scale, talent and innovation platforms serve 6,000 private and public-sector clients in 70 countries.

X-ITM’s extensive partner network helps drive collaboration and leverage technology independence. The company has established more than 200 industry-leading global Partner Network relationships, including 15 strategic partners: Amazon Web Services, AT&T, Dell Technologies, Google Cloud, HCL, HP, HPE, IBM, Micro Focus, Microsoft, Oracle, PwC, SAP, ServiceNow and VMware

.

X ITM