Mybatis-批量执行

前提

 必须要在数据库连接url加上 &rewriteBatchedStatements=true 来开启批处理，
否则还是一条一条写入的

1.基于JDBC的批量执行

 ps.addBatch()  ps.executeBatch();

 /**
     * 原生JDBC的批量操作方式 ps.addBatch()
     * @throws IOException
     */
    @Test
    public void testJdbcBatch() throws IOException {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            // 注册 JDBC 驱动
            Class.forName("com.mysql.jdbc.Driver");

            // 打开连接
            conn = DriverManager.getConnection("jdbc:mysql://rm-12312312313.mysql.rds.aliyuncs.com:3306/mybatis?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true", "test_mybatis", "Test@123456");
            ps = conn.prepareStatement(
                    "INSERT into blog values (?, ?, ?)");

            for (int i = 2000; i < 101000; i++) {
                Blog blog = new Blog();
                ps.setInt(1, i);
                ps.setString(2, String.valueOf(i));
                ps.setInt(3, 1001);
                ps.addBatch();
            }

            ps.executeBatch();
            // conn.commit();
            ps.close();
            conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) ps.close();
            } catch (SQLException se2) {
            }
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }

2.Mybatis 通过配置文件批量执行

配置 <setting name="defaultExecutorType" value="BATCH" />

/**
     * 循环插入  循环批量插入100000条，耗时：3762毫秒
     * 在spring集成后，spring的配置会覆盖mybatis的配置
     */
    @Test
    public void testBatchCRUD() throws IOException {
      
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession session = sqlSessionFactory.openSession();
        long start = System.currentTimeMillis();
        int count = 102000;
        try {
            BlogMapper mapper = session.getMapper(BlogMapper.class);


            for (int i=2000; i< count; i++) {
                Blog blog = new Blog();
                blog.setBid(i);
                blog.setName("测试插入");
                blog.setAuthorId(1111);
                String gender = i%2 == 0 ? "M" : "F";
                mapper.insertBlog(blog);
            }
            session.commit();
        } finally {
            session.close();
        }

        long end = System.currentTimeMillis();
        System.out.println("循环批量插入"+count+"条，耗时：" + (end -start )+"毫秒");
    }

3 Mybatis 通过API手动设置

/**
     * 循环插入  循环批量插入100000条，耗时：3762毫秒
     * 在spring集成后，spring的配置会覆盖mybatis的配置
     */
    @Test
    public void testBatchCRUD() throws IOException {

        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //SqlSession session = sqlSessionFactory.openSession();
        //手动设置
        SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
        long start = System.currentTimeMillis();
        int count = 102000;
        try {
            BlogMapper mapper = session.getMapper(BlogMapper.class);


            for (int i=2000; i< count; i++) {
                Blog blog = new Blog();
                blog.setBid(i);
                blog.setName("测试插入");
                blog.setAuthorId(1111);
                String gender = i%2 == 0 ? "M" : "F";
                mapper.insertBlog(blog);
            }
            session.commit();
        } finally {
            session.close();
        }

        long end = System.currentTimeMillis();
        System.out.println("循环批量插入"+count+"条，耗时：" + (end -start )+"毫秒");
    }

4.spring Boot +mybaties 实现批量执行

mybaties 中配置文件

<setting name="defaultExecutorType" value="BATCH" />

或者spring 配置文件

<!--配置一个可以执行批量的sqlSession，全局唯一，单例 -->
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
    <constructor-arg  name="sqlSessionFactory"  ref="sqlSessionFactory"></constructor-arg>
    <constructor-arg name="executorType"  value="BATCH"></constructor-arg>
</bean>

都可以，但是共存的时候，spring的配置会覆盖mybatis 的配置

 @Test
    public void testCRUD() {
//        循环批量插入100000条，耗时：3762毫秒
        long start = System.currentTimeMillis();
        int count = 100000;
        for (int i=0; i< count; i++) {
            String gender = i%2 == 0 ? "M" : "F";
            employeeMapper.insertSelective(new Employee(null, "TestName"+i, gender, "mahuateng@baidu.com", 1));
        }
        long end = System.currentTimeMillis();
        System.out.println("循环批量插入"+count+"条，耗时：" + (end -start )+"毫秒");
    }

问题：

可能会遇到批量执行不生效，

ExecutorType.BATCH不生效的原因是因为批量操作没有被spring的事务管理，导致每一次的数据操作，spring为了安全，都会将sqlSession关闭，每一次操作都去开启一个sqlSession，导致效率低下，请在你的类上注释@Servise和@Transactional

5.JDBCTemplate

  /**
     * 必须要在数据库连接url加上 &rewriteBatchedStatements=true 来开启批处理，否则还是一条一条写入的
     * 批量插入100000条员工数据完毕，总耗时：4124 毫秒
     * @param list
     */
    public void save(List<Employee> list) {
        final List<Employee> tempList = list;
        String sql = "insert into tbl_emp(emp_name, gender, email,d_id) "
                + "values(?, ?, ?, ?)";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                String empName =  tempList.get(i).getEmpName();
                String gender = tempList.get(i).getGender();
                String email = tempList.get(i).getEmail();
                Integer did = tempList.get(i).getdId();

                ps.setString(1, empName);
                ps.setString(2, gender);
                ps.setString(3,email);
                ps.setInt(4, did);
            }

            public int getBatchSize() {
                return tempList.size();
            }
        });

    }