JDBC批量更新问题 [英] JDBC Batch Update Problem

问题描述

我对Java-JDBC API轻微的独特要求与Oracle数据库一起。我有自动提交是默认这对于甲骨文是真实的，我使用类似这样的链接。

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.

然而，当我加说1000批次，可以说每个人都插入。并让我们假定约20记录违反某些限制，我想剩余980到（使用任何其他的连接，并从此可见于任何其他查询）到数据库中去COMMITTED并忽略20的记录。在上面的例子中，当一个行违反任何交易和甚至当我在catch块提交事务只能提交，直到第一次失败

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.

我知道批量更新要只做过，当你相当肯定的所有行会经过和异常处理是不是一个，而是我打算修补现有数据库，因此某种不良行为是好的:)任何code样品将是非常美联社preciated。

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.

**更多详情**

使用简单的插入/更新，因为我处理接近3M行，因此我每批处理1000条记录是不行的。简单地增加环路1000刀片（忽略例外）方式需要更多的时间（约5秒，每1000条记录），而不是批量更新＆LT; 300毫秒。

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.

问题：
随着Oracle数据库驱动程序似乎停止在第一次失败，即当1000行是成批和100失败了，我希望它继续下去，直到第1000行。我认为这不能在JDBC像链接完成（与Oracle）表示只有少数数据库的支持这样的特征，这也许甲骨文是不是一

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 link indicates only few databases support such feature and probably Oracle is not one

推荐答案

我一直在寻找上线的一些解决方案。随着Oracle数据库驱动程序似乎停止在第一次失败，即当1000行的批处理和100失败，我希望它继续下去，直到第1000行。基本上我想知道这是否可以与Oracle JDBC驱动程序来完成。

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.

然而多种答案已经被提出（大多数/所有这一切我已经考虑）
1）禁用限制/加载数据/删除冒犯行/重复很多次
2）是否所有检查前加载数据
3）降低批量大小为50 - 100。

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.

不幸的是我的支票不能做之前加载，使批量大小为50或100意味着将有更多的时间做500万行我有（逸岸的总时间增加到几个小时，而不是40分钟用一批次大小1000）。我已经使出保持的1000批量大小的接受维问题因为是并把code下一个while循环和做的工作，直到我们填补所有行。

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 is and put the code under a "while" loop and do the job until we fill up all rows.

就像我说的，因为有绝ORACLE批JDBC 第一次失败后进行，这个问题的答案将是不可行的，只是接受约束和记录的事实，这个工具需要大约40分钟才能完成：）

Like I said, since there is NO WAY WITH ORACLE BATCH JDBC to 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 :)

这篇关于JDBC批量更新问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！