今天要有个功能,要进行一批数据的插入和修改,为了不频繁调用数据库,所以想到了批量插入和修改,因为从毕业后,就没写过批量插入和批量修改,所以在这里记录一下,避免后续再遇到忘记怎么写了
批量插入(传入的参数是List<实体>list):
<insert id="insertList" keyColumn="id" keyProperty="id" useGeneratedKeys="true" parameterType="java.util.List">
insert into xhs_collection_data (note_id,
`status`,
title,
`desc`,
`time`,
user_id,
nickname,
liked_count,
collected_count,
comment_count,
share_count,
image_list,
tag_list,
batch_number,
file_name) values
<foreach collection="list" separator="," item="item">
( #{item.noteId,jdbcType=VARCHAR}, #{item.status,jdbcType=INTEGER},#{item.title,jdbcType=VARCHAR},#{item.desc,jdbcType=VARCHAR},#{item.time,jdbcType=TIMESTAMP},
#{item.userId,jdbcType=VARCHAR}, #{item.nickname,jdbcType=VARCHAR},#{item.likedCount,jdbcType=VARCHAR},#{item.collectedCount,jdbcType=VARCHAR},#{item.commentCount,jdbcType=TIMESTAMP},
#{item.shareCount,jdbcType=VARCHAR},#{item.imageList,jdbcType=VARCHAR},#{item.tagList,jdbcType=VARCHAR},#{item.batchNumber,jdbcType=BIGINT},#{item.fileName,jdbcType=VARCHAR}
)
</foreach>
</insert>
批量修改(传入的参数是List<实体>list):
sql原理语句:update table set 要修改的表字段A = case when 表字段 = 实体数据字段 then 实体数据字段 when 表字段 = 实体数据字段 then 实体数据字段 when … then… end,
要修改的表字段B = case when 表字段 = 实体数据字段 then 实体数据字段 when 表字段 = 实体数据字段 then 实体数据字段 when … then… end where 条件
注意:这里踩过一个坑,因为当时不会写批量修改的语句,所以让文心一言
帮忙生成了一个批量修改的sql,后续我忘记是不是手动给<foreach>标签手动加的<separator=“,”>这个属性,结果报错了,排查了半天这个sql哪里错了,最后还是放到数据库执行了一下看到了错误原因,当时我还让温馨一样帮忙检查了下我修改后的sql,结果还说没sql没问题,只是可能在拼接时报错
<update id="updateList" parameterType="java.util.List">
update xhs_collection_data
<trim prefix="set" suffixOverrides=",">
<trim prefix="`status` = case" suffix="end,">
<foreach collection="list" index="index" item="item" >
when note_id = #{item.noteId,jdbcType=VARCHAR} then #{item.status,jdbcType=INTEGER}
</foreach>
</trim>
<trim prefix="title = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when note_id = #{item.noteId,jdbcType=VARCHAR} then #{item.title,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="`desc` = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when note_id = #{item.noteId,jdbcType=VARCHAR} then #{item.desc,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="time = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when note_id = #{item.noteId,jdbcType=VARCHAR} then #{item.time,jdbcType=TIMESTAMP}
</foreach>
</trim>
<trim prefix="user_id = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when note_id = #{item.noteId,jdbcType=VARCHAR} then #{item.userId,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="nickname = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when note_id = #{item.noteId,jdbcType=VARCHAR} then #{item.nickname,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="liked_count = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when note_id = #{item.noteId,jdbcType=VARCHAR} then #{item.likedCount,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="collected_count = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when note_id = #{item.noteId,jdbcType=VARCHAR} then #{item.collectedCount,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="comment_count = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when note_id = #{item.noteId,jdbcType=VARCHAR} then #{item.commentCount,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="share_count = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when note_id = #{item.noteId,jdbcType=VARCHAR} then #{item.shareCount,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="image_list = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when note_id = #{item.noteId,jdbcType=VARCHAR} then #{item.imageList,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="tag_list = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when note_id = #{item.noteId,jdbcType=VARCHAR} then #{item.tagList,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="batch_number = case" suffix="end,">
<foreach collection="list" index="index" item="item">
<if test="item.batchNumber != null">
when note_id = #{item.noteId,jdbcType=VARCHAR} then #{item.batchNumber,jdbcType=BIGINT}
</if>
</foreach>
</trim>
<trim prefix="file_name = case" suffix="end,">
<foreach collection="list" index="index" item="item">
<if test="item.fileName != null">
when note_id = #{item.noteId,jdbcType=VARCHAR} then #{item.fileName,jdbcType=VARCHAR}
</if>
</foreach>
</trim>
</trim>
where note_id in
<foreach close=")" collection="list" item="item" open="(" separator=", ">
#{item.noteId,jdbcType=VARCHAR}
</foreach>
</update>