JAVA sql NOT IN list statement

Question

Am trying to select record from database where record id NOT IN list.

take a look @ my problem below.

String Sqlids = "2,6,3,9"; // this is dynamic so the number of element is unknown

String str= "SELECT TOP 1 * FROM student WHERE ID NOT IN (2,6,3,9) ORDER BY NEWID()";
PreparedStatement stat = con.prepareStatement(str);
ResultSet rs = stat.executeQuery();

The above statement work FINE, but if i change it to

String Sqlids = "2,6,3,9";
String str= "SELECT TOP 1 * FROM student WHERE ID NOT IN (Sqlids) ORDER BY NEWID()";
PreparedStatement stat = con.prepareStatement(str);
ResultSet rs = stat.executeQuery();

//i also try this
String Sqlids = "2,6,3,9";
String str= "SELECT TOP 1 * FROM student WHERE ID NOT IN (?) ORDER BY NEWID()";
PreparedStatement stat = con.prepareStatement(str);
stat.setString(1,Sqlids );

ResultSet rs = stat.executeQuery();

THE ABOVE STATEMENT DOESN'T FILTER Since Sqlids is one string is seeing it as one parameter so it return repeated rows, is there an integer format for storing values like 2,6,3,9 ? since the Sqlids is from an arraylist called SqlidList i try somtin like this

Iterator iTr = SqlidList.iterator();
while(iTr.hasNext()){
    stat.setString(1,iTr.next().toString()+",");
}

but the setString(1,--) is not available since is in a while loop

Answer 1

Use Connection#createArrayOf after converting your ids to a String[]

String[] ids = {"2", "6", "3", "9"};

String str= "SELECT TOP 1 * FROM student WHERE ID NOT IN ? ORDER BY NEWID()";
PreparedStatement stat = con.prepareStatement(str);
stat.setArray(1, con.createArrayOf("text",ids));
ResultSet rs = stat.executeQuery();

If createArrayOf is not supported by your JDBC driver (as in this case) I'd probably just construct the query string in place e.g:

String Sqlids = "2,6,3,9";
String str= "SELECT TOP 1 * FROM student WHERE ID NOT IN ("+Sqlids+") ORDER BY NEWID()";

or if you have a collection of ids use a utility method to create the array content:

public static String toSqlArray(List<String> strings) {
    StringBuilder sb = new StringBuilder();
    boolean doneOne = false;
    for(String str: strings){
        if(doneOne){
            sb.append(", ");
        }
        sb.append("'").append(str).append("'");
        doneOne = true;
    }
    return sb.toString();
}

Answer 2

The way I've solved the problem is :

1. SQL = "...WHERE ID NOT IN ({0}) ..."

2. have a method which builds a string containing a number of ? equal to the size of SqlidList

   public static String buildQuestionMarks(final int count) {
   StringBuilder sb = new StringBuilder();
   for (int i = 0; i < count; i++) {
      sb.append("?" + ",");
   }
   return sb.substring(0, sb.length() - 1);
   

   }

3. use java.text.MessageFormat.format() to insert the list of ? into the sql String finalSql = MessageFormat.format(SQL, questionMarksString);

4. have a method to set the params on teh prepared statement. Something similar to what you wrote although you need to increment the first parameter of stat.setString()

This should work for variable number of parameters.