Mybatis 批量修改数据,，并判断非空数据插入

方法一：

<update id="updateListPO">       
    <foreach collection="list" separator=";" item="item">
        UPDATE project_quotation_item
        <SET>
        	product_num = #{item.productNum},
        	product_price_total = #{item.productPriceTotal},
       		product_price_wttax = #{item.productPriceWttax},
        	certificate = #{item.certificate},
        	deliver_date = #{item.deliverDate},
        	product_brand = #{item.productBrand},
        	producer = #{item.producer},
        	exp = #{item.exp},
        	is_deleted = #{item.isDeleted},
        	remark = #{item.remark},
        	substitute_type = #{item.substituteType},
        	<if test='null != item.quotationStatus and "" != item.quotationStatus and "null" != item.quotationStatus'>
                quotation_status = #{item.quotationStatus},
        	</if>
        </set>
        WHERE
        id = #{item.id}
    </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

方法二：（优化后的）

<update id="updateListPO">
        update project_quotation_item
        <set>
            <trim prefix="product_num = case" suffix="end,">
                <foreach collection="list" item="item">
                	<if test='null != item.productNum and "" != item.productNum and "null" != item.productNum'>
                    	when id=#{item.id} then #{item.productNum}
                    </if>
                </foreach>
            </trim>
            <trim prefix="product_price_total = case" suffix="end,">
                <foreach collection="list" item="item">
                    when id=#{item.id} then #{item.productPriceTotal}
                </foreach>
            </trim>
            <trim prefix="product_price_wttax = case" suffix="end,">
                <foreach collection="list" item="item">
                    when id=#{item.id} then #{item.productPriceWttax}
                </foreach>
            </trim>
            <trim prefix="certificate = case" suffix="end,">
                <foreach collection="list" item="item">
                    when id=#{item.id} then #{item.certificate}
                </foreach>
            </trim>
            <trim prefix="deliver_date = case" suffix="end,">
                <foreach collection="list" item="item">
                    when id=#{item.id} then #{item.deliverDate}
                </foreach>
            </trim>
            <trim prefix="product_brand = case" suffix="end,">
                <foreach collection="list" item="item">
                    when id=#{item.id} then #{item.productBrand}
                </foreach>
            </trim>
            <trim prefix="producer = case" suffix="end,">
                <foreach collection="list" item="item">
                    when id=#{item.id} then #{item.producer}
                </foreach>
            </trim>
            <trim prefix="exp = case" suffix="end,">
                <foreach collection="list" item="item">
                    when id=#{item.id} then #{item.exp}
                </foreach>
            </trim>
            <trim prefix="is_deleted = case" suffix="end,">
                <foreach collection="list" item="item">
                    when id=#{item.id} then #{item.isDeleted}
                </foreach>
            </trim>
            <trim prefix="remark = case" suffix="end,">
                <foreach collection="list" item="item">
                    when id=#{item.id} then #{item.remark}
                </foreach>
            </trim>
            <trim prefix="substitute_type = case" suffix="end,">
                <foreach collection="list" item="item">
                    when id=#{item.id} then #{item.substituteType}
                </foreach>
            </trim>
            <trim prefix="quotation_status = case" suffix="end,">
                <foreach collection="list" item="item">
                    when id=#{item.id} then #{item.quotationStatus}
                </foreach>
            </trim>
        </set>
        <where>
             id in
            <foreach collection="list" separator="," item="item" open="(" close=")">
                #{item.id}
            </foreach>
        </where>
    </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

方法三：

<update id="updateBatch">
	update 
		T t
	<trim prefix="set" suffixOverrides=",">
	  <trim prefix="t.updateTime = case" suffix="end,">
		 <foreach collection="list" item="item" index="index">
			 <if test="item.updateTime != null">
			  	when t.id = #{item.id} then #{item.updateTime}
			 </if>
		 </foreach>
	  </trim>
	  <trim prefix="t.updateBy = case" suffix="end,">
		 <foreach collection="list" item="item" index="index">
			 <if test="item.updateBy != null">
			  	when t.id = #{item.id} then #{item.updateBy}
			 </if>
		 </foreach>
	  </trim>
	  <trim prefix="t.remark = case" suffix="end,">
		 <foreach collection="list" item="item" index="index">
			 <if test="item.remark != null">
			  	when t.id = #{item.id} then #{item.remark}
			 </if>
		 </foreach>
	  </trim>
	 </trim>
	where
	<foreach collection="list" item="item" index="index" open="t.id in (" separator="," close=")">
		#{item.id}
	</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