javapostgresqljdbcjdbijdbi3

How to use Jdbi to create multi argument IN clause


I have a batch of values in a map and need to update a boolean column in my database with for rows matching both values in the map e.g.

Map.of("a", 1,
       "b", 2)

the update I want to run is

update table set zoo = true where (foo, bar) in (:values)

where the map entries each get turned into

... in (('a', 1), ('b', 2))

in spring-data this happens when you pass a list of objects as a parameter but I can't find a way of making it work with JDBI. I'm specifically using the non annotation driven JDBI.

I've tried passing lists, maps and map entries as arguments but it doesn't look like they're handled this way by default.


Solution

  • I achieved this using bindBeanList and reading the Map#entrySet

    var values = Map.of("a", 1,
                        "b", 2);
    
    jdbi.withHandle(h -> h.createUpdate("""
        update table
        set zoo = true
        where (foo, bar) in (<entries>)
        """)
        .bindBeanList("entries",
            List.copyOf(values.entrySet()),
            List.of("key", "value"))
        .execute());