How to use IN operator with JDBI?

Question

I'm trying to do a IN query using MYSQL JDBI on Dropwizard (not relevant, I assume).

@SqlQuery("SELECT id FROM table where field in (<list>)")
List<Integer> findSomething(@BindIn("list") List<String> someList);

As suggested here, I've also annotated the class with

@UseStringTemplate3StatementLocator

But when I'm starting the application, I get the following error:

Exception in thread "main" java.lang.annotation.AnnotationFormatError: Invalid default: public abstract java.lang.Class org.skife.jdbi.v2.sqlobject.stringtemplate.UseStringTemplate3StatementLocator.errorListener()

Does anyone have a good idea on how to solve this issue?

Answer 1

There are two ways to achieve it.

1. Using UseStringTemplate3StatementLocator

This annotation expects Group Files with SQL statement in StringTemplate

Say I have this file PersonExternalizedSqlDAO

// PersonExternalizedSqlDAO.java

package com.daoexp.dao;

@@ExternalizedSqlViaStringTemplate3
@RegisterMapper(PersonMapper.class)
public interface PersonExternalizedSqlDAO {
    @SqlQuery
    List<Person> getPersonByNames(@BindIn("names") List<String> names);
}

Since we are using UseStringTemplate3StatementLocator we have to create *.sql.stg file in same class path. For ex: in resources/com/daoexp/dao/PersonExternalizedSqlDAO.sql.stg

group PersonExternalizedSqlDAO;

getPersonByNames(names) ::= <<
  select * from person where name in (<names>)
>>

Now you should be able to query without any issues.

---

2. Another approach is to use ArgumentFactory that handles your custom data type(in this case List) for JDBI with @Bind. This is most preferable approach.

So create this list argument factory

public class ListArgumentFactory implements ArgumentFactory<List> {
    @Override
    public boolean accepts(Class<?> expectedType, Object value, StatementContext ctx) {
        return value instanceof List;
    }

    @Override
    public Argument build(Class<?> expectedType, final List value, StatementContext ctx) {
        return new Argument() {
            @Override
            public void apply(int position, PreparedStatement statement, StatementContext ctx) throws SQLException {
                String type = null;
                if(value.get(0).getClass() == String.class){
                    type = "varchar";
                } else if(value.get(0).getClass() == Integer.class){
                    // For integer and so on...
                } else {
                    // throw error.. type not handled
                }
                Array array = ctx.getConnection().createArrayOf(type, value.toArray());
                statement.setArray(position, array);
            }
        };
    }
}

What this class does ?

• accepts instance of List
• convert the integer/string list to array and binds with prepared statement

Make sure you register this argument factory with your DBI instance.

final DBIFactory factory = new DBIFactory();
final DBI jdbi = factory.build(environment, configuration.getDataSourceFactory(), "h2");
jdbi.registerArgumentFactory(new ListArgumentFactory());

Now you should be able to query using List in more simpler way(i.e) you have to use @Bind. Thats it.

@RegisterMapper(PersonMapper.class)
public interface PersonDAO {
    @SqlQuery("select * from person where name = any(:names)")
    List<Person> getPersonByNames(@Bind("names") List<String> names);
}

Refer:

• jdbi doc

Additional Info:

// PersonMapper.java
public class PersonMapper implements ResultSetMapper<Person> {

    public Person map(int index, ResultSet r, StatementContext ctx) throws SQLException {
        Person person = new Person();
        person.setId(r.getInt("id"));
        person.setName(r.getString("name"));

        return person;
    }
}