java 有选择的查询,多个联合查询选择后java 8

Here is a query that I want to try out in MySQL

SELECT A.x

FROM A

WHERE A.y = 'P'

UNION

SELECT A.x

FROM A

WHERE A.y = 'Q'

The above is a cut-down, much simpler version of the original query that I am trying. In my original query, each SELECT statement involves multiple tables with INNER JOIN

If the possible number of values in 'y' column of table 'A' that I need to query upon is 'n', then my query will involve doing 'n-1' unions on 'n' SELECT statements

I know that JOOQ can do union of multiple SELECT statements. But is there a good way to do this post Java 8 style? maybe using Steam.collect()?

This is what I have but wondering if I could do better

String firstValueToQuery = valuesToQuery.get(0);

Select> selectQuery = getSelectQueryForValue(firstValueToQuery);

valuesToQuery.stream()

.skip(1)

.forEach(valueToQuery -> selectQuery.unionAll(getSelectQueryForValue(valueToQuery)));

selectQuery.fetchStream();

Here is how I implement getSelectQueryForValue

private Select> getSelectQueryForValue(String valueToQuery) {

return jooq.select(

A.P,

A.Q,

A.R,

A.S,

A.T)

.from(A)

.where(A.Y.eq(valueToQuery));

}

PS: I understand that I could rather use the 'IN' clause like below

SELECT A.x

FROM A

WHERE A.y IN ('P','Q',...)

But with my current data distribution in the database, MySQL is using a sub-optimal query plan. Thus using UNION so that the database implicitly prefers a faster query plan by making use of the right index

解决方案

The idiomatic approach here would be as follows (using JDK 9 API):

try (Stream> stream = valuesToQuery

.stream()

.map(this::getSelectQueryForValue)

.reduce(Select::union)

.stream() // JDK 9 method

.flatMap(Select::fetchStream)) {

...

}

It uses the useful Optional.stream() method, which was added in JDK 9. In JDK 8, you could do this instead:

valuesToQuery

.stream()

.map(this::getSelectQueryForValue)

.reduce(Select::union)

.ifPresent(s -> {

try (Stream> stream =

s.fetchStream()) {

...

}

})