Oracle批量插入，无主键缺失插入

我试图使用ojdbc14.jar驱动程序和Spring的SimpleJdbcTemplate batchUpdate方法将超过100,000条记录插入到没有主键的Oracle 9i表中。下面是我的代码片段:

private static final String TABLE_INSERT = "insert into TABLE_FINAL (ID, START_TIME, VALUE) VALUES (ID_SEQ.NEXTVAL, :startTime, :value)";
log.info("inputData list size={}",inputData.size());
Object[] dataArray = inputData.toArray();
log.info("dataArray length={}",dataArray.length);
final SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(inputData.toArray());
log.info("SqlParamterSource length={}", batch.length);
final int[] inserted = getJdbcTemplateJoa().batchUpdate(TABLE_INSERT, batch);
for(int i=0; i < inserted.length; i++){
if(inserted[i] != -2){
    System.out.println("i="+i +" insert[i]="+inserted[i]);
    System.out.println(batch[i]);
}

}

inputData List、dataArray的大小和批处理长度都是相同的期望值。batchUpdate完成时没有抛出任何异常，随后的for循环不打印任何内容，因为插入的数组中的每个项都返回-2(成功)。然而，只有42,000条记录被持久化到目标表，而不是预期的100,000多条记录。

如果我将batchUpdate替换为循环遍历输入集合并对每个条目执行更新，那么100,000多条记录将被持久化。但是，我想使用batchUpdate来利用改进后的性能。

是否有人有任何想法，为什么batchUpdate不工作?我不禁认为这与丢失的主键有关。

下面是源表中用于填充inputData List的数据:

0.1933,-0.0253,0,0,4/16/2011 5:00:00 AM,4/16/2011 6:00:00 AM,12,9,1,1
0.1917,-0.0253,0,0,4/16/2011 6:00:00 AM,4/16/2011 7:00:00 AM,12,9,1,1
0.1936,-0.0253,0,0,4/16/2011 7:00:00 AM,4/16/2011 8:00:00 AM,12,9,1,1
0.2017,-0.0253,0,0,4/16/2011 8:00:00 AM,4/16/2011 9:00:00 AM,12,9,1,1
0.2083,-0.0253,0,0,4/16/2011 9:00:00 AM,4/16/2011 10:00:00 AM,12,9,1,1
0.2133,-0.0253,0,0,4/16/2011 10:00:00 AM,4/16/2011 11:00:00 AM,12,9,1,1
0.2238,-0.0253,0,0,4/16/2011 11:00:00 AM,4/16/2011 12:00:00 PM,12,9,1,1
0.2309,-0.0253,0,0,4/16/2011 12:00:00 PM,4/16/2011 1:00:00 PM,12,9,1,1
0.2319,-0.0253,0,0,4/16/2011 1:00:00 PM,4/16/2011 2:00:00 PM,12,9,1,1
0.231,-0.0253,0,0,4/16/2011 2:00:00 PM,4/16/2011 3:00:00 PM,12,9,1,1
0.2283,-0.0253,0,0,4/16/2011 3:00:00 PM,4/16/2011 4:00:00 PM,12,9,1,1
0.2216,-0.0253,0,0,4/16/2011 4:00:00 PM,4/16/2011 5:00:00 PM,12,9,1,1
0.2164,-0.0253,0,0,4/16/2011 5:00:00 PM,4/16/2011 6:00:00 PM,12,9,1,1
0.2155,-0.0253,0,0,4/16/2011 6:00:00 PM,4/16/2011 7:00:00 PM,12,9,1,1
0.2162,-0.0253,0,0,4/16/2011 7:00:00 PM,4/16/2011 8:00:00 PM,12,9,1,1
0.2187,-0.0253,0,0,4/16/2011 8:00:00 PM,4/16/2011 9:00:00 PM,12,9,1,1
0.2203,-0.0253,0,0,4/16/2011 9:00:00 PM,4/16/2011 10:00:00 PM,12,9,1,1
0.2296,-0.0253,0,0,4/16/2011 10:00:00 PM,4/16/2011 11:00:00 PM,12,9,1,1
0.2323,-0.0253,0,0,4/16/2011 11:00:00 PM,4/17/2011,12,9,1,1
0.2293,-0.0253,0,0,4/17/2011,4/17/2011 1:00:00 AM,12,9,1,1
0.2154,-0.0253,0,0,4/17/2011 1:00:00 AM,4/17/2011 2:00:00 AM,12,9,1,1
0.2088,-0.0253,0,0,4/17/2011 2:00:00 AM,4/17/2011 3:00:00 AM,12,9,1,1
0.202,-0.0253,0,0,4/17/2011 3:00:00 AM,4/17/2011 4:00:00 AM,12,9,1,1
0.1916,-0.0253,0,0,4/17/2011 4:00:00 AM,4/17/2011 5:00:00 AM,12,9,1,1

