部分失败时的executeBatch行为

问题描述

我有一个java 1.6应用程序，它使用批量插入来使用jdbc驱动程序在Oracle数据库中插入记录。正如您在Statement对象上所知，有一个名为executeBatch（）的方法，我们将其用于批量更新。它有一个返回类型的int数组，其中包含每个记录的执行结果。但它也会在出现错误时抛出BatchUpdateException，我们也可以从中获取结果int数组。我的问题是我应该期待什么样的错误情况BatchUpdateException以及什么时候我应该期望没有抛出异常但是对于某些记录我会失败。

I have a java 1.6 application which use batch insert for inserting records in Oracle db using jdbc driver. As you know on Statement object there is a method called executeBatch() which we use for batch updates. It has a return type of int array which has result of execution for each record in it. But it also throws BatchUpdateException in case of error and we can get result int array from that too. My question is in what error situations I should expect BatchUpdateException and when I should expect there is no exception thrown but for some records I get failure.

注意：问题特别针对Oracle JDBC。
为了使它更清楚，我看到在执行executeBatch（）之后的情况我没有得到BatchUpdateException，但是一些insert语句失败了。我的问题是关于可能发生的情况？

Note: Question is spesifically for Oracle JDBC.And to make it more clear, I have seen situations that after executing executeBatch() I did not get BatchUpdateException however some of the insert statements failed. My question was about in what situation that can occur ?

这是Statement.executeBatch（）方法的返回javadoc。根据这里的一般意见，当一个条目失败时，执行抛出BatchUpdateException然后在哪种情况下我们可以预期返回数组中的某些条目失败。

This is the return javadoc of Statement.executeBatch() method. According to the general opinion here when one entry fails, execution throws BatchUpdateException then in which condition we can expect some entries in return array failed.

      * @return an array of update counts, with one entry for each command in the
 *         batch. The elements are ordered according to the order in which
 *         the commands were added to the batch.
 *         <p>
 *         <ol>
 *         <li> If the value of an element is >=0, the corresponding command
 *         completed successfully and the value is the update count for that
 *         command, which is the number of rows in the database affected by
 *         the command.</li>
 *         <li> If the value is SUCCESS_NO_INFO, the command completed
 *         successfully but the number of rows affected is unknown.
 *         <li>
 *         <li> If the value is EXECUTE_FAILED, the command failed.
 *         </ol>
 * @throws SQLException
 *             if an error occurs accessing the database
 */
public int[] executeBatch() throws SQLException;

推荐答案

如果发生错误，Oracle JDBC驱动程序会抛出BatchUpdateException在批处理的中间。

The Oracle JDBC driver throws a BatchUpdateException if an error occurs in the middle of the batch.

例如，让我们假设你发送一个包含10个条目的批处理（在你的情况下插入10行）。条目＃0到＃4成功。条目＃5遇到诸如主键违规之类的错误。执行在5处停止，驱动程序抛出BatchUpdateException。如果你调用getUpdateCounts（），你将得到一个大小为10的数组，其中包含5个SUCCESS_NO_INFO和5个EXECUTE_FAILED。

For example let's assume you're sending a batch with 10 entries (10 rows to insert in your case). Entries #0 through #4 are successful. Entry #5 hits an error such as a primary key violation. The execution stops at 5 and the driver throws a BatchUpdateException. If you call getUpdateCounts() you'll get an array of size 10 with 5 SUCCESS_NO_INFO and 5 EXECUTE_FAILED.

注意从12c开始（数据库和驱动程序）你可以得到批次的每个元素的更新计数。当您批量执行更新时，这更有用。对于批处理中的每个元素，您可以知道已更新的行数。

Note that starting in 12c (database and driver) you can get an update count for each element of the batch. This is more useful when you're executing updates in a batch. For each element in the batch you can know how many rows have been updated.

这篇关于部分失败时的executeBatch行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！