Sounds fancy, right? But it’s a really nice and reasonable approach to doing
dynamic SQL with jOOQ.
This blog post is inspired by a
Stack Overflow question, where a user wanted to turn a set of values into a dynamic UNION query like this:
SELECT T.COL1
FROM T
WHERE T.COL2 = 'V1'
UNION
SELECT T.COL1
FROM T
WHERE T.COL2 = 'V2'
...
UNION
SELECT T.COL1
FROM T
WHERE T.COL2 = 'VN'
Note, both the Stack Overflow user and I are well aware of the possibility of using IN predicates :-), let’s just assume for the sake of argument, that the UNION query indeed outperforms the IN predicate in the user’s particular MySQL version and database. If this cannot be accepted, just imagine a more complex use case.
The solution in Java is really very simple:
import static org.jooq.impl.DSL.*;
import java.util.*;
import org.jooq.*;
public class Unions {
public static void main(String[] args) {
List<String> list = Arrays.asList("V1", "V2", "V3", "V4");
System.out.println(
list.stream()
.map(Unions::query)
.reduce(Select::union));
}
// Dynamically construct a query from an input string
private static Select<Record1<String>> query(String s) {
return select(T.COL1).from(T).where(T.COL2.eq(s));
}
}
The output is:
Optional[(
select T.COL1
from T
where T.COL2 = 'V1'
)
union (
select T.COL1
from T
where T.COL2 = 'V2'
)
union (
select T.COL1
from T
where T.COL2 = 'V3'
)
union (
select T.COL1
from T
where T.COL2 = 'V4'
)]
If you’re using JDK 9+ (which has
Optional.stream()
), you can further proceed to running the query fluently as follows:
List<String> list = Arrays.asList("V1", "V2", "V3", "V4");
try (Stream<Record1<String>> stream = list.stream()
.map(Unions::query)
.reduce(Select::union))
.stream() // Optional.stream()!
.flatMap(Select::fetchStream)) {
...
}
This way, if the list is empty, reduce will return an empty optional. Streaming that empty optional will result in not fetching any results from the database.
Like this:
Like Loading...
Published by lukaseder
I made jOOQ
View all posts by lukaseder