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 abnormal
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 sort Area<Integer> End result<Record1<Integer>> end result = ctx.choose(BOOK.ID) .from(BOOK) .fetch(); // With the converter End result<Record1<Lengthy>> end result = ctx.choose(BOOK.ID.convertFrom(i -> i.longValue())) .from(BOOK) .fetch();
Whereas there are different methods to transform information varieties, e.g. through the use of
COERCE() expressions, this method attaches a
Converter to the sphere, which is known as proper after studying the
Integer worth from the JDBC
ResultSet to be able to flip it right into a
Lengthy. This conversion is finished on the consumer facet. The RDBMS that executes the question is just not conscious of it.
That’s an vital element! The RDBMS is just not conscious of it!
Caveat: Utilizing UNION
An attention-grabbing problem (#14693) was raised not too long ago on the difficulty tracker concerning using such ad-hoc converters in a
UNION. For instance, let’s assume this question is being run:
End result<Record1<Integer>> end result = 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 |
[1, 2, 3, 4] and accessible
[1, 2], the
UNION will take away duplicates.
What do you suppose will occur once we connect this ad-hoc converter solely to the second
End result<Record1<Integer>> end result = ctx.choose(BOOK.ID) .from(BOOK) .union( choose(AUTHOR.ID.convertFrom(i -> -i)) .from(AUTHOR)) .fetch();
Its objective appears to be to get the damaging worth of every
AUTHOR.ID, whereas protecting 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
- Moreover, jOOQ doesn’t know which
UNIONsubquery contributes which row, so it couldn’t probably determine whether or not to use the converter or not!
And that’s successfully what occurs. The end result continues 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 every other
Binding) that you simply connect to those projected columns. jOOQ will solely ever think about the row sort of the primary
UNION subquery when fetching outcomes from a JDBC (or R2DBC)
ResultSet. You solely have to ensure that each row varieties are suitable for the Java compiler to sort examine your question.
There are actually solely 2 options to such a scenario:
- Should you’re certain your conversion ought to occur in your consumer code (versus the server), then you need to apply it at the least to the primary
UNIONsubquery. Ideally, you’ll simply apply it to all of the
UNIONsubqueries for consistency causes, together with in case you extract a subquery for it to be reused.
- Presumably, you need to have moved the conversion to the server facet, within the first place
Within the latter case, this question would possibly make extra sense, if the intention was to create damaging
End result<Record1<Integer>> end result = ctx.choose(BOOK.ID) .from(BOOK) .union( choose(AUTHOR.ID.neg()) .from(AUTHOR)) .fetch();
It will now produce the next SQL question:
SELECT ebook.id FROM ebook UNION SELECT -author.id FROM creator
And a end result set like this:
|id | |---| |-2 | |-1 | |1 | |2 | |3 | |4 |
Preserve this in thoughts when utilizing ad-hoc converters together with