商城SKU后台设计

一、数据库表设计

    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;

  测试数据：

  

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;

  测试数据

  

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;

测试数据

 

4、商品表

   

二、代码实现

   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);
    }
}