sql - Jdbi - how to bind a list parameter in Java?

Question

We have an SQL statement which is executed by Jdbi (org.skife.jdbi.v2). For binding parameters we use Jdbi's bind method:

Handle handle = ...
Query<Map<String, Object>> sqlQuery = handle.createQuery(query);
sqlQuery.bind(...)

However we have a problem with in-lists and currently we are using String.format for this. So our query can look like this:

SELECT DISTINCT
    tableOne.columnOne,
    tableTwo.columnTwo,
    tableTwo.columnThree
FROM tableOne
JOIN tableTwo
    ON tableOne.columnOne = tableTwo.columnOne
WHERE tableTwo.columnTwo = :parameterOne
    AND tableTwo.columnThree IN (%s)

%s is replaced by String.format so we have to generate a proper string in java code. Then after all %s are replaced we are using jdbi's bind method to replace all other parameters (:parameterOne or ?).

Is there a way to replace String.format with jdbi? There is a method bind(String, Object) but it doesn't handle lists/arrays by default. I have found this article which explains how to write our own factory for binding custom objects but it looks like a lot of effort, especially for something that should be already supported.

Answer 1

The article you linked also descibes the @BindIn annotation. This provides a general purpose implementiation for lists.

@UseStringTemplate3StatementLocator
public class MyQuery {
  @SqlQuery("select id from foo where name in (<nameList>)")
  List<Integer> getIds(@BindIn("nameList") List<String> nameList);
}

Please note that you'll have to escape all pointy brackets < like this \<. There is a previous discusion on SO: How to do in-query in jDBI?