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 == "c" ">
i.`sell_counts` desc
</when>
<when test=" paramsMap.sort == "p" ">
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 == "c" ">
i.`sell_counts` desc
</when>
<when test=" paramsMap.sort == "p" ">
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;
}
});
},