使用mybatis的批处理batch来实现30万数据20S秒插入数据库

1.配置文件

如果你是使用mysql的情况下,你需要在你的mysql的url后面根上

&rewriteBatchedStatements=true

2.批量导入工具类

工具类的目的就是针对任何类都可以使用该方法

@Component
public class DbUtils {

    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    public <T> void batchInsert(Class<T> mapperClass, Consumer<T> consumer){

        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);

        try {
            T mapper = sqlSession.getMapper(mapperClass);
            consumer.accept(mapper);
            sqlSession.commit();
        } catch (Exception e) {
            sqlSession.rollback();
        }finally {
            sqlSession.close();
        }

    }
}

3,使用该工具类

 long startTime = System.currentTimeMillis();
        logger.info("batchInsertAccount method start,start time is ===> {}", startTime);
        dbUtils.batchInsert(AccountExtMapper.class, mapper -> {
            Account insertParam = new Account();
            accountList.forEach(item -> {
                insertParam.setId(item.getId());
                insertParam.setEmail(item.getEmail());
                insertParam.setPassword(item.getPassword());
                mapper.insertSelective(insertParam);
            });
        });
        logger.info("batchInsertAccount method  time is ===> {}", (System.currentTimeMillis() - startTime) / 1000);

4.insertSelective()  这个就是给数据库插入的一个方法，注意是插入一条

 insert into account
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        ID,
      </if>
      <if test="username != null">
        USERNAME,
      </if>
      <if test="mobile != null">
        MOBILE,
      </if>
      <if test="password != null">
        PASSWORD,
      </if>
      <if test="salt != null">
        SALT,
      </if>
      <if test="status != null">
        STATUS,
      </if>
      <if test="domain != null">
        DOMAIN,
      </if>
      <if test="email != null">
        EMAIL,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=BIGINT},
      </if>
      <if test="username != null">
        #{username,jdbcType=VARCHAR},
      </if>
      <if test="mobile != null">
        #{mobile,jdbcType=VARCHAR},
      </if>
      <if test="password != null">
        #{password,jdbcType=VARCHAR},
      </if>
      <if test="salt != null">
        #{salt,jdbcType=VARCHAR},
      </if>
      <if test="status != null">
        #{status,jdbcType=BIT},
      </if>
      <if test="domain != null">
        #{domain,jdbcType=VARCHAR},
      </if>
      <if test="email != null">
        #{email,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>

总结：我测试的是30万条数据，用了20秒