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?

Possible duplicate of [Jdbi - how to bind a list parameter in Java?](http://stackoverflow.com/questions/32526233/jdbi-how-to-bind-a-list-parameter-in-java) — pozs, Mar 10 '17 at 10:47

score 7 · Accepted Answer · answered Mar 10 '17 at 14:59

7

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

answered Mar 10 '17 at 14:59

Saurabh Nayar

387
2
9

3

Looks like the required annotation was changed to `@BindList` in JDBI 3. – raiks Oct 18 '18 at 14:12

score 5 · Answer 2 · answered Oct 18 '18 at 14:11

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

Johan Perez · Answer 3 · 2021-05-26T20:41:02.267

1

@SqlQuery("SELECT ... WHERE xxx in (<uids>)")

@BindIn(value = "uids", onEmpty = BindIn.EmptyHandling.VOID) List<String> ids

don't forget to annotate your class

@UseStringTemplate3StatementLocator

edited May 26 '21 at 20:41

answered May 26 '21 at 20:22

Johan Perez

171
1
4

Use a list of strings in IN clause with JDBI

3 Answers3