I had a problem when trying to bind a Java array to an IN clause with JDBI. Here are the solutions I found working.

The problem

I was hoping the following code snipet would work with JDBI. It truns out to be not working.

try (Handle handle = dbi.open()) {
    return handle.createQuery("SELECT COUNT(1) FROM company WHERE name IN ( :company_names )");
            .bind("company_names", new String[]{"aol", "msft", "yahoo"})
            .map(IntegerColumnMapper.PRIMITIVE)
            .first();
}

The reason, I guess, is that the array type is not equivalent to a table expression, which is conceptually a set. Furthermore, for the JDBI array binding to work, the parameter value to be bound needs to be of the type java.sql.Array.

The solutions

Following the above discussions, it is easier to understand why the following code works.

try (Handle handle = dbi.open()) {
    return handle.createQuery("SELECT COUNT(1) FROM company WHERE name IN (SELECT * FROM UNNEST(:company_names))");
            .bind("company_names", handle.getConnection().createArrayOf("text", new String[]{"aol", "msft", "yahoo"}))
            .map(IntegerColumnMapper.PRIMITIVE)
            .first();
}

Note the SELECT * FROM UNNEST(:company_names) part to convert the sql array passed in into a table expression, and the handle.getConnection().createArrayOf("text", new String[]{"aol", "msft", "yahoo"}) part to convert a normal Java array into a java.sql.Array of text type.

As an alternative, this also works:

try (Handle handle = dbi.open()) {
    return handle.createQuery("SELECT COUNT(1) FROM company WHERE ARRAY[name] && :company_names");
            .bind("company_names", handle.getConnection().createArrayOf("text", new String[]{"aol", "msft", "yahoo"}))
            .map(IntegerColumnMapper.PRIMITIVE)
            .first();
}

The ARRAY[name] part converts the name column value into an array with a single element, and then tests for intersection with the array passed in as a parameter.

The above workarounds only work for PostgreSQL. A more generic (and complex) solution would be to generate the sql string dynamically (e.g., using a template engine), then bind the array elements one by one in a loop.

References