mybatis 批量更新

mybatis 代码

注意方案一需要配置 allowMultiQueries=true

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/ruoyi?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull

    <!--批量更新测试1-->
    <update id="bathUpdateTest" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            update pms_customer
            <set>
                name=#{item.name},
                open_bank=#{item.openBank}
            </set>
            where id = #{item.id}
        </foreach>
    </update>

    <!--批量更新测试2-->
    <update id="bathUpdateTest2" parameterType="java.util.List">
        update `pms_customer`
        <trim prefix="set" suffixOverrides=",">
            <trim prefix=" `name` = case " suffix=" end, ">
                <foreach collection="list" item="item">
                    <if test="item.name != null">
                        when `id` = #{item.id} then #{item.name}
                    </if>
                </foreach>
            </trim>
            <trim prefix=" `open_bank` = case " suffix=" end, ">
                <foreach collection="list" item="item">
                    <if test="item.openBank != null">
                        when `id` = #{item.id} then #{item.openBank}
                    </if>
                </foreach>
            </trim>
        </trim>
        where
        `id` in
        <foreach collection="list" item="item" open="(" close=")" separator=",">
            #{item.id}
        </foreach>
    </update>

单元测试 代码

/**
     * TODO 批量更新（测试）
     */
    @Test
    public void bathUpdate() {
        List<PmsCustomerUpdate> list = new ArrayList<>();
        PmsCustomerUpdate vo1 = new PmsCustomerUpdate();
        vo1.setId(827420656390905856L);
        vo1.setName("测试客户demo3");
        vo1.setOpenBank("建设银行demo3");

        PmsCustomerUpdate vo2 = new PmsCustomerUpdate();
        vo2.setId(839689917666697216L);
        vo2.setName("测试一下同步demo3");
        vo2.setOpenBank("农业银行分行demo3");

        list.add(vo1);
        list.add(vo2);
        //方案一
        pmsCustomerDao.bathUpdateTest(list);
        //方案二
//        pmsCustomerDao.bathUpdateTest2(list);
    }