aspnet网站开发实战seo技术论坛

今天要有个功能，要进行一批数据的插入和修改，为了不频繁调用数据库，所以想到了批量插入和修改，因为从毕业后，就没写过批量插入和批量修改，所以在这里记录一下，避免后续再遇到忘记怎么写了

批量插入(传入的参数是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>