Available in versions: Dev (3.20) | Latest (3.19) | 3.18

This is experimental functionality, and as such subject to change. Use at your own risk!

Transform patterns

Applies to ❌ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The SPI methods handling these events are transformPattern(). This diagnostic depends entirely on the transform patterns feature.

A variety of patterns can be recognised and transformed into something better for any of these reasons:

  • The resulting expression is more "normalised". This doesn't objectively affect the query execution on most RDBMS and is purely a stylistic improvement.
  • The resulting expression is simpler. This also doesn't objectively affect the query execution on most RDBMS.
  • The resulting expression removes unnecessary parts. Unless the RDBMS can transform such expressions itself, this is likely to improve query execution objectively.

Why is it bad?

Expressions that aren't normalised and simplified this way generate too much work in your RDBMS:

  • In the case of there being unnecessary parts, some RDBMS may perform too much unnecessary work, if they do not also implement the same transformation as jOOQ does.
  • Similar expressions may be equivalent, but not identical in syntax. As such, most RDBMS will have to parse the statement twice, resulting in duplicate statements. The duplicate statement diagnostic also detects these cases, but doesn't report on the exact reason why two statements are duplicate. This diagnostic here helps look into details.

An example is given here:

// A custom DiagnosticsListener SPI implementation
class TransformPatterns implements DiagnosticsListener {
    public void transformPattern(DiagnosticsContext ctx) {
        System.out.println("Pattern transformation is possible from: " + ctx.part() + " to: " + ctx.transformedPart());

And then:

// Configuration is configured with the target DataSource, SQLDialect, etc. for instance Oracle.
try (Connection c = DSL.using(configuration.derive(new TransformPatterns()))
     Statement s = c.createStatement()) {

    try (ResultSet rs = s.executeQuery("SELECT * FROM author WHERE x = 1 OR x > 1")) {
        // ..


Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo