mybatis批量更新與插入方式

更新時(shí)間：2024年08月05日 09:43:31 作者：消碼哥

這篇文章主要介紹了mybatis批量更新與插入方式,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

引言

當(dāng)我們在使用mybatis的時(shí)候，可能會(huì)遇到批量插入和更新數(shù)據(jù)的問題。

如果數(shù)據(jù)一條一條的更新或插入，那么每一條數(shù)據(jù)都會(huì)涉及到一次數(shù)據(jù)庫的操作，包括操作網(wǎng)絡(luò)IO以及磁盤IO，可想而知，效率是非常低下的。

現(xiàn)在我們都很少直接使用原生的jdbc操作數(shù)據(jù)庫，而是使用比較成熟的ORM框架，現(xiàn)在就讓我們一起來學(xué)習(xí)，如何使用mybatis批量更新和插入數(shù)據(jù)。

批量模式

總下來批量處理共有兩種模式,foreach動(dòng)態(tài)標(biāo)簽拼接SQL語句和使用BatchExecutor批處理器

foreach拼接SQL

在mybatis的xml文件中，使用foreach循環(huán)動(dòng)態(tài)標(biāo)簽拼接SQL語句。程序會(huì)將拼接好的一長串SQL一次性發(fā)送給數(shù)據(jù)庫執(zhí)行，只需要進(jìn)行一次網(wǎng)絡(luò)IO，大大提高了執(zhí)行效率。

批量插入

拼接類似于如下的SQL語句：

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

實(shí)例：

<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對批量插入的數(shù)據(jù)量主要是mysql自身對接收數(shù)據(jù)量的大小限制，通過參數(shù)max_allowed_packet控制

查詢當(dāng)前大小：

select @@max_allowed_packet;

修改為256M：

SET GLOBAL max_allowed_packet=268435456;

批量更新

使用foreach循環(huán)動(dòng)態(tài)標(biāo)簽拼接，使每一條數(shù)據(jù)的更新對應(yīng)一條update語句，多條update語句之間使用";"號進(jìn)行拼接。

實(shí)例一：

<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>

拼接結(jié)果：

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;

注意，默認(rèn)情況下，數(shù)據(jù)庫是不支持執(zhí)行這樣由";“號拼接的長串的，執(zhí)行的時(shí)候會(huì)報(bào)錯(cuò)，提示說執(zhí)行的SQL有語法錯(cuò)誤。

我們需要通過在數(shù)據(jù)庫連接URL中指定allowMultiQueries參數(shù)值為true告訴數(shù)據(jù)庫以支持”;"號分隔的多條語句的執(zhí)行。

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

實(shí)例二：

<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>

拼接結(jié)果：

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)

可以發(fā)現(xiàn)，如果批量數(shù)據(jù)量太大，要更新的字段太多，那這個(gè)SQL就會(huì)非常難看且復(fù)雜，充斥大量的case when判斷，而且這種case when感覺也會(huì)增大數(shù)據(jù)庫壓力，因?yàn)檫@種case when都需要數(shù)據(jù)庫自己去做判斷，所以個(gè)人感覺不太好，所以不推薦。

BatchExecutor批處理

MyBatis 提供了 BatchExecutor 批處理器，可以在一次數(shù)據(jù)庫會(huì)話中批量執(zhí)行多個(gè) SQL 語句。

這種方式需要在代碼中手動(dòng)創(chuàng)建批處理器或配置文件配置一下，并調(diào)用 batch 方法執(zhí)行批量更新。

注意，此方式可能不支持所有數(shù)據(jù)庫，因此請仔細(xì)查閱文檔以確認(rèn)你的數(shù)據(jù)庫是否支持。

手動(dòng)創(chuàng)建批處理器

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();
}

實(shí)例

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;
    }

自動(dòng)創(chuàng)建批處理器

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

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

service方法：

airPutOrderGoodsSMapper 使用自動(dòng)注入的對象就可以了，不需要手動(dòng)創(chuàng)建。

@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;
}