Mybatis目录树 当Mybatis遇上目录树超全完美解决方案
砖业洋__ 人气:0相信你也遇到过这种场景,判断二级目录属于哪个一级目录,一个员工属于哪个上级员工领导…
当Mybatis
遇上目录树,有哪些解决方法?
一般来说,有
xml
直接实现和java
代码递归赋值实现。
方式一:xml直接实现
这里列出category
数据表数据
表结构如下
type
表示分类类型,也就是目录级别,1表示一级目录,3表示三级目录
大家就不要关注数据类型规范了,比如这里id
应该bigint
,type
明明可以tinyint
之类的,我抓我看到的例子直接讲解。
目录为甜点/蛋糕的id
为1,而蛋糕和点心的father_id
为1,目录为饼干/膨化的id
为2,饼干、薯片、虾条的father_id
就是2,一级目录id
对应二级子目录的father_id
,这就是所属对应关系,可以理解为父子关系。
实体类是mybatis-generator
插件自动生成的
public class Category { private Integer id; private String name; private Integer type; private Integer fatherId; private String logo; private String slogan; private String catImage; private String bgColor; //=====篇幅原因,省掉Getter和Setter方法====== ...... }
一般我们看到的商城,鼠标放到一级分类目录就会展示出二级分类目录。我们的需求是当鼠标移动到一级分类,我们需要提供二级分类和三级分类。
这里贴出需要返回给前端的聚合模型view object
数据
/** * 二级分类VO */ public class CategoryVO { private Integer id; private String name; private String type; private Integer fatherId; // 三级分类vo list private List<SubCategoryVO> subCatList; //=====篇幅原因,省掉Getter和Setter方法====== ...... }
public class SubCategoryVO { private Integer subId; private String subName; private String subType; private Integer subFatherId; //=====篇幅原因,省掉Getter和Setter方法====== ...... }
这就涉及到自连接查询子目录的技巧了,我们试试查找father_id
是1
的子分类数据,也就是查询甜点/蛋糕分类下面的二级和三级分类,执行如下语句
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
结果如下
可以看到二级分类为蛋糕、点心时,有哪些对应的三级分类可以提供给前端,便于展示。
我这里分为CategoryVO
、SubCategoryVO
,而不是把所有属性放在一个VO
,是为了便于理解。如果不用List
集合,而把所有属性放在一个VO
,前端收到的数据形式和你此时在数据库查询出来的一样,有多条蛋糕记录,底下对应着不同具体食品,这让前端不好处理也不符合逻辑,正常逻辑应该是只有一个蛋糕分类,然后这个分类里面有数组去装着蛋糕对应子分类才对。
这里其实只用一个CategoryVO
里面也可以处理,在后面第二种方式用java
代码处理多级目录时,你会看到我只用了一个CategoryVO
就能处理。
注意,二级分类的实体类CategoryVO
有个
private List<SubCategoryVO> subCatList;
这个subCatList
是为了存放三级分类的vo list
,在xml
中三级分类用了collection
对应这个list
<?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.me.mapper.CategoryMapperCustom" > <resultMap id="myCategoryVO" type="com.me.pojo.vo.CategoryVO"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="type" property="type"/> <!-- column一定要在sql语句中找到,property一定要在对应实体类中找到 因为sql用as写了别名,所以column才能用fatherId,如果不用别名,还是得写father_id --> <result column="fatherId" property="fatherId"/> <!-- collection 标签:用于定义关联的list集合类型的封装规则 property:对应三级分类的list属性名 ofType:集合的类型,三级分类的vo --> <collection property="subCatList" ofType="com.me.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>
首先让前端展示在首页的一级分类,前端调用一级分类接口,我们只需要查询type
为1
的数据返回给前端,鼠标移动到一级分类,就调用获取子分类的接口,前端传入对应一级分类的id
给后端,后端将这个id
作为father_id
去查询子分类。最后我们可以调用getSubCatList
来得到所有目录。
@Transactional(propagation = Propagation.SUPPORTS) @Override public List<CategoryVO> getSubCatList(Integer rootCatId) { return categoryMapperCustom.getSubCatList(rootCatId); }
最后数据就是这样,如下
{ "status": 200, "msg": "OK", "data": [{ "id": 11, "name": "蛋糕", "type": "2", <==================type=2表示二级目录 "fatherId": 1, "subCatList": [{ "subId": 37, "subName": "蒸蛋糕", "subType": "3", <================subType=3表示3级目录 "subFatherId": 11 }, { "subId": 38, "subName": "软面包", "subType": "3", "subFatherId": 11 }, { "subId": 39, "subName": "脱水蛋糕", "subType": "3", "subFatherId": 11 }, { "subId": 40, "subName": "马卡龙", "subType": "3", "subFatherId": 11 }, { "subId": 41, "subName": "甜甜圈", "subType": "3", "subFatherId": 11 }, { "subId": 42, "subName": "三明治", "subType": "3", "subFatherId": 11 }, { "subId": 43, "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 }, { "subId": 46, "subName": "沙琪玛", "subType": "3", "subFatherId": 12 }, { "subId": 47, "subName": "鸡蛋卷", "subType": "3", "subFatherId": 12 }, { "subId": 48, "subName": "蛋饼", "subType": "3", "subFatherId": 12 }, { "subId": 49, "subName": "凤梨酥", "subType": "3", "subFatherId": 12 }, { "subId": 50, "subName": "手撕面包", "subType": "3", "subFatherId": 12 }] }] }
方式二:java代码递归处理二级三级目录
此刻我换一个数据库例子,但是还是和上面一个处理一级二级三级分类的例子一样
数据表如下
表结构如下
和上一个例子大同小异,type
依然表示目录级别
此刻需要返回给前端的VO
如下,此刻我只写了一个CategoryVO
,没有写子VO
,可以对比前一种方式看看,道理都是一样的。
public class CategoryVO { private Integer id; private String name; private Integer type; private Integer parentId; private Integer orderNum; private Date createTime; private Date updateTime; private List<CategoryVO> childCategory = new ArrayList<>(); //=====篇幅原因,省掉Getter和Setter方法====== ...... }
@Override public List<CategoryVO> listCategoryForCustomer(Integer parentId) { ArrayList<CategoryVO> categoryVOList = new ArrayList<>(); recursivelyFindCategories(categoryVOList, parentId); return categoryVOList; } // 以该parentId对应的目录为根节点,查询下面所有子目录信息,categoryVOList是要返回给前端展示的聚合模型数据 private void recursivelyFindCategories(List<CategoryVO> categoryVOList, Integer parentId) { // 递归获取所有子类别,并组合成为一个"目录树" List<Category> list= categoryMapper.selectCategoriesByParentId(parentId); // 通过父id查询子分类 if (!CollectionUtils.isEmpty(list)) { for (int i = 0; i < list.size(); ++i) { Category category = list.get(i); CategoryVO categoryVO = new CategoryVO(); BeanUtils.copyProperties(category, categoryVO); categoryVOList.add(categoryVO); // 这里当前目录id作为下一次的父id,查询有没有对应的子目录,getChildCategory()方法是返回定义的List<CategoryVO> childCategory recursivelyFindCategories(categoryVO.getChildCategory(), categoryVO.getId()); } } }
XML
文件如下:
...... <resultMap id="BaseResultMap" type="com.me.mall.model.pojo.Category"> <id column="id" jdbcType="INTEGER" property="id" /> <result column="name" jdbcType="VARCHAR" property="name" /> <result column="type" jdbcType="INTEGER" property="type" /> <result column="parent_id" jdbcType="INTEGER" property="parentId" /> <result column="order_num" jdbcType="INTEGER" property="orderNum" /> <result column="create_time" jdbcType="TIMESTAMP" property="createTime" /> <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" /> </resultMap> <sql id="Base_Column_List"> id, `name`, `type`, parent_id, order_num, create_time, update_time </sql> <select id="selectCategoriesByParentId" parameterType="int" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from category where parent_id = #{parentId} </select> ......
我们手动查询模拟一下递归的过程,首先查询parent_id
为3
的二级分类
select * from category where parent_id = 3
结果递归查询的时候,又会发现parent_id=4
时还有数据,即还有三级分类,我们手动查询试试
select * from category where parent_id = 4
示例数据如下:
{ "status": 10000, "msg": "SUCCESS", "data": [ { "id": 4, "name": "橘子橙子", "type": 2, <=================代表二级目录 "parentId": 3, "orderNum": 1, "createTime": "2019-12-17T17:17:00.000+0000", "updateTime": "2019-12-28T08:25:10.000+0000", "childCategory": [ <===============代表还有三级目录 { "id": 19, "name": "果冻橙", "type": 3, "parentId": 4, "orderNum": 1, "createTime": "2019-12-17T17:17:00.000+0000", "updateTime": "2020-02-10T16:37:02.000+0000", "childCategory": [] } ] }, { "id": 11, "name": "草莓", "type": 2, "parentId": 3, "orderNum": 2, "createTime": "2019-12-17T17:17:00.000+0000", "updateTime": "2019-12-28T07:44:42.000+0000", "childCategory": [] }, { "id": 12, "name": "奇异果", "type": 2, "parentId": 3, "orderNum": 3, "createTime": "2019-12-17T17:17:00.000+0000", "updateTime": "2019-12-28T08:25:12.000+0000", "childCategory": [] }, { "id": 14, "name": "车厘子", "type": 2, "parentId": 3, "orderNum": 4, "createTime": "2019-12-17T17:17:00.000+0000", "updateTime": "2019-12-28T08:25:12.000+0000", "childCategory": [] }, { "id": 28, "name": "其他水果", "type": 2, "parentId": 3, "orderNum": 4, "createTime": "2019-12-17T17:17:00.000+0000", "updateTime": "2019-12-28T08:25:12.000+0000", "childCategory": [] } ] }
加载全部内容