[REQUEST FOR FEEDBACK] New utility that inlines bind values for a certain QueryPart

[Lukas Eder]

Dear group,

In jOOQ's internals and integration tests, I often find myself wanting to inline several parameters, e.g. when passing constant literals to an IN list. For example, I query the SQL Server sys.all_objects table, and I want to produce this predicate

    sys.all_objects.type in ('S', 'U', 'V')

For performance and readability reasons, I want to inline those constant literals, as opposed to generating bind values, given that these aren't really input parameters, they're not dynamic. I.e. I want to avoid this:

    sys.all_objects.type in (?, ?, ?)

The way to go here in jOOQ is to write the following jOOQ logic:

    SYS.ALL_OBJECTS.TYPE.in(inline("S"), inline("U"), inline("v"))

This is cumbersome, as I have to repeat this inline "noise" 3x. The DSL.inline() vs DSL.val() semantics was chosen explicitly for a reason. It is always good to use bind variables per default. While SQL injection is mostly a non-issue with jOOQ, performance is still critical. We don't want to miss the execution plan cache when executing similar queries frequently. But again, this is not the case in this case here, where the IN list is constant.

Question:

Is this something you've found yourself frequently wishing for, as well? If so, how did you work around it?

Suggestion:

There are tools to inline all bind values for a given query. This is rarely reasonable, because there's always at least 1-2 additional bind variables that are dynamic, where we want to profit from execution plan caching by using bind variables. But maybe, it would be useful to have a utility that can wrap e.g. an org.jooq.Condition and inline all the bind values only for that particular condition. For example:

    inlineAll(SYS.ALL_OBJECTS.TYPE.in("S", "U", "V"))

Maybe, a better method name is possible.

The drawback of this approach is that it only works with some types of QueryPart, not all, as the wrapping type that is returned must retain all the semantics of the type it wraps. With Condition, this shouldn't be too hard, and it's possible to continue using Condition API as such:

    inlineAll(SYS.ALL_OBJECTS.TYPE.in("S", "U", "V")).and(X.eq(Y)

But there are some types, such as SelectFieldOrAsterisk, which might be more tricky to wrap.

Thoughts?

Lukas

[Marshall Pierce]

On 6/20/19 6:56 AM, Lukas Eder wrote:

> The way to go here in jOOQ is to write the following jOOQ logic:
>
>     SYS.ALL_OBJECTS.TYPE.in(inline("S"), inline("U"), inline("v"))

Since I write 80% kotlin these days when I’m using the JVM, this is how
I would solve it:

SYS.ALL_OBJECTS.TYPE
.in(*listOf(“S”, “U”, "v”).map(::inline).toTypedArray())

The leading * is the spread operator:
https://kotlinlang.org/docs/reference/functions.html#variable-number-of-arguments-varargs

More typing than `inlineAll`, but close enough that if it was me (and
Kotlin was available) I probably wouldn’t feel the need to add a special
purpose library function. The equivalent in Java is pretty long though,
and that probably is past the “ugh, we need a function to do this”
threshold...

[Lukas Eder]

Hi Marshall,

Thanks for sharing. Of course, this could be done in Java as well - using jOOλ, we'd write:

SYS.ALL_OBJECTS.TYPE.in(Seq.of("S","U","V").map(DSL::inline).toList())

Or, using vanilla streams:

SYS.ALL_OBJECTS.TYPE.in(Stream.of("S","U","V").map(DSL::inline).collect(toList()))  

Perhaps, this is already sufficient for most use-cases in this particular case - invalidating the thought of adding new jOOQ API (which I was kinda hoping for)