[SOLVED] Bind array with JDBI with "IN" clauses

Bind array with JDBI with "IN" clauses

I'm trying to execute the following code:

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

And got a quite uninformative error:

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:[]}]

My code based on JDBI Developer Guide example:

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

And the following code works well:

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

What am I doing wrong?

jdbi version: 3.25.0

jdk: 16

Solution

For in clause you should use bindList with <name> syntax, example from the JDBI doc

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