批量插入

  • 原始方法

    <!--这种方式没有会用mybatis的缓存机制,效率略差-->
    <insert id="insertBatch" parameterType="java.util.List">  
     insert into talarmvirtualpolicy (  <include refid="Base_Column_List"/>  
     ) values  <foreach collection="list" item="item" index="index" separator=",">  
     (#{item.guid}, #{item.deviceNo}, #{item.alarmTypeList}, #{item.policyJson}, #{item.createRoleId}, #{item.modifyTime}, #{item.createUsername})  </foreach>  
    </insert>
  • MyBatis推荐批量插入(MyBatis动态SQL)

    <dependency>
      <groupId>org.mybatis.dynamic-sql</groupId>
      <artifactId>mybatis-dynamic-sql</artifactId>
      <version>1.4.0</version>
    </dependency>
     try(SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
      SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);
      List<SimpleTableRecord> records = getRecordsToInsert(); // not shown
    
      BatchInsert<SimpleTableRecord> batchInsert = SqlBuilder.insert(records)
              .into(simpleTable)
              .map(id).toProperty("id")
              .map(firstName).toProperty("firstName")
              .map(lastName).toProperty("lastName")
              .map(birthDate).toProperty("birthDate")
              .map(employed).toProperty("employed")
              .map(occupation).toProperty("occupation")
              .build()
              .render(RenderingStrategies.MYBATIS3);
    
      batchInsert.insertStatements().forEach(mapper::insert);
    
      session.commit();
    }

    官网链接地址:https://mybatis.org/mybatis-dynamic-sql/docs/insert.html#batch-insert-support
    其他示例代码:

    try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false)) {
      TestMapper testMapper = sqlSession.getMapper(TestMapper.class);
      for (int i = 0; i < 1000; i++) {
          TestEntity entity = new TestEntity();
          entity.setName("Name " + i);
          entity.setId((long) i);
          entity.setCreated(new Date());
          testMapper.insert(entity);
          if ((i + 1) % 50 == 0) {
              //每50条执行一次commit操作
              sqlSession.commit();
              sqlSession.clearCache();
          }
      }
    }
  • MyBatis-Plus批量插入优化

MyBatis-Plus批量插入是通过实现com.baomidou.mybatisplus.extension.service.IService接口,插入数据时调用saveBatch方法。此时会发现批量插入效率非常差。解决方法:
在jdbc url中添加&rewriteBatchedStatements=true参数。例如:
jdbc:mysql://127.0.0.1:35200/vss?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true&zeroDateTimeBehavior=convertToNull

批量更新

  • 根据testId更新username和modifyTime字段

    <update id="updateBatch" parameterType="java.util.List">  
     update t_test set  <foreach collection="testList" item="item" open="username=case testId" close="end">  
     when ${item.testId} then #{item.username}  </foreach>  
    <foreach collection="testList" item="item" open=",modifyTime=case testId" close="end">  
     when ${item.testId} then #{item.modifyTime}  </foreach>  
     where testId in  <foreach collection="testList" item="item" open="(" close=")" separator=",">  
     ${item.testId}  </foreach>  
    </update>
  • SQL语句

    UPDATE t_test SET 
      username = CASE testId
          WHEN 1 THEN "xxx" 
          WHEN 2 THEN "yyy" 
          ELSE "other" 
      END,
      modifyTime = CASE testId  
          WHEN 1 THEN '2021-07-28 18:12:34' 
          WHEN 2 THEN '2021-07-28 18:12:34' 
          ELSE '0000-00-00 00:00:00' 
      END
    WHERE testId IN (1, 2)
最后修改:2022 年 09 月 15 日
如果觉得我的文章对你有用,请随意赞赏