how to send a list of integer values into prepared statement

Question

I have been looking for sending a list of integer array into prepared statement, but i was unable to do it. can somebody help me do it.

my code as follows:

public int  Verify(int[] val) {
  try {
if (connection != null) {
    pstmt = connection.prepareStatement("select cola from tableA where val in(?)");
    pstmt.setInt(1, val);
    rs = pstmt.executeQuery();

i know this wont work.can someone help me to send all values in array at one into "?".

Any help could be greatly appreciated.

Note that I prefer the most voted answer rather than the accepted answer. — Luiggi Mendoza, May 03 '14 at 15:29
I think you should use the setArray- Method: http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setArray%28int,%20java.sql.Array%29 — pL4Gu33, May 03 '14 at 15:31

score 1 · Accepted Answer · answered May 03 '14 at 15:43

Try something like this. To construct proper query you have to be careful while appending the last ? in the query string. One more problem I see is that you pass an integer array and you are trying to set Strings in psmt.

// Array of values to be passed in query
        String[] values;

        // Construct the query string first
        StringBuilder stringBuilder = new StringBuilder(
                "select cola from tableA where val in(");

        for (int i = 0; i < values.length; i++) {
            if (i + 1 == values.length) {
                stringBuilder.append("?)");
            } else {
                stringBuilder.append("?,");
            }
        }

        pstmt = connection.prepareStatement(stringBuilder.toString());
        int i = 0;
        for (String value : values) {
            pstmt.setString(i++, value);
        }

        rs = pstmt.executeQuery();

// Hope it helps

score 0 · Answer 2 · edited Aug 27 '19 at 06:16

0

You have answer here.

It would be the answer for this question. Just convert your array to Arrays.asList(arrayVariable) and do the process as mentioned there.

edited Aug 27 '19 at 06:16

Mike Doe

16,349
11
65
88

answered May 03 '14 at 15:35

Surendheran

187
1
18

score 0 · Answer 3 · answered May 03 '14 at 15:49

Step 1: If you are not sure that your int[] contains unique values, try using a Set.

Set<Integer> valSet = new HashSet<Integer>(Arrays.asList(val));

Step 2: You should either use Kannan's post or ... In your case where ? values are int only, I think that you can get rid of preparedStatement and you may use a simple statement as well, it seems to be safe.

Just create your query from the Set (or array)

StringBuilder b = new StringBuilder("SELECT cola FROM tableA WHERE val IN(");
for (Integer a: valSet) {
    b.append(a);
    b.append(",");
}
b.setLength(b.length()-1); //remove last comma
b.append(")");
String query = b.toString();

how to send a list of integer values into prepared statement

3 Answers3