[FEATURE REQUEST] Pass array to WHERE IN statements.

[johnnybigoode-zz]

I'm a little irritated when I need to use JDBC for any kind of query that demands complicated WHERE clauses.

Since requirements state that we are able to filter the lowest grain over 11 different parameters of a query, WHERE X IN (Z), where Z is an array would be the logical solution.

So, getting the array information from a request could lead to a prepared statement like this:

Array arrayToSet = this.connection.createArrayOf("String", paramArrayString.split(","));
this.preparedStatement .setArray(x, pp);

But any attempt of using the setArray returns throwNotSupportedException, according to source code:

@Override
    public final void setArray(int i, java.sql.Array x) throws SQLException {
        SQLServerException.throwNotSupportedException(connection, this);
    }

Stackoverflow seems to understand this as stated in the top voted answer:

There's indeed no straightforward way to do this in JDBC. Some JDBC drivers seem to support PreparedStatement#setArray() on the IN clause. I am only not sure which ones that are.

Another reply to another question seems more concise:

The suggested options are:

• Prepare SELECT my_column FROM my_table WHERE search_column = ?, execute it for each value and UNION the results client-side. Requires only one prepared statement. Slow and painful.

• Prepare SELECT my_column FROM my_table WHERE search_column IN (?,?,?) and execute it. Requires one prepared statement per size-of-IN-list. Fast and obvious.

• Use a stored procedure to construct the result set.

• Prepare N different size-of-IN-list queries; say, with 2, 10, and 50 values. To search for an IN-list with 6 different values, populate the size-10 query so that it looks like SELECT my_column FROM my_table WHERE search_column IN (1,2,3,4,5,6,6,6,6,6). Any decent server will optimize out the duplicate values before running the query.

But he states in the end:

The Right Answer, if you are using JDBC4 and a server that supports x = ANY(y), is to use PreparedStatement.setArray as described here.

In the other question there's a small implementation which solves the problem:

/**
 * Converts a SQL statement containing exactly one IN clause to an IN clause
 * using multiple comma-delimited parameters.
 *
 * @param sql The SQL statement string with one IN clause.
 * @param params The number of parameters the SQL statement requires.
 * @return The SQL statement with (?) replaced with multiple parameter
 * placeholders.
 */
public static String any(String sql, final int params) {
    // Create a comma-delimited list based on the number of parameters.
    final StringBuilder sb = new StringBuilder(
            new String(new char[params]).replace("\0", "?,")
    );

    // Remove trailing comma.
    sb.setLength(Math.max(sb.length() - 1, 0));

    // For more than 1 parameter, replace the single parameter with
    // multiple parameter placeholders.
    if (sb.length() > 1) {
        sql = sql.replace("(?)", "(" + sb + ")");
    }

    // Return the modified comma-delimited list of parameters.
    return sql;
}

The setArray example in JDBC 4.1 specs states that

methods setArray and setObject may be called to
pass an Array value as an input parameter to a PreparedStatement object.

and while the example mentioned talks about storing array objects, its use for IN or X = ANY(y) seems universal, while not generally implemented.

[cheenamalhotra]

Hi @johnnybigoode

Investigations are underway, we'll let you know soon!

[johnnybigoode-zz]

@cheenamalhotra Thank you. If you need help to implement a solution, just pass me some direction that I'll gladly do it.

[cheenamalhotra]

Hi @johnnybigoode

As per JDBC Specifications, setArray() API:
Sets the designated parameter to the given java.sql.Array object. The driver converts this to an SQL ARRAY value when it sends it to the database.

I'm sure you're aware that SQL Server has no SQL ARRAY equivalent Type. Hence the driver cannot send a SQL ARRAY type object to database. This is the main reason why this API could never be supported.

Now coming to your proposal, if you are trying to work with java.sql.Array object by:

1. First, converting a SQL query containing IN clause to an IN clause with multiple comma-delimited parameters based on Array size.
2. And then, opening Array object internally in the driver and attaching them to the prepared statement on execution.

IMO, I don't like the idea and it does not seem a promising solution due to many factors, some of which are below:

• It does not still respect JDBC Specifications to pass java.sql.Array object to an equivalent SQL ARRAY object.
• The implementations of java.sql.Array are very different than being just list of values, and have further complexities related to them which will not get supported completely due to SQL Server limitations.
• Its an overhead for the driver to parse query and replace IN clause in the query based on Array Size and then open the Array object and attach the data to the SQL Query.
• The driver will also have to handle duplicate data possibilities, etc.

It looks like a forced solution to make the API work, and not something that respects specifications with a clean defined solution. If you think differently and have a cleaner solution to answer all the concerns I mentioned, please go ahead with a PR against dev branch, we can review it.

[johnnybigoode-zz]

@cheenamalhotra

Oh wow, thanks for the detailed reply. I trully appreciate it.

And I guess since SQL Server has no SQL ARRAY, it's obviously not a good idea to implement a hack for a specific use-case.

I guess this issue should be closed until further notice.