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.
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?