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