Use a list of strings in IN clause with JDBI



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

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



Source: stackoverflow