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
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/154c8282-df34-4f4b-877f-f4203f020c26%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
