Passing an Array of Strings to a preparedStatement?

Question

Passing an Array of Strings to a preparedStatement?

Navigation

#1 by (3 votes)

0

I'm 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 looks 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 exception line is:

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

and occurs when I try to create the array.

I've looked everywhere, like passing an Array to an preparedStatement, and everyone talks to do so, but it does not seem to work with SQLServer.

java sql-server jdbc

asked by anonymous 04.07.2017 / 00:29

1 answer

Rename All MySQL Database Foreach in SQL to put a value in a variable

score 3 · Accepted Answer

This problem has no solution. The SQLServer for Java driver does not support the fetature of including arrays.

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

Oracle's documentation explains just that.

Because of what I've been researching, several banks / drivers will no longer implement one feature or another.

Alternatively, you can use StringBuilder and separate your clauses with a 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 setArray() method documentation. It describes the reason why SQLFeatureNotSupportedException can be posted.