如何使用JDBI的Sql Object API在运行时创建动态Sql查询? [英] How do I create a Dynamic Sql Query at runtime using JDBI's Sql Object API?

问题描述

我一直在将现有项目从jdbc迁移到jdbi，并且已经从jdbi的漂亮SQL Object API中大量使用了.我们正在使用mysql.

I've been moving an existing project from jdbc to jdbi, and I've been making much use out of jdbi's beautiful SQL Object API. We're using mysql.

尽管SQL Object API可以构造在编译时已知的已处理查询，但我找不到在运行时生成查询的方法.

While the SQL Object API can construct handled queries that are known at compile time, I couldn't find a way of generating queries at run time.

具体地说，我希望能够执行以下操作:

Specifically, I want to be able to do something like this:

@SqlUpdate(
  "UPDATE record SET "+
    @IfNotZero("foo") "foo=:foo" +
    @IfNotNull("bar") "bar=:bar" +
    @IfNotNull("baz") "baz=:baz" +
  "WHERE id=:id"
)
public abstract int updateRecord(
  @Bind("id") int id,
  @Bind("foo") int foo,
  @Bind("bar") String bar,
  @Bind("baz") String baz
);

推荐答案

JDBI不太适合构建动态查询. IMO，该库的全部目的是尽可能地将代码和SQL查询分开.

JDBI is not very well suited for constructing dynamic queries. IMO the whole point of this library is to separate code and SQL queries as much as possible.

但是，您的特殊情况可以通过SQL解决:

However, your particular case might be solved by means of SQL:

COALESCE(:foo, foo) 

如果'foo'是表中列的名称，并且:foo将解析为NULL，则mysql SET将有效

if 'foo' is the name of the column in the table, and :foo will resolve to NULL, then mysql SET will be effectively

SET foo=foo

即它什么也不会做(在您的情况下是理想的行为).如果:foo不为null，则等同于

i.e. it will do nothing (which is desired beaviour in your case). If :foo is not null, it will be equivalent to

SET foo=:foo

这篇关于如何使用JDBI的Sql Object API在运行时创建动态Sql查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！