使用带有“IN”子句的JDBI绑定数组

Question

我试图执行以下代码：

return jdbi.withHandle(
    handle -> handle.createQuery("SELECT * FROM alias WHERE id IN (?)")
        .bind(0, new int[]{1, 2})
        .mapTo(AliasEntry.class)
        .list()
);

得到了一个相当无用的错误：

org.jdbi.v3.core.statement.UnableToExecuteStatementException: org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "[1, 2]"; SQL statement:
SELECT * FROM alias WHERE id IN (?) [22018-200] [statement:"SELECT * FROM alias WHERE id IN (?)", arguments:{positional:{0:[I@52fee500}, named:{}, finder:[]}]

我的代码基于JDBI Developer Guide示例：

handle.createUpdate("insert into groups (id, user_ids) values (:id, :userIds)")
      .bind("id", 1)
      .bind("userIds", new int[] { 10, 5, 70 })
      .execute();

以下代码运行良好：

int id = 1;
handle.createQuery("SELECT * FROM alias WHERE id = ?")
      .bind(0, id)
      .mapTo(AliasEntry.class)
      .findOne()

我做错了什么？

jdbi版本：3.25.0

jdk:16

Answer 1

对于中的子句，您应该在中使用bindList

handle.createQuery("SELECT value FROM items WHERE kind in (<listOfKinds>)")
      .bindList("listOfKinds", keys)
      .mapTo(String.class)
      .list();

// Or, using the 'vararg' definition
handle.createQuery("SELECT value FROM items WHERE kind in (<varargListOfKinds>)")
      .bindList("varargListOfKinds", "user_name", "docs", "street", "library")
      .mapTo(String.class)
      .list();