The jOOQ User Manual SQL building SQL Parser SQL Parser Listener

previous : next

SQL Parser Listener

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

In order to implement custom parser behaviour, it is possible to provide your Configuration with a set of custom org.jooq.ParseListener implementations.

The current SPI offers hooking into the parsing of 3 types of syntactic elements (see also the parser grammar):

• term to parse org.jooq.Field expressions
• predicate to parse org.jooq.Condition expressions
• tableFactor to parse org.jooq.Table expressions

And also these two lifecycle events:

• Before the start of a parse call
• After the end of a parse call

The idea is that any listener implementation you provide the parser with may be able to parse functions, top-level-precedence operators, etc. without having to deal with all the lower level precedence operators, such as AND, OR, NOT, +, -, etc. etc.

For example, assuming you want to add support for a logical LOGICAL_XOR operator as a function:

Query query = configuration
    .derive(ParseListener.onParseCondition(ctx -> {
        if (ctx.parseFunctionNameIf("LOGICAL_XOR")) {
            ctx.parse('(');
            Condition c1 = ctx.parseCondition();
            ctx.parse(',');
            Condition c2 = ctx.parseCondition();
            ctx.parse(')');

            return c1.andNot(c2).or(c2.andNot(c1));
        }

        // Let the parser take over if we don't know the token
        return null;
    })
    .dsl()
    .parser()
    .parseQuery("select * from t where logical_xor(t.a = 1, t.b = 2)");

The above will just translate the convenience function LOGICAL_XOR(c1, c2) into its formal definition c1 AND NOT c2 OR c2 AND NOT c1. But we can do even better than this. If a dialect has native XOR support, why not support that?

Query query = configuration
    .derive(ParseListener.onParseCondition(ctx -> {
        if (ctx.parseFunctionNameIf("LOGICAL_XOR")) {
            ctx.parse('(');
            Condition c1 = ctx.parseCondition();
            ctx.parse(',');
            Condition c2 = ctx.parseCondition();
            ctx.parse(')');

            return CustomCondition.of(c -> {
                switch (c.family()) {
                    case MARIADB:
                    case MYSQL:
                        c.visit(condition("{0} xor {1}", c1, c2));
                        break;
                    default:
                        c.visit(c1.andNot(c2).or(c2.andNot(c1)));
                        break;
                }
            });
        }

        // Let the parser take over if we don't know the token
        return null;
    }))
    .dsl()
    .parser()
    .parseQuery("select * from t where logical_xor(t.a = 1, t.b = 2)");

System.out.println(DSL.using(SQLDialect.MYSQL).render(query));
System.out.println(DSL.using(SQLDialect.ORACLE).render(query));

The output of the above is now:

-- MYSQL:
select * from t where (t.a = 1 xor t.b = 2);

-- ORACLE:
select * from t where (t.a = 1 and not (t.b = 2)) or (t.b = 2 and not (t.a = 1));

This way, with a modest effort, you can parse and/or translate arbitrary column expressions, table expressions, or conditional expressions that jOOQ does not support natively, or override the default behaviour of the parser in this area.

The jOOQ User Manual SQL building SQL Parser SQL Parser Listener

previous : next