今天在项目中碰到一个问题，就是Oracle需要批量提交10000条语句，刚开始使用的是statement.addBatch(),结果提交的时候发现需要26秒；检查半天，发现批量提交的时候也要关掉自动提交(重要),不然极其影响性能。
改了一下程序，最终代码如下，执行时间为1秒钟:
// 获取一个连接
Connection conn = null;
PreparedStatement pstm = null;
try {
conn = DBConnectionManager.getInstance().getConnection(
ConfigDef.DS_UPDATE_KEY);
conn.setAutoCommit(false);
// statement = conn.createStatement();
Map resultMap = collectResult.get(0);
//获取第一个
StringBuilder keyParams = new StringBuilder();
StringBuilder valueParams = new StringBuilder();
for (Map.Entry entry : resultMap.entrySet()) {
keyParams.append(", ").append(entry.getKey());
valueParams.append(", ? ");
}
String sqlTemplate = MessageFormat.format(SqlConstant.INSERT_SQL_TEMPLATE, new Object[]{"TB_BMP_SXAUDIT_DATA_TEMP",keyParams,valueParams});
logger.info("sqlTemplate:"+ sqlTemplate);
pstm = conn.prepareStatement(sqlTemplate);
int len = collectResult.size();
String uid = auditObject.getAuditObjectId()+"_"+ taskSeq;
for (int i = 0; i < len; i++) {
// 对于每一个结果,都要生成往临时表插入一条SQL语句
resultMap = collectResult.get(i);
//第一个参数是UID
pstm.setString(1, uid);
// 第2个参数是TASK_ID
pstm.setInt(2, taskInfo.getPlanId());
// 第3个参数是BATCH_NO
Date batchNO = new Date(format.parse(receiveTask.getTaskStartTime()).getTime());
System.out.println(batchNO);
pstm.setDate(3, batchNO);
//第4个参数是CollectID
pstm.setLong(4,Long.valueOf(auditObject.getAuditObjectId()));
// 第5个参数是TARGET_DB
pstm.setString(5, auditObject.getTargetDb());
// 第8个参数是AUDIT_TIMES
pstm.setInt(6,1);
int idx = 7;
for (Map.Entry entry : resultMap.entrySet()) {
pstm.setString(idx++, entry.getValue());
}
pstm.addBatch();
}
logger.info("开始执行批量提交");
pstm.executeBatch();
conn.commit();
//等待任务执行成功,调用稽核存过
logger.info("开始调用存过");
：