mybatis批量更新与插入

引言

当我们在使用mybatis的时候，可能会遇到批量插入和更新数据的问题。如果数据一条一条的更新或插入，那么每一条数据都会涉及到一次数据库的操作，包括操作网络IO以及磁盘IO，可想而知，效率是非常低下的。现在我们都很少直接使用原生的jdbc操作数据库，而是使用比较成熟的ORM框架，现在就让我们一起来学习，如何使用mybatis批量更新和插入数据。

批量模式

总下来批量处理共有两种模式,foreach动态标签拼接SQL语句和使用BatchExecutor批处理器

foreach拼接SQL

在mybatis的xml文件中，使用foreach循环动态标签拼接SQL语句。程序会将拼接好的一长串SQL一次性发送给数据库执行，只需要进行一次网络IO，大大提高了执行效率。

批量插入

拼接类似于如下的SQL语句：

INSERT tmp_user(user_code,user_name)VALUES('1001','张三'),('1002','李四'),('1003','王二')

实例：

<insert id="insertUser">
	INSERT INTO tmp_user(user_code,user_name) VALUES
	<foreach item="item" index="index" collection="list"  separator=",">
		(#{item.userCode},#{item.userName})
	</foreach>
</insert>

注意：

mybatis对批量插入的数据量主要是mysql自身对接收数据量的大小限制，通过参数max_allowed_packet控制

查询当前大小

select @@max_allowed_packet;

修改为256M：

SET GLOBAL max_allowed_packet=268435456;

批量更新

使用foreach循环动态标签拼接，使每一条数据的更新对应一条update语句，多条update语句之间使用";"号进行拼接。
实例一：

<update id="updateBatchUserById">
	<foreach item="item" index="index" collection="list" separator=";">
		UPDATE tmp_user SET user_code = #{userCode},user_name = #{userName} WHERE id = #{id}
	</foreach>
</update>

拼接结果：

UPDATE tmp_user SET user_code = '1001',user_name = '张三' WHERE id = 1;UPDATE tmp_user SET user_code = '1002',user_name = '李四' WHERE id = 2;UPDATE tmp_user SET user_code = '1003',user_name = '王五' WHERE id = 3;

注意，默认情况下，数据库是不支持执行这样由";“号拼接的长串的，执行的时候会报错，提示说执行的SQL有语法错误。我们需要通过在数据库连接URL中指定allowMultiQueries参数值为true告诉数据库以支持”;"号分隔的多条语句的执行。

spring.datasource.url=jdbc:mysql://localhost:3306/test?allowMultiQueries=true

实例二：

<update id="updateBatchUserById">
	UPDATE tmp_user(user_code,user_name)
	<trim prefix="set" suffixOverrides=",">
		<trim prefix=" user_code = CASE " suffix=" end, ">
			<foreach collection="list" item="item">
				<if test="item.userCode != null">
					WHEN id = #{item.id} THEN #{item.userCode}
				</if>
			</foreach>
		</trim>
		<trim prefix=" user_name = CASE " suffix=" end, ">
			<foreach collection="list" item="item">
				<if test="item.userName != null">
					WHEN id = #{item.id} THEN #{item.userName}
				</if>
			</foreach>
		</trim>
	</trim>
	WHERE
	id IN
	<foreach collection="list" item="item" open="(" close=")" separator=",">
		#{item.id}
	</foreach>
</update>

拼接结果：

UPDATE tmp_user 
SET user_code =
CASE
		WHEN id = 1 THEN '1001' 
		WHEN id = 2 THEN '1002' 
		WHEN id = 3 THEN '1003' 
	END,
	user_name =
CASE
	WHEN id = 1 THEN '张三'
	WHEN id = 2 THEN '李四'
	WHEN id = 3 THEN '王五'
END 
WHERE id IN (1,2,3)

可以发现，如果批量数据量太大，要更新的字段太多，那这个SQL就会非常难看且复杂，充斥大量的case when判断，而且这种case when感觉也会增大数据库压力，因为这种case when都需要数据库自己去做判断，所以个人感觉不太好，所以不推荐。

BatchExecutor批处理

MyBatis 提供了 BatchExecutor 批处理器，可以在一次数据库会话中批量执行多个 SQL 语句。这种方式需要在代码中手动创建批处理器或配置文件配置一下，并调用 batch 方法执行批量更新。注意，此方式可能不支持所有数据库，因此请仔细查阅文档以确认你的数据库是否支持。

手动创建批处理器

SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
 
YourMapper mapper = sqlSession.getMapper(YourMapper.class);
try {
    for (YourEntity entity : entities) {
        mapper.update(entity);
    }
    sqlSession.commit();
} finally {
    sqlSession.close();
}

实例

mapper 方法

int updateSupNumber(@Param("supNumber") String supNumber, @Param("id") Long putOrerId);

xml配置：

<update id="updateSupNumber">
	UPDATE air_put_order SET sup_number = #{supNumber} WHERE id = #{id}
</update>

service方法：

 @Override
    public int updateBuyPrice(JSONObject form) {
        SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH,false);
        int result = 0;
        try {
            airPutOrderGoodsSMapper = session.getMapper(AirPutOrderGoodsMapper.class);
            JSONArray prices = form.getJSONArray("prices");
            for(int i = 0;i<prices.size();i++){
                JSONObject jsonObject = prices.getJSONObject(i);
                airPutOrderGoodsSMapper.updateBuyPrice(jsonObject);
            }
            String supNumber = form.getString("supNumber");
            Long putOrerId = form.getLong("puOrderId");
            result = airPutOrderGoodsSMapper.updateSupNumber(supNumber,putOrerId);
            session.commit();
        }finally {
            session.close();
        }
        return result;
    }

自动创建批处理器

在配置文件中配置一下default-executor-type: batch即可

# MyBatis配置
mybatis:
  configuration:
    default-executor-type: batch

service方法：
airPutOrderGoodsSMapper 使用自动注入的对象就可以了，不需要手动创建。

@Override
@Transactional
public int updateBuyPrice(JSONObject form) {
      JSONArray prices = form.getJSONArray("prices");
      for(int i = 0;i<prices.size();i++){
          JSONObject jsonObject = prices.getJSONObject(i);
          airPutOrderGoodsSMapper.updateBuyPrice(jsonObject);
      }
      String supNumber = form.getString("supNumber");
      Long putOrerId = form.getLong("puOrderId");
      int result = airPutOrderGoodsSMapper.updateSupNumber(supNumber,putOrerId);
  return result;
}

总结

foreach模式批量插入模式与MyBatis中Batch模式对比差异：

1.二者速度差异不大，for模式使用简单，Batch模式使用复杂
2.如果mysql自身对接收数据量有大小限制，建议使用Batch模式