mybatis的ExecutorType.BATCH模式批量插入SqlServe/2012下 70w条数据与JDBC批量插入方式对比

1、需求描述

• 根据算法模型那边返回的大量数据，程序中某个功能需要实现50-100w行数据的update或者insert操作。

2、功能设计

2.1、使用JDBC方式批量操作

    private void testInsertJDBCReportEoh(List<ReportEoh> reportEohList) {
        List<ReportEoh> reportEohs = reportEohList;
        Connection connection = sqlSessionTemplate.getSqlSessionFactory().openSession().getConnection();
        PreparedStatement preparedStatement = null;
        try {
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement("insert into SCPS.REPORT_EOH_copy1(SCENARIO_ID, ID, NAME, PERIOD, AG, PRODUCT_FAMILY, OWNER, LOCATION, STAGE, SELENE_ATTRIBUTE, PART_LEVEL, MRP_CONTROLLER, PART_NUMBER, CPN, CCT, CRI, MATERIAL_DESCRIPTION, LIFE_CYCLE, IS_ERP_PART, BIN, TARGET_SAFETY_STOCK, STD_COST, STORAGE, STORAGE_COST, INFLOW, OUTFLOW, BIN_ORIGINAL, ORDER_NUMBER, ITEM_NUMBER, OPTION_CODE) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

            for (int i = 0; i < reportEohs.size(); i++) {//reportEohs.size()
                int index = 0;
                preparedStatement.setObject(++index, reportEohs.get(i).getScenarioId());
                preparedStatement.setObject(++index, reportEohs.get(i).getId());
                preparedStatement.setObject(++index, reportEohs.get(i).getName());
                preparedStatement.setObject(++index, reportEohs.get(i).getPeriod());
                preparedStatement.setObject(++index, reportEohs.get(i).getAg());
                preparedStatement.setObject(++index, reportEohs.get(i).getProductFamily());
                preparedStatement.setObject(++index, reportEohs.get(i).getOwner());
                preparedStatement.setObject(++index, reportEohs.get(i).getLocation());
                preparedStatement.setObject(++index, reportEohs.get(i).getStage());
                preparedStatement.setObject(++index, reportEohs.get(i).getSeleneAttribute());
                preparedStatement.setObject(++index, reportEohs.get(i).getPartLevel());
                preparedStatement.setObject(++index, reportEohs.get(i).getMrpController());
                preparedStatement.setObject(++index, reportEohs.get(i).getPartNumber());
                preparedStatement.setObject(++index, reportEohs.get(i).getCpn());
                preparedStatement.setObject(++index, reportEohs.get(i).getCct());
                preparedStatement.setObject(++index, reportEohs.get(i).getCri());
                preparedStatement.setObject(++index, reportEohs.get(i).getMaterialDescription());
                preparedStatement.setObject(++index, reportEohs.get(i).getLifeCycle());
                preparedStatement.setObject(++index, reportEohs.get(i).getIsErpPart());
                preparedStatement.setObject(++index, reportEohs.get(i).getBin());
                preparedStatement.setObject(++index, reportEohs.get(i).getTargetSafetyStock());
                preparedStatement.setObject(++index, reportEohs.get(i).getStdCost());
                preparedStatement.setObject(++index, reportEohs.get(i).getStorage());
                preparedStatement.setObject(++index, reportEohs.get(i).getStorageCost());
                preparedStatement.setObject(++index, reportEohs.get(i).getInflow());
                preparedStatement.setObject(++index, reportEohs.get(i).getOutflow());
                preparedStatement.setObject(++index, reportEohs.get(i).getBinOriginal());
                preparedStatement.setObject(++index, reportEohs.get(i).getOrderNumber());
                preparedStatement.setObject(++index, reportEohs.get(i).getItemNumber());
                preparedStatement.setObject(++index, reportEohs.get(i).getOptionCode());

                preparedStatement.addBatch();
                //防止内存溢出。
                                if ((i!=0)&&((i%100000 ==0) || (i == reportEohs.size()-1))){
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();
                }
            }
            preparedStatement.executeBatch();
            connection.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(connection, preparedStatement);
        }
    }

• .addBatch() 方法将该参数提交到参数集合中。
• .executeBatch(); 方法将 参数集合提交到数据库服务器上面执行；这里只有一次网络请求。

2.2、使用mybatis的ExecutorType.BATCH模式进行批量操作

    private void testInsertMybatisReportEoh() {
        SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH,false);
        ReportEohDao mapper = sqlSession.getMapper(ReportEohDao.class);
        List<ReportEoh> reportEohs = reportEohDao.queryByAll(null);

                    for (int i = 0; i < reportEohs.size(); i++) {         //reportEohs.size()
                        mapper.insert(reportEohs.get(i));
                    }
                    try {
                        sqlSession.commit();   
                        sqlSession.clearCache();
                    } catch (Exception e) {
                        System.out.println("---------Exception----------" + e.getMessage());//commit异常后，不回滚继续执行。  可以指定个别异常捕获后继续执行。
                    } finally {
                        sqlSession.close();
                    }
            }
        });

    }

2.3、遇到的问题

• 1、经过实际的测试，发现JDBC批量插入的方式比mybatis的ExecutorType.BATCH模式下批量插入要快三倍左右。

2.4、数据库现象对比

• 1、jdbc的commit后，该表锁住了，直到commit结束事务提交，该表才可以查询。
• 2、mybatis的ExecutorType.BATCH模式下sqlsession.commit()后，每次查询该表都能查询到事务里面未提交完成的脏数据。
• 猜测：一个事务读到了另一个事务未commit的数据，猜测是数据库隔离级别有关，查看SqlServer数据库隔离级别为读已提交。
• 3、发现jdbc方式满足数据库隔离级别，但是mybatis批量提交方式不满足。猜测mybatis的批量提交语句并没有进行整个事务管理，每条insert语句的事务管理是独立的
• todo：
• 注意弄明白原生Mybatis、Mybatis-Spring和底层JDBC的事务的关系；以及和数据库事务的关系？

3、问题解决

• 将mybatis批量insert（）语句以及commit（）语句放进编程式事务控制语句中。

    private void testInsertMybatisReportEoh() {
        SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH,false);
        ReportEohDao mapper = sqlSession.getMapper(ReportEohDao.class);
        List<ReportEoh> reportEohs = reportEohDao.queryByAll(null);

        long startTime3 = System.currentTimeMillis();
        /*
         *  执行无返回值的事务管理
         */
        transactionTemplate.execute(new TransactionCallbackWithoutResult() {
            @Override
            protected void doInTransactionWithoutResult(TransactionStatus transactionStatus) {
                try {

                    for (int i = 0; i < reportEohs.size(); i++) {         //reportEohs.size()
                        mapper.insert(reportEohs.get(i));
                    }

                    try {

                        sqlSession.commit();                 
                        sqlSession.clearCache();
                    } catch (Exception e) {
                    
                    } finally {
                        sqlSession.close();
                    }
                } catch (Exception e){
                    transactionStatus.setRollbackOnly();
                }

            }
        });
    }

• 至此mybatis的ExecutorType.BATCH模式批量插入和JDBC的批量插入拥有了一样的现象，差不多的执行时间。

4、总结：

• 如果使用Spring集成Mybatis，SqlSessionTemplate会判断当前是否存在事务，不存在的话，每次执行sql都会进行提交，这样的话即使ExecutorType设置为BATCH，批量操作也不会生效。
• 最近在学习Mybatis框架，关于事务和批处理的关系，原生Mybatis、Mybatis-Spring和底层JDBC的事务的关系，还有许多问题有待梳理。