MyBatis自查询递归生成目录菜单树

发表于 分类于 ， 阅读次数： 本文字数： 6.6k 阅读时长 ≈ 6 分钟

需求

要求前端展示所有奖励政策分类，一共有两级菜单，如下图所示：
在这里插入图片描述

代码实现

数据库表设计脚本如下：

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `t_incentive_policy_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '奖励分类 id',
  `type_name` varchar(255) DEFAULT NULL COMMENT '分类名称',
  `parent_id` int(11) DEFAULT NULL COMMENT '父id',
  `status` int(11) DEFAULT NULL COMMENT '状态  0 开启  1 关闭',
  `icon` varchar(255) DEFAULT NULL COMMENT '图标',
  `sort` int(11) NOT NULL DEFAULT '-1' COMMENT '排序号',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

插入几条测试数据：
在这里插入图片描述

实体类：

1
2
3
4
5
6
7
8
9
10
11
12
13
@Data
public class IncentivePolicyType {
    private Integer id;
    private String typeName;
    private String parentTypeName;
    private Integer parentId;
    private Integer status;
    private String icon;
    private Integer sort;
    private Date createTime;
    private Date updateTime;
    private List<IncentivePolicyType> childTypeList;
}

Controller请求方法：

1
2
3
4
5
6
7
8
9
@PostMapping("/querytypetree")
@ApiOperation("查询分类列表树")
public ListResponseMsg queryTypeTree(){
    List<IncentivePolicyType> policyTypeList = policyTypeService.queryTypeTree();
    if(policyTypeList.isEmpty()){
        return this.getListResponseMsg(MessageCode.Data_Not_Found.code);
    }
    return this.getListResponseMsg(MessageCode.RSP_CODE_SUCCED.code,policyTypeList);
}

服务接口实现类：

1
2
3
4
@Override
public List<IncentivePolicyType> queryTypeTree() {
    return  policyTypeMapper.queryTypeTree();
}

MyBatisXMl映射文件(核心)：
为了更方便演示，sql中没有筛选status的值

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
<?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.aspirecn.rewardportal.mapper.IncentivePolicyTypeMapper">
  <!-- collection中的property对应IncentivePolicyType中的属性 -->
  <resultMap id="BaseResultMap" type="com.aspirecn.rewardportal.entity.IncentivePolicyType">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="type_name" jdbcType="VARCHAR" property="typeName" />
    <result column="parent_id" jdbcType="INTEGER" property="parentId" />
    <result column="status" jdbcType="INTEGER" property="status" />
    <result column="icon" jdbcType="VARCHAR" property="icon" />
    <result column="sort" jdbcType="INTEGER" property="sort" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <collection property="childTypeList" ofType="com.aspirecn.rewardportal.entity.IncentivePolicyType" column="id" select="getChildren"/>
  </resultMap>
  <sql id="Base_Column_List">
    id, type_name, parent_id, status, icon, sort, create_time, update_time
  </sql>
  <!-- 先查询菜单根级目录 返回结果必须为resultMap，并且值为上面构建的resultMap的id的值 -->
  <select id="queryTypeTree" resultMap="BaseResultMap">
    select <include refid="Base_Column_List" />
    FROM t_incentive_policy_type
    WHERE parent_id is null
    order by sort desc
  </select>
  <!-- 再利用上次查询结果colliection中column的值id做递归查询，查出所有子菜单 -->
  <!-- 这里的返回结果必须为resultMap，并且值为上面构建的resultMap的id的值 -->
  <select id="getChildren" resultMap="BaseResultMap">
    select <include refid="Base_Column_List" />
    FROM t_incentive_policy_type
    WHERE parent_id = #{id}
    order by sort desc
  </select>
</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
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
{
    "status": "200",
    "message": "成功",
    "data": [
        {
            "id": 7,
            "typeName": "0952测试奖励政策",
            "parentTypeName": null,
            "parentId": null,
            "status": 0,
            "icon": "/hello/policytype",
            "sort": 7,
            "createTime": "2021-07-28T01:45:55.000+00:00",
            "updateTime": "2021-07-28T02:06:25.000+00:00",
            "childTypeList": [
                {
                    "id": 5,
                    "typeName": "1739测试啦啊",
                    "parentTypeName": null,
                    "parentId": 7,
                    "status": 0,
                    "icon": null,
                    "sort": 8,
                    "createTime": "2021-07-27T09:40:03.000+00:00",
                    "updateTime": "2021-07-28T02:06:42.000+00:00",
                    "childTypeList": []
                },
                {
                    "id": 4,
                    "typeName": "科技创新",
                    "parentTypeName": null,
                    "parentId": 7,
                    "status": 0,
                    "icon": null,
                    "sort": 4,
                    "createTime": "2021-07-27T09:35:32.000+00:00",
                    "updateTime": "2021-07-27T09:35:33.000+00:00",
                    "childTypeList": []
                }
            ]
        },
        {
            "id": 1,
            "typeName": "0727测试测试",
            "parentTypeName": null,
            "parentId": null,
            "status": 1,
            "icon": null,
            "sort": 1,
            "createTime": "2021-07-27T09:34:25.000+00:00",
            "updateTime": "2021-07-27T09:34:26.000+00:00",
            "childTypeList": [
                {
                    "id": 6,
                    "typeName": "1000测试奖励政策",
                    "parentTypeName": null,
                    "parentId": 1,
                    "status": 0,
                    "icon": "/hello/policytype",
                    "sort": 6,
                    "createTime": "2021-07-28T01:43:05.000+00:00",
                    "updateTime": "2021-07-28T01:54:25.000+00:00",
                    "childTypeList": []
                },
                {
                    "id": 2,
                    "typeName": "0728哈哈哈哈啊哈哈",
                    "parentTypeName": null,
                    "parentId": 1,
                    "status": 1,
                    "icon": null,
                    "sort": 2,
                    "createTime": "2021-07-27T09:34:43.000+00:00",
                    "updateTime": "2021-07-27T09:34:44.000+00:00",
                    "childTypeList": []
                },
                {
                    "id": 8,
                    "typeName": "0953测试奖励政策",
                    "parentTypeName": null,
                    "parentId": 1,
                    "status": 1,
                    "icon": "/hello/policytype",
                    "sort": -1,
                    "createTime": "2021-07-28T01:46:38.000+00:00",
                    "updateTime": "2021-07-28T02:06:42.000+00:00",
                    "childTypeList": []
                }
            ]
        }
    ]
}

用JSON在线工具验证发现结果完全正确，搞定！