商品搜索（关键字模糊搜索、三级分类搜索商品）

一、数据表结构

   

create table `foodie-shop-dev`.items
(
    id            varchar(64) not null comment '商品主键id'
        primary key,
    item_name     varchar(32) not null comment '商品名称 商品名称',
    cat_id        int         not null comment '分类外键id 分类id',
    root_cat_id   int         not null comment '一级分类外键id',
    sell_counts   int         not null comment '累计销售 累计销售',
    on_off_status int         not null comment '上下架状态 上下架状态,1:上架 2:下架',
    content       text        not null comment '商品内容 商品内容',
    created_time  datetime    not null comment '创建时间',
    updated_time  datetime    not null comment '更新时间'
)
    comment '商品表 商品信息相关表：分类表，商品图片表，商品规格表，商品参数表' charset = utf8mb4;

create table `foodie-shop-dev`.items_spec
(
    id             varchar(64)   not null comment '商品规格id'
        primary key,
    item_id        varchar(64)   not null comment '商品外键id',
    name           varchar(32)   not null comment '规格名称',
    stock          int           not null comment '库存',
    discounts      decimal(4, 2) not null comment '折扣力度',
    price_discount int           not null comment '优惠价',
    price_normal   int           not null comment '原价',
    created_time   datetime      not null comment '创建时间',
    updated_time   datetime      not null comment '更新时间'
)
    comment '商品规格 每一件商品都有不同的规格，不同的规格又有不同的价格和优惠力度，规格表为此设计' charset = utf8mb4;

     

二、SQL查询语句

 1、根据关键字搜索商品列表

   

SELECT
    i.id AS itemId,
    i.item_name AS itemName,
    i.sell_counts AS sellCounts,
    ii.url AS imgUrl,
    tempSpec.priceDiscount AS price 
FROM
    items i
    LEFT JOIN items_img ii ON i.id = ii.item_id
    LEFT JOIN ( SELECT item_id, MIN( price_discount ) AS priceDiscount FROM items_spec GROUP BY item_id ) tempSpec ON i.id = tempSpec.item_id 
WHERE
    ii.is_main = 1
    i.item_name like '%%'
    

 2、三级分类商品列表

SELECT
    i.id AS itemId,
    i.item_name AS itemName,
    i.sell_counts AS sellCounts,
    ii.url AS imgUrl,
    tempSpec.priceDiscount AS price 
FROM
    items i
    LEFT JOIN items_img ii ON i.id = ii.item_id
    LEFT JOIN ( SELECT item_id, MIN( price_discount ) AS priceDiscount FROM items_spec GROUP BY item_id ) tempSpec ON i.id = tempSpec.item_id 
WHERE
    ii.is_main = 1 
    AND i.cat_id = 73
    

     

