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:
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;
}
}
