Mybatis之update联表批量修改

根据id联表修改

废话少说，思路：前端传一个包装好的集合id，把集合遍历，取id，根据id set赋值

controller层

/**
     * 根据id批量修改
     *
     * @param
     * @return
     */
    @RequestMapping(value = "batchupdate", method = RequestMethod.POST)
    public Response batchUpdate(@RequestBody String jsonstr) {
        try {
            JSONObject jsonObj = JSONObject.parseObject(jsonstr);
            Integer[] ids = jsonObj.getObject("ids", Integer[].class);
            String isdelstatus = jsonObj.getString("isdelstatus");
            int i = dynamicService.batchUpdate(ids, isdelstatus);
            /*根据list大小判断与修改行数是否一致*/
            if (0 < i) {
                return new Response(Response.SUCCESS, Response.SUCCESS_MSG, i);
            }
            return new Response(Response.NO_PERMISSION, Response.FAIL_MSG, "修改" + i + "行");

        } catch (Exception e) {

            return new Response(Response.NO_PERMISSION, Response.FAIL_MSG, e.getMessage());
        }
    }

service

/**
     * 根据id批量修改
     * @param ids 
     * @return
     */
    int batchUpdate(Integer[] ids,String isdelstatus);

**serviceImpl **

 @Override
    public int batchUpdate(Integer[] ids, String isdelstatus) {
        DynamicUpdaDto dto = new DynamicUpdaDto();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
        int i = 0;
        for (Integer id : ids) {
            dto.setDynamicid(id);
            dto.setIsdelstatus(isdelstatus);
            dto.setUpdatetime(df.format(new Date()));
            i = dynamicDao.batchUpdate(dto);
        }
        return i;
    }

dao

  /**
     * 根据id批量联表修改
     * @param dto
     * @return
     */
    int batchUpdate(DynamicUpdaDto dto);

这里是xml

<update id="batchUpdate">
        UPDATE dynamic c LEFT JOIN
        dynamic_image p
        ON c.dynamic_id =p.dynamic_id
        <set>
            <if test="isdelstatus !=null and isdelstatus !=''">
                c.isdel_status= #{isdelstatus,jdbcType=VARCHAR},
                p.isdelstatus = #{isdelstatus,jdbcType=VARCHAR},
            </if>
            <if test="updatetime !=null and updatetime !=''">
                c.updatetime= #{updatetime,jdbcType=VARCHAR},
            </if>
            <if test="auditdetails != null and auditdetails != ''">
                c.auditdetails = #{auditdetails,jdbcType=VARCHAR},
                p.auditdetails = #{auditdetails,jdbcType=VARCHAR}
            </if>
        </set>
        WHERE
        c.dynamic_id = #{dynamicid}
    </update>

修改条件不同，修改参数相同情况的SQL和实现类

@Override
    public int updateReadCount(List<Dynamic> list) {
        Integer ids []=new Integer[list.size()];
        for (int i = 0; i < list.size(); i++) {
           ids[i]=list.get(i).getDynamicId();
        }
        return dynamicMapper.updateReadCount(ids);
    }

xml

<update id="updateReadCount">
        update dynamic set read_count=read_count+1 where dynamic_id in(
        <foreach collection="array" index="index" item="item" separator=",">
            #{item}
        </foreach>
        )
    </update>

批量插入

<insert id="insertBatch">
        insert into zcb_goods_sku(skuname, skuimageurl, goodsid, skuprice, skucxprice, skugroupprice, stock, tm,status)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.skuname},#{item.skuimageurl},#{item.goodsid},#{item.skuprice},#{item.skucxprice},#{item.skugroupprice},#{item.stock},SYSDATE(),#{item.status})
        </foreach>
</insert>