Jdbi - 如何在Java中绑定list参数？

问题描述

我们有一个由Jdbi（ org.skife.jdbi.v2 ）执行的SQL语句。对于绑定参数，我们使用Jdbi的 bind 方法：

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

但我们在列表中遇到问题目前我们正在使用 String.format 。所以我们的查询看起来像这样：

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 是替换为 String.format 所以我们必须在java代码中生成一个正确的字符串。然后在替换所有％s 后，我们使用jdbi的 bind 方法替换所有其他参数（：parameterOne 或？）。

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

有没有办法替换 String.format 使用jdbi？有一个方法 bind（String，Object）但默认情况下它不处理列表/数组。我找到了，它解释了如何编写我们自己的工厂用于绑定自定义对象，但它看起来很费劲，特别是对于应该已经支持的东西。

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.

推荐答案

也描述了 @BindIn 注释。这为列表提供了通用的实现。

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

请注意，你必须逃脱所有尖括号< 喜欢这个 \\< 。之前有一篇关于SO的论述：

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?

这篇关于Jdbi - 如何在Java中绑定list参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！