1.SPU和SKU数据结构
(1)SPU
SPU表：
CREATE TABLE `tb_spu` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'spu id', `title` varchar(255) NOT NULL DEFAULT '' COMMENT '标题', `sub_title` varchar(255) DEFAULT '' COMMENT '子标题', `cid1` bigint(20) NOT NULL COMMENT '1级类目id', `cid2` bigint(20) NOT NULL COMMENT '2级类目id', `cid3` bigint(20) NOT NULL COMMENT '3级类目id', `brand_id` bigint(20) NOT NULL COMMENT '商品所属品牌id', `saleable` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否上架，0下架，1上架', `valid` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否有效，0已删除，1有效', `create_time` datetime DEFAULT NULL COMMENT '添加时间', `last_update_time` datetime DEFAULT NULL COMMENT '最后修改时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=208 DEFAULT CHARSET=utf8 COMMENT='spu表，该表描述的是一个抽象的商品，比如 iphone8';
CREATE TABLE `tb_spu_detail` ( `spu_id` bigint(20) NOT NULL, `description` text COMMENT '商品描述信息', `generic_spec` varchar(10000) NOT NULL DEFAULT '' COMMENT '通用规格参数数据', `special_spec` varchar(1000) NOT NULL COMMENT '特有规格参数及可选值信息，json格式', `packing_list` varchar(3000) DEFAULT '' COMMENT '包装清单', `after_service` varchar(3000) DEFAULT '' COMMENT '售后服务', PRIMARY KEY (`spu_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
这张表中的数据都比较大，为了不影响主表的查询效率我们拆分出这张表。
需要注意的是这两个字段：generic_spec和special_spec。
-
key：对应的规格参数的
spec_param的id -
(2)SKU
SKU表
CREATE TABLE `tb_sku` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'sku id', `spu_id` bigint(20) NOT NULL COMMENT 'spu id', `title` varchar(255) NOT NULL COMMENT '商品标题', `images` varchar(1000) DEFAULT '' COMMENT '商品的图片，多个图片以‘,’分割', `price` bigint(15) NOT NULL DEFAULT '0' COMMENT '销售价格，单位为分', `indexes` varchar(100) COMMENT '特有规格属性在spu属性模板中的对应下标组合', `own_spec` varchar(1000) COMMENT 'sku的特有规格参数，json格式', `enable` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否有效，0无效，1有效', `create_time` datetime NOT NULL COMMENT '添加时间', `last_update_time` datetime NOT NULL COMMENT '最后修改时间', PRIMARY KEY (`id`), KEY `key_spu_id` (`spu_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='sku表,该表表示具体的商品实体,如黑色的64GB的iphone 8';
还有一张表，代表库存：
CREATE TABLE `tb_stock` ( `sku_id` bigint(20) NOT NULL COMMENT '库存对应的商品sku id', `seckill_stock` int(9) DEFAULT '0' COMMENT '可秒杀库存', `seckill_total` int(9) DEFAULT '0' COMMENT '秒杀总数量', `stock` int(9) NOT NULL COMMENT '库存数量', PRIMARY KEY (`sku_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='库存表，代表库存，秒杀库存等信息';
2.商品查询
(1)效果预览
接下来，我们实现商品管理的页面，先看下我们要实现的效果：
(2)页面请求
先看整体页面结构（Goods.vue）：
并且在Vue实例挂载后就会发起查询(mounted调用getDataFromServer方法初始化数据)：
我们刷新页面，可以看到浏览器发起已经发起了查询商品数据的请求：
(3)后台代码(代码顺序代表代码逻辑的先后关系)
<1>在leyou-item-interface工程中添加实体类：
(1)Spu.java(数据库表tb_spu对应实体类)
package lucky.leyou.item.domain; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table; import java.util.Date; @Table(name = "tb_spu") public class Spu { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; private Long brandId; private Long cid1;// 1级类目 private Long cid2;// 2级类目 private Long cid3;// 3级类目 private String title;// 标题 private String subTitle;// 子标题 private Boolean saleable;// 是否上架 private Boolean valid;// 是否有效，逻辑删除用 private Date createTime;// 创建时间 private Date lastUpdateTime;// 最后修改时间 public Long getId() { return id; } public void setId(Long id) { this.id = id; } public Long getBrandId() { return brandId; } public void setBrandId(Long brandId) { this.brandId = brandId; } public Long getCid1() { return cid1; } public void setCid1(Long cid1) { this.cid1 = cid1; } public Long getCid2() { return cid2; } public void setCid2(Long cid2) { this.cid2 = cid2; } public Long getCid3() { return cid3; } public void setCid3(Long cid3) { this.cid3 = cid3; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String getSubTitle() { return subTitle; } public void setSubTitle(String subTitle) { this.subTitle = subTitle; } public Boolean getSaleable() { return saleable; } public void setSaleable(Boolean saleable) { this.saleable = saleable; } public Boolean getValid() { return valid; } public void setValid(Boolean valid) { this.valid = valid; } public Date getCreateTime() { return createTime; } public void setCreateTime(Date createTime) { this.createTime = createTime; } public Date getLastUpdateTime() { return lastUpdateTime; } public void setLastUpdateTime(Date lastUpdateTime) { this.lastUpdateTime = lastUpdateTime; } }
(2)SpuDetail.java
package lucky.leyou.item.domain; import javax.persistence.Id; import javax.persistence.Table; @Table(name="tb_spu_detail") public class SpuDetail { @Id private Long spuId;// 对应的SPU的id private String description;// 商品描述 private String specialSpec;// 商品特殊规格的名称及可选值模板 private String genericSpec;// 商品的全局规格属性 private String packingList;// 包装清单 private String afterService;// 售后服务 public Long getSpuId() { return spuId; } public void setSpuId(Long spuId) { this.spuId = spuId; } public String getDescription() { return description; } public void setDescription(String description) { this.description = description; } public String getSpecialSpec() { return specialSpec; } public void setSpecialSpec(String specialSpec) { this.specialSpec = specialSpec; } public String getGenericSpec() { return genericSpec; } public void setGenericSpec(String genericSpec) { this.genericSpec = genericSpec; } public String getPackingList() { return packingList; } public void setPackingList(String packingList) { this.packingList = packingList; } public String getAfterService() { return afterService; } public void setAfterService(String afterService) { this.afterService = afterService; } }
<2>mapper
(1)SpuMapper
package lucky.leyou.item.mapper; import lucky.leyou.item.domain.Spu; import tk.mybatis.mapper.common.Mapper; public interface SpuMapper extends Mapper<Spu> { }
(2)SpuDetailMapper
package lucky.leyou.item.mapper; import lucky.leyou.item.domain.SpuDetail; import tk.mybatis.mapper.common.Mapper; public interface SpuDetailMapper extends Mapper<SpuDetail> { }
<3>service
所有商品相关的业务（包括SPU和SKU）放到一个业务下：GoodsService。
package lucky.leyou.item.service.impl; import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; import lucky.leyou.common.domain.PageResult; import lucky.leyou.item.bo.SpuBo; import lucky.leyou.item.domain.Spu; import lucky.leyou.item.mapper.BrandMapper; import lucky.leyou.item.mapper.SpuDetailMapper; import lucky.leyou.item.mapper.SpuMapper; import lucky.leyou.item.service.ICategoryService; import lucky.leyou.item.service.IGoodsService; import org.apache.commons.lang.StringUtils; import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import tk.mybatis.mapper.entity.Example; import java.util.ArrayList; import java.util.Arrays; import java.util.List; @Service public class GoodsServiceImpl implements IGoodsService { @Autowired private SpuMapper spuMapper; @Autowired private SpuDetailMapper spuDetailMapper; @Autowired private BrandMapper brandMapper; @Autowired private ICategoryService categoryService; @Override public PageResult<SpuBo> querySpuBoByPage(String key, Boolean saleable, Integer page, Integer rows) { Example example = new Example(Spu.class); Example.Criteria criteria = example.createCriteria(); //查询条件 // 01 添加文本框中用户输入的搜索条件 if (StringUtils.isNotBlank(key)) { //注意：criteria.andLike该方法的参数1是数据库表的字段名，参数2为模糊查询的表达式 criteria.andLike("title", "%" + key + "%"); } //02 添加上下架的过滤条件 if (saleable != null) { criteria.andEqualTo("saleable", saleable); } // 03 分页条件 PageHelper.startPage(page, rows); // 04 执行查询,获取spu集合 List<Spu> spus = this.spuMapper.selectByExample(example); PageInfo<Spu> pageInfo = new PageInfo<>(spus); List<SpuBo> spuBos = new ArrayList<>(); //05 spu集合转化为spubo集合 //java8 foreach循环 spus.forEach(spu->{ SpuBo spuBo = new SpuBo(); // copy共同属性的值到新的对象 BeanUtils.copyProperties(spu, spuBo); // 查询分类名称 List<String> names = this.categoryService.queryNamesByIds(Arrays.asList(spu.getCid1(), spu.getCid2(), spu.getCid3())); spuBo.setCname(StringUtils.join(names, "/")); //StringUtils.join将集合元素用指定的分隔符连接成字符串 // 查询品牌的名称 spuBo.setBname(this.brandMapper.selectByPrimaryKey(spu.getBrandId()).getName()); spuBos.add(spuBo); }); //06 利用PageResult的构造方法返回PageResult对象 return new PageResult<>(pageInfo.getTotal(), spuBos); } }
mapper的selectByIdList方法是来自于通用mapper。不过需要我们在mapper上继承一个通用mapper接口SelectByIdListMapper：
package lucky.leyou.item.mapper; import lucky.leyou.item.domain.Category; import tk.mybatis.mapper.additional.idlist.SelectByIdListMapper; import tk.mybatis.mapper.common.Mapper; public interface CategoryMapper extends Mapper<Category>,SelectByIdListMapper<Category,Long> { }
<4>controller
package lucky.leyou.item.controller; import lucky.leyou.common.domain.PageResult; import lucky.leyou.item.bo.SpuBo; import lucky.leyou.item.service.IGoodsService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.ResponseEntity; import org.springframework.stereotype.Controller; import org.springframework.util.CollectionUtils; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestParam; @Controller public class GoodsController { @Autowired private IGoodsService goodsService; /** * 根据条件分页查询商品信息 * 注意：利用注解@RequestParam接收url中？后携带的参数 * @param key * @param saleable 是否可售 * @param page 当前页数 * @param rows 行数 * @return */ @GetMapping(path = "/spu/page") public ResponseEntity<PageResult<SpuBo>> querySpuBoByPage( @RequestParam(value = "key", required = false)String key, @RequestParam(value = "saleable", required = false)Boolean saleable, @RequestParam(value = "page", defaultValue = "1")Integer page, @RequestParam(value = "rows", defaultValue = "5")Integer rows ){ PageResult<SpuBo> pageResult = this.goodsService.querySpuBoByPage(key, saleable, page, rows); if(CollectionUtils.isEmpty(pageResult.getItems())){ return ResponseEntity.notFound().build(); } return ResponseEntity.ok(pageResult); } }
<5>效果图
