spu和sku 搜索服务
流程分析
- 搜索服务:从elasticsearch中搜索数据,默认情况下es中没有数据
- sku查询功能:从数据库查询sku所有相关信息
- 数据同步:将数据库中的数据,同步到es中。()
- 数据库查询搜索慢、es查询速度快
- 同步时机:1. 定时器(理论)、2. 测试程序(实施)
表结构分析
- 商品相关表总览
- 表间关系
查询所有SKU
- 接口
GET http://localhost:10010/web-service/sku/esData
后端实现:JavaBean
- 数据库相关JavaBean
- Spu类似分类
- Sku 商品详情
- SkuComment sku的评论
- ES相关JavaBean
- ESData 准备存放到es中的数据,是以上三种数据的组合数据。
Spu
package com.czxy.changgou4.pojo;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.util.Date;
@TableName("tb_spu")
@Data
public class Spu {
@TableId(type = IdType.AUTO)
private Integer id;
//spu名字
@TableField(value="spu_name")
private String spuName;
//spu副名称
@TableField(value="spu_subname")
private String spuSubname;
//商品logo
@TableField(value="logo")
private String logo;
//分类1Id
@TableField(value="cat1_id")
private Integer cat1Id;
//分类2ID
@TableField(value="cat2_id")
private Integer cat2Id;
//分类3Id
@TableField(value="cat3_id")
private Integer cat3Id;
@TableField(value="brand_id")
private Integer brandId;
@TableField(exist = false)
private Brand brand;
//审核时间
@TableField(value="check_time")
private String checkTime;
//审核状态 审核状态,0:未审核,1:已通过,2:未通过
@TableField(value="check_status")
private String checkStatus;
//价格
@TableField(value="price")
private String price;
//是否上架
@TableField(value="is_on_sale")
private Integer isOnSale;
//上架时间
@TableField(value="on_sale_time")
private Date onSaleTime;
//删除时间
@TableField(value="deleted_at")
private String deletedAt;
@TableField(value="weight")
private String weight;
//商品描述
@TableField(value="description")
private String description;
//规格与包装
@TableField(value="packages")
private String packages;
//售后保障
@TableField(value="aftersale")
private String aftersale;
//规格列表,json串
@TableField(value="spec_list")
private String specList;
@TableField(value="created_at")
private String createdAt;
@TableField(value="updated_at")
private String updatedAt;
}
Sku
package com.czxy.changgou4.pojo;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@TableName("tb_sku")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Sku {
@TableId(type = IdType.AUTO)
private Integer id;
//库存量
@TableField(value="stock")
private Integer stock;
@TableField(value="spu_id")
private Integer spuId;
@TableField(exist = false)
private Spu spu;
//sku名字
@TableField(value="sku_name")
private String skuName;
@TableField(value="images")
private String images;
@TableField(value="price")
private Double price;
//1:1|2:6|6:22
@TableField(value="spec_info_id_list")
private String specInfoIdList;
//规格列表码,格式:{"机身颜色":"白色","内存":"3GB","机身存储":"16GB"}
@TableField(value="spec_info_id_txt")
private String specInfoIdTxt;
SkuComment
package com.czxy.changgou4.pojo;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
@TableName("tb_sku_comment")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class SkuComment {
@TableId(type = IdType.AUTO)
private Integer id;
@TableField(value="created_at")
private Date createdAt;
@TableField(value="updated_at")
private Date updatedAt;
@TableField(value="user_id")
private Integer userId;
@TableField(exist = false)
private User user;
@TableField(value="spu_id")
private Integer spuId;
@TableField(exist = false)
private Spu spu;
@TableField(value="sku_id")
private Integer skuId;
@TableField(exist = false)
private Sku sku;
@TableField(value="ratio")
private String ratio;
@TableField(value="spec_list")
private String specList;
@TableField(value="content")
private String content;
@TableField(value="star")
private Integer star;
@TableField(value="isshow")
private String isShow;
@TableField(value="sn")
private String sn;
}
SkuPhoto
package com.czxy.changgou4.pojo;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonProperty;
import lombok.Data;
@TableName("tb_sku_photo")
@Data
public class SkuPhoto {
@TableId(type = IdType.AUTO)
private Integer id;
//外键
@TableField(value="sku_id")
@JsonProperty("sku_id")
private Integer skuId;
@TableField(exist = false)
private Sku sku;
@TableField(value="url")
private String url;
}
ESData
package com.czxy.changgou4.vo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
import java.util.Map;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ESData {
private Integer id; // skuId
private String logo;//图片地址
private String skuName;//sku名字
private String all; // 所有需要被搜索的信息,包含标题,分类,甚至品牌
private Date onSaleTime;//上架时间
//品牌编号
private Integer brandId;
//分类id
private Integer catId;
//规格列表
private Map<String, Object> specs;// 可搜索的规格参数,key是参数名,值是参数值
private Double price;// 价格
private String spuName;
private Integer stock;
private String description;
private String packages;//规格与包装
private String aftersale;//售后保障
private String midlogo;
//评价数
private Integer commentCount;
// 销量
private Integer sellerCount;
}
后端实现:搭建环境
- 步骤一:修改父项目的pom.xml,锁定fastjson的依赖
<fastjson.version>1.2.9</fastjson.version>
<!--fastjson-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>${fastjson.version}</version>
</dependency>
- 步骤二:修改web服务,添加fastjson依赖
<!--json转换-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
</dependency>
- 步骤三:修改网关,确定 ESData路径
后端实现:查询
- SpuMapper:通过SpuId查询详情,含品牌信息
package com.czxy.changgou4.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.czxy.changgou4.pojo.Spu;
import org.apache.ibatis.annotations.*;
@Mapper
public interface SpuMapper extends BaseMapper<Spu> {
@Select("select * from tb_spu where id = #{spuId}")
@Results({
@Result(property = "spuName" , column = "spu_name"),
@Result(property = "spuSubname" , column = "spu_subname"),
@Result(property = "cat1Id" , column = "cat1_id"),
@Result(property = "cat2Id" , column = "cat2_id"),
@Result(property = "cat3Id" , column = "cat3_id"),
@Result(property = "brandId" , column = "brand_id"),
@Result(property = "checkTime" , column = "check_time"),
@Result(property = "checkStatus" , column = "check_status"),
@Result(property = "isOnSale" , column = "is_on_sale"),
@Result(property = "onSaleTime" , column = "on_sale_time"),
@Result(property = "deletedAt" , column = "deleted_at"),
@Result(property = "specList" , column = "spec_list"),
@Result(property = "onSaleTime" , column = "on_sale_time"),
@Result(property = "createdAt" , column = "created_at"),
@Result(property = "updatedAt" , column = "updated_at"),
@Result(property="brand", column="brand_id",
one=@One(
select="com.czxy.changgou4.mapper.BrandMapper.selectById"
))
})
public Spu findSpuById(@Param("spuId") Integer spuId);
}
- SkuCommentMapper:查询评论数
package com.czxy.changgou4.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.czxy.changgou4.pojo.SkuComment;
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface SkuCommentMapper extends BaseMapper<SkuComment> {
/**
* 查询评论数
* @param spu_id
* @return
*/
@Select("select count(*) from tb_sku_comment where spu_id = #{spu_id}")
public Integer findNumBySpuId(@Param("spu_id") Integer spu_id);
}
- SkuMapper:查询所有sku,含对应的spu信息
package com.czxy.changgou4.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.czxy.changgou4.pojo.Sku;
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface SkuMapper extends BaseMapper<Sku> {
@Select("select * from tb_sku")
@Results(id="skuResult" , value={
@Result(id=true,column="id",property="id"),
@Result(column="stock",property="stock"),
@Result(column="spu_id",property="spuId"),
@Result(column="sku_name",property="skuName"),
@Result(column="spec_info_id_list",property="specInfoIdList"),
@Result(column="spec_info_id_txt",property="specInfoIdTxt"),
@Result(column="spu_id",property="spu",
one=@One(
select="com.czxy.changgou4.mapper.SpuMapper.findSpuById"
))
})
public List<Sku> findAllSkus();
}
- SkuPhotoMapper
package com.czxy.changgou4.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.czxy.changgou4.pojo.SkuPhoto;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface SkuPhotoMapper extends BaseMapper<SkuPhoto> {
}
package com.czxy.changgou4.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.czxy.changgou4.pojo.Sku;
import com.czxy.changgou4.vo.ESData;
import java.util.List;
public interface SkuService extends IService<Sku> {
/**
*
* @return
*/
public List<ESData> findESData();
}
package com.czxy.changgou4.service.impl;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.czxy.changgou4.mapper.SkuCommentMapper;
import com.czxy.changgou4.mapper.SkuMapper;
import com.czxy.changgou4.pojo.Sku;
import com.czxy.changgou4.service.SkuService;
import com.czxy.changgou4.vo.ESData;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@Service
@Transactional
public class SkuServiceImpl extends ServiceImpl<SkuMapper, Sku> implements SkuService {
@Resource
private SkuCommentMapper skuCommentMapper;
@Override
public List<ESData> findESData() {
//1 查询所有详情sku
List<Sku> skulist = baseMapper.findAllSkus();
//2 将SKU 转换成 ESData
List<ESData> esDataList = new ArrayList<>();
for (Sku sku:skulist){
ESData esData = new ESData();
// id
esData.setId(sku.getId());
// 图片地址
esData.setLogo(sku.getSpu().getLogo());
// 商品名称
esData.setSkuName(sku.getSkuName());
// all “华为xx {"机身颜色":"白色","内存":"3GB","机身存储":"16GB"} 荣耀 ”
esData.setAll(sku.getSkuName()+" " + sku.getSpecInfoIdTxt() + " " +sku.getSpu().getBrand().getBrandName());
// on_sale_time
esData.setOnSaleTime(sku.getSpu().getOnSaleTime());
// brand_id
esData.setBrandId(sku.getSpu().getBrandId());
// cat_id
esData.setCatId(sku.getSpu().getCat3Id());
// Map<String, Object> specs;// 可搜索的规格参数,key是参数名,值是参数值
Map<String,Object> specs = JSON.parseObject(sku.getSpecInfoIdTxt(), Map.class);
// Map newSpecs = new HashMap();
// for(String key : specs.keySet()){
// newSpecs.put("spec" + key , specs.get(key));
// }
esData.setSpecs(specs);
// price 价格
esData.setPrice(sku.getPrice());
// spu_name
esData.setSpuName(sku.getSpu().getSpuName());
// stock 库存
esData.setStock(sku.getStock());
// description
esData.setDescription(sku.getSpu().getDescription());
// packages;//规格与包装
esData.setPackages(sku.getSpu().getPackages());
// aftersale;//售后保障
esData.setAftersale(sku.getSpu().getAftersale());
// midlogo;
esData.setMidlogo(sku.getSpu().getLogo());
// comment_count; 评价数
Integer comment_count = skuCommentMapper.findNumBySpuId(sku.getSpu().getId());
esData.setCommentCount(comment_count);
//销售量
esData.setSellerCount(10);
esDataList.add(esData);
}
return esDataList;
}
}
package com.czxy.changgou4.controller;
import com.czxy.changgou4.service.SkuService;
import com.czxy.changgou4.vo.BaseResult;
import com.czxy.changgou4.vo.ESData;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("/sku")
public class SkuController {
@Autowired
private SkuService skuService;
@GetMapping("/esData")
public BaseResult<List<ESData>> findESData(){
List<ESData> esData = skuService.findESData();
return BaseResult.ok("查询成功", esData);
}
}
这就是今日份所有的分享 如果感觉不错 希望大家给i个一键三连。
你的支持就是对我这个无名小博主最大的鼓励! !!