使用多个联合查询选择post java 8

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

这是我想在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

上面是我正在尝试的原始查询的一个简化的、更简单的版本。在我最初的查询中，每个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

如果我需要查询的表A的“y”列中的值的可能数目是“n”，那么我的查询将涉及在“n”SELECT语句上执行“n-1”联合

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()?

我知道JOOQ可以做多个SELECT语句的联合。但是有什么好的方法可以实现后Java 8风格吗?也许使用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();

Here is how I implement getSelectQueryForValue

下面是我实现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: I understand that I could rather use the 'IN' clause like below

PS:我理解我更愿意使用“IN”子句，如下所示

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

但是，使用数据库中的当前数据分布，MySQL使用的是次优查询计划。因此，使用UNION使数据库通过使用正确的索引隐式地倾向于更快的查询计划。

1 个解决方案

#1

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

这里的惯用方法如下(使用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)) {
    ...
}

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

它使用了有用的option .stream()方法，它是在JDK 9中添加的。在JDK 8中，您可以这样做:

valuesToQuery
    .stream()
    .map(this::getSelectQueryForValue)
    .reduce(Select::union)
    .ifPresent(s -> {
        try (Stream<Record5<UUID, UUID, String, Integer, String>> stream = 
             s.fetchStream()) {
            ...
        }
    })

I blogged about this in more detail here.

我在博客中对此做了更详细的描述。

1 个解决方案

#1

更多相关文章

随机推荐