Mybatis的mapper.xml批量插入、修改sql

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

批量插入(传入的参数是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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

批量修改(传入的参数是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，后续我忘记是不是手动给标签手动加的这个属性，结果报错了，排查了半天这个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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83