当Mybatis遇上目录树，有哪些解决方法？

快来打我* 2023-01-15 14:23 180阅读 0赞

相信你也遇到过这种场景，判断二级目录属于哪个一级目录，一个员工属于哪个上级员工领导…

当`Mybatis`遇上目录树，有哪些解决方法？

> 一般来说，有`xml`直接实现和`java`代码递归赋值实现。

### 文章目录 ###

*  方式一：xml直接实现
 *  方式二：java代码递归处理二级三级目录

# 方式一：xml直接实现 #

这里列出`category`数据表数据

![20210422215251900.png][]

表结构如下  
![20210423172817235.png][]  
`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方法======
    	......
    }

一般我们看到的商城，鼠标放到一级分类目录就会展示出二级分类目录。**我们的需求是当鼠标移动到一级分类，我们需要提供二级分类和三级分类。**

![20210423180928287.png][]

这里贴出需要返回给前端的聚合模型`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

结果如下  
![20210423181420411.png][]

可以看到二级分类为蛋糕、点心时，有哪些对应的三级分类可以提供给前端，便于展示。

我这里分为`CategoryVO`、`SubCategoryVO` ，而不是把所有属性放在一个`VO`，是为了便于理解。如果不用`List`集合，而把所有属性放在一个`VO`，前端收到的数据形式和你此时在数据库查询出来的一样，有多条蛋糕记录，底下对应着不同具体食品，这让前端不好处理也不符合逻辑，正常逻辑应该是只有一个蛋糕分类，然后这个分类里面有数组去装着蛋糕对应子分类才对。

![20210426162642397.png][]

这里其实只用一个`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"/>
          
        <result column="fatherId" property="fatherId"/>
    
        
        <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代码递归处理二级三级目录 #

此刻我换一个数据库例子，但是还是和上面一个处理一级二级三级分类的例子一样  
数据表如下  
![20210423185547823.png][]  
表结构如下  
![20210423185658693.png][]  
和上一个例子大同小异，`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

![在这里插入图片描述][20210423191453684.png]  
结果递归查询的时候，又会发现`parent_id=4`时还有数据，即还有三级分类，我们手动查询试试

select *
    from category
    where parent_id = 4

![在这里插入图片描述][20210423191554980.png]  
示例数据如下：

{
        
        "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": []
            }
        ]
    }

欢迎一键三连~  
  
有问题请留言，大家一起探讨学习  
  
\----------------------Talk is cheap, show me the code-----------------------

[20210422215251900.png]: /images/20221022/e04cc47a6a4e4ea8b6ca1e4bb85d7f71.png
[20210423172817235.png]: /images/20221022/208f0b492d674f03ad0524af4a4104ac.png
[20210423180928287.png]: /images/20221022/d752b2e2e8204975b6c44c8304902022.png
[20210423181420411.png]: /images/20221022/92cf7f8613b34472b163586cf933afe1.png
[20210426162642397.png]: /images/20221022/face436f4690485b8190bd160556ab89.png
[20210423185547823.png]: /images/20221022/fce40cd2a51b43bdb55f3d53b4894408.png
[20210423185658693.png]: /images/20221022/39df3306235d4c02a775eb22e673435b.png
[20210423191453684.png]: /images/20221022/60e4b5522a2b4197a81db1188c2478d8.png
[20210423191554980.png]: /images/20221022/03542451e7194f9485ff204be49b850a.png