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

possible duplicate of [Parameterizing an SQL IN clause?](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause) — artbristol, Oct 24 '12 at 10:18

Jim · Answer 1 · 2012-10-25T14:06:36.597

6

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();
}

edited Oct 25 '12 at 14:06

answered Oct 24 '12 at 11:07

Jim

1,040
1
10
16

it return error, java.sql.SQLFeatureNotSupportedException: This operation is not supported – easyscript Oct 24 '12 at 13:43
Ah your JDBC version doesn't support the optional createArrayOf operation. – Jim Oct 24 '12 at 14:50
Today, I tried two different drivers for MSSQL (jtds and microsoft jdbc). Neither of them has this feature implemented. While it's a part of JDBC4 API, it seems that only a few drivers can handle it (e.g. PGSQL). Since the question is about sql-server, which is how Microsoft calls its MSSQL, I guess your answer is not really helpful here. – Vlasec Jan 29 '15 at 14:51

Dan Iliescu · Answer 2 · 2012-10-24T10:45:21.720

The way I've solved the problem is :

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

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);

}

use java.text.MessageFormat.format() to insert the list of ? into the sql String finalSql = MessageFormat.format(SQL, questionMarksString);
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.

score 1 · Answer 3 · answered Oct 24 '12 at 10:21

1

Did you tried using

    int[] array = {2,6,3,9};
    String str= "SELECT TOP 1 * FROM student WHERE ID NOT IN (?,?,?,?) ORDER BY NEWID()";
    PreparedStatement stat = con.prepareStatement(str);
    for(int i = 1; i <= array.length; i++)
        stat.setString(i,array[i - 1]);

    ResultSet rs = stat.executeQuery();

answered Oct 24 '12 at 10:21

Marl

1,492
2
22
37

how do you increase the number of (?) questionmark? because the length of int[] array is unknown, is it possible to add a loop in the sql statment so as to increase questionmark automatically? – easyscript Oct 24 '12 at 13:28

JAVA sql NOT IN list statement

3 Answers3