Mybatis:通过on duplicate key update实现批量插入或更新
Mybatis:通过on duplicate key update实现批量插入或更新
2022-04-14 1070
简介: Mybatis:通过on duplicate key update实现批量插入或更新
目录
1.根据selectkey判断查询的count值是否为1,然后再进行新增或更新
2.根据相应的唯一主键来判断是否新增或更新 [对事务支持较好]
ON DUPLICATE KEY UPDATE 附带更新条件
3. insert ... on duplicate key update column=IF(条件,值1,值2 )
批量的saveOrupdate:
- 使用要点:
(1) 表要求必须有主键或唯一索引才能起效果,否则insert或update无效;
(2) 注意语法on duplicate key update后面应为需要更新字段,不需要更新的字段不用罗列;
(3) 相较于replace into(insert加强版,不存在时insert,存在时先delete后insert)虽然也能达到批量更新目的,但因为删除和添加需要重复维护索引,所以大批量比on duplicate key update性能要差,小量可忽略,自选为主。- foreach中()后面不要有空格,如果报错
org.springframework.jdbc.BadSqlGrammarException:
### Error updating database.
Cause: java.sql.SQLSyntaxErrorException:
You have an error in your SQL syntax;
check the manual that corresponds to
your MySQL server version for the right syntax to use near '
- 除了检查,是否缺少或者多余,还要检查是否有多余空格
<!--批量的插入 or 更新的操作-->
<insert id="batchInsertStaff">
insert into xxxTable (
merchant_id, app_id, department_id,
`name`, mobile, token,
created_at, updated_at)
values
<foreach collection="list" item="item" separator="," >
(
#{item.merchantId,jdbcType=INTEGER},#{item.appId,jdbcType=INTEGER},
#{item.departmentId},
#{item.name},#{item.mobile},#{item.token},
NOW(),NOW()
)
</foreach>
on duplicate key update department_id = values(department_id),updated_at = values(updated_at)
</insert>
单条的saveOrupdate:
1.根据selectkey判断查询的count值是否为1,然后再进行新增或更新
<insert id="insertOrUpdateOneUserInfo">
<selectKey keyProperty="count" resultType="int" order="BEFORE">
select count(*) as count from `${tableName}` where userid = #{user.userid}
</selectKey>
<!-- 如果大于0则更新 -->
<if test="count>0">
UPDATE `${tableName}`
<set >
<if test="user.appId != null" >
`app_id` = #{user.appId},
</if>
<if test="user.yunid != null" >
`yunid` = #{user.yunid},
</if>
<if test="user.qr_scene != null" >
`qr_scene` = #{user.qr_scene},
</if>
<if test="user.openid != null" >
`openid` = #{user.openid},
</if>
<if test="user.qr_scene_str != null" >
`qr_scene_str` = #{user.qr_scene_str},
</if>
<if test="user.nickname != null" >
`nickname` = #{user.nickname},
</if>
<if test="user.language != null" >
`language` = #{user.language},
</if>
<if test="user.city != null" >
`city` = #{user.city},
</if>
<if test="user.country != null" >
`country` = #{user.country},
</if>
<if test="user.remark != null" >
`remark` = #{user.remark},
</if>
<if test="user.headimgurl != null" >
`headimgurl` = #{user.headimgurl},
</if>
<if test="user.province != null" >
`province` = #{user.province},
</if>
<if test="user.tagIdList != null" >
`tagid_list` = #{user.tagIdList},
</if>
<if test="user.subscribe_scene != null" >
`subscribe_scene` = #{user.subscribe_scene},
</if>
<if test="user.unionid != null" >
`unionid` = #{user.unionid},
</if>
<if test="user.subscribe != null" >
`subscribe` = #{user.subscribe},
</if>
<if test="user.groupid != null" >
`groupid` = #{user.groupid},
</if>
<if test="user.subTime != null" >
`subscribe_time` = #{user.subTime},
</if>
<if test="user.sexStr != null" >
`sex` = #{user.sexStr},
</if>
<if test="user.updatedAt != null" >
`updated_at` = #{user.updatedAt},
</if>
</set>
WHERE userid = #{user.userid}
</if>
<!-- 如果等于0则保存 -->
<if test="count==0">
INSERT IGNORE INTO `${tableName}`
(
<include refid="wx_temp_params" />