Home Programming Traversing jOOQ Expression Bushes with the brand new Traverser API – Java, SQL and jOOQ.

Traversing jOOQ Expression Bushes with the brand new Traverser API – Java, SQL and jOOQ.

Traversing jOOQ Expression Bushes with the brand new Traverser API – Java, SQL and jOOQ.


Ranging from jOOQ 3.16, we’re investing lots into opening up our inner question object mannequin (QOM) as a public API. That is primarily helpful for individuals who use jOOQ’s parser and want to entry the parsed expression tree, or to rework SQL, e.g. to implement row degree safety in jOOQ.

However often, even with atypical jOOQ dynamic SQL utilization, it may be helpful to entry the expression tree.

Please word that as of jOOQ 3.16, all of this new API is experimental and thus topic to incompatible modifications sooner or later. Use it at your individual threat.

The Question Object Mannequin (QOM) API

The primary enchancment is to supply an API to the question object mannequin itself. A brand new kind known as org.jooq.impl.QOM accommodates all of this new public API, whereas the implementations are nonetheless the identical previous ones, within the org.jooq.impl package deal, however with package-private visibility.

Whenever you create a SUBSTRING() perform name expression, you’re getting a Area<String> expression that implements QOM.Substring. On that kind, you possibly can name numerous accessor strategies at all times beginning with a "$" signal to entry the perform arguments:

