多个联合查询选择后java 8 [英] Union query with multiple selects post java 8

问题描述

以下是我想在MySQL中试用的查询

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'

以上是我正在尝试的原始查询的简化版本。在我的原始查询中，每个 SELECT 语句涉及多个表，其中 INNER JOIN

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

如果我需要查询的表'A'的'y'列中可能的值为'n'，那么我的查询将涉及'n'<$上的'n-1'联合c $ c> SELECT 语句

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

我知道JOOQ可以组合多个 SELECT 陈述。但是有没有一个很好的方法来发布Java 8风格？也许使用Steam.collect（）？

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<Record5<UUID, UUID, String, Integer, String>> selectQuery =  getSelectQueryForValue(firstValueToQuery);
valuesToQuery.stream()
             .skip(1)
             .forEach(valueToQuery -> selectQuery.unionAll(getSelectQueryForValue(valueToQuery)));
selectQuery.fetchStream();

以下是我如何实现 getSelectQueryForValue

private Select<Record5<UUID, UUID, String, Integer, String>> getSelectQueryForValue(String valueToQuery) {
        return jooq.select(
                A.P,
                A.Q,
                A.R,
                A.S,
                A.T)
                .from(A)
                .where(A.Y.eq(valueToQuery));
    }

PS：据我所知，我宁可使用下面的'IN'子句

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

SELECT A.x
FROM A
WHERE A.y IN ('P','Q',...)

但是我目前的数据分布在数据库中，MySQL正在使用次优的查询计划。因此，使用UNION使数据库通过使用正确的索引隐式地更喜欢更快的查询计划

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

推荐答案

这里惯用的方法会如下（使用JDK 9 API）：

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

try (Stream<Record5<UUID, UUID, String, Integer, String>> stream = valuesToQuery
        .stream()
        .map(this::getSelectQueryForValue)
        .reduce(Select::union)
        .stream() // JDK 9 method
        .flatMap(Select::fetchStream)) {
    ...
}

它使用有用的 Optional.stream（） 方法，已在JDK 9中添加。在JDK 8中，您可以这样做：

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<Record5<UUID, UUID, String, Integer, String>> stream = 
             s.fetchStream()) {
            ...
        }
    })

我在这里更详细地写了这篇博文。

这篇关于多个联合查询选择后java 8的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！