【MySQL】xml中MyBatis动态sql之批量修改、批量新增（使用foreach标签一条sql解决）

批量修改：

1.Mapper.java

@Mapper
public interface DeviceMapper {

void updateDevNotList(List<Map<String, Object>> list);

}

2.Mapper.xml

说明：根据list中每条数据的id修改对应的install_address、product_name   WHEN是条件，THEN是赋值

<update id="updateDevNotList" >
        UPDATE
            dev_device_instance
        SET
            install_address =
                <foreach collection="list" item="item" index="index" separator=" " open="case id" close="end">
                    WHEN #{item.id} THEN #{item.installAddress}
                </foreach>

            ,product_name =
                <foreach collection="list" item="item" index="index" separator=" " open="case id" close="end">
                    WHEN #{item.id} THEN #{item.productName}
                </foreach>

            WHERE id IN
                <foreach collection="list" item="item" open="(" separator="," close=")">
                    #{item.id,jdbcType=INTEGER}
                </foreach>
    </update>

 批量新增

1.Mapper.java

@Mapper
public interface DeviceMapper {

void importDevice(List<WaterMeterImport> list);

}

2.Mapper.xml

说明：前提是字段都有传过来，如果是新增哪个传哪个，就要加<if>标签判断

<insert id="importDevice">
        INSERT INTO dev_device_instance( id, product_id, product_name, name, creator_name, create_time)
        VALUES
        <foreach collection="list" separator="," item="item">
            (#{item.deviceId}, #{item.productId}, #{item.productName}, #{item.deviceName}, #{item.createBy}, #{item.createTime})
        </foreach>
 </insert>

即使人生大写着失败，也不妨碍我继续向前！