Use a list of strings in IN clause with JDBI

Question

I'm using JDBI / Dropwizard for a project and would like to run some simple queries. I have a query like so:

private static final String GET_STUFF = "SELECT * FROM myTable WHERE state IN (:desiredState)"

I bind the variable in my method like so:

@Mapper(MyType.class)
@SqlQuery(GET_STUFF)
public MyType getStuff(@Bind(desiredState) List<String> states);

However, I get the following error when running:

org.skife.jdbi.v2.exceptions.UnableToCreateStatementException: 
Exception while binding 'desiredState'....
Caused by: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.

I'm passing in states as an ArrayList of type String, so I'm a bit confused what's going on here. Does anyone know the correct way to do In with JDBI?

Answer 1

Use annotation @BindIn instead of @Bind. Also, :desiredState should be written as <desiredState>

Answer 2

Here's what worked for me (JDBI 3.1.1):

import org.jdbi.v3.sqlobject.customizer.BindList;
import org.jdbi.v3.sqlobject.statement.SqlUpdate;

public interface JdbiRoom {
    @SqlUpdate("update Room set available = 0 where id in (<roomIds>)")
    int markRoomsAsUnavailable(@BindList("roomIds") List<Long> roomIds);
}

Also, we discovered that @BindIn doesn't play well with @UseStringTemplateSqlLocatorannotation, so roomIds must be passed as a query parameter when using named queries (see https://github.com/jdbi/jdbi/issues/1131) for details:

markRoomsAsUnavailable(roomIds) ::= <<
    update rooms set available = 0 where id in (<roomIds>)
>>