Update with JDBC for multiple rows

Question

I have a very simple SQL "update table where ID in (1,2,3)"

When I am doing

String sql = "update table where ID in (?)";
List<Integer> ids = new ArrayList();
ids.add(1);
ids.add(2);
ids.add(3);
jdbc.update(sql, new Object[] { ids });

I recieve wrong sql in output

update table where ID in ('1,2,3')

How can I rid of this extra quote?

Answer 1

Unfortunately, you can't pass an Array as a Parameter in a PreparedStatement. A similar question can be found here:

PreparedStatement IN clause alternatives?

And some alternatives can be found here:

http://www.javaranch.com/journal/200510/Journal200510.jsp#a2

Answer 2

Your SQL statement needs to be of the form:

UPDATE table_name
SET column1=?, column2=?,...
WHERE some_column=some_value

i.e. note that

• you have to specify the columns that you want to update
• for each value that you need to provide, you need a placeholder ? symbol
• for each value, you need a corresponding setInt(pos, value) call

Check out the PreparedStatement tutorial for more info. Excerpt below:

String updateString =
    "update " + dbName + ".COFFEES " +
    "set SALES = ? where COF_NAME = ?";
updateSales = con.prepareStatement(updateString);
updateSales.setInt(1, 100);
....