How to execute with Jooq arbitrary number of selects joined with UNION

Question

I want to execute UNION of List<Select> like

SELECT #1
UNION
SELECT #2
UNION
...

How can I do it with Jooq?

UPDATE: In my method I check a set of parameters and if their are not nul add subquery to the list. I need functionality like strings joiner.

So I solved my task by downgrading to raw sql and using Joiner(" UNION ").join(List<String>subqueries)

Answer 1

Maybe the following could be applied to call union on several Selects.

<T extends Record> Result<T> fetchUnion(List<Select<T>> selects) {
    return selects.stream()
                  .reduce(Select::union)
                  .map(Select::fetch)
                  .orElseThrow(() -> /* your exception? */);
}

Note that I did not yet test it and you may need to alter it a bit to get it working. Alternatively:

<T extends Record> Result<T> fetchUnion(Select ... selects) {
   return Stream.of(selects)
                .reduce(Select::union)
                .map(Select::fetch)
                .orElse ... ;
}

A possible call could then be:

Result result = fetchUnion(select(T1.A, T1.B).from(T1), 
                           select(T2.A, T2.B).from(T2), 
                           select(T3.A, T3.B).from(T3));

The static import mentioned by Lukas is also required here:

import static org.jooq.impl.DSL.*;

Answer 2

Here you go:

using(configuration)
   .select(T1.A, T1.B)
   .from(T1)
   .union(
    select(T2.A, T2.B)
   .from(T2))
   .union(
    select(T3.A, T3.B)
   .from(T3))
   .fetch();

The above answer is assuming the following import:

import static org.jooq.impl.DSL.*;