根据关键字获取分类查询对应的分页商品信息，并可以价格和销量进行排序切换
步骤1：mapper.xml编写sql语句
<!-- k: 默认，代表默认排序，根据name-->
<!-- c: 根据销量排序-->
<!-- p: 根据价格排序-->
<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.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 == "c" ">
i.sell_counts desc
</when>
<when test=" paramsMap.sort == "p" ">
tempSpec.price_discount asc
</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.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 == "c" ">
i.sell_counts desc
</when>
<when test=" paramsMap.sort == "p" ">
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);
}
