oracle数据库提交,Oracle批量提交

今天在项目中碰到一个问题，就是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("开始调用存过");