java - 如何使用 SQL 查询删除列与任何集合匹配的行？

Question

我正在尝试删除行中每一列与给定键匹配的行。在这里，我想要执行批量操作，如果其值在任何列表中匹配，我将删除所有行。我们可以循环遍历，逐一删除，例如

for(Entries entry: list) // delete where column = entry

我正在寻找使用 SQL 和 Java 进行上述操作作为批处理操作。类似这样的东西，

delete rows where column match any of list

public ResponseEntity deleteEntries(@RequestBody String ids) throws SQLException {
        String[] entries = ids.split(",");
        for(int i = 0; i < ids.length; i++) {
            PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM  LIST_ITEMS WHERE ID = ? ");
            preparedStatement.setInt(1, ids[i]);
            preparedStatement.executeQuery();
        }
        return new ResponseEntity(HttpStatus.OK);
    }

批量运行上述查询，因为我将删除 1000 到 10K 项，如何批量运行上述查询？

Answer 1

您可以使用 IN key world 在查询中传递列表，而不是:

DELETE FROM  LIST_ITEMS WHERE ID = ? 

你可以使用:

DELETE FROM  LIST_ITEMS WHERE ID IN (?)
                                 ^^

您的代码可以如下所示:

String query = "DELETE FROM  LIST_ITEMS WHERE ID IN (?)";
PreparedStatement preparedStatement = connection.prepareStatement(query);
Array array = preparedStatement.getConnection().createArrayOf("VARCHAR", entries);
preparedStatement.setArray(1, array);
preparedStatement.executeQuery();