How do I bind a list of tuples in jdbi?

Question

I have a mysql query in this format

SELECT * from xyz where (key1, key2) in (('val1', 'val2'), ('val3', 'val4'));

I'm using jdbi to make this query. How do I bind the list of tuples in jdbi ? I was trying to use something like this

List<String[]> query = new ArrayList<>();
for(String key: vars.keySet()){
  String[] entry = {key, vars.get(key)};
  query.add(entry);
}
List<String> result = getBasicQuery() + " WHERE (key, val) in (<query>)".bindList("query", query);

Getting this error on using bind this way

 No argument factory registered for '[Ljava.lang.String;@11fa461a' of type class [Ljava.lang.String;

score 7 · Answer 1 · answered Nov 28 '19 at 01:02

It's actually quite easy to do this in JDBI3, but for some reason the solution isn't documented on jdbi.org.

Here it is:

List<String> result = handle.createQuery(getBasicQuery() + " WHERE (key, val) in (<query>)")
    .bindMethodsList("query", query, List.of("getKey", "getValue"))
    .mapTo(String.class)
    .list();

Note that to use bindMethodsList() you'll need to have your query objects defined as instances of a class with public methods (ie. getters). Something like this would work just fine:

class Query {
  String key;
  String value;

  Query(String key, String value) {
    this.key = key;
    this.value = value;
  }

  public String getKey() {
    return key;
  }

  public String getValue() {
    return value;
  }
}

Alternatively, you can skip the getters by making the properties themselves public and using bindBeanList().

Both approach throws UnableToCreateStatementException error for me: https://stackoverflow.com/questions/76667296/jdbi-bindmethodslist-bindbeanlist-throws-unabletocreatestatementexception — EzyHoo, Jul 12 '23 at 16:38

score 1 · Answer 2 · answered Jan 05 '22 at 12:58

1

As @talon55 said, bindBeanList method is an alternative:


String QRY_STRING = "SELECT id FROM settlement_report WHERE (user_id, reference_id, has_type_id, record_type) IN (<conditions>)";

List<String> fieldsBean = Arrays.asList("userId", "referenceId", "hasTypeId", "recordType");

handle.createQuery(QRY_STRING)
.bindBeanList("conditions", records, fieldsBean);

answered Jan 05 '22 at 12:58

Sebastian Cardona Osorio

475
7
8

This approach throws UnableToCreateStatementException error for me: https://stackoverflow.com/questions/76667296/jdbi-bindmethodslist-bindbeanlist-throws-unabletocreatestatementexception – EzyHoo Jul 12 '23 at 16:39

score 1 · Answer 3 · answered Mar 08 '23 at 20:00

You can also use annotated interface methods for this query. Following mentioning my findings extending the example given by @talon55.

@RegisterRowMapper(xyzMapper.class)
public interface xyzDAO {

    @SqlQuery("SELECT * FROM xyz WHERE (key, value) in (<query>)")
    List<String> findAll(@BindMethodsList(value = "query", methodNames = {"getKey", "getValue"}) List<Query> query) throws UnableToExecuteStatementException;

}

How do I bind a list of tuples in jdbi?

3 Answers3