递归查询多级菜单数据

哎呦喂O_o嗨

已于 2022-08-05 17:43:12 修改

阅读量1.2k

点赞数 3

分类专栏： java日常文章标签： java 前端数据库

于 2022-06-30 15:05:38 首次发布

本文链接：https://blog.csdn.net/weixin_42402326/article/details/125540573

版权

java日常专栏收录该内容

25 篇文章 1 订阅

订阅专栏

1.准备表和数据

DROP TABLE IF EXISTS `sys_menu_test`;
CREATE TABLE `sys_menu_test`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '分类id',
  `name` char(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '分类名称',
  `pId` bigint NULL DEFAULT NULL COMMENT '父分类id',
  `showStatus` tinyint NULL DEFAULT 1 COMMENT '是否显示[0-不显示，1显示]',
  `sort` int NULL DEFAULT 0 COMMENT '排序',
  `icon` char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '图标地址',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `pId_index`(`pId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1437 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '菜单信息表' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of sys_menu_test
-- ----------------------------
INSERT INTO `sys_menu_test` VALUES (1, '中国', 0, 1, 1, NULL);
INSERT INTO `sys_menu_test` VALUES (2, '美国', 0, 1, 2, NULL);
INSERT INTO `sys_menu_test` VALUES (3, '加拿大', 0, 1, 3, NULL);
INSERT INTO `sys_menu_test` VALUES (4, '安徽省', 1, 1, 1, NULL);
INSERT INTO `sys_menu_test` VALUES (5, '江苏省', 1, 1, 2, NULL);
INSERT INTO `sys_menu_test` VALUES (6, '浙江省', 1, 1, 3, NULL);
INSERT INTO `sys_menu_test` VALUES (7, '徐州市', 5, 1, 1, NULL);
INSERT INTO `sys_menu_test` VALUES (8, '合肥市', 4, 1, 1, NULL);
INSERT INTO `sys_menu_test` VALUES (9, '六安市', 4, 1, 2, NULL);
INSERT INTO `sys_menu_test` VALUES (10, '金安区', 9, 1, 1, NULL);
INSERT INTO `sys_menu_test` VALUES (11, '裕安区', 9, 1, 2, NULL);
INSERT INTO `sys_menu_test` VALUES (12, '高新区', 8, 1, 3, NULL);
INSERT INTO `sys_menu_test` VALUES (13, '孙岗镇', 10, 1, 1, NULL);
INSERT INTO `sys_menu_test` VALUES (14, '椿树镇', 10, 1, 2, NULL);
INSERT INTO `sys_menu_test` VALUES (15, '蜀西湖', 12, 1, 3, NULL);
INSERT INTO `sys_menu_test` VALUES (16, '杭州市', 6, 1, 1, NULL);
INSERT INTO `sys_menu_test` VALUES (17, '西湖区', 16, 1, 1, NULL);
INSERT INTO `sys_menu_test` VALUES (18, '灵隐寺', 17, 1, 1, NULL);

2.创建实体类

package com.mozheng.manageframe.entity;

import lombok.Data;
import lombok.ToString;

import java.util.List;

/**
 * @Description: 菜单实体类
 * @Author yizhixiansheng
 * @Date 2021/6/30
 */
@Data
@ToString
public class MenuTest{

    private static final long serialVersionUID = 1L;

    /**
     * 菜单id
     */
    private Long id;

    /**
     * 菜单名称
     */
    private String name;

    /**
     * 父菜单id
     */
    private Long pId;

    /**
     * 是否显示[0-不显示，1显示]
     */
    private Integer showStatus;

    /**
     * 排序
     */
    private Integer sort;

    /**
     * 图标地址
     */
    private String icon;

    /**
     * 子菜单集合
     */
    private List<MenuTest> children;
}

3.创建sql查询语句的xml文件

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.mozheng.manageframe.mapper.MenuMapper">
    <!-- 查询全部 -->
    <select id="listInfo" resultType="com.mozheng.manageframe.entity.MenuTest">
		select id,`name`,pId,showStatus,sort,icon from sys_menu_test
	</select>
</mapper>

4.创建mapper接口

package com.mozheng.manageframe.mapper;

import com.mozheng.manageframe.entity.MenuTest;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;

@Mapper
public interface MenuMapper {
    List<MenuTest> listInfo();
}

5.创建业务层接口

package com.mozheng.manageframe.service;

import com.mozheng.manageframe.entity.MenuTest;
import java.util.List;

public interface MenuService {
    List<MenuTest> listInfo();
}

6.创建业务层接口实现类

package com.mozheng.manageframe.service.Impl;

import com.mozheng.manageframe.entity.MenuTest;
import com.mozheng.manageframe.mapper.MenuMapper;
import com.mozheng.manageframe.service.MenuService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resource;
import java.util.*;
import java.util.stream.Collectors;

@Service
@Transactional
public class MenuServiceImpl implements MenuService {
    /**
     * 方法一
     **/
    @Override
    public List<MenuTest> listInfo() {
        // 获取所有菜单
        List<MenuTest> allMenu = menuMapper.listInfo();
        // 获取所有父节点为0的节点，为根节点
        List<MenuTest> rootMenu = allMenu
                .stream()
                .filter(item -> item.getPId().equals(0L))
                .collect(Collectors.toList());
        // 为根菜单设置子菜单
        rootMenu.forEach(menuTest -> menuTest.setChildren(getChildList(menuTest.getId(), allMenu)));
        // 根据顺序字段排序
        rootMenu.sort(Comparator.comparing(MenuTest::getSort));
        return rootMenu;
    }

    /**
     * 方法一
     * 根据 id，获取其子节点集合
     * @param id 父节点id
     * @param allMenu 所有菜单列表
     * @return java.util.List<com.mozheng.manageframe.entity.MenuTest>
     * @author yizhixiansheng
     * @create 2021/6/30
     **/
    private List<MenuTest> getChildList(Long id, List<MenuTest> allMenu) {
        // 获取传过来的节点下的所有子节点
        List<MenuTest> childList = allMenu
                .stream()
                .filter(menuTest ->  menuTest.getPId().equals(id))
                .collect(Collectors.toList());
        // 当传过来的节点下没有子节点，则退出递归
        if (Objects.isNull(childList) || childList.size() <= 0) {
            return new ArrayList<>();
        }
        // 继续递归
        childList.forEach(menuTest -> menuTest.setChildren(getChildList(menuTest.getId(), allMenu)));
        // 根据顺序字段排序
        childList.sort(Comparator.comparing(MenuTest::getSort));
        return childList;
    }

	/**
     * 方法二
     **/
	@Override
    public List<MenuTest> listInfo() {
        List<MenuTest> menuTestList = menuMapper.listInfo();
        return transformData(menuTestList ,0L);
    }

	/**
	
     * 方法二
     * 转换为树结构
     * @param allList数据集合
     * @param rootId 根目录id
     * @return java.util.List<com.mozheng.manageframe.entity.MenuTest>
     * @author yizhixiansheng
     * @create 2021/7/15
     **/
    public static List<MenuTest> transformData(List<MenuTest> allList, Long rootId){
        List<MenuTest> childList = new LinkedList<>();
        allList.forEach(menuTest -> {
            if (menuTest .getPId().equals(rootId)){
                menuTest .setChildren(transformData(allList,menuTest.getId()));
                childList.add(menuTest );
            }
        });
        return childList;
    }

}

7.创建测试类

package com.mozheng.manageframe;

import com.alibaba.fastjson.JSONObject;
import com.mozheng.manageframe.entity.Menu;
import com.mozheng.manageframe.entity.MenuTest;
import com.mozheng.manageframe.service.MenuService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;

@SpringBootTest
class ManageframeApplicationTests {

    @Autowired
    private MenuService menuService;

    @Test
    void contextLoads() {
        List<MenuTest> menuTestList = menuService.listInfo();
        System.out.println(JSONObject.toJSON(menuTestList));
    }

}

看结果

[{"children":[{"children":[{"children":[{"children":[{"children":[],"name":"蜀西湖","pId":12,"showStatus":1,"id":15,"sort":3}],"name":"高新区","pId":8,"showStatus":1,"id":12,"sort":3}],"name":"合肥市","pId":4,"showStatus":1,"id":8,"sort":1},{"children":[{"children":[{"children":[],"name":"孙岗镇","pId":10,"showStatus":1,"id":13,"sort":1},{"children":[],"name":"椿树镇","pId":10,"showStatus":1,"id":14,"sort":2}],"name":"金安区","pId":9,"showStatus":1,"id":10,"sort":1},{"children":[],"name":"裕安区","pId":9,"showStatus":1,"id":11,"sort":2}],"name":"六安市","pId":4,"showStatus":1,"id":9,"sort":2}],"name":"安徽省","pId":1,"showStatus":1,"id":4,"sort":1},{"children":[{"children":[],"name":"徐州市","pId":5,"showStatus":1,"id":7,"sort":1}],"name":"江苏省","pId":1,"showStatus":1,"id":5,"sort":2},{"children":[{"children":[{"children":[{"children":[],"name":"灵隐寺","pId":17,"showStatus":1,"id":18,"sort":1}],"name":"西湖区","pId":16,"showStatus":1,"id":17,"sort":1}],"name":"杭州市","pId":6,"showStatus":1,"id":16,"sort":1}],"name":"浙江省","pId":1,"showStatus":1,"id":6,"sort":3}],"name":"中国","pId":0,"showStatus":1,"id":1,"sort":1},{"children":[],"name":"美国","pId":0,"showStatus":1,"id":2,"sort":2},{"children":[],"name":"加拿大","pId":0,"showStatus":1,"id":3,"sort":3}]

在这里插入图片描述

彩蛋来了
使用MySQL8的with recursive实现递归

// 根据id获取所有父级
with recursive temp as
(
	select * from sys_dept where dept_id = '106'
	union all
	select ta.* from sys_dept ta join temp on ta.dept_id = temp.parent_id
)
select * from temp

// 根据id获取所有子集
with recursive temp as
(
	select * from sys_dept where dept_id = '106'
	union all
	select ta.* from sys_dept ta join temp on ta.parent_id = temp.dept_id
)
select * from temp