How do I create a Dynamic Sql Query at runtime using JDBI's Sql Object API?

Question

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.

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

score 5 · Accepted Answer · answered Oct 23 '13 at 07:28

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.

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

COALESCE(:foo, foo)

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

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

That's what I ended up doing. I should add that you you should create a custom @Binder object to handle other conditions, i.e. Update if not zero. — Hans Z, Oct 23 '13 at 16:11

score 4 · Answer 2 · answered Oct 08 '13 at 13:07

4

See @Define annotation and UseStringTemplate3StatementLocator usage.

answered Oct 08 '13 at 13:07

Cemo

5,370
10
50
82

Perfect! This just gave me a reason to continue to use JDBI – Yuriy Zubarev Nov 15 '14 at 00:34
2

I've looked at both the `@Define` and `@UseStringTemplate3StatementLocator` annotations, and I don't see how either or both of them could combine to solve the OP's problem. Could you please explain in a little more detail, or provide an example? – Steve K Dec 15 '14 at 21:50

score 0 · Answer 3 · answered Jun 01 '16 at 19:02

You can write a query like this:

UPDATE record SET
  foo = CASE WHEN :foo > 0 THEN :foo ELSE foo END
, bar = CASE WHEN :bar > 0 THEN :bar ELSE bar END
, baz = CASE WHEN :baz > 0 THEN :baz ELSE bar END
WHERE id = :id

In other words, you set the column to the variable if it matches condition, otherwise set it to itself (i.e. don't set it).

How do I create a Dynamic Sql Query at runtime using JDBI's Sql Object API?

3 Answers3

Linked