三、Mapper子模块实现

   1. xml文件定义

       根据关键字搜索商品列表

       

   三级分类商品列表

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.imooc.mapper.ItemsMapperCustom" >
  <select id="queryItemComments" parameterType="Map" resultType="com.imooc.pojo.vo.ItemCommentVO">
      SELECT
          ic.comment_level AS commentLevel,
          ic.content AS content,
          ic.sepc_name AS specName,
          ic.created_time AS createdTime,
          u.face AS userFace,
          u.nickname AS nickName
      FROM
          items_comments ic
          LEFT JOIN users u ON ic.user_id = u.id
      WHERE
          ic.item_id = #{paramsMap.itemId}
        <if test=" paramsMap.level !=null and paramsMap.level !='' ">
          AND ic.comment_level = #{paramsMap.level}
        </if>
  </select>

    <select id="searchItems" parameterType="Map" resultType="com.imooc.pojo.vo.SearchItemsVO">
            SELECT
                i.id AS itemId,
                i.item_name AS itemName,
                i.sell_counts AS sellCounts,
                ii.url AS imgUrl,
                tempSpec.priceDiscount AS price
            FROM
                items i
                LEFT JOIN items_img ii ON i.id = ii.item_id
                LEFT JOIN ( SELECT item_id, MIN( price_discount ) AS priceDiscount FROM items_spec GROUP BY item_id ) tempSpec
                ON i.id = tempSpec.item_id
            WHERE
                ii.is_main = 1
                <if test=" paramsMap.keywords !=null and paramsMap.keywords !='' ">
                    AND i.item_name like  '%${paramsMap.keywords}%'
                </if>
                    order by
               <choose>

                   <when test=" paramsMap.sort == &quot;c &quot;  ">
                      i.sell_counts desc
                   </when>
                   <when test=" paramsMap.sort ==  &quot;p &quot;  ">
                       tempSpec.priceDiscount desc
                   </when>
                   <otherwise>
                       i.item_name asc
                   </otherwise>
               </choose>


    </select>

    <select id="searchItemsByThirdCat" parameterType="Map" resultType="com.imooc.pojo.vo.SearchItemsVO">
        SELECT
        i.id AS itemId,
        i.item_name AS itemName,
        i.sell_counts AS sellCounts,
        ii.url AS imgUrl,
        tempSpec.priceDiscount AS price
        FROM
        items i
        LEFT JOIN items_img ii ON i.id = ii.item_id
        LEFT JOIN ( SELECT item_id, MIN( price_discount ) AS priceDiscount FROM items_spec GROUP BY item_id ) tempSpec
        ON i.id = tempSpec.item_id
        WHERE
        ii.is_main = 1

          AND i.cat_id = #{paramsMap.catId}
          order by
        <choose>

            <when test=" paramsMap.sort == &quot;c &quot;  ">
                i.sell_counts desc
            </when>
            <when test=" paramsMap.sort ==  &quot;p &quot;  ">
                 tempSpec.priceDiscount desc
            </when>
            <otherwise>
                i.item_name asc
            </otherwise>
        </choose>


    </select>
    <!-- k:默认，代表默认排序，根据name -->
    <!-- c:根据销量排序 -->
    <!-- p：根据价格排序 -->
</mapper>

   2. po 定义（前端展示使用的数据）

   

    

package com.imooc.pojo.vo;

import java.util.Date;

/**
 * 用于展示商品搜索结果VO
 */
public class SearchItemsVO {

    private String itemId;
    private String itemName;
    private int sellCounts;
    private String imgUrl;
    private int price;  //以分为单位
}

   3  接口定义 

 

  

package com.imooc.mapper;

import com.imooc.pojo.vo.ItemCommentVO;
import com.imooc.pojo.vo.SearchItemsVO;
import org.apache.ibatis.annotations.Param;


import java.util.List;
import java.util.Map;

public interface ItemsMapperCustom  {

    public List<ItemCommentVO> queryItemComments (@Param("paramsMap") Map<String, Object> map);

    public List<SearchItemsVO> searchItems(@Param("paramsMap") Map<String, Object> map);

    public List<SearchItemsVO> searchItemsByThirdCat(@Param("paramsMap") Map<String, Object> map);


}

四、Service子模块实现

  1.接口定义

package com.imooc.service;


