How to pass a List of Integer value with PreparedStatement

Question

I try to write a code for make a SELECT on MySql DB like this

SELECT MESE,IMPORTO,ANNO FROM VISTASTATISTICHEMENSILI WHERE ANNO in(?)

So, I would like to pass a list of Integer value like this:

PreparedStatement stmt = db.prepareStatement(queryDettaglio);
Integer[] myArr = new Integer[2];
myArr[0] = 1;
myArr[1] = 2;
stmt.setArray(1, db.createArrayOf("INTEGER", myArr));  
ResultSet rs = stmt.executeQuery();

So, when I try to run this code I have this error:

DEBUG [AWT-EventQueue-0] (MyLog4J.java:45) - java.sql.SQLFeatureNotSupportedException
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at java.lang.Class.newInstance0(Class.java:357)
    at java.lang.Class.newInstance(Class.java:310)
    at com.mysql.jdbc.SQLError.notImplemented(SQLError.java:1332)
    at com.mysql.jdbc.JDBC4Connection.createArrayOf(JDBC4Connection.java:58)

When I fix it?

Reguards

You cannot do that. See http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives or http://stackoverflow.com/questions/3107044/preparedstatement-with-list-of-parameters-in-a-in-clause — Erik Pragt, Sep 04 '14 at 20:24
The exception you're getting is "Feature not supported". Check this out: http://stackoverflow.com/questions/12176709/how-can-i-simulate-an-array-variable-in-mysql — Darius X., Sep 04 '14 at 20:27

score 1 · Answer 1 · answered Sep 04 '14 at 21:03

You can not pass an array to a ? in a prepared statement. The correct way to do it and avoid all chance of injection attack is like this:

StringBuilder idList = new StringBuilder()
for (int id : myArr) {
   if (idList.length() > 0) {
     idList.append(",");
   }
   idList.append("?");
}
PreparedStatement ps = con.prepareStement("SELECT MESE,IMPORTO,ANNO FROM VISTASTATISTICHEMENSILI WHERE ANNO in("+idList+");
for (int i = 0; i < myArr.length; i++) {
  ps.setInt(i+1,myArr[i]);
}

Basically you are building a prepared statement with the right number of ? marks and then setting the parameters in.

Because myArr is declared as an Integer[] array, you can also just do this:

StringBuilder idList = new StringBuilder()
for (int id : myArr) {
   if (idList.length() > 0) {
     idList.append(",");
   }
   idList.append(id);
}
Statement stmt = con.createStatement();
stmt.executeQuery("SELECT MESE,IMPORTO,ANNO FROM VISTASTATISTICHEMENSILI WHERE ANNO in("+idList+");

There is no issue with injection because the integers can't possibly have injected characters (if they did, they wouldn't be integers)

score 0 · Answer 2 · answered Sep 04 '14 at 21:07

Try binding n integers manually inside for loop.

Before that you should create ?,?,...,? pattern in sql. Good luck.

import org.apache.commons.lang3.StringUtils;

/*
 * example: for n=4 creates pattern: ?,?,?,?
 */
public String createInListPattern(int n) {
    return StringUtils.repeat("?", ",", n);
}

public void doSelect(Integer[] myArr, Connection conn) {
    int size = myArr.length;
    String sql = "SELECT MESE,... FROM TABLE WHERE ANNO in ("
                    + createInListPattern(size) + ")";

    // be sure to properly handle sql exceptions
    PreparedStatement stmt = conn.prepareStatement(sql);

    // bind parameters
    for (int i = 0; i < size; i++) {
        stmt.setInt(i + 1, myArr[i]);
    }

    // execute query
    ResultSet rs = stmt.executeQuery();
}

How to pass a List of Integer value with PreparedStatement

2 Answers2