Mybatis 批量操作8种实现总结

2024-07-16 14:10| 来源: 网络整理| 查看: 265

批量新增 1.方式一（常用） INSERT INTO lp_user_test_batch ( id, user_id, user_name, user_age, type, create_time, update_time ) VALUES ( #{item.id,jdbcType=BIGINT}, #{item.userId,jdbcType=VARCHAR}, #{item.userName,jdbcType=VARCHAR}, #{item.userAge,jdbcType=INTEGER}, #{item.type,jdbcType=INTEGER}, #{item.createTime,jdbcType=TIMESTAMP}, #{item.updateTime,jdbcType=TIMESTAMP} )

测试结果

数量耗时10001469ms20002534ms30002613ms40003549ms50004733ms80005761ms100006055ms 2.方式二

批量新增或更新方式注：需要给唯一主键添加唯一索引，update才会生效

INSERT INTO lp_user_test_batch ( id, user_id, user_name, user_age, type, create_time, update_time ) VALUES ( #{item.id,jdbcType=BIGINT}, #{item.userId,jdbcType=VARCHAR}, #{item.userName,jdbcType=VARCHAR}, #{item.userAge,jdbcType=INTEGER}, #{item.type,jdbcType=INTEGER}, #{item.createTime,jdbcType=TIMESTAMP}, #{item.updateTime,jdbcType=TIMESTAMP} ) ON DUPLICATE KEY UPDATE user_name = VALUES(user_name), user_age = VALUES(user_age), type = VALUES(type), update_time = VALUES(update_time)

测试结果

数量耗时10001692ms20002346ms30003249ms40003443ms50003999ms80006460ms100007053ms 3.方式三

单条sql+批量方式的SqlSession

INSERT INTO lp_user_test_batch ( id, user_id, user_name, user_age, type, create_time, update_time ) values ( #{id,jdbcType=BIGINT}, #{userId,jdbcType=VARCHAR}, #{userName,jdbcType=VARCHAR}, #{userAge,jdbcType=INTEGER}, #{type,jdbcType=INTEGER}, #{createTime,jdbcType=TIMESTAMP}, #{updateTime,jdbcType=TIMESTAMP} ) @Resource(name = "sqlSessionFactory") private SqlSessionFactory sqlSessionFactory; /** * 利用 MyBatis 批处理特性，批量提交 */ public void batchInsert(List testBatchDAOList) { //集合非空 if (CollectionUtils.isEmpty(testBatchDAOList)) { return; } //批处理方式 SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH); //获得对应的Mapper UserTestBatchDOMapper userTestBatchDOMapper = sqlSession.getMapper(UserTestBatchDOMapper.class); try { for (UserTestBatchDO testBatchDO : testBatchDAOList) { userTestBatchDOMapper.insert(testBatchDO); } //统一提交 sqlSession.commit(); } catch (Exception e) { //没有提交的数据可以回滚 sqlSession.rollback(); } finally { //关闭 sqlSession sqlSession.close(); } }

测试结果

数量耗时10002174ms20003104ms30003801ms40004991ms50005930ms80008151ms100008252ms 批量修改 1.方式一

批量新增或更新方式注：需要给唯一主键添加唯一索引，update才会生效

测试结果注：当前表内数据行数 10000

数量耗时10001505ms20002617ms30002922ms40003292ms50003443ms80004832ms100004886ms

优点：速度快缺点：使用特殊语法 on duplicate key update 语法增加sql难度性

2.方式二

单条sql+批量方式的SqlSession

UPDATE lp_user_test_batch SET user_name = #{userName,jdbcType=VARCHAR}, user_age = #{userAge,jdbcType=INTEGER}, type = #{type,jdbcType=INTEGER}, update_time = #{updateTime,jdbcType=TIMESTAMP} WHERE user_id = #{userId,jdbcType=VARCHAR} @Resource(name = "sqlSessionFactory") private SqlSessionFactory sqlSessionFactory; /** * 利用 MyBatis 批处理特性，批量更新 */ public void batchUpdate(List testBatchDAOList) { //集合非空 if (CollectionUtils.isEmpty(testBatchDAOList)) { return; } //批处理方式 SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH); //获得对应的Mapper UserTestBatchDOMapper userTestBatchDOMapper = sqlSession.getMapper(UserTestBatchDOMapper.class); try { for (UserTestBatchDO testBatchDO : testBatchDAOList) { userTestBatchDOMapper.updateByUserId(testBatchDO); } //统一提交 sqlSession.commit(); //清理缓存，防止溢出 sqlSession.clearCache(); } catch (Exception e) { //没有提交的数据可以回滚 sqlSession.rollback(); } finally { //关闭 sqlSession sqlSession.close(); } }

测试结果注：当前表内数据行数 10000

数量耗时10003158ms20004324ms30006466ms40007572ms50009812ms800012846ms1000016088ms

优点：通过日志观察，生成一条执行语句sql ,多行参数，统一commit 缺点：比方式一速度略慢

3.方式三

java程序循环调用单条修改语句执行方式：一条sql ,程序循环执行

for (UserTestBatchDO userTestBatch : testBatchDAOList) { userTestBatchDOMapper.updateByUserId(userTestBatch); }

测试结果注：当前表内数据行数 10000

数量耗时100033907ms200042866ms300089675ms5000104833ms

优点：方便单条控制提交事物缺点：耗时，耗性能、每一次循环都需要与数据库交互一次

4.方式四

Mybatis foreach 循环执行方式：拼接好一条sql,后执行

UPDATE lp_user_test_batch SET user_name = #{item.userName,jdbcType=VARCHAR}, user_age = #{item.userAge,jdbcType=INTEGER}, type = #{item.type,jdbcType=INTEGER}, update_time = #{item.updateTime,jdbcType=TIMESTAMP} WHERE user_id = #{item.userId,jdbcType=VARCHAR}

测试结果注：当前表内数据行数 10000

数量耗时10002671ms20004170ms30004514ms40005152ms50006572ms800010209ms1000012158ms

优点：生成多条sql,统一执行，与数据库交互次数少缺点 : 生成多条拼接的update语句,update语句比较多，量大了就有可能造成sql阻塞。

5.方式五

mybatis sql 使用 case when

update lp_user_test_batch when user_id = #{item.userId,jdbcType=VARCHAR} then #{item.userName,jdbcType=VARCHAR} when user_id = #{item.userId,jdbcType=VARCHAR} then #{item.userAge,jdbcType=INTEGER} when user_id = #{item.userId,jdbcType=VARCHAR} then #{item.type,jdbcType=INTEGER} when user_id = #{item.userId,jdbcType=VARCHAR} then #{item.updateTime,jdbcType=TIMESTAMP} user_id in #{item.userId,jdbcType=VARCHAR}

测试结果注：当前表内数据行数 10000

数量耗时10003201ms20004804ms30006833ms40008554ms500011688ms800026501ms1000034724ms

缺点: xml中的循环体有点多，每一个case when 都要循环一遍list集合，所以大批量拼sql的时候会比较慢。生成多条拼接sql,sql长度过长，容易sql超长引起报错 Packet for query is too large。

MySQL 最大允许的 packet 在这里插入图片描述

Mybatis批处理介绍

Mybatis内置执行器类型ExecutorType有3种分别是 ExecutorType.SIMPLE: 不做特殊处理，为每个语句的执行创建一个新的预处理语句。 ExecutorType.REUSE: 可以复用预处理语句。 ExecutorType.BATCH:可以批量执行所有更新语句

SIMPLE与BATCH（批量）对比默认的是simple，该模式下它为每个语句的执行创建一个新的预处理语句，单条提交sql；而batch模式重复使用已经预处理的语句，并且批量执行所有更新语句，显然batch性能将更优；但是批量模式无法返回自增主键

测试环境配置

系统：win 8.1 Mysql : 5.7 java环境：junit 注：环境不同可能会引起耗时存在差异。

总结

单次批量操作不要过大，批量新增使用方式一，批量更新方式一与方式二经过测试是最优的选择也可以根据安全方面综合考虑，选择适合的方式。

关注程序员小强公众号更多编程趣事，知识心得与您分享在这里插入图片描述

【本文地址】

公司简介

联系我们