业务场景
在项目开发过程中，往往会遇到多级菜单、分类等多层级结构数据的查询。
for example：

请看上图，这是一个电商项目中常见的多级类目功能，如图所示，共分为一、二、三，共三级类目，每一个一级类目有各自的二级目录，每个二级目录有自己的三级目录

再看这个例子，下图是一个多级菜单的功能，和上面的例子类似

在这种场景下，通常我们要用递归进行处理。下面博主以电商项目的多级类目功能，通过MyBatis进行递归查询功能说明

开发环境

数据库

表结构

CREATE TABLE `shopping_commodity_category`
(
    `id`                int          NOT NULL AUTO_INCREMENT,
    `name`              varchar(255) NOT NULL COMMENT 'category name',
    `picture`           varchar(255)          DEFAULT NULL COMMENT 'category picture id',
    `superior_category` int          NOT NULL DEFAULT 0 COMMENT 'superior category id',
    `sort_number`       int          NOT NULL COMMENT 'sort number',
    `create_time`       datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time of this record',
    `update_time`       datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time of this record',
    PRIMARY KEY (`id`),
    UNIQUE KEY (`superior_category`, `name`),
    UNIQUE KEY (`superior_category`, `sort_number`)
);
1
2
3
4
5
6
7
8
9
10
11
12
13

字段解释：

约束解释：

由于博主这个地方业务需要，用到了另一张表的数据，为了说明问题，博主将另一张表的结构也贴出来参考

CREATE TABLE `storage_multimedia_file`
(
    `id`            int      NOT NULL AUTO_INCREMENT,
    `absolute_path` text     NOT NULL COMMENT 'file absolute path',
    `create_time`   datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time of this record',
    `update_time`   datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time of this record',
    PRIMARY KEY (`id`)
);
1
2
3
4
5
6
7
8

这个表很简单，就一个主要的字段，存储的这个文件的绝对路径，方便后续通过IO流读取

插入测试数据

shopping_commodity_category表

INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (1, '手机通讯', null, 0, 1, '2022-08-11 17:27:15', '2022-08-11 17:27:15');
INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (2, '手机配件', null, 1, 1, '2022-08-11 17:29:59', '2022-08-11 17:29:59');
INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (3, '手机耳机', '2', 2, 1, '2022-08-11 17:29:59', '2022-08-11 17:29:59');
INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (4, '蓝牙耳机', '3', 2, 2, '2022-08-11 17:31:26', '2022-08-11 17:31:26');
INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (5, '手机壳/保护壳', '4', 2, 3, '2022-08-11 17:32:51', '2022-08-11 17:32:51');
INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (6, '手机贴膜', '5', 2, 4, '2022-08-11 17:33:44', '2022-08-11 17:33:44');
INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (7, '运营商', null, 1, 2, '2022-08-11 17:34:44', '2022-08-11 17:34:44');
INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (8, '办号卡', '6', 7, 1, '2022-08-11 17:36:25', '2022-08-11 17:36:25');
INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (9, '家用电器', null, 0, 2, '2022-08-11 17:36:54', '2022-08-11 17:36:54');
INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (10, '生活电器', null, 9, 1, '2022-08-11 17:37:46', '2022-08-11 17:37:46');
INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (11, '吸尘器', '7', 10, 1, '2022-08-11 17:38:55', '2022-08-11 17:38:55');
INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (12, '厨房小电', null, 9, 2, '2022-08-11 17:40:04', '2022-08-11 17:40:04');
INSERT INTO shopping_commodity_category (id, name, picture, superior_category, sort_number, create_time, update_time) VALUES (13, '电饭煲', '8', 12, 1, '2022-08-11 17:41:17', '2022-08-11 17:41:17');
1
2
3
4
5
6
7
8
9
10
11
12
13

storage_multimedia_file表

