报错信息
2021-04-13 14:48:06,154 ERROR (ExceptionHandler.java:49) - An error occurred in com.bosssoft.helpPool.controller.backend.CapitalController#readImpFile
org.apache.ibatis.exceptions.PersistenceException:
### Error committing transaction. Cause: org.apache.ibatis.executor.BatchExecutorException: com.bosssoft.helpPool.mapper.CapitalMapper.saveCapital (batch index #298) failed. 297 prior sub executor(s) completed successfully, but will be rolled back. Cause: java.sql.BatchUpdateException: ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01000: 超出打开游标的最大数
ORA-01000: 超出打开游标的最大数
### Cause: org.apache.ibatis.executor.BatchExecutorException: com.bosssoft.helpPool.mapper.CapitalMapper.saveCapital (batch index #298) failed. 297 prior sub executor(s) completed successfully, but will be rolled back. Cause: java.sql.BatchUpdateException: ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01000: 超出打开游标的最大数
ORA-01000: 超出打开游标的最大数
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:177) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:169) ~[mybatis-3.2.8.jar:3.2.8]
at com.bosssoft.helpPool.service.impl.CapitalServiceImpl.saveCapital(CapitalServiceImpl.java:174) ~[help-pool-service-impl-0.0.1-SNAPSHOT.jar:?]
at com.bosssoft.helpPool.service.impl.CapitalServiceImpl.impData(CapitalServiceImpl.java:969) ~[help-pool-service-impl-0.0.1-SNAPSHOT.jar:?]
解决:一次提交99条
@Transactional
public void saveCapitalAdmin(Map<String, Integer> map, List<Capital> capitalList, String tableName) {
//由于框架自身的mybatis在批量新增的时候,每次都要新建一个sqlsession,效率很低,并且它不读mybatis-config,所以
//这里采用手动获取批处理sqlsession的方式来处理
SqlSession sqlSession=getBatchSession();
CapitalMapper capitalMapper1=sqlSession.getMapper(CapitalMapper.class);
long startTime = System.currentTimeMillis();
int i = 0;
for (Capital capital : capitalList) {
String uuid = NBUtil.createUUID();
capital.setCapitalId(uuid);
capital.setOperationTime(new Date());
capital.setOperationUserId(TokenManager.getUserCode());
capital.setOperationRelName(TokenManager.getToken().getUserName());
capital.setIdcard(capital.getIdcard().toUpperCase());
capital.setName(capital.getName().replaceAll(" ", ""));
capital.setIsFormula("01");
// 判断是否含有字母,含有字母只去前后空格
if (NBUtil.judgeContainsStr(capital.getName())){
capital.setName(capital.getName().trim());
}else{
capital.setName(capital.getName().replaceAll(" ", ""));
}
capitalMapper1.saveCapital(capital, tableName);
//统计年份
map.put(capital.getGrantYear(), null == map.get(capital.getGrantYear()) ? 1 : map.get(capital.getGrantYear()) + 1);
///99 条提交一次
i++;
if(i==99){
sqlSession.commit();
logger.info("》》》》》》》》》》》》提交99条");
i=0;
}
}
long endTime = System.currentTimeMillis();
sqlSession.commit();
System.out.println("程序运行时间:" + (endTime - startTime) + "ms");
sqlSession.close();
}