当我处理BatchUpdateException时，executeBatch()期间出现SQLException [英] SQLException during executeBatch() when I'm handling BatchUpdateException

问题描述

对此我遇到了麻烦.

我正在尝试列出一些有关在executeUpdate()期间失败的查询的数据，并且我已阅读到可以捕获BatchUpdateException，然后获取updateCount，它告诉您哪些查询有效，哪些无效，但是何时查询失败，因为数据转换错误，它启动了SQLException，我无法捕获整个批处理执行错误.

I'm trying to list some data about the queries that failed during an executeUpdate() and I have read that BatchUpdateException can be caught and then get the updateCount, that tell you which queries worked and which not, but when a query fails because a bad data conversion, it launches an SQLException and I can't catch the whole batch execution error.

无需进行任何类型的验证即可从XML提取进行查询的数据.

The data to make the queries is fetched from a XML without any kind of validations.

这是代码:

try {
    pSt_insertCabecera.executeBatch();
    } 
catch (BatchUpdateException buex){
    int[] updateCounts = buex.getUpdateCounts();  
    for (int i = 0; i < updateCounts.length; i++) {  
        if (updateCounts[i] == Statement.EXECUTE_FAILED) {  
            logger.error(nombreClase + "[ESB_P]: Ha fallado la inserción de la cabecera de pedidos: " + cabecerasAInsertar.get(i).toString());
            throw new SQLException();
        }
    }
}

如果是，则抛出SQLException，这是因为稍后在代码中，我捕获到它以执行回滚.

The SQLException thrown besides the if it's because later on the code I catch it to perform a rollback.

StackTrace看起来像这样:

The StackTrace looks as this:

com.microsoft.sqlserver.jdbc.SQLServerException: Error al convertir una cadena de caracteres en fecha y/u hora.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(Unknown Source)

我正在使用SQLServer 2012.

I'm using SQLServer 2012.

如果您需要更多代码或信息，请询问.

If you need more code or information please ask.

非常感谢大家.

推荐答案

我能够重新创建您的问题.对于名为[Clients]的表，该表具有名为[DOB]的datetime列，代码为

I was able to recreate your issue. For a table named [Clients] with a datetime column named [DOB] the code

String[] datesToApply = new String[] 
        {
        "1978-12-31",
        "junk",
        "1981-11-11"
        };

String sql = 
        "UPDATE Clients SET DOB=? WHERE ID=1";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    for (String dt : datesToApply) {
        ps.setString(1, dt);
        ps.addBatch();
    }
    int[] updateCounts = null;
    try {
        updateCounts = ps.executeBatch();
    } catch (BatchUpdateException bue) {
        System.out.println("executeBatch threw BatchUpdateException: " + bue.getMessage());
        updateCounts = bue.getUpdateCounts();
    } catch (SQLException se) {
        System.out.println("executeBatch threw SQLException: " + se.getMessage());
    }
    if (updateCounts != null) {
        for (int uc : updateCounts) {
            System.out.println(uc);
        }
    }
    if (!conn.getAutoCommit()) {
        conn.commit();
    }

如果AutoCommit关闭，则

抛出SQLException，但是如果AutoCommit打开，则抛出BatchUpdateException.检查.getUpdateCounts()返回的数组会向我们显示批处理中第一次发生故障的点

throws a SQLException if AutoCommit is off, but it throws a BatchUpdateException if AutoCommit is on. Inspecting the array returned by .getUpdateCounts() shows us the point in the batch at which the first failure occurred

executeBatch threw BatchUpdateException: Conversion failed when converting date and/or time from character string.
1
-3
-3

但是它也显示SQL Server在第一次失败后放弃".似乎没有选择告诉SQL Server继续处理其余的批处理(如continueBatchOnError用于MySQL Connector/J).

but it also shows that SQL Server "gives up" after the first failure. There seems to be no option to tell SQL Server to continue processing the rest of the batch (like continueBatchOnError for MySQL Connector/J).

此外，由于AutoCommit处于打开"状态，因此已经提交了对该点的更新，因此没有选择批量回滚这些更改.如果希望批处理是全部或全部"，则必须编写一些代码以返回并撤消在第一次失败之前成功应用的更改.

In addition, since AutoCommit is "on" the updates to that point are already committed so there's no option to roll back those changes en masse. If you wanted the batch to be "all or nothing" you'd have to write some code to go back and reverse the changes that were successfully applied before the first failure.

(当然，另一种选择是在 尝试将其放入数据库之前验证数据.)

(The other option, of course, is to validate the data before trying to put it into your database.)

这篇关于当我处理BatchUpdateException时，executeBatch()期间出现SQLException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！