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