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;

Answer 1

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

Answer 2

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