【Mybatis】批量插入的多种方式

0、配置URL

通过连接配置url设置【&rewriteBatchedStatements=true】

1、MyBatis的插入

@Test
    public void sqlSessionBatchInsert() {

        long start = System.currentTimeMillis();

        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        try {
            list.stream().forEach(li -> ciawSndpriSzjdMapper.insert(li));
            sqlSession.commit();
            sqlSession.clearCache();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        } finally {
            sqlSession.close();
        }

        System.out.println(System.currentTimeMillis() - start);

    }

1.2、Mybatis的插入（利用并行流）

• parallelStream

// 控制并发数目,根据具体情况设置并发数目;
System.setProperty("java.util.concurrent.ForkJoinPool.common.parallelism", "6");

@Test
    public void sqlSessionBatchInsert() {
    
		// 控制并发数目
		System.setProperty("java.util.concurrent.ForkJoinPool.common.parallelism", "6");

        long start = System.currentTimeMillis();

        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        try {
            list.parallelStream().forEach(li -> ciawSndpriSzjdMapper.insert(li));
            sqlSession.commit();
            sqlSession.clearCache();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        } finally {
            sqlSession.close();
        }

        System.out.println(System.currentTimeMillis() - start);

    }

2、Mybatis Foreach标签插入

可能会出现拼接SQL过长的Exception

• XML

  <insert id="insertList">

    insert into ciaw_sndpri_szjd (id, pbi_id, number_interpret,relative_position, instruction) values
    <foreach collection="list" separator="," item="item">
      (#{item.id,jdbcType=VARCHAR},
       #{item.pbiId,jdbcType=VARCHAR},
       #{item.numberInterpret,jdbcType=VARCHAR},
       #{item.relativePosition,jdbcType=VARCHAR},
       #{item.instruction,jdbcType=VARCHAR})
    </foreach>

  </insert>

• 测试类

   @Test
    @Transactional
    public void xmlForeachInsert(){
        long start = System.currentTimeMillis();

        ciawSndpriSzjdMapper.insertList(list);

        System.err.println(System.currentTimeMillis() - start);
    }

3、JDBC原生批量插入

    @Autowired
    DataSource dataSource;
    
	/**
     * 500条jdbc原生批量插入
     * 1.193s
     * 5000条jdbc原生批量插入
     * 2.795s  |   3.139
     * 100W 条 453.233  7分钟
     */
    @SneakyThrows
    @Test
    public void jdbcBatchInsert(){

        long start = System.currentTimeMillis();

        String sql = "insert into ciaw_sndpri_szjd (id, pbi_id, number_interpret, relative_position, instruction) values (?, ?, ?, ?, ?)";

        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < list.size(); i++) {
                ps.setString(1, list.get(i).getId());
                ps.setString(2, list.get(i).getPbiId());
                ps.setString(3, list.get(i).getNumberInterpret());
                ps.setString(4, list.get(i).getRelativePosition());
                ps.setString(5, list.get(i).getInstruction());
                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit(); //所有语句都执行完毕后才手动提交sql语句
        } catch (SQLException e) {
            conn.rollback();
            e.printStackTrace();
        }finally {
           DataSourceUtils.releaseConnection(conn, dataSource);
        }

        System.err.println(System.currentTimeMillis() - start);

    }