INSERT INTO trembling_bird.storage_multimedia_file (id, absolute_path, create_time, update_time) VALUES (1, 'D:\\files\\trembling-bird\\commodity-previews\\1.webp', '2022-08-11 16:18:49', '2022-08-11 16:18:49');
INSERT INTO trembling_bird.storage_multimedia_file (id, absolute_path, create_time, update_time) VALUES (2, 'D:\\files\\trembling-bird\\category-preview\\1.jpg', '2022-08-11 17:30:12', '2022-08-11 17:30:12');
INSERT INTO trembling_bird.storage_multimedia_file (id, absolute_path, create_time, update_time) VALUES (3, 'D:\\files\\trembling-bird\\category-preview\\2.jpg', '2022-08-11 17:31:07', '2022-08-11 17:31:07');
INSERT INTO trembling_bird.storage_multimedia_file (id, absolute_path, create_time, update_time) VALUES (4, 'D:\\files\\trembling-bird\\category-preview\\3.png', '2022-08-11 17:32:07', '2022-08-11 17:32:07');
INSERT INTO trembling_bird.storage_multimedia_file (id, absolute_path, create_time, update_time) VALUES (5, 'D:\\files\\trembling-bird\\category-preview\\4.jpg', '2022-08-11 17:33:36', '2022-08-11 17:33:36');
INSERT INTO trembling_bird.storage_multimedia_file (id, absolute_path, create_time, update_time) VALUES (6, 'D:\\files\\trembling-bird\\category-preview\\5.png', '2022-08-11 17:35:48', '2022-08-11 17:35:48');
INSERT INTO trembling_bird.storage_multimedia_file (id, absolute_path, create_time, update_time) VALUES (7, 'D:\\files\\trembling-bird\\category-preview\\6.jpg', '2022-08-11 17:38:22', '2022-08-11 17:38:22');
INSERT INTO trembling_bird.storage_multimedia_file (id, absolute_path, create_time, update_time) VALUES (8, 'D:\\files\\trembling-bird\\category-preview\\7.jpg', '2022-08-11 17:40:38', '2022-08-11 17:40:38');
1
2
3
4
5
6
7
8

关键代码

实体类代码

实体类的公共父类（因为数据库中每个表都有共同的字段，如id，create_time,update_time,所以将这些共有的字段抽取出来放到公共的父类，让其他实体类继承此父类，就有了这些公共字段，降低代码冗余）

package com.fenzhimedia.commons.pojo;

import lombok.Data;

import java.io.Serializable;
import java.time.LocalDateTime;

/**
 * @author Yi Dai 484201132@qq.com
 * @since 2022/3/21 13:55
 */

@Data
public abstract class BasePojo implements Serializable {
    /**
     * the unique identification of this record in the database table
     */
    protected int id;
    /**
     * creation time of this record
     */
    protected LocalDateTime createTime;
    /**
     * update time of this record
     */
    protected LocalDateTime updateTime;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

描述类目的实体类

package com.fenzhimedia.commons.shopping.pojo;

import com.fenzhimedia.commons.pojo.BasePojo;
import com.fenzhimedia.commons.storage.pojo.MultimediaFile;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.util.List;

/**
 * @author Yi Dai 484201132@qq.com
 * @since 2022/4/23 10:43
 */

@Data
@EqualsAndHashCode(callSuper = true)
public class CommodityCategory extends BasePojo {
    /**
     * category name
     */
    private String name;
    /**
     * entity class encapsulating picture information
     */
    private MultimediaFile picture;
    /**
     * used to specify the order of categories,
     * which is only valid under the same level category
     */
    private Integer sortNumber;
    /**
     * sub commodity category
     */
    private List<CommodityCategory> subCommodityCategories;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

描述文件的实体类

package com.fenzhimedia.commons.storage.pojo;

import com.fenzhimedia.commons.pojo.BasePojo;
import lombok.Data;
import lombok.EqualsAndHashCode;

/**
 * @author Yi Dai 484201132@qq.com
 * @since 2022/8/11 16:03
 */

@Data
@EqualsAndHashCode(callSuper = true)
public class MultimediaFile extends BasePojo {

