JDBC批量插入异常处理 [英] JDBC Batch insert exception handling

问题描述

每次执行程序时，我都在执行JDBC批量插入（一次插入大约1000行）。但是我无法正确处理某些记录抛出的异常。

I am performing a JDBC batch insert (inserting 1000 rows approx at a time) each time my program is executed. But i am not able to handle the exception thrown by some of the records properly.

假设1000条记录中的第100条记录由于数据无效而抛出异常或某些值的大小超过列大小。一旦发生异常，其余记录未插入且程序在两者之间失败。

Suppose, the 100th record out of 1000 records is throwing an exception because of an invalid data or size of some value exceeds the column size. Once the exception has occured, the remaining records are not getting inserted and the program fails in between.

我想要的是即使是第100个记录是抛出异常，剩余的插入应该在我的程序结束之前照常进行。

What I want is even if the 100th record is throwing exception, the remaining insertions should happen as usual before my program ends.

我无法理解如何实现这一目标。请建议。

I am not able to understand how to achieve this. Please suggest.

编辑：

以下是我使用的示例代码在我的应用程序批量插入。假设结果集有大约1000条记录。

Here is a sample code I am using in my app for batch insert. Assume that the result set has got approx 1000 records.

PreparedStatement ps = null;
while(rs.next()){
  //Retrieve the value and set it to a Prepared statement

  String name = rs.getString("Name");
  int age = rs.getInt("Age");
  ps.setInt(1, age);
  ps.setString(2, name);

  //Finally invoke addBatch
  ps.addBatch();
}

//Finally call the executeBatch method
ps.executeBatch();

如果第100条记录抛出异常，那么我也想从100号开始触发该过程第1000次记录。有没有办法做到这一点，以便我可以从记录中重新启动进程，该记录将异常开始再次结束？如何实现这一目标？

If the 100th record is throwing an exception, then I want to trigger the process only from the 100th too 1000th record. Is there some way to do this such that I can restart the process from the record which threw exception onwards till the end again? How to achieve this?

推荐答案

您声明您正在执行批处理的SQL语句，因此我假设您正在使用则ExecuteBatch 。当您 executeBatch 一批语句时，某些语句可能会成功，某些语句可能会失败。如果一个语句失败，那并不意味着JDBC驱动程序回滚成功的语句。如果一个语句失败，JDBC驱动程序可能会选择尝试执行批处理中的所有语句，或者一旦语句失败，它可能会选择停止执行批处理中的语句（这听起来就像您正在使用的驱动程序选择停止执行语句一样很快就会出现故障）。

You state that you are executing batches of SQL statements so I'm assuming that you are using executeBatch. When you executeBatch a batch of statements, some of the statements may succeed and some of the statements may fail. If one statement fails, that does not mean that the JDBC driver rolls back the statements that succeeded. The JDBC driver may choose to attempt to execute all the statements in the batch if one statement fails or it may choose to stop executing statements in the batch once a statement fails (it sounds like the driver you're using chooses to stop executing statements as soon as there is a failure).

当一个批处理中的一个语句失败时，你应该得到一个 BatchUpdateException 。在异常处理程序中，您需要调用 getUpdateCounts 。这将为您提供一个 int 数组，它告诉您语句更新了多少行， Statement.SUCCESS_NO_INFO 指示语句成功但没有行计数，或者 Statement.EXECUTE_FAILED 表示语句失败。如果前99个语句成功，第100个语句生成错误，其余语句不执行，则应返回100个元素数组，其中前99个元素表示成功，第100个元素表示语句。 EXECUTE_FAILED 。然后，您的代码需要重试那些未执行的语句（在本例中为语句101-1000）。

When one statement in a batch fails, you should get a BatchUpdateException. In your exception handler, you need to call getUpdateCounts. That will give you an array of int that tells you either how many rows the statement updated, a Statement.SUCCESS_NO_INFO indicating the statement succeeded but no row count was available, or a Statement.EXECUTE_FAILED indicating that the statement failed. If the first 99 statements succeed, the 100th statement generates an error, and the remaining statements are not executed, you should get back a 100 element array where the first 99 elements indicate success and the 100th element indicates Statement.EXECUTE_FAILED. Your code would then need to retry those statements that were not executed (in this case, statements 101-1000).

这篇关于JDBC批量插入异常处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！