Is there support for list in bind method?

[kobayashirgp]

Is there support for list in bind method? I'm trying to perform a query with an "IN (list of numbers)" clause and an exception is thrown

java.lang.IllegalArgumentException: Unsupported Java type:class java.util.ArrayList
    at oracle.r2dbc.impl.OracleStatementImpl.requireSupportedJavaType(OracleStatementImpl.java:1554) ~[oracle-r2dbc-0.3.0.jar:0.3.0]

[Michael-A-McMahon]

Sorry, but we don't support List as a bind type. You'd need to generate the parameter markers, and then set a bind for each. Maybe something like the example below would work?

import io.r2dbc.spi.Connection;
import io.r2dbc.spi.ConnectionFactories;
import io.r2dbc.spi.Statement;
import reactor.core.publisher.Flux;

import java.util.Collection;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * This code will bind a variable length of values into an IN clause
 */
public class InClause {

  public static void main(String[] args) {

    // Query values having ids from this list
    List<Integer> ids = List.of(1, 2, 3, 4, 5);

    Flux.usingWhen(
      // TODO: Configure a connection to your own database
      ConnectionFactories.get(
        "r2dbc:oracle://test:test@localhost:1521/pdb21")
        .create(),
      connection -> {
        Statement statement = connection.createStatement(
          "SELECT value FROM test WHERE id " + generateInClause(ids.size()));
        // Provide the offset of 0. If there are other ? markers before the
        // IN clause, then provide the offset after those markers.
        bindInClause(statement, 0, ids);
        return Flux.from(statement.execute())
          .flatMap(result ->
            result.map(row -> row.get("value")));
      },
      Connection::close)
      .toStream()
      .forEach(System.out::println);
  }

  /**
   * Generates an IN clause with the given {@code length} of parameter markers.
   * For example, if the {@code length} is 3, this method returns:
   * <pre>
   *   IN (?, ?, ?)
   * </pre>
   */
  static String generateInClause(int length) {
    return "IN ("
      + Stream.generate(() -> "?")
          .limit(length)
          .collect(Collectors.joining(", "))
      + ")";
  }

  /**
   * Binds {@code values} to {@code statement}, starting a given
   * {@code offset}.
   */
  static void bindInClause(
    Statement statement, int offset, Collection<?> values) {
    for (Object value : values)
      statement.bind(offset++, value);
  }
}

[Michael-A-McMahon]

Also, because ARRAY might be a logical SQL type mapping for a Java List, I was curious to see if Oracle JDBC would support binding a java.sql.Array to an IN(?) clause:

  static void jdbc() throws SQLException {
    try (var connection = DriverManager.getConnection(
      "jdbc:oracle:thin:@localhost:1521/pdb21", "test", "test")) {

      try (PreparedStatement preparedStatement = connection.prepareStatement(
        "SELECT value FROM test WHERE id IN (?)")) {
        Array array = connection.unwrap(OracleConnection.class)
          .createOracleArray("TEST.NUMBER_ARRAY", new int[]{1,2,3});
        preparedStatement.setObject(1, array);

        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next())
          System.out.println(resultSet.getString(1));
      }
    }
  }

Sadly, the code above results in: ORA-00932: inconsistent datatypes: expected NUMBER got TEST.NUMBER_ARRAY

This error seems to indicate that the database itself does not support binding an ARRAY type as the operand of a IN clause.

[kobayashirgp]

Thanks, your example worked, it will definitely help other people with this same question!

[mcpiroman]

When using the repeated ? solution, do remember that, at least some versions of, Oracle database support up to 1000 bind variables per query. So if you try to pass list longer than, at most, 1000, you will get exception.