java - 删除操作未发生批量执行

Question

我正在尝试以批量更新操作的方式从数据库中删除行。我编写了以下方法来完成任务，但是当我执行时，它不会从表中删除条目。所以 table 保持不变。如果代码中有任何错误，请告诉我。

public void removeFromDb(String partnerId, List<String> packagedServiceIdList) throws CustomException {

    Connection con = null;
    PreparedStatement ps = null;
    try {
        con = getConnection();
        ps = con.prepareStatement(REMOVE_DATA_MAP);
        for(int i=0; i<packagedServiceIdList.size();i++) {

            ps.setString(1, partnerId);
            ps.setString(2, packagedServiceIdList.get(i));
            ps.addBatch();
            gLogger.debug("query is: "+ps.toString());
        }
        ps.executeUpdate();

    } catch (SQLException sqle) {
        gLogger.error("Exception while removing the record from table, SQLException:{}", sqle);
        throw new CustomException(feErrorEnum.INTERNAL_EXCEPTION, sqle.getMessage());
    } finally {
        closeConnection(con, ps, null);
    }
}

Answer 1

您必须使用executeBatch()

ps.executeBatch();

而不是:

ps.executeUpdate();

注意 executeBatch():

Returns an array of update counts containing one element for each command in the batch. The elements of the array are ordered according to the order in which commands were added to the batch.

因此，要检查您的批处理是否正常工作，您可以使用:

int[] count = ps.executeBatch();

<小时/>

你必须将你的批处理放在

con.setAutoCommit(false);//Disabling auto-commit mode

ps = con.prepareStatement(REMOVE_DATA_MAP);
for (int i = 0; i < packagedServiceIdList.size(); i++) {

    ps.setString(1, partnerId);
    ps.setString(2, packagedServiceIdList.get(i));
    ps.addBatch();
    gLogger.debug("query is: " + ps.toString());
}

int[] count = ps.executeBatch();//execute the batch

con.commit();//Commit the SQL statements