Using JDBC, how can I substitute multiple IDs into "DELETE FROM T WHERE id IN (?)"

Question

I have some code that produces a set of primary key values that I want to delete from a database table.

long[] keysToDelete = { 0, 1, 2, 3 };

and I'd like to use a PreparedStatement to execute the equivalent of

DELETE FROM MyTable WHERE myPrimaryKey IN (0, 1, 2, 3);

Any idea how?

Answer 1

Two steps:

1. Build up the PreparedStatement SQL String with the appropriate # of parameters.
2. Loop over the array of values and bind each one to its parameter.

Unfortunately, there's no good way to bind an array all at once.

Answer 2

Not totally sure but this might help:

PreparedStatement pstmt = Connection.prepareStatement("DELETE FROM MyTable WHERE myPrimaryKey IN (?)");
pstmt.setArray(1, idArray);

Answer 3

Is this what you are looking for?

http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause#337817