Search code examples
postgresqljooq

jOOQ's field not converting {0} in SQL

I have a jOOQ field declared like this (this is an over simplification):

field("INTERVAL '{0} minutes'", Duration.class, X)

I expected that to generate a Duration with X minutes that I could use in my queries, but instead it generated a duration of 0 minutes all the time. I inspected the generated SQL with toString and I saw instead of the expected INTERVAL '30 minutes' jOOQ generated INTERVAL '{0} minutes', so, it's treating {0} as a literal instead of replacing it by the template variable.

This is how I tested, this:

using(configuration).select(field("INTERVAL '30 minutes'", Duration.class)).toString()

yields:

select INTERVAL '30 minutes'

Whereas this:

using(configuration).select(field("INTERVAL '{0} minutes'", Duration.class, 30)).toString()

yields:

select INTERVAL '{0} minutes'

Is there a workaround to this behaviour?

Solution

  • As you've shown in your own answer, and as is documented for jOOQ's plain SQL templating feature, the templates preprocess string literals (and comments, and some other things), to prevent modifying them.

    But you should never resort to concatenating strings with jOOQ. There's always a better way. You could do it like this, for example:

    field("INTERVAL {0}", Duration.class, DSL.inline(X + " minutes"))

    I know. I'm still concatenating strings. But I'm doing that in a "safe place", wrapped by DSL.inline() (or DSL.val()), which produce properly escaped literals (or bind variables). What I meant is there should never be a reason to concatenate the SQL fragment, even with plain SQL templating.