如何在Vertx JDBC客户端中将列表用作SQL查询的参数源? [英] How to use a list as a parameter source for SQL queries with Vertx JDBC Client?

问题描述

我有一个Vert.x Web应用程序，该应用程序需要查询运行Postgres 10.7的AWS RDS实例. Vert.x JDBC客户端是io.vertx:vertx-jdbc-client:3.8.4.我想查询一个表，该表的约束是某个列的值包含在一组值中:

I have a Vert.x web application that needs to query an AWS RDS instance running Postgres 10.7. The Vert.x JDBC client is io.vertx:vertx-jdbc-client:3.8.4. I want to query a table with the constraint that a certain column's value is included in a set of values:

select from table where column in/any (?)

我遵循了Vertx文档，该文档要求创建一个JsonArray并在其中填充要插入查询中的值.该列的类型为text，我要匹配的列表是Java ArrayList<String>.我的查询代码如下:

I followed the Vertx documentation, which says to create a JsonArray and populate it with the values to inject into the query. The column is of type text and the list that I want to match on is a Java ArrayList<String>. My query code looks like:

String sql = "SELECT a FROM table WHERE col IN (?)";
List<String> values = someObject.someField();

            sqlClient.getConnection(connectionResult -> {
                if (connectionResult.failed()) {
                    // handle
                } else {
                    SQLConnection connection = connectionResult.result();

                    JsonArray params = new JsonArray()
                            .add(values);
                    connection.queryWithParams(sql, params, queryResult -> {
                       if (queryResult.failed()) {
                           // handle
                       } else {
                           // parse
                       }
                    });
                }
            });

查询失败，并显示以下错误:org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of io.vertx.core.json.JsonArray. Use setObject() with an explicit Types value to specify the type to use.

The query fails with the error: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of io.vertx.core.json.JsonArray. Use setObject() with an explicit Types value to specify the type to use.

我知道在最坏的情况下，我可以创建文字SQL字符串where col in (?, ?, ?, ..., ?)并将列表中的每个String添加到JsonArray，但是必须有一种方法可以仅将ArrayList<String>添加为参数并保持查询简单.如何在查询中指定要匹配的值列表?

I know that in the worst case, I can create a literal SQL string where col in (?, ?, ?, ..., ?) and add each String from the list to a JsonArray, but there must be a way to just add the ArrayList<String> as a parameter and keep the query simple. How can I specify a list of values to match against in my query?

推荐答案

Vert.x JDBC客户端不支持查询中的数组参数.

The Vert.x JDBC Client does not support array parameters in queries.

但是，通过 Vert.x Pg客户端，这是可能的，不依赖JDBC.您需要先修改查询:

However it is possible with the Vert.x Pg Client, which does not depend on JDBC. You need to modify your query first:

SELECT a FROM table WHERE col = ANY(?)

然后:

pgClient.preparedQuery(query, Tuple.of(possibleValues), collector, handler);

这篇关于如何在Vertx JDBC客户端中将列表用作SQL查询的参数源?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！