- 作者:老汪软件技巧
- 发表时间:2024-09-25 17:01
- 浏览量:
前言
最近我有位小伙伴问我,在实际工作中,批量更新的代码要怎么写。
这个问题挺有代表性的,今天拿出来给大家一起分享一下,希望对你会有所帮助。
苏三的免费刷题网站:里面:面试八股文、BAT面试真题、工作内推、工作经验分享、技术专栏等等什么都有,欢迎收藏和转发。
1 案发现场
有一天上午,在我的知识星球群里,有位小伙伴问了我一个问题:批量更新你们一般是使用when case吗?还是有其他的批量更新方法?
我的回答是:咱们星球的商城项目中,有批量更新的代码可以参考一下,这个项目中很多代码,大家平时可以多看看。
然后我将关键代码发到群里了,这是批量重置用户密码的业务场景:
id="updateForBatch" parameterType="cn.net.susan.entity.sys.UserEntity">
"list" item="entity" separator=";">
UPDATE sys_user
SET password = #{entity.password},update_user_id=#{entity.updateUserId},update_user_name=#{entity.updateUserName}
<where>
id = #{entity.id}
有小伙伴说,第一次见到这种写法,涨知识了。
还有小伙伴问,上面这种写法,跟直接for循环中update有什么区别?
for(UserEntity userEntity: list) {
userMapper.update(userEntity);
}
直接for循环需要多次请求数据库,网络有一定的开销,很显然没有批量一次请求数据库的好。
2 其他的批量更新写法
有小伙说,他之前一直都是用的case when的写法。
类似下面这样的:
id="updateForBatch" parameterType="cn.net.susan.entity.sys.UserEntity">
update sys_user
prefix="set" suffixOverrides=",">
prefix="password = case id" suffix="end,">
collection="list" item="item">
when #{item.id} then #{item.password}
prefix="update_user_id = case id" suffix="end,">
collection="list" item="item">
when #{item.id} then #{item.updateUserId}
prefix="update_user_name = case id" suffix="end">
collection="list" item="item">
when #{item.id} then #{item.updateUserName}
id in (
collection="list" separator="," item="item">
#{item.id}
)
但这种写法显然需要拼接很多条件,有点复杂,而且性能也不太好。
还有些文章中介绍,可以使用在insert的时候,可以在语句最后加上ON DUPLICATE KEY UPDATE关键字。
id="updateForBatch" parameterType="cn.net.susan.entity.sys.UserEntity">
insert into sys_user
(id,username,password) values
collection="list" index="index" item="item" separator=",">
(#{item.id},
#{item.username},
#{item.password})
ON DUPLICATE KEY UPDATE
password=values(password)
在插入数据时,数据库会先判断数据是否存在,如果不存在,则执行插入操作。如果存在,则执行更新操作。
这种方式我之前也用过,一般需要创建唯一索引。
因为很多时候主键id,是自动增长的或者根据雪花算法生成的,每次都不一样,没法区分多次相同业务参数请求的唯一性。
因此,建议创建一个唯一索引,来保证业务数据的唯一性。
比如:给username创建唯一索引,在insert的时候,发现username已存在,则执行update操作,更新password。
这种方式批量更新数据,性能比较好,但一般的大公司很少会用,因为非常容易出现死锁的问题。
因此,目前批量更新数据最好的选择,还是我在文章开头介绍的第一种方法。
3 发现了一个问题
群里另外一位小伙伴,按照我的建议,在自己的项目中尝试了一下foreach的这种批量更新操作,但代码报了一个异常:
sql injection violation, multi-statement not allow
这个异常是阿里巴巴druid包的WallFilter中报出来了。
它里面有个checkInternal方法,会对sql语句做一些校验,如果不满足条件,就会抛异常:
而druid默认不支持一条sql语句中包含多个statement语句,例如:我们的批量update数据的场景。
此外,MySQL默认也是关闭批量更新数据的,不过我们可以在jdbc的url要上,添加字符串参数:&allowMultiQueries=true,开启批量更新操作。
比如:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
druid:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/console?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true
username: root
password: root
这个改动非常简单。
但WallFilter中的校验问题如何解决呢?
于是,我上网查了一下,可以通过参数调整druid中的filter的判断逻辑,比如:
spring:
datasource:
url: jdbc:xxx&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true&allowMultiQueries=true
username: xxx
password: xxx
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
druid:
filter:
wall:
config:
multi-statement-allow: true
none-base-statement-allow: true
通过设置filter中的multi-statement-allow和none-base-statement-allow为true,这样就能开启批量更新的功能。
最近就业形势比较困难,为了感谢各位小伙伴对苏三一直以来的支持,我特地创建了一些工作内推群, 看看能不能帮助到大家。
你可以在群里发布招聘信息,也可以内推工作,也可以在群里投递简历找工作,也可以在群里交流面试或者工作的话题。