如何使用Vertx SQL接口合并数据库查询结果

I would like to use Vertx common SQL Interface to query from table t1, t2, t3 in database TDB and together with table s1, s2, s3 from database SDB and return them as a JsonObject. The final result should be like this

我想使用Vertx通用SQL接口从数据库TDB中的表t1，t2，t3和数据库SDB中的表s1，s2，s3一起查询并将它们作为JsonObject返回。最终的结果应该是这样的

{
    "t1": [{...},{...},...],
    "t2": [{...},{...},...],
    "t3": [{...},{...},...],
    "s1": [{...},{...},...],
    "s2": [{...},{...},...],
    "s3": [{...},{...},...]
}

If it were to be only one table, I would do it like this

如果它只是一张桌子，我会这样做

JDBCClient tdbClient = JDBCClient.createShared(vertx, tdbConfig, "TDB");
JDBCClient sdbClient = JDBCClient.createShared(vertx, sdbConfig, "SDB");
vertx.eventBus().consumer("myservice.getdata").handler(msg -> {
    tdbClient.getConnection(tConresult -> { 
        if (tConresult.succeeded()) {
            SQLConnection tConnection = tConresult.result();
            tConnection.query("select * from t1", t1 -> { 
                if (t1.succeeded()) {
                    JsonArray t1Result = new JsonArray(t1.result().getRows());
                    JsonObject allResult = new JsonObject()
                        .put("t1", t1Result);
                    msg.reply(allResult);
                } else {
                    msg.fail(1, "failt to query t1");
                }
            });
        } else {
            msg.fail(1, "connot get connection to TDB");
        }
    });
});

But since it have to be many tables, I find an ugly way like this

但由于它必须是很多表，我发现这样一个丑陋的方式

vertx.eventBus().consumer("myservice.getdata").handler(msg -> {
    tdbClient.getConnection(tConresult -> { if (tConresult.succeeded()) {
    sdbClient.getConnection(sConresult -> { if (sConresult.succeeded()) {
    SQLConnection tConnection = tConresult.result();
    SQLConnection sConnection = sConresult.result();
        tConnection.query("select * from t1", t1 -> { if (t1.succeeded()) {
        tConnection.query("select * from t2", t2 -> { if (t2.succeeded()) {
        tConnection.query("select * from t3", t3 -> { if (t3.succeeded()) {
        sConnection.query("select * from s1", s1 -> { if (s1.succeeded()) {
        sConnection.query("select * from s2", s2 -> { if (s2.succeeded()) {
        sConnection.query("select * from s3", s3 -> { if (s3.succeeded()) {
            JsonArray t1Result = new JsonArray(t1.result().getRows());
            JsonArray t2Result = new JsonArray(t2.result().getRows());
            JsonArray t3Result = new JsonArray(t3.result().getRows());
            JsonArray s1Result = new JsonArray(s1.result().getRows());
            JsonArray s2Result = new JsonArray(s2.result().getRows());
            JsonArray s3Result = new JsonArray(s3.result().getRows());
            JsonObject allResult = new JsonObject()
                .put("t1", t1Result)
                .put("t2", t2Result)
                .put("t3", t3Result)
                .put("s1", s1Result)
                .put("s2", s2Result)
                .put("s3", s3Result);
            msg.reply(allResult);
        } else {msg.fail(1, "failt to query s3");}});
        } else {msg.fail(1, "failt to query s2");}});
        } else {msg.fail(1, "failt to query s1");}});
        } else {msg.fail(1, "failt to query t3");}});
        } else {msg.fail(1, "failt to query t2");}});
        } else {msg.fail(1, "failt to query t1");}});
    } else {msg.fail(1, "connot get connection to SDB");}});
    } else {msg.fail(1, "connot get connection to TDB");}});
});

But I think I'm doing it wrong, despite of the ugly code, it takes a lot of time to process because it doesn't do the queries in parallel.

但是我认为我做错了，尽管代码很难看，但是它需要花费很多时间来处理，因为它不会并行执行查询。

Please suggest a better way to achieve this.

请建议一个更好的方法来实现这一目标。

1 个解决方案

#1

What you are experiencing here is the callback hell. Vert.x provides some features to handle AsyncResult in a much more composable and convient way than callbacks. They are called Future. I suggest you read about them in the documentation. A Future is a placeholder for results of asynchronous calls. Vert.x is full of asynchronous calls. If asynchronous calls depend on each other you typically end up with a callback hell. With Future you can do something like this:

你在这里遇到的是回调地狱。 Vert.x以比回调更加可组合和方便的方式提供了一些处理AsyncResult的功能。他们被称为未来。我建议你在文档中阅读它们。 Future是异步调用结果的占位符。 Vert.x充满了异步调用。如果异步调用彼此依赖，那么通常会得到一个回调地狱。有了Future，你可以这样做：

Future<SQLConnection> tConResultFuture = Future.future();
tdbClient.getConnection(tConresult -> {
  if (tConresult.succeeded()) {
    logger.info("Yeah got a connection! tCon");
    tConResultFuture.complete(tConresult.result());
  } else {
    tConResultFuture.fail(tConresult.cause());
  }
});

The Handler for the AsyncResult<SQLConnection> put the asynchronous result of getting a SQLConnection into the Future tConResultFuture. Now you can a Handler for the Future and wait for the asynchronous result for getConnection:

AsyncResult 的Handler将获取SQLConnection的异步结果放入Future tConResultFuture中。现在您可以使用Handler for the Future并等待getConnection的异步结果：

tConResultFuture.setHandler(result -> {
  // ...
});

But that wouldn't make much sense as you already could to that with the first Handler. Now think of an example like yours – with many depending Futures. I use your example add a second connection – the sConresult:

但是，对于第一个处理程序，你已经可以做到这一点没有多大意义。现在想一个像你这样的例子 - 很多依赖期货。我用你的例子添加第二个连接 - sConresult：

Future<SQLConnection> sConResultFuture = Future.future();
sdbClient.getConnection(sConresult -> {
  if (sConresult.succeeded()) {
    logger.info("Yeah got a connection! sCon");
    sConResultFuture.complete(sConresult.result());
  } else {
    sConResultFuture.fail(sConresult.cause());
  }
});

So lets say, you want to wait for both Future results because they depend on each other. Here we use Vert.x' CompositeFuture:

因此，假设您想要等待Future结果，因为它们彼此依赖。这里我们使用Vert.x的CompositeFuture：

CompositeFuture.all(tConResultFuture, sConResultFuture).setHandler(connections -> {
  if (connections.succeeded()) {
    logger.info("Both connections are ready for use!");

    SQLConnection tCon = tConResultFuture.result();
    SQLConnection sCon = sConResultFuture.result();

    // do stuff...
  } else {
    logger.severe("Both or one connections attempt failed!");
  }
});

The CompositeFuture waits for Future tConResultFuture and Future sConResultFuture to complete successfully or not and than call its Handler. Now both asynchronous results are finished and you can call their results.

CompositeFuture等待Future tConResultFuture和Future sConResultFuture成功完成或不成功，而不是调用其Handler。现在两个异步结果都已完成，您可以调用它们的结果。

You and the nice thing, both asynchronous calls are done concurrently.

你和好事，两个异步调用都是同时完成的。