java JDBC批量更新问题

JDBC Batch Update Problem

提问by Kannan Ekanath

I have a slightly unique requirement with the Java-JDBC API along with Oracle Database. I have autoCommit to be default which is true for Oracle and I am using the example similar to this link.

我对 Java-JDBC API 和 Oracle 数据库有一个稍微独特的要求。我将 autoCommit 设为默认值，这对于 Oracle 来说是正确的，并且我正在使用类似于此链接的示例。

However, when I add say 1000 batches and lets say each of them are inserts. And Let us assume that about 20 records violated some constraints, I want the remaining 980 to go COMMITTED (and henceforth visible to any other queries using any other connection) to the database and ignore the 20 records. In the above example, when one row violates any transaction then even when I commit in the catch block the transaction only commits until the first failure.

但是，当我添加 1000 个批次并假设每个批次都是插入时。让我们假设大约 20 条记录违反了一些约束，我希望剩余的 980 条记录变为 COMMITTED（此后对使用任何其他连接的任何其他查询可见）到数据库并忽略这 20 条记录。在上面的例子中，当一行违反任何事务时，即使我在 catch 块中提交，事务也只会提交到第一次失败。

I know batch updates are to be done ONLY when you are fairly sure all rows will go through and exception processing is not one, but am planning to PATCH an existing database so some kind of "bad practices" is okay :) Any code samples will be highly appreciated.

我知道只有当您相当确定所有行都将通过并且异常处理不是一个时才进行批量更新，但我计划修补现有数据库，因此某种“不良做法”是可以的:) 任何代码示例都将受到高度赞赏。

**** MORE DETAILS ****

**** 更多细节 ****

Using Simple insert/update is not okay since I am processing close to 3M rows so am batching every 1000 records. Simply adding 1000 inserts in loop (ignoring exceptions) takes way more time (about 5 seconds for every 1000 records) as opposed to the batch update < 300ms.

使用简单插入/更新是不行的，因为我正在处理接近 300 万行，所以每 1000 条记录进行批处理。与批量更新 < 300 毫秒相比，简单地在循环中添加 1000 次插入（忽略异常）需要更多时间（每 1000 条记录大约需要 5 秒）。

Problem: With Oracle database the Driver seems to stop at the first FAILURE, ie when 1000 rows are batched and 100th failed, I want it to go ahead till the 1000th row. Me thinks this cannot be done in JDBC (with Oracle) Like the linkindicates only few databases support such feature and probably Oracle is not one

问题：对于 Oracle 数据库，驱动程序似乎在第一个 FAILURE 时停止，即当批处理 1000 行并且第 100 次失败时，我希望它继续进行到第 1000 行。我认为这不能在 JDBC (with Oracle) 中完成 就像链接表明只有少数数据库支持这样的特性，可能 Oracle 不是一个

采纳答案by Kannan Ekanath

I was looking some solution on the line of "With Oracle database the Driver seems to stop at the first FAILURE, ie when 1000 rows are batched and 100th failed, I want it to go ahead till the 1000th row." Basically I wanted to know if this can be done with Oracle JDBC driver.

我正在寻找关于“使用 Oracle 数据库，驱动程序似乎在第一个失败时停止的解决方案，即当 1000 行被批处理并且第 100 次失败时，我希望它继续进行到第 1000 行。” 基本上我想知道这是否可以用 Oracle JDBC 驱动程序完成。

However a variety of answers have been proposed (most/all of which I had already considered) 1) Disabling the constraints/load data/remove offending rows/repeat this many times 2) Do all the checking before loading data 3) Decrease the batch size to 50 - 100.

然而，已经提出了各种各样的答案（大多数/所有我已经考虑过）1）禁用约束/加载数据/删除违规行/重复多次2）在加载数据之前进行所有检查3）减少批处理大小为 50 - 100。

Unfortunately my checking cannot be done before loading and making batch size to be 50 or 100 means taking more time to do the 5M rows I have (infact the total time increased to a few hours instead of 40 mins with a batch size of 1000). I have resorted to keeping the batch size of 1000 ACCEPTING THE problem as isand put the code under a "while" loop and do the job until we fill up all rows.

不幸的是，我无法在加载之前进行检查，并使批量大小为 50 或 100 意味着需要花费更多时间来处理我拥有的 5M 行（事实上，在批量大小为 1000 时，总时间增加到几个小时而不是 40 分钟）。我已经采取保持 1000 的批量大小接受问题，并将代码置于“while”循环下并完成工作，直到我们填满所有行。

Like I said, since there is NO WAY WITH ORACLE BATCH JDBCto proceed after first failure, the answer to this question will be "NOT DOABLE" and just accept the constraints and document the fact that this tool takes about 40 mins to complete :)

就像我说的，因为第一次失败后ORACLE BATCH JDBC无法继续，所以这个问题的答案将是“不可行”，只需接受约束并记录这个工具需要大约 40 分钟才能完成的事实:)

回答by Martlark

You can use a PL/SQL stored procedure using the SAVE EXCEPTIONS clause which will allow you to issue a bulk update and then return those rows which could not be updated. Here is a couple of example links:

您可以使用使用 SAVE EXCEPTIONS 子句的 PL/SQL 存储过程，这将允许您发出批量更新，然后返回那些无法更新的行。这是几个示例链接：

http://rwijk.blogspot.com/2007/11/save-exceptions.html

http://rwijk.blogspot.com/2007/11/save-exceptions.html

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8912264456901

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8912264456901

回答by Thilo

You should insert into a working table that does not have the constraints, then delete or fix what would be in violation and INSERT SELECT the rest over into the real table in a single SQL statement.

您应该插入一个没有约束的工作表，然后删除或修复违反的内容，然后在单个 SQL 语句中将其余的 INSERT SELECT 插入到实际表中。

回答by russoue

Could you try the Oracle merge-when-not-matched statement? Example: http://www.idevelopment.info/data/Oracle/DBA_tips/SQL/SQL_14.shtml

你能试试 Oracle 的 merge-when-not-matched 语句吗？示例：http: //www.idevelopment.info/data/Oracle/DBA_tips/SQL/SQL_14.shtml

回答by Salandur

i should check first to see if there is a constraint violation, than insert that record if the contraints is not violated.

我应该首先检查是否存在违反约束的情况，如果没有违反约束，则插入该记录。

回答by Erich Kitzmueller

You could try this: Start with batches of 50 or 100. (Choose a size so they have a good probablity to be processed successfully). Those which fail, process one-by-one.

你可以试试这个：从 50 或 100 的批次开始。（选择一个大小，这样它们就有很大的可能被成功处理）。失败者，一一处理。

Other possibility: Disable the constraints, load the data, delete those rows which violate the constraints.

其他可能性：禁用约束，加载数据，删除那些违反约束的行。

回答by user2091897

Have an exceptions table and make sure your proc never raises exception but saves all exceptions in database. Once all done, query exceptions table and see records could not go through.

有一个异常表并确保您的 proc 永远不会引发异常但将所有异常保存在数据库中。全部完成后，查询异常表和查看记录都无法通过。