statement.executeBatch() 批量提交

 需求:部分数据失败后将剩余数据继续添加到数据库  并做错误数据记录

Connection connection = null; Statement statement = null; try { long startTime=System.currentTimeMillis(); SqlSession sqlSession = this.oracleSqlSessionFactory.openSession(); connection = sqlSession.getConnection(); statement = connection.createStatement(); connection.setAutoCommit(false); for (Object[] data : datas){
          // 添加数据的sql,包含数据(也可以使用预编译) statement.addBatch(
" insert into " + tableName + sql + " (" + StringUtils.join(data, ",") + ")"); } statement.executeBatch(); connection.commit(); System.out.println(new Date()+ " 程序运行时间: "+(System.currentTimeMillis()-startTime)/1000 +"s"); } catch (Exception em){ em.printStackTrace(); try {
          // 获取批量添加执行成功的条数
int updateCount = statement.getUpdateCount();
          // 截取剩余未添加的数据继续添加数据
if (updateCount > 0){ datas = datas.subList(updateCount,datas.size()); } connection.setAutoCommit(true); statement.clearBatch(); } catch (SQLException e) { e.printStackTrace(); } // 如果有错再循环处理数据 for (Object[] data : datas){ try { statement.execute(" insert into " + tableName + sql + " (" + StringUtils.join(data, ",") + ")"); } catch (Exception e){ String message = e.getMessage(); // 将错误信息封装返回 errorDatas.add(data); errorMessage.add(message); } } } finally { try { if (ObjectUtil.isNotNull(statement)){ statement.close(); } } catch (SQLException e) { e.printStackTrace(); } try { if (ObjectUtil.isNotNull(connection)){ connection.close(); } } catch (SQLException e) { e.printStackTrace(); } }

 

posted @ 2021-08-06 14:34  涂小二  阅读(1016)  评论(0编辑  收藏  举报