How to find the offending insertion from a BatchUpdateException?

Question

When I have a BatchUpdateException as the result of a unique constraint violation is there a way for me to determine which record in the batch insert is in violation? For example let's say I'm performing a batch insert by calling PreparedStatement.executeBatch() and I catch the BatchUpdateException which has as it's cause "ORA-00001: unique constraint (ABC.SYS_123) violated". When debugging using Eclipse this is about as much info as I can coax from this exception, but I'd like to find out which actual insert is causing the violation of the unique constraint. Is there a way I can find this information?

My code currently looks (more or less) like this:

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);
    }
}

I am using Spring's JdbcTemplate and an Oracle 11G database, in case that is relevant.

Thanks in advance for any advice.

--James

Answer 1

From the Java API documentation of BatchUpdateException:

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.

Now, I'm unsure about the behavior of the Oracle JDBC driver that you are using, but it is apparent that either of the techniques mentioned should work - if there are N elements in the array returned by the call to BatchUpdateException.getUpdateCounts, then N elements in the batch have been processed. Or, if the array returned has the same size as the number of batched statements, then all the array elements whose value is Statement.EXECUTE_FAILED would have failed execution in the batch.