import com.imooc.pojo.Items;
import com.imooc.pojo.ItemsImg;
import com.imooc.pojo.ItemsParam;
import com.imooc.pojo.ItemsSpec;
import com.imooc.pojo.vo.CommentLevelCountsVO;
import com.imooc.pojo.vo.ItemCommentVO;
import com.imooc.pojo.vo.SearchItemsVO;
import com.imooc.utils.PagedGridResult;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface ItemService {

    /**
     * 根据商品ID查询详情
     * @param itemId
     * @return
     */
    public Items queryItemById(String itemId);

    /**
     * 根据商品ID查询图片列表
     * @param itemId
     * @return
     */
    public List<ItemsImg> queryItemImgList(String itemId);

    /**
     * 根据商品ID查询商品规格列表
     * @param itemId
     * @return
     */
    public List<ItemsSpec> queryItemSpecList(String itemId);

    /**
     * 根据商品ID查询商品参数
     * @param itemId
     * @return
     */
    public ItemsParam queryItemParam(String itemId);

    /**
     * 根据商品id查询商品的评价等级数量
     * @param itemId
     */
    public CommentLevelCountsVO queryItemCommentCounts(String itemId);

    /**
     * 根据商品id查询商品评价（分页）
     * @param itemId
     * @param leve
     * @return
     */
    public PagedGridResult queryPagedComments (String itemId, Integer leve,Integer page,Integer pageSize);

    /**
     * 搜索商品列表
     * @param keyWords
     * @param sort
     * @param page
     * @param pageSize
     * @return
     */
    public PagedGridResult searchItems(String keyWords,String sort,Integer page,Integer pageSize);

    /**
     * 三级分类商品列表
     * @param catId
     * @param sort
     * @param page
     * @param pageSize
     * @return
     */
    public PagedGridResult searchItemsByThirdCat(Integer catId,String sort,Integer page,Integer pageSize);


}

2.接口实现

 

package com.imooc.service.impl;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.imooc.enums.CommentLevel;
import com.imooc.mapper.*;
import com.imooc.pojo.*;
import com.imooc.pojo.vo.CommentLevelCountsVO;
import com.imooc.pojo.vo.ItemCommentVO;
import com.imooc.pojo.vo.SearchItemsVO;
import com.imooc.service.ItemService;
import com.imooc.utils.DesensitizationUtil;
import com.imooc.utils.PagedGridResult;
import io.swagger.annotations.ApiParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import tk.mybatis.mapper.entity.Example;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ItemServiceImpl implements ItemService {
    @Autowired
    ItemsMapper itemsMapper;

    @Autowired
    ItemsImgMapper itemsImgMapper;

    @Autowired
    ItemsSpecMapper itemsSpecMapper;

    @Autowired
    ItemsParamMapper itemsParamMapper;

    @Autowired
    ItemsCommentsMapper itemsCommentsCommentsMapper;

    @Autowired
    ItemsMapperCustom itemsMapperCustom;

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public Items queryItemById(String itemId) {
        return itemsMapper.selectByPrimaryKey(itemId);
    }

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public List<ItemsImg> queryItemImgList(String itemId) {
        Example itemsImgExp = new Example(ItemsImg.class);
        Example.Criteria criteria =itemsImgExp.createCriteria();
        criteria.andEqualTo("itemId",itemId);
        return itemsImgMapper.selectByExample(itemsImgExp);
    }

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public List<ItemsSpec> queryItemSpecList(String itemId) {
        Example itemsSpecExp = new Example(ItemsSpec.class);
        Example.Criteria criteria =itemsSpecExp.createCriteria();
        criteria.andEqualTo("itemId",itemId);
        return itemsSpecMapper.selectByExample(itemsSpecExp);
    }

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public ItemsParam queryItemParam(String itemId) {
        Example itemsParamExp = new Example(ItemsParam.class);
        Example.Criteria criteria =itemsParamExp.createCriteria();
        criteria.andEqualTo("itemId",itemId);
        return itemsParamMapper.selectOneByExample(itemsParamExp);
    }



    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public CommentLevelCountsVO queryItemCommentCounts(String itemId) {

       //Integer totalCounts=getCommentCounts(itemId);
        Integer goodCounts=getCommentCounts(itemId, CommentLevel.Good.type);
        Integer normalCounts=getCommentCounts(itemId, CommentLevel.NORMAL.type);
        Integer badCounts=getCommentCounts(itemId, CommentLevel.BAD.type);
        Integer totalCounts=goodCounts+normalCounts+badCounts;

        CommentLevelCountsVO commentLevelCountsVO=new CommentLevelCountsVO();
        commentLevelCountsVO.setTotalCounts(totalCounts);
        commentLevelCountsVO.setGoodCounts(goodCounts);
        commentLevelCountsVO.setNormalCounts(normalCounts);
        commentLevelCountsVO.setBadCounts(badCounts);
        return commentLevelCountsVO;
    }

    @Transactional(propagation = Propagation.SUPPORTS)
     Integer getCommentCounts(String itemId,Integer level){

        ItemsComments confdition =new ItemsComments();
        confdition.setItemId(itemId);
        if (level != null) {
            confdition.setCommentLevel(level);
        }

      return   itemsCommentsCommentsMapper.selectCount(confdition);

    }
    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public PagedGridResult queryPagedComments(String itemId,
                                                  Integer level,
                                                  Integer page,
                                                  Integer pageSzie) {
        Map<String,Object> map =new HashMap<>();
        map.put("itemId",itemId);
        map.put("level",level);
        /**
         * page:第几页
         * pageSize:每页显示多少条
         */
        PageHelper.startPage(page,pageSzie);
        List<ItemCommentVO> list=itemsMapperCustom.queryItemComments(map);
        for (ItemCommentVO vo : list
             ) {
              vo.setNickName(DesensitizationUtil.commonDisplay(vo.getNickName()));
        }
        return setterPagedGrid(list,page);

    }

    private PagedGridResult setterPagedGrid(List<?> list,Integer page){

        PageInfo<?> pageList = new PageInfo<>(list);
        PagedGridResult grid = new PagedGridResult();
        grid.setPage(page);
        grid.setRows(list);
        grid.setTotal(pageList.getPages());
        grid.setRecords(pageList.getTotal());
        return grid;

    }
    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public PagedGridResult searchItems(String keywords, String sort, Integer page, Integer pageSize) {
        Map<String,Object> map =new HashMap<>();
        map.put("keywords",keywords);
        map.put("sort",sort);
        /**
         * page:第几页
         * pageSize:每页显示多少条
         */
        PageHelper.startPage(page,pageSize);
        List<SearchItemsVO> list=itemsMapperCustom.searchItems(map);
        return setterPagedGrid(list,page);
    }

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public PagedGridResult searchItemsByThirdCat(Integer catId, String sort, Integer page, Integer pageSize) {
        Map<String,Object> map =new HashMap<>();
        map.put("catId",catId);
        map.put("sort",sort);
        /**
         * page:第几页
         * pageSize:每页显示多少条
         */
        PageHelper.startPage(page,pageSize);
        List<SearchItemsVO> list=itemsMapperCustom.searchItemsByThirdCat(map);
        return setterPagedGrid(list,page);
    }
}

