温州快速网站建设排名友链外链app

根据关键字获取分类查询对应的分页商品信息，并可以价格和销量进行排序切换

步骤1：mapper.xml编写sql语句

<!-- k: 默认，代表默认排序，根据name-->
<!-- c: 根据销量排序-->
<!-- p: 根据价格排序-->
<select id="searchItems" parameterType="Map" resultType="com.imooc.pojo.vo.SearchItemsVO">SELECTi.id as itemId,i.item_name as itemName,i.sell_counts as sellCounts,ii.url as imgUrl,tempSpec.price_discount as priceFROMitems iLEFT JOINitems_img iioni.id = ii.item_idLEFT JOIN(SELECT item_id,MIN(price_discount) as price_discount from items_spec GROUP BY item_id) tempSpeconi.id = tempSpec.item_idWHEREii.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 asc</when><otherwise>i.item_name asc</otherwise></choose>
</select><select id="searchItemsByThirdCat" parameterType="Map" resultType="com.imooc.pojo.vo.SearchItemsVO">SELECTi.id as itemId,i.item_name as itemName,i.sell_counts as sellCounts,ii.url as imgUrl,tempSpec.price_discount as priceFROMitems iLEFT JOINitems_img iioni.id = ii.item_idLEFT JOIN(SELECT item_id,MIN(price_discount) as price_discount from items_spec GROUP BY item_id) tempSpeconi.id = tempSpec.item_idWHEREii.is_main = 1andi.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 asc</when><otherwise>i.item_name asc</otherwise></choose>
</select>

步骤2：创建封装搜索的VO类

/*** 用于展示商品搜索列表结果的VO*/
public class SearchItemsVO {private String itemId;private String itemName;private int sellCounts;private String imgUrl;private int price;
}

步骤3：mapper接口添加对应接口方法

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

步骤4：service接口和实现类

/*** 搜索商品列表* @param keywords* @param sort* @param page* @param pageSize* @return*/
public PagedGridResult searhItems(String keywords, String sort,  Integer page, Integer pageSize);/*** 根据分类id搜索商品列表* @param catId* @param sort* @param page* @param pageSize* @return*/
public PagedGridResult searhItems(Integer catId, String sort, Integer page, Integer pageSize);

@Transactional(propagation = Propagation.SUPPORTS)
@Override
public PagedGridResult searhItems(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> list = itemsMapperCustom.searchItems(map);return setterPagedGrid(list, page);
}@Transactional(propagation = Propagation.SUPPORTS)
@Override
public PagedGridResult searhItems(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> list = itemsMapperCustom.searchItemsByThirdCat(map);return setterPagedGrid(list, page);
}

步骤5：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 grid = itemService.searhItems(keywords,sort,page,pageSize);return JSONResult.ok(grid);}@ApiOperation(value = "通过分类id搜索商品列表", 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 grid = itemService.searhItems(catId,sort,page,pageSize);return JSONResult.ok(grid);}