// Create an expression utilizing the DSL API:
Area<String> discipline = substring(BOOK.TITLE, 2, 4);
// Entry the expression's internals utilizing the mannequin API
if (discipline instanceof QOM.Substring substring) {
    Area<String> string = substring.$string();
    Area<? extends Quantity> startingPosition = 
    Area<? extends Quantity> size = substring.$size();

Some issues which may be topic to alter:

1. It’s not clear but if the DSL methodology substring() returns the QOM kind Substring, or the DSL kind Area. There are execs and cons to each, although there’s a slight desire for maintaining the QOM kind out of sight for DSL customers.

2. The "$" prefix is used to obviously distinguish between the DSL API (no prefix) and the QOM API ("$" prefix) as the sort hierarchy is now shared between the 2 APIs, and it needs to be clear for customers whether or not they’re establishing jOOQ objects for utilization within the DSL, or whether or not they’re manipulating objects of the expression tree.

For every accessor, there’s additionally a “mutator”, a technique that produces a brand new QOM kind containing the mutated worth. The entire QOM kind is immutable, so the unique Substring occasion isn’t affected by a modification like this:

Substring substring1 = (Substring) substring(BOOK.TITLE, 2, 4);
Substring substring2 = substring1

assertEquals(substring2, substring(BOOK.TITLE, 3, 5));

The entire above API, the accessors, and the mutators shall be obtainable to all jOOQ editions, together with the jOOQ Open Supply Version.

Expression tree traversal

The true enjoyable begins once you wish to traverse the expression tree, e.g. to search for the presence of objects, to gather objects, and so forth. For this, we’ve got launched the brand new Traverser API within the business jOOQ distributions.

A Traverser works fairly equally as a JDK Collector, which traverses a Stream and collects components into some information construction. However the Traverser operates on a tree, and thus has a number of further options:

  • It might probably obtain occasions earlier than and after visiting a tree component (and their subtrees!)
  • It might probably determine for every tree component, whether or not the traversal ought to recurse into the subtree. That is very helpful, for instance, in case you don’t care about traversing subqueries of any type.
  • It might probably determine whether or not to abort traversal early, e.g. when the primary object has been discovered. I’m not conscious of JDK Collector providing such quick circuiting strategies, although I feel that may be helpful, there, too. (It’s doable with a Spliterator, however that’s far more cumbersome)
  • It’s not parallel succesful. Parallelism is already an elective function with streams, however with bushes, we haven’t discovered the advantage of supporting that but, maintaining traversals a lot easier.

A easy traversal instance could be to depend all of the QueryPart objects in an expression, like this:

// Comprises 7 question components
        .$traverse(() -> 0, (c, p) -> c + 1)

The straightforward comfort methodology supplies an auxiliary information construction (right here an int), and a perform that accumulates each question half into that information construction. The result’s the information construction (the int) itself.

Why does it depend 7? As a result of it traverses the next tree:

1: T_BOOK.ID.eq(1).or(T_BOOK.ID.eq(2))
2: T_BOOK.ID.eq(1)
4: 1
5: T_BOOK.ID.eq(2)
7: 2

Or visually:

├── EQ
│   ├── T_BOOK.ID
│   └── 1
└── EQ
    ├── T_BOOK.ID
    └── 2

For those who needed to easily accumulate every particular person QueryPart, simply do it like this:

            () -> new ArrayList<QueryPart>(),
            (checklist, p) -> {
                return checklist;

The output of that is (not native formatting):

  ("PUBLIC"."T_BOOK"."ID" = 1 or "PUBLIC"."T_BOOK"."ID" = 2),
  "PUBLIC"."T_BOOK"."ID" = 1,
  "PUBLIC"."T_BOOK"."ID" = 2,

This instance reveals that the tree is traversed in a depth-first method.

However you don’t have to write down such easy Traversers your self. Any JDK Collector can be utilized as a Traverser, so the above two examples could be rewritten like this:



Need to accumulate all of the concerned tables of a question? No drawback!

                p -> p instanceof TableField<?, ?> tf 
                    ? tf.getTable() 
                    : p,
                    p -> p instanceof Desk,

This may be learn as:

  • Map all TableField references to their Desk containers
  • Filter out all Desk references
  • Acquire them to a definite Set of tables.



Expression tree transformations

What if you wish to substitute one expression by one other? There are numerous use-cases, which we’ll ultimately assist out of the field within the business jOOQ editions, however you may as well roll your individual extensions utilizing this API.

A quite simple instance of such a change would take away redundant boolean negation:

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

Regardless of having explicitly written not(not(x)), the output is simply x, or particularly:

"BOOK"."ID" = 1

Precise instance use-cases for such transformations embody:

Optimisations and replacements of frequent patterns

There are a number of causes to normalise and enhance frequent patterns of SQL strings:

Ranging from jOOQ 3.17, we’ll provide a whole lot of these transformations out of the field. You may flip them on for various causes:

  • To usually optimise your SQL output
  • To detect issues in your queries, each carried out by way of jOOQ API, or when intercepting them by way of the parser – the rule of thumb being that if this sample recognition function finds one thing to rework, then your individual SQL question needs to be improved. A linter, so to talk.

Out of the field function ranging from jOOQ 3.17: https://github.com/jOOQ/jOOQ/points/7284

Row degree safety or shared schema multi tenancy

You may already as we speak implement client-side row degree safety utilizing jOOQ’s VisitListener SPI, a predecessor to those SQL transformation options which might be primarily based on the brand new question object mannequin. However with the brand new substitute API, it will likely be a lot easier each for customers, in addition to for us to assist an out of the field row degree safety function. Briefly, think about that each time you question a restricted desk, akin to ACCOUNT:

What you need is to make sure customers can solely entry their very own accounts, i.e. this needs to be patched into the question, transparently for the developer:

SELECT * FROM account WHERE account_id IN (:userAccountList)

A easy algorithm could be to write down:

QueryPart q = choose(ACCOUNT.ID).from(ACCOUNT);
    q.$substitute(p -> {
        if (p instanceof Choose<?> s) {

            // Examine if the question accommodates the related desk(s) in
            // the FROM clause
            if (s.$from().$traverse(Traversers.containing(ACCOUNT)) && (

                // Within the absence of a WHERE clause
                s.$the place() == null ||

                // Or, if we have not already added our IN checklist
                !s.$the place().$traverse(Traversers.containing(
                    x -> x instanceof InList<?> i 
                        && ACCOUNT.ID.equals(i.$arg1())
            )) {

                // Append a predicate to the question
                // Think about this studying some context information
                return s.$the place(DSL.and(s.$the place(), 
                    ACCOUNT.ID.in(1, 2, 3)));
        return p;

The results of the above shall be:

choose "PUBLIC"."ACCOUNT"."ID"
the place "PUBLIC"."ACCOUNT"."ID" in (
  1, 2, 3

Discover how the enter SQL question doesn’t include any such predicate. Clearly, that is removed from full. It doesn’t deal with outer joins accurately (the place the predicate might need to enter the ON clause), and different caveats. Keep tuned for extra, on this space!

Out of the field function with no launch goal but:

Extra use-cases

There are lots of extra use-cases, which we’re planning on supporting out of the field, primarily based on the above function set. These embody:

  • Delicate deletion, reworking DELETE statements into “equal” UPDATE .. SET deleted = true statements, in addition to SELECT statements into “equal” SELECT .. WHERE NOT deleted, see https://github.com/jOOQ/jOOQ/points/2683
  • Audit column assist the place we replace “audit” fields akin to CREATED_AT, CREATED_BY, MODIFIED_AT, MODIFIED_BY each time they’re touched by any DML question, see https://github.com/jOOQ/jOOQ/points/1592

Use-case agnosticity

Do not forget that like most different jOOQ options, this one, too, is totally use-case agnostic. It doesn’t matter in case you’re utilizing jOOQ:

  • As an inner DSL to create dynamic (or “static”) SQL queries
  • As a parser to translate between SQL dialects
  • As a parser to counterpoint your utility that’s primarily based on a legacy ORM
  • As a diagnostics utility to run checks in your legacy ORM primarily based utility

No matter the use-case, you need to use this API to analyse and remodel your SQL queries.

Limitations (as of jOOQ 3.16)

As talked about earlier than, so far, that is an experimental function, not really manufacturing prepared but. There are fairly a number of identified limitations of the present design and implementation. Please take into account this difficulty for open points:


A very powerful limitations up to now embody:

  • Help just for SELECT, no different statements
  • Traversal doesn’t but go into JOIN bushes or UNION / INTERSECT / EXCEPT subqueries

There are extra limitations, however these ones are an important ones. So, keep tuned for extra thrilling developments on this space coming quickly within the subsequent jOOQ releases.


Supply hyperlink


Please enter your comment!
Please enter your name here