mybatis使用case when按照条件进行更新

男娘i 2023-09-27 17:54 122阅读 0赞

**目录**

1.示例代码

2.说明

--------------------

### 1.示例代码 ###

<update id="updateNrPurchaseOrderDetailByGoodsBarcode">
        update nr_purchase_order_detail
        set stored_num =
        <foreach collection="barcodeList" item="item" index="index"
                 separator=" " open="case goods_barcode" close="end">
            when #{item.barcode} then stored_num + #{item.receiveNum}
        </foreach>
        ,unstored_num =
        <foreach collection="barcodeList" item="item" index="index"
                 separator=" " open="case goods_barcode" close="end">
            when #{item.barcode} then actual_goods_num - stored_num
        </foreach>
        where goods_barcode in
        <foreach collection="barcodeList" item="item" index="index" separator="," open="(" close=")">
            #{item.barcode}
        </foreach>
        and parent_id = #{id}
    </update>

<update id="testCaseWhen" parameterType="java.util.Map">
        UPDATE ${user}
        <set>
            <trim prefix="id= CASE id" suffix="END,">
                <foreach collection="list" item="param">
                    <if test="param.id != null and id != ''">
                        WHEN #{param.id} THEN #{param.id}
                    </if>
                </foreach>
            </trim>
            <trim prefix="username = CASE id" suffix="END,">
                <foreach collection="list" item="param">
                    <if test="param.username != null and param.username != ''">
                        WHEN #{param.} THEN #{param.username}
                    </if>
                </foreach>
            </trim>
            <trim prefix="password = CASE id" suffix="END,">
                <foreach collection="list" item="param">
                    <if test="param.password != null and param.password != ''">
                        WHEN #{param.id} THEN #{param.password}
                    </if>
                </foreach>
            </trim>
        </set>
        <where>
            id in
            <foreach collection="list" item="param" separator="," open="(" close=")">
                #{param.id}
            </foreach>
        </where>
    </update>

### 2.说明 ###

上述第一个示例中，按照条码进行批量更新，将所有的条码对应的入库数量和剩余数量进行更新。

第二个示例中，又使用了set标签和trim标签，感觉更为复杂。