java - 如何从 BatchUpdateException 中找到有问题的插入？

Question

当我由于违反唯一约束而出现 BatchUpdateException 时，是否有办法确定批插入中的哪条记录违反了规定？例如，假设我正在通过调用 PreparedStatement.executeBatch() 执行批量插入，并且我捕获了 BatchUpdateException，它的原因是“ORA-00001:违反了唯一约束 (ABC.SYS_123)”。使用 Eclipse 进行调试时，这是我可以从该异常中获得的尽可能多的信息，但我想找出是哪个实际插入导致违反了唯一约束。有什么办法可以找到这些信息吗？

我的代码目前(或多或少)看起来像这样:

public void batchInsert(final Collection<MyObject> objectCollection)
{
    try
    {
        if (connection == null)
        {
            connection = getJdbcTemplate().getDataSource().getConnection();
        }

        // get the last entity ID value so we can know where to begin
        Long entityId = getJdbcTemplate().queryForLong("SELECT MAX(" + MyObject.ID_COLUMN_NAME +
                                                       ") FROM " + MyObject.TABLE_NAME);
        entityId++;

        // get a date to use for the created and updated dates
        Date now = new Date(new java.util.Date().getTime());

        // set auto commit to false so we can batch save without committing each individual insert
        connection.setAutoCommit(false);

        // create the prepared statement
        String insertSql = "INSERT INTO " + MyObject.TABLE_NAME + " (" +
                           MyObject.ID_COLUMN_NAME + ", VALUE_1, VALUE_2) " +
                           "VALUES (?, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(insertSql);

        // add a batch entry for each of the SurfaceMetObservations objects
        for (MyObject object : objectCollection)
        {
            preparedStatement.setLong(1, entityId);
            preparedStatement.setBigDecimal(2, object.getValue1());
            preparedStatement.setBigDecimal(3, object.getValue2());
            preparedStatement.addBatch();
            entityId++;
        }

        int updateCounts[] = preparedStatement.executeBatch();
        preparedStatement.close();
        if (confirmUpdateCounts(updateCounts))
        {
            connection.commit();
        }
        else
        {
            connection.rollback();
            throw new RuntimeException("One or more inserts failed to execute.");
        }
    }
    catch (SQLException ex)
    {
        throw new RuntimeException(ex);
    }
}

我正在使用 Spring 的 JdbcTemplate 和 Oracle 11G 数据库，以防相关。

提前感谢您的任何建议。

--詹姆斯

Answer 1

来自 BatchUpdateException 的 Java API 文档:

After a command in a batch update fails to execute properly and a BatchUpdateException is thrown, the driver may or may not continue to process the remaining commands in the batch. If the driver continues processing after a failure, the array returned by the method BatchUpdateException.getUpdateCounts will have an element for every command in the batch rather than only elements for the commands that executed successfully before the error. In the case where the driver continues processing commands, the array element for any command that failed is Statement.EXECUTE_FAILED.

现在，我不确定您正在使用的 Oracle JDBC 驱动程序的行为，但很明显，所提到的任何一种技术都应该有效 - 如果调用 BatchUpdateException 返回的数组中有 N 个元素。 getUpdateCounts，则该批处理中的 N 个元素已被处理。或者，如果返回的数组的大小与批处理语句的数量相同，则值为 Statement.EXECUTE_FAILED 的所有数组元素将在批处理中执行失败。