jOOQ's field not converting {0} in SQL

user_303914 0
Viewed 59

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?

jooq postgresql
2 Answers

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.

user_5217990

I solved my issue with this:

field("INTERVAL '" + X + " minutes'", Duration.class)

If you check the jOOQ documentation here you'll see this relevant part:

When processing these plain SQL templates, a mini parser is run that handles things like

  • String literals
  • ...

The above are recognised by the templating engine and contents inside of them are ignored when replacing numbered placeholders and/or bind variables.

So, according to the docs, it's expected that a {0} inside of quotes doesn't get replaced. But that was quite unexpected for the case of building Postgres intervals with jOOQ, where Postgres will just swallow that {0} and cause a bug that doesn't produce an exception, so there is no track of it:

# select interval '{0} minutes';

 interval 
----------
 00:00:00
(1 row)
user_3039140
Related