Java : SQL delete with "IN" condition

Question

I would like to execute this query :

    String query = "delete from table_a where id_table_a = ? and id_entity in ()";
    PreparedStatement prepare = db_connection.prepareStatement(query);
    prepare.setInt(1, var_a);

In the SQL query, I'm trying to put between the parentheses an attribute (defined as "id") of objects (defined as "Entity"). Objects "Entity" are contained in an ArrayList.

My goal is to put in a string all the "Entity.id" separated by commas. And then I will put this string between parentheses in my query.

I'd like to have advice cause I don't know if it's the better way to realize it...

Review this answer: https://stackoverflow.com/questions/3107044/preparedstatement-with-list-of-parameters-in-a-in-clause — 0xh3xa, Mar 22 '20 at 14:56

score 1 · Accepted Answer · answered Mar 23 '20 at 02:50

Try this one:

List<String> idEntities = new ArrayList<>();

// Create an array of idEntities
Array arrIdEntities = db_connection.createArrayOf("string", idEntities.toArray());

// Added a placeholder '?' in the IN clause.
String query = "delete from table_a where id_table_a = ? and id_entity in (?)";
PreparedStatement prepare = db_connection.prepareStatement(query);
prepare.setInt(1, var_a);
prepare.setArray(2, arrIdEntities); // Assign the array of entities to the 2nd parameter.

return result;

Please see explanations in the comments.

Java : SQL delete with "IN" condition

1 Answers1