The jOOQ User Manual SQL building The model API Replacement

previous : next

Replacement

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

A very powerful way to transform your SQL is to replace specific org.jooq.QueryPart elements in any expression tree by something else using the QueryPart.replace() API. This API treats the expression tree as a persistent data structure, i.e. the resulting tree may consist of parts of the existing tree, but the existing tree is not modified.

Let's assume you wish to implement an optimisation engine that removes redundant SQL clauses. For example, an expression NOT(NOT(p)) can be replaced by p in standard SQL (it may not be the exact same thing in some "clever" dialects without standard BOOLEAN type support):

// Contains redundant operators
Condition c = not(not(BOOK.ID.eq(1)));
System.out.println(c);
System.out.println(c.$replace(q ->
    q instanceof QOM.Not n1 && n1.$arg1() instanceof QOM.Not n2
        ? n2.$arg1()
        : q
));

The above prints:

not (not ("BOOK"."ID" = 1))
"BOOK"."ID" = 1

The replacement algorithm will attempt to run the replacement function recursively on your tree until it no longer affects the tree. This means two things:

• You can implement all of your replacement logic in a single function, for various rules. The order of application of the rules is the one you define in your function.
• The algorithm stops only when no more rules apply. If two rules turn A > B and B > A, then the algorithm may never stop.

Here's a more complex example that logs the replacements with println() calls:

// Contains redundant operators
Condition c = not(not(not(BOOK.ID.ne(1))));
QueryPart result = c.$replace(q -> {
    if (q instanceof QOM.Not n1 && n1.$arg1() instanceof QOM.Not n2) {
        System.out.println("Replacing NOT(NOT(p)) by NOT(p): " + q);
        return n2.$arg1();
    }
    else if (q instanceof QOM.Not n1 && n1.$arg1() instanceof QOM.Ne<?> n2) {
        System.out.println("Replacing NOT(x != y) by x = y: " + q);
        return n2.$arg1().eq((Field) n2.$arg2());
    }

    return q;
}));
System.out.println("Result: " + result);

The output is:

Replacing NOT(x != y) by x = y: not ("BOOK"."ID" <> 1)
Replacing NOT(NOT(p)) by NOT(p): not (not ("BOOK"."ID" = 1))
Result: "BOOK"."ID" = 1

As you can see:

• The replacement function is invoked several times.
• The second invocation can work on the result of the first invocation, where the NOT (x != y) predicate has already been improved.
• The replacement works recursively, depth first, and bottom up.
• It stops when no more replacements take place.

This obviously also works when you use jOOQ's parser, and is extremely useful when used via the parsing connection, e.g. to optimise any type of JDBC or R2DBC based application!

// Contains redundant operators
Condition c = create.parser().parseCondition("not not not book.id != 1");
QueryPart result = c.$replace(q -> {
    if (q instanceof QOM.Not n1 && n1.$arg1() instanceof QOM.Not n2) {
        System.out.println("Replacing NOT(NOT(p)) by NOT(p): " + q);
        return n2.$arg1();
    }
    else if (q instanceof QOM.Not n1 && n1.$arg1() instanceof QOM.Ne<?> n2) {
        System.out.println("Replacing NOT(x != y) by x = y: " + q);
        return n2.$arg1().eq((Field) n2.$arg2());
    }

    return q;
}));
System.out.println("Result: " + result);

The result is exactly the same:

Replacing NOT(x != y) by x = y: not (book.id <> 1)
Replacing NOT(NOT(p)) by NOT(p): not (not (book.id = 1))
Result: book.id = 1

Built-in replacers

The following sections show a few examples of built-in replacers.

Limitations

Just like model API traversal, replacers cannot traverse into "opaque" org.jooq.QueryPart instances, including custom QueryParts or plain SQL templates. See also features requiring code generation for more details.

Table of contents

3.4.3.1.

Listening Replacer (new)

3.4.3.2.

Decomposing Replacer (new)

The jOOQ User Manual SQL building The model API Replacement

previous : next