mybatis批量修改

雨点打透心脏的1/2处 2022-11-24 12:57 266阅读 0赞

单个修改会消耗mysql性能,使用批量修改功能

<resultMap type="FanGoods" id="FanGoodsResult">
            <result property="id" column="id"    />
            <result property="goodsName" column="goodsName"    />
            <result property="companyId" column="companyId"    />
            <result property="itemNo" column="itemNo"    />
            <result property="image" column="image"    />
            <result property="num" column="num"    />
            <result property="price" column="price"    />
            <result property="isRecycle" column="isRecycle"    />
            <result property="supplierId" column="supplierId"    />
            <result property="supplierName" column="supplierName"    />
            <result property="createTime" column="createTime"    />
            <result property="createId" column="createId"    />
            <result property="updateTime" column="updateTime"    />
            <result property="updateId" column="updateId"    />
        </resultMap>
    
    <update id="updateFanGoodsList" parameterType="FanGoods">
            <foreach collection="list" item="item" index="index" separator=";">
                update fan_goods
                <set>
                    <if test="item.goodsName != null and item.goodsName != ''">goodsName = #{item.goodsName}, </if>
                    <if test="item.companyId != null and item.companyId != ''">companyId = #{item.companyId}, </if>
                    <if test="item.itemNo != null and item.itemNo != ''">itemNo = #{item.itemNo}, </if>
                    <if test="item.image != null and item.image != ''">image = #{item.image}, </if>
                    <if test="item.num != null and item.num != ''">num = #{item.num}, </if>
                    <if test="item.price != null and item.price != ''">price = #{item.price}, </if>
                    <if test="item.isRecycle != null and item.isRecycle != ''">isRecycle = #{item.isRecycle}, </if>
                    <if test="item.supplierId != null and item.supplierId != ''">supplierId = #{item.supplierId}, </if>
                    <if test="item.supplierName != null and item.supplierName != ''">supplierName = #{item.supplierName}, </if>
                    <if test="updateTime != null ">updateTime = #{updateTime}, </if>
                    <if test="item.updateId != null and item.updateId != ''">updateId = #{item.updateId}</if>
                </set>
                where id = #{item.id}
            </foreach>
        </update>

然后是service类

Map<String,Object> map = new HashMap<>();
    map.put("updateTime",new Date());
    map.put("list",list);
    //先修改仓库数据 仓库数据修改主要是数量和总价
    fanGoodsMapper.updateFanGoodsList(map);

执行之后可能会报错

Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update fan_goods
                 SET goodsName = '会场话筒', 
                    c' at line 15

原因是mybatis中的批量修改携带了分号;

需要配置mysql的url,添加allowMultiQueries=true