jOOQ 3.15 launched the idea of an ad-hoc converter, a converter that’s utilized “ad-hoc” to a single question. It makes use of the identical underlying mechanisms as any bizarre Converter
that’s hooked up to generated code to be used in each question.
An instance of such an ad-hoc converter is that this:
// With out the converter, assuming BOOK.ID is of kind Area<Integer>
End result<Record1<Integer>> outcome =
ctx.choose(BOOK.ID)
.from(BOOK)
.fetch();
// With the converter
End result<Record1<Lengthy>> outcome =
ctx.choose(BOOK.ID.convertFrom(i -> i.longValue()))
.from(BOOK)
.fetch();
Whereas there are different methods to transform knowledge varieties, e.g. by utilizing CAST()
or COERCE()
expressions, this method attaches a Converter
to the sphere, which is known as proper after studying the Integer
worth from the JDBC ResultSet
so as to flip it right into a Lengthy
. This conversion is completed on the consumer aspect. The RDBMS that executes the question just isn’t conscious of it.
That’s an necessary element! The RDBMS just isn’t conscious of it!
Caveat: Utilizing UNION
An fascinating problem (#14693) was raised lately on the problem tracker relating to using such ad-hoc converters in a UNION
. For instance, let’s assume this question is being run:
End result<Record1<Integer>> outcome =
ctx.choose(BOOK.ID)
.from(BOOK)
.union(
choose(AUTHOR.ID)
.from(AUTHOR))
.fetch();
This would possibly produce one thing like:
|id | |---| |1 | |2 | |3 | |4 |
Assuming obtainable BOOK.ID
are [1, 2, 3, 4]
and obtainable AUTHOR.ID
are [1, 2]
, the UNION
will take away duplicates.
What do you assume will occur once we connect this ad-hoc converter solely to the second UNION
subquery?
End result<Record1<Integer>> outcome =
ctx.choose(BOOK.ID)
.from(BOOK)
.union(
choose(AUTHOR.ID.convertFrom(i -> -i))
.from(AUTHOR))
.fetch();
Its purpose appears to be to get the detrimental worth of every AUTHOR.ID
, whereas holding the BOOK.ID
intact. However bear in mind:
- The conversion occurs within the consumer, not the server, so the RDBMS isn’t conscious of it
- This implies it has no impact on the
UNION
operator - Moreover, jOOQ doesn’t know which
UNION
subquery contributes which row, so it couldn’t presumably determine whether or not to use the converter or not!
And that’s successfully what occurs. The outcome remains to be:
|id | |---| |1 | |2 | |3 | |4 |
And the lambda i -> -i
isn’t known as! This isn’t simply true for ad-hoc converters, it’s additionally true for some other Converter
(or Binding
) that you simply connect to those projected columns. jOOQ will solely ever think about the row kind of the primary UNION
subquery when fetching outcomes from a JDBC (or R2DBC) ResultSet
. You solely have to ensure that each row varieties are appropriate for the Java compiler to kind examine your question.
Answer
There are actually solely 2 options to such a state of affairs:
- If you happen to’re positive your conversion ought to occur in your consumer code (versus the server), then it is best to apply it at the very least to the primary
UNION
subquery. Ideally, you’ll simply apply it to all of theUNION
subqueries for consistency causes, together with in case you extract a subquery for it to be reused. - Probably, it is best to have moved the conversion to the server aspect, within the first place
Within the latter case, this question would possibly make extra sense, if the intention was to create detrimental AUTHOR.ID
values:
End result<Record1<Integer>> outcome =
ctx.choose(BOOK.ID)
.from(BOOK)
.union(
choose(AUTHOR.ID.neg())
.from(AUTHOR))
.fetch();
It will now produce the next SQL question:
SELECT e-book.id
FROM e-book
UNION
SELECT -author.id
FROM writer
And a outcome set like this:
|id | |---| |-2 | |-1 | |1 | |2 | |3 | |4 |
Maintain this in thoughts when utilizing ad-hoc converters together with MULTISET
, specifically!