，下面是在batchUpdate之后持久化的内容:

47987296,4/19/2011 4:37:15 PM,0.1933,-0.0253,4/16/2011 5:00:00 AM,4/16/2011 6:00:00 AM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
47961249,4/19/2011 4:37:15 PM,0.2238,-0.0253,4/16/2011 11:00:00 AM,4/16/2011 12:00:00 PM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
47966094,4/19/2011 4:37:15 PM,0.2309,-0.0253,4/16/2011 12:00:00 PM,4/16/2011 1:00:00 PM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
47968596,4/19/2011 4:37:15 PM,0.2319,-0.0253,4/16/2011 1:00:00 PM,4/16/2011 2:00:00 PM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
47972962,4/19/2011 4:37:15 PM,0.231,-0.0253,4/16/2011 2:00:00 PM,4/16/2011 3:00:00 PM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
47978129,4/19/2011 4:37:15 PM,0.2283,-0.0253,4/16/2011 3:00:00 PM,4/16/2011 4:00:00 PM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
47982943,4/19/2011 4:37:15 PM,0.2216,-0.0253,4/16/2011 4:00:00 PM,4/16/2011 5:00:00 PM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
48005719,4/19/2011 4:37:15 PM,0.2164,-0.0253,4/16/2011 5:00:00 PM,4/16/2011 6:00:00 PM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
47990490,4/19/2011 4:37:15 PM,0.2088,-0.0253,4/17/2011 2:00:00 AM,4/17/2011 3:00:00 AM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
47993531,4/19/2011 4:37:15 PM,0.202,-0.0253,4/17/2011 3:00:00 AM,4/17/2011 4:00:00 AM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
48000722,4/19/2011 4:37:15 PM,0.1916,-0.0253,4/17/2011 4:00:00 AM,4/17/2011 5:00:00 AM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011

源表中的24行在目标表中也应该有24行，但是只填充了11行。

使用SimpleJdbcTemplate时。batchUpdate(String sql, SqlParameterSource[] source)与ojdbc14.jar和大量数据(超过60K)，数据从目标表中丢失，正如我在原始帖子中描述的那样。我发现，如果我将输入数据分成10K个块，数据就会成功地持久化。我还尝试使用JdbcTemplate。batchUpdate(String [] sql)方法，可以正确地持久化，但比循环调用SimpleJdbcTemplate.update要慢。好的方面是，JdbcTemplate。batchUpdate(String [] sql)返回一个int[]，其中数组中的每个项目包含受影响的行数。

我将Oracle驱动程序更改为ojdbc6.jar，并使用SimpleJdbcTemplate重新测试。batchUpdate(String sql, SqlParamterSource[] source)传入所有100,000+源记录，它工作了!!不幸的是，我们有其他依赖项需要ojdbc14.jar，所以我们还不能升级。

对于最终的解决方案，数据将被分解为10K块，如下所示，并且在batchUpdate之后添加一个验证数据持久化的sql查询。

if(inputData.size() > 10000){
            int beginIndex =0;
            int endIndex = 10000;
            List<InputData> partialList = null;
            while(beginIndex < inputData.size()){
                partialList = inputData.subList(beginIndex, endIndex);
                final SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(partialList.toArray());
                getJdbcTemplateJoa().batchUpdate(TABLE_INSERT, batch);
                beginIndex = endIndex;
                endIndex = endIndex + 10000 < inputData.size() ? endIndex + 10000 : inputData.size();
            }
} else{
            final SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(inputData.toArray());
            getJdbcTemplateJoa().batchUpdate(TABLE_INSERT, batch);
        }

可能存在被捕获但没有传递的异常。试着安装一个servererror触发器来发现Oracle是否向客户端传递了任何异常。

这里有一个例子。

顺便说一句，我对你在工作中实现的性能改进很感兴趣。如果没有区别我也不会感到惊讶....

相关内容

最新更新

热门标签：