CODEWITHSUNDEEP
javamysqlsqljdbc
Alexey
Alexey

Reputation: 517

Update with JDBC for multiple rows

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?

Upvotes: 1

Views: 8409

Answers (2)

everag
everag

Reputation: 7662

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

Upvotes: 4

Brian Agnew
Brian Agnew

Reputation: 272217

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

Upvotes: 0

Related Questions