Java 如何在 jDBI 中进行查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19424573/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to do in-query in jDBI?
提问by Mohit Verma
How can I execute somethings like this in jDBI ?
我怎样才能在 jDBI 中执行这样的事情？
@SqlQuery("select id from foo where name in <list of names here>")
List<Integer> getIds(@Bind("nameList") List<String> nameList);
Table: foo(id int,name varchar)
桌子： foo(id int,name varchar)
Similar to @SelectProvider from myBatis.
类似于 myBatis 中的 @SelectProvider。
Similar questions has been asked How do I create a Dynamic Sql Query at runtime using JDBI's Sql Object API?, but somehow answer is not clear to me.
有人问过类似的问题如何使用 JDBI 的 Sql 对象 API 在运行时创建动态 Sql 查询？，但不知何故我不清楚答案。
回答by Raven
Use @Define annotation to build dynamic queries in jDBI. Example:
使用@Define 注解在jDBI 中构建动态查询。例子：
@SqlUpdate("insert into <table> (id, name) values (:id, :name)")
public void insert(@Define("table") String table, @BindBean Something s);
@SqlQuery("select id, name from <table> where id = :id")
public Something findById(@Define("table") String table, @Bind("id") Long id);
回答by Deinlandel
This should work:
这应该有效：
@SqlQuery("select id from foo where name in (<nameList>)")
List<Integer> getIds(@BindIn("nameList") List<String> nameList);
Don't forget to annotate class containing this method with:
不要忘记使用以下方法注释包含此方法的类：
@UseStringTemplate3StatementLocator
annotation (beacuse under the hood JDBI uses Apache StringTemplate to do such substitutions). Also note that with this annotation, you cannot use '<' character in your SQL queries without escaping (beacause it is a special symbol used by StringTemplate).
注释（因为在幕后 JDBI 使用 Apache StringTemplate 进行此类替换）。另请注意，使用此注释，您不能在 SQL 查询中使用 '<' 字符而不进行转义（因为它是 StringTemplate 使用的特殊符号）。
回答by Thomas Van Doren
With PostgreSQL, I was able to use the ANY comparison and bind the collection to an array to achieve this.
使用 PostgreSQL，我可以使用 ANY 比较并将集合绑定到数组来实现这一点。
public interface Foo {
@SqlQuery("SELECT id FROM foo WHERE name = ANY (:nameList)")
List<Integer> getIds(@BindStringList("nameList") List<String> nameList);
}
@BindingAnnotation(BindStringList.BindFactory.class)
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.PARAMETER})
public @interface BindStringList {
String value() default "it";
class BindFactory implements BinderFactory {
@Override
public Binder build(Annotation annotation) {
return new Binder<BindStringList, Collection<String>>() {
@Override
public void bind(SQLStatement<?> q, BindStringList bind, Collection<String> arg) {
try {
Array array = q.getContext().getConnection().createArrayOf("varchar", arg.toArray());
q.bindBySqlType(bind.value(), array, Types.ARRAY);
} catch (SQLException e) {
// handle error
}
}
};
}
}
}
NB: ANY is not part of the ANSI SQL standard, so this creates a hard dependency on PostgreSQL.
注意：ANY 不是 ANSI SQL 标准的一部分，因此这会造成对 PostgreSQL 的硬依赖。
回答by Alex Spurling
If you are using the JDBI 3 Fluent API, you can use bindList()with an attribute:
如果您使用的是 JDBI 3 Fluent API，您可以使用bindList()一个属性：
List<String> keys = new ArrayList<String>()
keys.add("user_name");
keys.add("street");
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();
Note how the query string uses <listOfKinds>instead of the usual :listOfKinds.
请注意查询字符串如何使用<listOfKinds>而不是通常的:listOfKinds.
Documentation is here: http://jdbi.org/#_binding_arguments
