我要评论引言
当我们在使用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模式
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论