联合主键进行批量删除

最新推荐文章于 2023-01-08 20:40:53 发布

原创最新推荐文章于 2023-01-08 20:40:53 发布 · 2.3k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#java #spring boot

场景：菜单表是菜单id+菜单功能+菜单地址一起的联合主键，需求是进行批量删除

之前有写过这样的需求，写完了就没在意，最近发现对于部分开发者来说，这个问题也造就了一时的困扰，仅此记录下（在数据库执行的sql样例）：

DELETE
FROM uc_user
WHERE (USER_ID = 'a4d37ad02f9a4d9a82baef24af15caf2' and
       PASSWORD = 'a2cRdfnvLzv8NQQ5zQIFqbXRpTMq4yzPX097SLEJIJ6MKkr8XWFB03DVknm9it8p')
   or (USER_ID = 'c6a4aa1ed0c4409783e6795bbeb3c749' and
       PASSWORD = '1ooho5E99DjXnCFtRzQUDYiD+m5v9OON+mB17JRPa/2MKkr8XWFB03DVknm9it8p')

Controller：

    /**
     * 删除商铺商品
     */
    @Log("删除商铺商品")
    @RequestMapping(value = "/delete")
    public Result delete(HttpServletRequest request) {
        JSONObject jsonObject = (JSONObject) request.getAttribute("sign");
        String shopId = (String) jsonObject.get("shopId");
        jsonObject.remove("shopId");
        List<String> idList = JSONObject.parseArray(jsonObject.getJSONArray("goodsInfoId").toString(), String.class);
        List<ShopGoods> list = idList.stream().map(e -> new ShopGoods().setGoodsId(e).setShopId(shopId)).collect(Collectors.toList());
        int deleteCount = iShopGoodsService.deleteByIds(list);
        return deleteCount > 0 ? Result.success() : Result.fail();
    }

ServiceImpl :

    @Override
    public int deleteByIds(List<ShopGoods> idList) {

        return  shopGoodsMapper.deleteByIds(idList);
    }

Mapper:

    /**
     * 批量删除
     */
    int deleteByIds(List<ShopGoods>idList);

Mybatis(mapper.xml):

    <delete id="deleteByIds" parameterType="java.util.List">
        DELETE
        FROM
        shop_goods
        WHERE
        <foreach collection="list" item="ShopGoods" index="index" separator="or">
            ( shop_id=#{ShopGoods.shopId}
            AND goods_id=#{ShopGoods.goodsId} )
        </foreach>
    </delete>