Mybatis批量事务

小鱼儿 2023-02-14 13:07 12阅读 0赞

## Mybatis批量事务 ##

mysql数据库不能同时支持30万数据的 一次性事务提交，需要分批次地去进行事务的提交，否则数据将堆积到mysql数据库中，导致数据库性能过低。（需要采用编程式事务的方式分批次多次提交）

service 层  
通过流式表达式实现数据1000条地操作

@Service
    @Transactional
    public class UserService extends ServiceImpl<UserInfoPojoMapper, UserInfoPojo> implements IUserService { 
    
        @Value(value = "${batchInsertCount}")
        Integer batchInsertCount;
    
        @Autowired
        SqlSessionFactory sqlSessionFactory;
        //日志记录器
        private static Logger logger = LogManager.getLogger(LogManager.ROOT_LOGGER_NAME);
    
    
        private void insertBatch(List<UserInfoPojo> list) { 
            SqlSession sqlSession =sqlSessionFactory.openSession();
            int index = list.size() / batchInsertCount;
            int count = list.size();
            long startTime = System.currentTimeMillis();   //获取开始时间
            for (int i = 0; i <= index; i++) { 
                if (count > 0) { 
                    try {  //每一千条提交一次避免数据堆积
                        //stream流表达式，skip表示跳过前i*10000条记录，limit表示读取当前流的前1000条记录
                        userInfoPojoMapper.BatchInsert(list.stream().skip(i * batchInsertCount).limit(batchInsertCount).collect(Collectors.toList()));
                        sqlSession.commit();
                        long endTime = System.currentTimeMillis(); //获取结束时间
                        count = count - 1000;
                        logger.info("用户模块批量插入-总条数：" + list.size() + ";还剩：" + (count>0?count:0) + "条，已用时：" + (endTime - startTime) + "ms");
                    } catch (Exception e) { 
                        logger.error("批量插入数据：" + batchInsertCount + "条一次，第" + (i + 1) + "出现异常，进行事务回滚"+e.getMessage(),e);
                        sqlSession.rollback();
                    }
                }
            }
        }
        private void updateBatch(List<UserInfoPojo> list) { 
            SqlSession sqlSession =sqlSessionFactory.openSession();
            int index = list.size() / batchInsertCount;
            int count = list.size();
            long startTime = System.currentTimeMillis();   //获取开始时间
            for (int i = 0; i <= index; i++) { 
                if (count > 0) { 
                    try {  //每一千条提交一次避免数据堆积
                        //stream流表达式，skip表示跳过前i*10000条记录，limit表示读取当前流的前1000条记录
                        userInfoPojoMapper.BatchUpdate(list.stream().skip(i * batchInsertCount).limit(batchInsertCount).collect(Collectors.toList()));
                        sqlSession.commit();
                        long endTime = System.currentTimeMillis(); //获取结束时间
                        count = count - 1000;
                        logger.info("用户模块批量更新-总条数：" + list.size() + ";还剩：" + (count>0?count:0) + "条，已用时：" + (endTime - startTime) + "ms");
                    } catch (Exception e) { 
                        logger.error("批量更新数据：" + batchInsertCount + "条一次，第" + (i + 1) + "出现异常，进行事务回滚"+e.getMessage(),e);
                        sqlSession.rollback();
                    }
                }
            }
        }
    
    }

\*\*\*\*Mapper.xml  
useGeneratedKeys=“true” keyProperty=“id” 这里是为了实现 insert后将id以set的方式注入到原POJO中  
使用\#\{option.username,jdbcType=VARCHAR\}而不是\#\{option.username\} 是为即使该值即使为null，也不会报sql错误

<insert id="BatchInsert" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
        insert into tb_user (id, username, password,
      usertype, memo, creater,
      create_time, updater, update_time
      )
    values
      <foreach collection="list" item="option" index="index" separator=",">
    (#{ option.id,jdbcType=INTEGER}, #{ option.username,jdbcType=VARCHAR}, #{ option.password,jdbcType=VARCHAR},
      #{ option.usertype,jdbcType=VARCHAR}, #{ option.memo,jdbcType=VARCHAR}, #{ option.creater,jdbcType=VARCHAR},
      now(), #{ option.updater,jdbcType=VARCHAR}, now()
      )
       </foreach>
    </insert>
    
    
    
    <update id="BatchUpdate" parameterType="java.util.List">
        <foreach collection="list" item="option" index="index" open="" close="" separator=";">
            update tb_user
            <set>
                password = #{ option.password,jdbcType=VARCHAR},
                usertype = #{ option.usertype,jdbcType=VARCHAR},
                memo = #{ option.memo,jdbcType=VARCHAR},
                creater = #{ option.creater,jdbcType=VARCHAR},
                create_time = now(),
                updater = #{ option.updater,jdbcType=VARCHAR},
                update_time = now(),
            </set>
            where username = #{ option.username,jdbcType=VARCHAR}
        </foreach>
    </update>

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200812232817798.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA==,size_16,color_FFFFFF,t_70#pic_center