如何从 BatchUpdateException 中找到有问题的插入？
当我因唯一约束违规而出现 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 数据库（如果相关的话）。
预先感谢您的任何建议。
——詹姆斯
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
来自 BatchUpdateException 的 Java API 文档：
现在，我不确定您正在使用的 Oracle JDBC 驱动程序的行为，但显然上述任何一种技术都应该有效 - 如果调用 BatchUpdateException.getUpdateCounts 返回的数组中有 N 个元素，则批次中的 N 个元素已被处理。或者，如果返回的数组的大小与批处理语句的数量相同，则值为 Statement.EXECUTE_FAILED 的所有数组元素都将在批处理中执行失败。
From the Java API documentation of BatchUpdateException:
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.