Passing an Array of Strings to a preparedStatement?

Question

I am trying to pass an array of Strings to a preparedStatement, but it’s returning this exception:

java.sql.SQLFeatureNotSupportedException: This operation is not supported.
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.createArrayOf(SQLServerConnection.java:2763)
    at entity.dao.getRecords(Dao.java:168)
    at entity.dao.main(Dao.java:227)

My code is like this:

public List<Record> getRecords() throws SQLException {
        String sql = "select * from table where clause in (?)";

        PreparedStatement ps = this.connection.prepareStatement(sql);

        List<String> strings = new ArrayList<>();
        strings.add("string1");
        strings.add("string2");
        strings.add("string3");

        Array array = this.connection.createArrayOf("VARCHAR", strings.toArray());

        ps.setArray(1, array);

        ResultSet executeQuery = ps.executeQuery();
        List<Record> records = new ArrayList<Record>();
        Record record;
        while (executeQuery.next()) {
            // ...
        }
        return records;
    }

the line of exception is:

Array array = this.connection.createArrayOf("VARCHAR", strings.toArray());

and occurs when I try to create the array.

I’ve looked everywhere, how to pass an Array to a preparedStatement, and everyone talks to do so, but it doesn’t seem to work with Sqlserver.

Answer 1

This problem has no solution. The Sqlserver driver for Java does not support fetature to include arrays.

This exception is released when the driver you are using does not support or has not implemented an optional JDBC function.

To documentation Oracle explains exactly this.

From what I’ve been researching, several databases/ drivers fail to implement a Feature or other.

---

Alternatively, you can use a StringBuilder and separate their clauses with comma and make a setString() normal. For example:

List<String> strings = new ArrayList<>();
strings.add("string1");
strings.add("string2");
strings.add("string3");
.
.
.
strings.add("stringN");

//itera a lista  
StringBuilder ins = new StringBuilder();
for(String val : strings) {
  ins.append(val);
  ins.append(", ");
}
//remove o ultimo ", "
ins.delete((ins.length() - 2), ins.length());

//finalmente inclui no PreparedStatement
ps.setString(1, ins.toString());

---

For reference, here is the documentation of the method setArray(). It describes why the SQLFeatureNotSupportedException can be cast.