五、Api子模块实现

 

   

  

package com.imooc.controller;

import com.imooc.enums.YesOrNo;
import com.imooc.pojo.*;
import com.imooc.pojo.vo.CategoryVO;
import com.imooc.pojo.vo.CommentLevelCountsVO;
import com.imooc.pojo.vo.ItemInfoVO;
import com.imooc.pojo.vo.NewItemsVO;
import com.imooc.service.CarouselService;
import com.imooc.service.CategoryService;
import com.imooc.service.ItemService;
import com.imooc.utils.IMOOCJSONResult;
import com.imooc.utils.PagedGridResult;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@Api(value = "商品接口",tags = "商品信息展示的相关接口")
@RestController
@RequestMapping("item")
public class ItemController {

    @Autowired
     private ItemService itemService;

    @ApiOperation(value="查询商品详情",notes = "查询商品详情",httpMethod = "GET")
    @GetMapping("/info/{itemId}")
    public IMOOCJSONResult info(
            @ApiParam(name = "itemId",value = "商品ID",required = true)
            @PathVariable() String itemId) {
         if (StringUtils.isBlank(itemId)) {

             return IMOOCJSONResult.errorMsg("");

         }


        Items item = itemService.queryItemById(itemId);
        List<ItemsImg> itemImgList=itemService.queryItemImgList(itemId);
        List<ItemsSpec> itemSpecList=itemService.queryItemSpecList(itemId);
        ItemsParam itemParam=itemService.queryItemParam(itemId);
        ItemInfoVO itemInfoVO=new ItemInfoVO();
        itemInfoVO.setItem(item);
        itemInfoVO.setItemImgList(itemImgList);
        itemInfoVO.setItemSpecList(itemSpecList);
        itemInfoVO.setItemParam(itemParam);

        return IMOOCJSONResult.ok(itemInfoVO);
    }


