商城SKU后台设计

ゝ一世哀愁。 2022-04-18 01:38 178阅读 0赞

一、数据库表设计

1、 规格属性表：（通过p\_spec\_id和spec\_id维护规格和属性的关系）

DROP TABLE IF EXISTS `t_spec`;
    CREATE TABLE `t_spec` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `p_spec_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '父id',
      `spec_id` varchar(255) DEFAULT NULL,
      `spec_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '名称',
      `is_parent` int(11) DEFAULT '0' COMMENT '0属性 1规格',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

测试数据：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70][]

2、商品sku组合中间表

DROP TABLE IF EXISTS `t_sku`;
    CREATE TABLE `t_sku` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `p_sku_id` varchar(255) DEFAULT NULL,
      `sku_id` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

测试数据

![20181106172854912.png][]

3、库存规格对照表

DROP TABLE IF EXISTS `t_stock`;
    CREATE TABLE `t_stock` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `sku_id` varchar(255) DEFAULT NULL,
      `stock_num` int(11) DEFAULT NULL,
      `goods_id` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

测试数据

![20181106173104330.png][]

4、商品表

![20181106173317929.png][]

二、代码实现

1、mapper接口

/**
         * @param  goodsId  商品id
         * @return 获取该商品所有的sku集合
         */
        List<SkuPO>  findSkuListByGoodsId(String  goodsId);
    
        /**
         * @param   skuIDs 商品sku组合id
         * @return   取该商品所有规格
         */
        List<SpecPO>  findSpecListBySkuIds(List<String> skuIDs);
    
        /**
         * @param  pSpecId
         * @return 查询商品所有属性
         */
        List<SpecPO> findAttrListByPspecIdAndSkuIds(@Param("pSpecId") String  pSpecId, @Param("skuIds") List<String> skuIds);

2、mapper.xml配置

<select id="findSkuListByGoodsId" parameterType="java.lang.String" resultMap="findSkuRes">
             SELECT  sku.sku_id FROM  t_sku  AS  sku  WHERE sku.id
             <if test="#{value}!=null">
                 IN(SELECT  stock.sku_id FROM  t_stock AS stock WHERE stock.goods_id=#{value});
             </if>
        </select>
    
    
        <select id="findSpecListBySkuIds" parameterType="java.util.List" resultMap="findSpecRes">
            SELECT * FROM t_spec WHERE id IN(SELECT p_spec_id FROM t_spec  WHERE id
            IN(<foreach item="item" index="index" collection="list" separator=",">#{item}</foreach>)
            GROUP BY p_spec_id);
        </select>
    
    
        <select id="findAttrListByPspecIdAndSkuIds" resultMap="findSpecRes">
            SELECT * FROM t_spec WHERE
                <if test="pSpecId!=null">
                    p_spec_id=#{pSpecId}
                </if>
                AND id IN
                <if test="skuIds!=null and skuIds.size>0">
                    <foreach item="item" index="index" collection="skuIds" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
        </select>

3、service服务

public interface IGoodsService {
    
        /**
         *
         * @param goodsId
         * @return 某一商品的所有sku组合
         * @throws Exception
         */
        List<String>   findSkuIdListByGoodsId(String  goodsId) throws Exception;
    
        /**
         *
         * @return 某商品的所有属性集合
         * @throws Exception
         */
        List<SpecPO>   findSpecListBySkuIds(List<String> skuList) throws Exception;
    
        /**
         *
         * @param goodsId
         * @return 获取商品规格属性组合
         */
        Map<String,List<SpecPO>>  findAttrListByGoodsId(String  goodsId);
    }

@Service
    @Transactional(rollbackFor = Exception.class)
    public class GoodsServiceImpl  implements IGoodsService {
    
        @Autowired
        private GoodsMapper goodsMapper;
    
    
        @Override
        public List<String> findSkuIdListByGoodsId(String goodsId) throws Exception {
            List<SkuPO> attrList=goodsMapper.findSkuListByGoodsId(goodsId);
            List<String>  skuIds=new ArrayList<>();
            for(SkuPO t:attrList){
                String[]  specAttrId= t.getSkuId().split(",");
                for (String tt:specAttrId){
                    String attrId=tt.split(";")[1];
                    if (!skuIds.contains(attrId)){
                        skuIds.add(attrId);
                    }
                }
            }
            return skuIds;
        }
    
    
        @Override
        public List<SpecPO> findSpecListBySkuIds(List<String> skuList) throws Exception {
            return goodsMapper.findSpecListBySkuIds(skuList);
        }
    
    
        @Override
        public Map<String, List<SpecPO>> findAttrListByGoodsId(String goodsId) {
            Map<String,List<SpecPO>> map=null;
            try {
                List<String>  skuList=findSkuIdListByGoodsId(goodsId);
    
                List<SpecPO>  specPOList=findSpecListBySkuIds(skuList);
    
                map=new HashMap<>(specPOList.size());
                for(SpecPO  t:specPOList){
                    List<SpecPO> tempList=goodsMapper.findAttrListByPspecIdAndSkuIds(t.getSpecId(),skuList);
                    map.put(t.getSpecName(),tempList);
                }
    
            } catch (Exception e) {
                e.printStackTrace();
            }
            return map;
        }
    
    }

4、api层

@Controller
    @RequestMapping("/goods")
    public class GoodsController 
        @Autowired
        private IGoodsService  goodsService;
        @RequestMapping("/getAttrList")
        @ResponseBody
        public Map<String,List<SpecPO>>  getGoodsSpecList(String  goodsId){
            return goodsService.findAttrListByGoodsId(goodsId);
        }
    }

![201811061740182.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70]: /images/20220418/10b41c4ea12142288ba4e8492e8311e8.png
[20181106172854912.png]: /images/20220418/d150fb0eef2a4fddbc9edfc8dc5fab1f.png
[20181106173104330.png]: /images/20220418/dec68168368344beb9f866be9f4d5ab8.png
[20181106173317929.png]: /images/20220418/93fa13155ba44841bb7759456e578e5a.png
[201811061740182.png]: /images/20220418/1067b5dddebb4becb9f91b400dc013cb.png