    private String absolutePath;

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

MyBatis的mapper接口代码

package com.fenzhimedia.shopping.mapper;

import com.fenzhimedia.commons.shopping.pojo.CommodityCategory;

import java.util.List;

/**
 * @author Yi Dai 484201132@qq.com
 * @since 2022/8/11 16:46
 */

public interface CommodityCategoryMapper {

    List<CommodityCategory> queryCommodityCategories(int superiorCategoryId);

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

mapper映射文件代码

DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.fenzhimedia.shopping.mapper.CommodityCategoryMapper">

    <select id="queryCommodityCategories" resultMap="commodityCategoryMap">
        select `shopping_commodity_category`.`id`                as `shopping_commodity_category_id`,
               `shopping_commodity_category`.`name`              as `shopping_commodity_category_name`,
               `shopping_commodity_category`.`picture`           as `shopping_commodity_category_picture`,
               `shopping_commodity_category`.`superior_category` as `shopping_commodity_category_superior_category`,

               `storage_multimedia_file`.`id`                    as `storage_multimedia_file_id`,
               `storage_multimedia_file`.`absolute_path`         as `storage_multimedia_file_absolute_path`
        from `shopping_commodity_category`
                 left join `storage_multimedia_file`
                           on `shopping_commodity_category`.`picture` = `storage_multimedia_file`.`id`
        where `superior_category` = #{categoryId}
        order by `shopping_commodity_category`.`sort_number`
    select>

    <resultMap id="commodityCategoryMap" type="commodityCategory">
        <id property="id" column="shopping_commodity_category_id"/>
        <result property="name" column="shopping_commodity_category_name"/>
        <association property="picture" javaType="multimediaFile">
            <id property="id" column="storage_multimedia_file_id"/>
            <result property="absolutePath" column="storage_multimedia_file_absolute_path"/>
        association>
        <collection property="subCommodityCategories"
                    ofType="commodityCategory"
                    column="shopping_commodity_category_id"
                    select="queryCommodityCategories"/>
    resultMap>

mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

代码解释：
可以看到，queryCommodityCategories是一个类目表和文件表的左连接查询，然后分别起了别名，接收一个int型的参数，为上级类目的id，当然也就是0，然后声明一个resultMap ，将字段映射起来，都是基本操作，唯一值得注意的就是
subCommodityCategories作为一个集合，它有通过select直接调用了queryCommodityCategories这个查询，而参数正是由column属性传递过去的，参数的值就是当前类目的id(shopping_commodity_category_id)（有点绕），其实这样就递归查询起来了

那么有的小伙伴可能会疑问，既然是已知是0，为何不直接写道xml中，而是大费周章的，在接口上声明一个参数，然后传递进来？其实这是因为后面的递归查询的时候需要传入上级分类的id，一旦写死了，就只能查询上级分类id为0的，很显然，达不到想要的效果

既然如此，那么就需要业务代码中来传递上级类目id这个参数，很显然我们直接在代码中写死不是那么的优雅。博主这里为了更灵活，我想要实现一个如果前台传递了上级分类id，那么就帮他查询指定上级类目的子级类目，如果没有传递，那么就查询所有的类目及其子类目。所以博主是这么处理的：