    @ApiOperation(value="查询商品评价分页",notes = "查询商品评价分页",httpMethod = "GET")
    @GetMapping("/comments")
    public IMOOCJSONResult comments(
            @ApiParam(name = "itemId",value = "商品ID",required = true)
            @RequestParam() String itemId,
            @ApiParam(name = "level",value = "评价等级",required = false)
            @RequestParam() Integer level,
            @ApiParam(name = "page",value = "查询下一页的第几页",required = false)
            @RequestParam() Integer page,
            @ApiParam(name = "pageSize",value = "分页每一页显示的条数",required = false)
            @RequestParam() Integer pageSize
            ) {
        if (StringUtils.isBlank(itemId)) {

            return IMOOCJSONResult.errorMsg("");
        }
        if(page==null) {
            page=1;
        }
        if(pageSize==null) {
            pageSize=10;
        }


        PagedGridResult grid = itemService.queryPagedComments(itemId,level,page,pageSize);
        return IMOOCJSONResult.ok(grid);
    }

    @ApiOperation(value="查询商品评价等级",notes = "查询商品评价等级",httpMethod = "GET")
    @GetMapping("/commentLevel")
    public IMOOCJSONResult commentLevel(
            @ApiParam(name = "itemId",value = "商品ID",required = true)
            @RequestParam() String itemId) {
        if (StringUtils.isBlank(itemId)) {

            return IMOOCJSONResult.errorMsg("");

        }
        CommentLevelCountsVO countsVO = itemService.queryItemCommentCounts(itemId);
        return IMOOCJSONResult.ok(countsVO);
    }

    @ApiOperation(value="搜索商品列表分页",notes = "搜索商品列表分页",httpMethod = "GET")
    @GetMapping("/search")
    public IMOOCJSONResult search(
            @ApiParam(name = "keywords",value = "关键字",required = true)
            @RequestParam() String keywords,
            @ApiParam(name = "sort",value = "排序",required = false)
            @RequestParam() String sort,
            @ApiParam(name = "page",value = "查询下一页的第几页",required = false)
            @RequestParam() Integer page,
            @ApiParam(name = "pageSize",value = "分页每一页显示的条数",required = false)
            @RequestParam() Integer pageSize
    ) {
        if (StringUtils.isBlank(keywords)) {

            return IMOOCJSONResult.errorMsg("");
        }
        if (page == null) {
            page = 1;
        }

        if(pageSize==null) {
            pageSize=20;
        }

        PagedGridResult grid = itemService.searchItems(keywords,sort,page,pageSize);
        return IMOOCJSONResult.ok(grid);
    }

    @ApiOperation(value="通过三级分类Id搜索商品列表分页",notes = "通过三级分类Id搜索商品列表分页",httpMethod = "GET")
    @GetMapping("/catItems")
    public IMOOCJSONResult catItems(
            @ApiParam(name = "catId",value = "三级分类id",required = true)
            @RequestParam() Integer catId,
            @ApiParam(name = "sort",value = "排序",required = false)
            @RequestParam() String sort,
            @ApiParam(name = "page",value = "查询下一页的第几页",required = false)
            @RequestParam() Integer page,
            @ApiParam(name = "pageSize",value = "分页每一页显示的条数",required = false)
            @RequestParam() Integer pageSize
    ) {
        if (catId==null) {

            return IMOOCJSONResult.errorMsg("");
        }
        if (page == null) {
            page = 1;
        }

        if(pageSize==null) {
            pageSize=20;
        }

        PagedGridResult grid = itemService.searchItemsByThirdCat(catId,sort,page,pageSize);
        return IMOOCJSONResult.ok(grid);
    }
}