实现模糊搜索和分类搜索(带分页)

1.直接先看图


2.数据库设计


3.封装接收的数据VO
SearchItemsVO

public class SearchItemsVO {
    private String itemId;
    private String itemName;
    private int sellCounts;
    private String imgUrl;
    private int price;
    省略get和set方法......
}

4.自定义Mapper
ItemsMapperCustom

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

<select id="searchItems" parameterType="Map" resultType="com.lzx.pojo.vo.SearchItemsVO">
    SELECT
      i.`id` AS itemId,
      i.`item_name` AS itemName,
      i.`sell_counts` AS sellCounts,
      ii.`url` AS imgUrl,
      tempSpec.price_discount 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 price_discount 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.price_discount desc
      </when>
      <otherwise>
        i.`item_name` asc
      </otherwise>
    </choose>
  </select>
  <select id="searchItemsByThirdCat" parameterType="Map" resultType="com.lzx.pojo.vo.SearchItemsVO">
    SELECT
    i.`id` AS itemId,
    i.`item_name` AS itemName,
    i.`sell_counts` AS sellCounts,
    ii.`url` AS imgUrl,
    tempSpec.price_discount 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 price_discount 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.price_discount desc
      </when>
      <otherwise>
        i.`item_name` asc
      </otherwise>
    </choose>
  </select>

5.编写Service接口
ItemService

    /**
     * 根据商品关键字查询商品
     * @param keywords
     * @param sort
     * @param page
     * @param pageSize
     * @return
     */
    public PagedGridResult searchItems(String keywords, String sort, Integer page, Integer pageSize);

    /**
     * 根据分类Id查询商品
     * @param catId
     * @param sort
     * @param page
     * @param pageSize
     * @return
     */
    public PagedGridResult searchItemsByThirdCat(Integer catId, String sort, Integer page, Integer pageSize);

6.实现Service实现类
ItemServiceImpl

 @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);
        PageHelper.startPage(page,pageSize);
        List<SearchItemsVO> lists = itemsMapperCustom.searchItems(map);
        return setterPagedGrid(lists,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);
        PageHelper.startPage(page,pageSize);
        List<SearchItemsVO> lists = itemsMapperCustom.searchItemsByThirdCat(map);
        return setterPagedGrid(lists,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;
    }

7.编写Controller

@ApiOperation(value = "搜索商品",notes = "搜索商品",httpMethod = "GET")
    @GetMapping("/search")
    public JSONResult 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 JSONResult.errorMsg(null);
        }
        if (page ==  null) {
            page = 1;
        }
        if (pageSize == null) {
            pageSize = PAGE_SIZE ;
        }
        PagedGridResult result = itemService.searchItems(keywords,sort,page,pageSize);

        return JSONResult.ok(result);
    }

    @ApiOperation(value = "搜索商品",notes = "根据分类Id搜搜商品" ,httpMethod = "GET")
    @GetMapping("/catItems")
    public JSONResult 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 JSONResult.errorMsg(null);
        }
        if (page ==  null) {
            page = 1;
        }
        if (pageSize == null) {
            pageSize = PAGE_SIZE ;
        }
        PagedGridResult result = itemService.searchItemsByThirdCat(catId,sort,page,pageSize);

        return JSONResult.ok(result);
    }

8.部分前端代码

searchInBackend(keywords, sort, page, pageSize) {
					// debugger;
					var serverUrl = app.serverUrl;
					axios.defaults.withCredentials = true;
					axios.get(serverUrl + '/items/search?keywords=' + keywords + "&sort=" + sort + "&page=" + page + "&pageSize=" + pageSize, {})
						.then(res => {
							if (res.data.status == 200) {
								var grid = res.data.data;
								var itemsList = grid.rows;
								this.itemsList = itemsList;

								var maxPage = grid.total;		// 获得总页数
								var total = grid.records;		// 获得总记录数
								
								this.maxPage = maxPage;
								this.total = total;

								// console.log(itemsList);
							} else if (res.data.status == 500) {
								alert(res.data.msg);
								return;
							}
						});
				},

searchCatItemsInBackend(catId, sort, page, pageSize) {
					// debugger;
					var serverUrl = app.serverUrl;
					axios.defaults.withCredentials = true;
					axios.get(serverUrl + '/items/catItems?catId=' + catId + "&sort=" + sort + "&page=" + page + "&pageSize=" + pageSize, {})
						.then(res => {
							if (res.data.status == 200) {
								var grid = res.data.data;
								var itemsList = grid.rows;
								this.itemsList = itemsList;

								var maxPage = grid.total;		// 获得总页数
								var total = grid.records;		// 获得总记录数
								
								this.maxPage = maxPage;
								this.total = total;

								// console.log(itemsList);
							} else if (res.data.status == 500) {
								alert(res.data.msg);
								return;
							}
						});
				},