 @GetMapping("/queryCommodityCategories")
 public ResponseBody queryCommodityCategories(@RequestParam(required = false, defaultValue = "0") int superiorCategoryId) {
     return commodityCategoryService.queryCommodityCategories(superiorCategoryId);
 }
1
2
3
4

查询测试

返回结果

statusCode、message是通用返回实体类的结构，无需关注，查询的数据在data中

{
    "statusCode": 200, 
    "message": null, 
    "data": [
        {
            "id": 1, 
            "createTime": null, 
            "updateTime": null, 
            "name": "手机通讯", 
            "picture": null, 
            "sortNumber": null, 
            "subCommodityCategories": [
                {
                    "id": 2, 
                    "createTime": null, 
                    "updateTime": null, 
                    "name": "手机配件", 
                    "picture": null, 
                    "sortNumber": null, 
                    "subCommodityCategories": [
                        {
                            "id": 3, 
                            "createTime": null, 
                            "updateTime": null, 
                            "name": "手机耳机", 
                            "picture": {
                                "id": 2, 
                                "createTime": null, 
                                "updateTime": null, 
                                "absolutePath": "D:\\files\\trembling-bird\\category-preview\\1.jpg"
                            }, 
                            "sortNumber": null, 
                            "subCommodityCategories": [ ]
                        }, 
                        {
                            "id": 4, 
                            "createTime": null, 
                            "updateTime": null, 
                            "name": "蓝牙耳机", 
                            "picture": {
                                "id": 3, 
                                "createTime": null, 
                                "updateTime": null, 
                                "absolutePath": "D:\\files\\trembling-bird\\category-preview\\2.jpg"
                            }, 
                            "sortNumber": null, 
                            "subCommodityCategories": [ ]
                        }, 
                        {
                            "id": 5, 
                            "createTime": null, 
                            "updateTime": null, 
                            "name": "手机壳/保护壳", 
                            "picture": {
                                "id": 4, 
                                "createTime": null, 
                                "updateTime": null, 
                                "absolutePath": "D:\\files\\trembling-bird\\category-preview\\3.png"
                            }, 
                            "sortNumber": null, 
                            "subCommodityCategories": [ ]
                        }, 
                        {
                            "id": 6, 
                            "createTime": null, 
                            "updateTime": null, 
                            "name": "手机贴膜", 
                            "picture": {
                                "id": 5, 
                                "createTime": null, 
                                "updateTime": null, 
                                "absolutePath": "D:\\files\\trembling-bird\\category-preview\\4.jpg"
                            }, 
                            "sortNumber": null, 
                            "subCommodityCategories": [ ]
                        }
                    ]
                }, 
                {
                    "id": 7, 
                    "createTime": null, 
                    "updateTime": null, 
                    "name": "运营商", 
                    "picture": null, 
                    "sortNumber": null, 
                    "subCommodityCategories": [
                        {
                            "id": 8, 
                            "createTime": null, 
                            "updateTime": null, 
                            "name": "办号卡", 
                            "picture": {
                                "id": 6, 
                                "createTime": null, 
                                "updateTime": null, 
                                "absolutePath": "D:\\files\\trembling-bird\\category-preview\\5.png"
                            }, 
                            "sortNumber": null, 
                            "subCommodityCategories": [ ]
                        }
                    ]
                }
            ]
        }, 
        {
            "id": 9, 
            "createTime": null, 
            "updateTime": null, 
            "name": "家用电器", 
            "picture": null, 
            "sortNumber": null, 
            "subCommodityCategories": [
                {
                    "id": 10, 
                    "createTime": null, 
                    "updateTime": null, 
                    "name": "生活电器", 
                    "picture": null, 
                    "sortNumber": null, 
                    "subCommodityCategories": [
                        {
                            "id": 11, 
                            "createTime": null, 
                            "updateTime": null, 
                            "name": "吸尘器", 
                            "picture": {
                                "id": 7, 
                                "createTime": null, 
                                "updateTime": null, 
                                "absolutePath": "D:\\files\\trembling-bird\\category-preview\\6.jpg"
                            }, 
                            "sortNumber": null, 
                            "subCommodityCategories": [ ]
                        }
                    ]
                }, 
                {
                    "id": 12, 
                    "createTime": null, 
                    "updateTime": null, 
                    "name": "厨房小电", 
                    "picture": null, 
                    "sortNumber": null, 
                    "subCommodityCategories": [
                        {
                            "id": 13, 
                            "createTime": null, 
                            "updateTime": null, 
                            "name": "电饭煲", 
                            "picture": {
                                "id": 8, 
                                "createTime": null, 
                                "updateTime": null, 
                                "absolutePath": "D:\\files\\trembling-bird\\category-preview\\7.jpg"
                            }, 
                            "sortNumber": null, 
                            "subCommodityCategories": [ ]
                        }
                    ]
                }
            ]
        }
    ]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164

完结

如有编辑错误或其他问题，欢迎联系博主:18380924397(微信同号)