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

Deinlandel · Accepted Answer · 2013-10-23 07:28:40Z

5

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

answered Oct 23, 2013 at 7:28

Deinlandel

1,0331 gold badge8 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hans Z Over a year ago

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.

Cemo · Accepted Answer · 2013-10-08 13:07:09Z

4

See @Define annotation and UseStringTemplate3StatementLocator usage.

answered Oct 8, 2013 at 13:07

Cemo

5,61011 gold badges54 silver badges83 bronze badges

2 Comments

Yuriy Zubarev Over a year ago

Perfect! This just gave me a reason to continue to use JDBI

Steve K Over a year ago

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?

David Phillips · Accepted Answer · 2016-06-01 19:02:44Z

0

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

answered Jun 1, 2016 at 19:02

David Phillips

10.2k7 gold badges45 silver badges55 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related