How to use the IN operator in conjunction with the JDBI?

Question

Good afternoon.

I'm trying to bind a list with strings to the query that the IN operator uses. im use Oracle. I did following the example that was described by the link: How to use IN operator with JDBI?

List<String> ms = new ArrayList();
    ms.add("Novosibirsk");
    ms.add("Perm");

public interface CityDAO {
   @RegisterMapper(CitiesMapper.class)
   @SqlQuery("SELECT *
              FROM Universities
              WHERE Location IN (:cities)")
   List<cities> getItems(@Bind("cities") List<String> cities);}
 }

I created a ListArgumentFactory

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

I registered the factory

   public class DBI extends AbstractModule {
     private DBI dbi;

     @Override
     protected void configure() {
       this.dbi = new DBI(provideConfig().url());
       this.dbi.registerArgumentFactory(new ListArgumentFactory());
    }
  }

But when I make a request I get an exception

org.skife.jdbi.v2.exceptions.UnableToCreateStatementException: Exception while binding 'cities' [statement:"SELECT * FROM Universities WHERE Location IN (:cities)", arguments:{ positional:{}, named {cities:factory.ListArgumentFactory$1@6788168c}, finder:[]}]

Help me figure out what I'm doing wrong

What is the cause of the exception? I have tried the same on a local environment using Oracle and I am getting: `Caused by: java.sql.SQLException: Unsupported feature at oracle.jdbc.driver.PhysicalConnection.createArrayOf(PhysicalConnection.java:9283)` — André Barbosa, Oct 28 '17 at 20:02
hello @AndréBarbosa. Strange, in jdbc there is a method createArrayOf — DmitryJS, Oct 29 '17 at 12:12

score 0 · Answer 1 · answered Oct 29 '17 at 17:14

According to the JDBI documentation, achieving something like that using Oracle could be quite complex so might be a better idea to use the first approach described (UseStringTemplate3StatementLocator):

Oracle supports something similar, but you need to use Oracle specific APIs and oracle.sql.ARRAY instances. In the Oracle case you have to pre-declare the array type in the database first, and as it stores the array in the database, free it after the call.

Having said that, there is a simple approach that can be used to make this work in Oracle which is to join the elements in the list with a comma. I have modified the ListArgumentFactory using the Java 8 String.join method:

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 {
                statement.setString(position, String.join(",", value));
            }
        };
    }
}

I have tried the approach described in the JDBI documentation to use oracle.sql.ARRAY and a custom TYPE in the Oracle DB but was not successful for me.

How to use the IN operator in conjunction with the JDBI?

1 Answers1