jOOQ not binding values in DSL.condition()

Question

I've the following code which uses jOOQ:

        dslContext.select(CONNECTOR.asterisk())
            .from(CONNECTOR)
            .where(DSL.and(
                CONNECTOR.STATUS.notEqual(ConnectorStatus.DELETED.name()),
                DSL.condition("JSON_CONTAINS(vendor_data, '\"?\"', '$.id')", id)
            ))
            .fetchInto(Connector.class);

The problem is with this plain SQL condition, the '?' doesn't get filled in the resulting SQL for some reason, although the docs say that we can pass bindings.

The resulting SQL I got from the logs:

select `connector`.* from `connector` where (`connector`.`status` <> 'DELETED' and (JSON_CONTAINS(vendor_data, '"?"', '$.id')))

What am I missing? What's the way to do it properly?

jOOQ version: 3.12.1

Answer 1

Plain SQL template processing includes a small parser that prevents modifying string literals and comments as documented in the manual section about plain SQL templating. This is desired as a string literal isn't capable of containing bind parameters neither in JDBC nor on any server implementation.

You have 2 options:

• Use a JSON bind value instead, e.g. JSON.json("\"" + id + "\"") (making sure you properly escape the JSON
• Cast a bind value to JSON on the server, e.g. JSON_QUOTE(CAST(? AS VARCHAR))

I'm seeing that you're still using jOOQ 3.12. I recommend upgrading as later versions of jOOQ have implemented tons of JSON support, which you could use.