最终效果
数据库表设计:
实现懒加载
首页分类展示需求:
1、第一次刷新主页查询大分类,渲染展示到首页
2、如果鼠标移动到了大分类上面,则加载器子分类的内容,如果分类已存在,则不加载(懒加载)
1、获取一级分类
一级分类在页面开始生命周期时即请求后端获取
====================== Controller ======================
@ApiOperation(value = "获取商品分类(一级分类)", notes = "获取商品分类(一级分类)", httpMethod = "GET")
@GetMapping("/cats")
public MyJSONResult cats() {
List<Category> list = categoryService.queryAllRootLevelCat();
return MyJSONResult.ok(list);
}
====================== Service ======================
@Transactional(propagation = Propagation.SUPPORTS)
@Override
public List<Category> queryAllRootLevelCat() {
Example example = new Example(Category.class);
Example.Criteria criteria = example.createCriteria();
criteria.andEqualTo("type",1);
List<Category> result = categoryMapper.selectByExample(example);
return result;
}
2、根据一级分类id获取其子分类
1)、编写sql语言
SELECT
f.`id` AS id,
f.`name` AS `name`,
f.type AS type,
f.father_id AS fatherId,
c.id AS subId,
c.`name` AS subName,
c.type AS subType,
c.father_id AS subFatherId
FROM
category f
LEFT JOIN #🔥自联表查询🔥
category c
ON
f.id = c.father_id
WHERE
f.father_id = 1
2)、分析创建二级VO、三级VO
我们可以看到通过联表查询,二级分类都是重复的,所以我们可以将二级分类整合成为一个VO,这个二级分类里又含有它的三级分类VO List。
最终返回给前端数据如下:
"data": [
{
"id": 11, // ==================== 二级分类 ====================
"name": "蛋糕",
"type": "2",
"fatherId": 1,
"subCatList": [ // 👇👇👇👇👇👇👇👇二级分类下的三级分类👇👇👇👇👇👇👇👇
{
"subId": 37,
"subName": "蒸蛋糕",
"subType": "3",
"subFatherId": 11
},
{
"subId": 38,
"subName": "软面包",
"subType": "3",
"subFatherId": 11
}
//...
] // 👆👆👆👆👆👆👆二级分类下的三级分类👆👆👆👆👆👆👆
},
{
"id": 12,
"name": "点心",
"type": "2",
"fatherId": 1,
"subCatList": [
{
"subId": 44,
"subName": "肉松饼",
"subType": "3",
"subFatherId": 12
},
{
"subId": 45,
"subName": "华夫饼",
"subType": "3",
"subFatherId": 12
}
//...
]
}
]
所以我们创建俩个VO来封装返回给前端你的数据,由前端进行解析
package com.shiers.pojo.vo;
import java.util.List;
/**
* 二级分类VO
*
* @author shierS
* @date 2021/5/29
*/
public class CategoryVO {
private Integer id;
private String name;
private String type;
private Integer fatherId;
//三级分类VO list
private List<SubCategoryVO> subCatList;
//省略get、set方法
}
package com.shiers.pojo.vo;
/**
* 三级分类VO
*
* @author shierS
* @date 2021/5/29
*/
public class SubCategoryVO {
private Integer subId;
private String subName;
private String subType;
private Integer subFatherId;
//省略get、set方法
}
3)、创建自定义mapper
由于我们使用了通用mapper,它只能用于单表查询,对于自联表(多表查询)需要我们自定义mapper
创建CategoryMapperCustom,custom代表是我们自定义的mapper
返回类型封装在List
public interface CategoryMapperCustom {
public List<CategoryVO> getSubCatList(Integer rootCatId);
}
创建CategoryMapperCustom.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.shiers.mapper.CategoryMapperCustom">
<resultMap id="myCategoryVO" type="com.shiers.pojo.vo.CategoryVO">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="type" property="type"/>
<result column="fatherId" property="fatherId"/>
<!--
collection 标签:用于定义关联的lsit集合类型的封装规则
property:对应三级分类的list属性名
ofType:集合的类型,三级分类vo
-->
<collection property="subCatList" ofType="com.shiers.pojo.vo.SubCategoryVO">
<id column="subId" property="subId"/>
<result column="subName" property="subName"/>
<result column="subType" property="subType"/>
<result column="subFatherId" property="subFatherId"/>
</collection>
</resultMap>
<select id="getSubCatList" resultMap="myCategoryVO" parameterType="int">
SELECT
f.`id` AS id,
f.`name` AS `name`,
f.type AS type,
f.father_id AS fatherId,
c.id AS subId,
c.`name` AS subName,
c.type AS subType,
c.father_id AS subFatherId
FROM
category f
LEFT JOIN
category c
ON
f.id = c.father_id
WHERE
f.father_id = #{rootCatId}
</select>
</mapper>
4)、Service
@Autowired
private CategoryMapperCustom categoryMapperCustom; //🔥使用我们自定义mapper🔥
@Transactional(propagation = Propagation.SUPPORTS)
@Override
public List<CategoryVO> getSubCatList(Integer rootCatId) {
List<CategoryVO> subCatList = categoryMapperCustom.getSubCatList(rootCatId);
return subCatList;
}
5)、Controller
@ApiOperation(value = "获取商品子分类", notes = "获取商品子分类", httpMethod = "GET")
@GetMapping("/subCat/{rootCatId}")
public MyJSONResult subCat(
@ApiParam(name = "rootCatId", value = "一级分类id", required = true)
@PathVariable Integer rootCatId) {
if (rootCatId == null) {
return MyJSONResult.errorMsg("分类不存在");
}
List<CategoryVO> list = categoryService.getSubCatList(rootCatId);
return MyJSONResult.ok(list);
}