java递归查询数据库树

先查询第一层的数据，然后调用递归循环第一层的数据，查询父Id等于第一层的Id，执行完成后第一层一下的所有数据就全部查询出来了。。。

场景描述 ：每个应用（最顶层，树的根） 下面是多级菜单  每个菜单下是多个按钮（最下层，叶子结点）

关联关系 id和pid

根节点 pid为空，可根据type=APP查询出根节点

查询出所有根节点，然后递归查出子孙节点

Menu 的 List<Menu> list 是该节点的子节点

 

public  List<Menu> getTreeList(String topId) {
        EntityWrapper wrapper = new EntityWrapper<Menu>();
        wrapper.eq("menu_type","CORP");
        wrapper.eq("type","APP");
        if(StringUtils.isNotBlank(topId)){
            wrapper.eq("id",topId);
        }
        List<Menu> firstmenus = this.selectList(wrapper);
        List<Menu> trees = getSubList(firstmenus);
        return trees;
    }
    private   List<Menu> getSubList(List<Menu> entityList) {
        for (int i = 0; i < entityList.size(); i++) {
            List<Menu> firstmenus = this.selectList(new EntityWrapper<Menu>().eq("pid",entityList.get(i).getId()).eq("menu_type","CORP"));
            if(firstmenus != null && firstmenus.size()>0){
                List<Menu> list2= this.selectList(new EntityWrapper<Menu>().eq("pid",entityList.get(i).getId()).eq("menu_type","CORP"));
                entityList.get(i).setList(list2);
                getSubList(entityList.get(i).getList());
            }
        }
        return entityList;
    }

 

树节点TreeNode 类 以便ztree渲染

package com.ps.uzkefu.util;

import java.util.Collections;
import java.util.Comparator;
import java.util.List;

/**
 * 展示类别树用的树节点类
 * <p/>
 * WuZhiWei
 * 2014-5-19
 * 添加类说明以及属性注释
 */
public class TreeNode {
    /**
     * 节点id
     */
    private String id;
    /**
     * 父节点id
     */
    private String pId;
    /**
     * 节点内容
     */
    private String name;
    /**
     * 是否展开
     */
    private String open="false";

    private boolean childOuter=false;
    private boolean dropRoot=false;
//    是否是父节点
    private boolean isParent=false;
    /**
     * 不可移动成为父节点
     */
    private boolean isEnd = false ;
    /**
     * 不可移动成为根节点
     */
    private boolean nocheck= false ;

    private int level=0;
//   是否被选中
    private boolean checked=false;

    private boolean halfCheck=false;

    private boolean chkDisabled=false;


    private int other=0;

    private List<TreeNode> children;

    private Integer orderNum;//排序数字

    private Boolean leaf = true;//是否是最底层的叶子结点

    public TreeNode(String id, String pId, String name, String open, Integer orderNum) {
        this.id = id;
        this.pId = pId;
        this.name = name;
        this.open = open;
        this.orderNum = orderNum;
    }

    public TreeNode() {
    }

    public List<TreeNode> getChildren() {
        return children;
    }

    public void setChildren(List<TreeNode> children) {
        Collections.sort(children,new Comparator<TreeNode>() {
            @Override
            public int compare(TreeNode o1, TreeNode o2) {
                // 返回值为int类型，大于0表示正序，小于0表示逆序
                return o1.getOrderNum()-o2.getOrderNum();
            }
        });
        this.children = children;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getpId() {
        return pId;
    }

    public void setpId(String pId) {
        this.pId = pId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getOpen() {
        return open;
    }

    public void setOpen(String open) {
        this.open = open;
    }

    public boolean isParent() {
        return isParent;
    }

    public void setParent(boolean isParent) {
        this.isParent = isParent;
    }

    public boolean isEnd() {
        return isEnd;
    }

    public void setEnd(boolean isEnd) {
        this.isEnd = isEnd;
    }

    public boolean isNocheck() {
        return nocheck;
    }

    public void setNocheck(boolean nocheck) {
        this.nocheck = nocheck;
    }

    public int getLevel() {
        return level;
    }

    public void setLevel(int level) {
        this.level = level;
    }

    public boolean isChecked() {
        return checked;
    }

    public void setChecked(boolean checked) {
        this.checked = checked;
    }

    public boolean isHalfCheck() {
        return halfCheck;
    }

    public void setHalfCheck(boolean halfCheck) {
        this.halfCheck = halfCheck;
    }

    public boolean isChkDisabled() {
        return chkDisabled;
    }

    public void setChkDisabled(boolean chkDisabled) {
        this.chkDisabled = chkDisabled;
    }


    public int getOther() {
        return other;
    }

    public void setOther(int other) {
        this.other = other;
    }

    public boolean isChildOuter() {
        return childOuter;
    }

    public void setChildOuter(boolean childOuter) {
        this.childOuter = childOuter;
    }

    public boolean isDropRoot() {
        return dropRoot;
    }

    public void setDropRoot(boolean dropRoot) {
        this.dropRoot = dropRoot;
    }

    public Integer getOrderNum() {
        return orderNum;
    }

    public void setOrderNum(Integer orderNum) {
        this.orderNum = orderNum;
    }

    public Boolean getLeaf() {
        return leaf;
    }

    public void setLeaf(Boolean leaf) {
        this.leaf = leaf;
    }
}

 

3、菜单类Menu

package com.ps.uzkefu.apps.oms.account.entity;

import com.baomidou.mybatisplus.annotations.TableField;
import com.baomidou.mybatisplus.activerecord.Model;
import com.baomidou.mybatisplus.annotations.TableName;
import com.ps.uzkefu.base.BaseEntity;
import lombok.Getter;
import lombok.Setter;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * <p>
 * 菜单,权限配置说明：
 *   1.菜单分为两种类型，
 *   （1）页面跳转权限即 路由权限、如果是按钮需配置 module privilege，在页面按钮上添加class permission,添加属性：module-privilege="callcenter-playVoice"
 *   （2）api请求权限，需在对应controller方法添加     @Permission(module = "crm",privilege = "saveOrUpdate"),
 *        如果一个controller方法被多出应用，则module值需一致，且privilege值可用逗号分隔
 * </p>
 *
 * @author WuZhiWei
 * @since 2018-06-22
 */
@Setter
@Getter
@TableName("t_oms_menu")
public class Menu extends BaseEntity<Menu> {

    private static final long serialVersionUID = 1L;

    /**
     * 菜单类型 商家（CORP）、平台（PLATFORM） 
     */
    @TableField("menu_type")
    private String menuType;
    /**
     * 菜单名称（与视图的文件夹名称和路由路径对应），如：system
     */
    private String name;
    /**
     * 菜单标题 如：系统设置
     */
    private String title;
    /**
     * 菜单图标样式 如：layui-icon-component
     */
    private String icon;
    /**
     * 菜单路由地址，默认按照 name 解析。一旦设置，将优先按照 jump 设定的路由跳转
     */
    private String jump;
    /**
     * 是否默认展子菜单，默认不展开 0不展开 1展开
     */
    private Integer spread;
    /**
     * 顺序
     */
    @TableField("show_order")
    private Integer showOrder;
    /**
     * 父级菜单id
     */
    private String pid;

    private String module; //权限所属模块
    private String privilege; //权限

    /**
     *  类型：（应用：APP、菜单：MENU、按钮：BUTTON）
     */
    private String type;
    /**
     *  应用描述
     */
    private String description;
    /**
     *  封面图
     */
    @TableField("logo_url")
    private String logoUrl;
    /**
     *  标签(热销：HOT、内测中：BETA)
     */
    private String sign;
    /**
     *  指导价
     */
    @TableField("guide_price")
    private BigDecimal guidePrice;
    /**
     *  售价
     */
    private BigDecimal price;
    /**
     *  详情内容
     */
    private String detail;
    /**
     *  是否默认开通（0 不开通 1开通）
     */
    private Boolean open;
    /**
     *  状态（上架：ONSALE、下架：OFFSALE）
     */
    private String status;
    /**
     *  浏览数
     */
    @TableField("view_count")
    private Integer viewCount;
    /**
     *  购买企业数
     */
    @TableField("buy_count")
    private Integer buyCount;

    
    @TableField(exist = false)
    private List<Menu> list;

    @TableField(exist = false)
    private Boolean isExpire;

    @TableField(exist = false)
    private Boolean isBought;

    @TableField(exist = false)
    private String expireTimeText;

    public Menu() {
    }

    public Menu(String id, String corpCode, Integer isDel, String creater,
                Date createTime, Date lastModifyTime, String lastModify,
                String menuType, String name, String title, String icon,
                String jump, Integer spread, Integer showOrder, String pid,
                String module, String privilege) {
        this.id = id;
        this.corpCode = corpCode;
        this.isDel = isDel;
        this.creater = creater;
        this.createTime = createTime;
        this.lastModifyTime = lastModifyTime;
        this.lastModify = lastModify;
        this.menuType = menuType;
        this.name = name;
        this.title = title;
        this.icon = icon;
        this.jump = jump;
        this.spread = spread;
        this.showOrder = showOrder;
        this.pid = pid;
        this.module = module;
        this.privilege = privilege;
    }
}

 

菜单转为TreeNode

 

 /**
     * 获取根节点
     * @param appid 根节点id集合
     * @param checkedIds  选中节点的id集合
     * @return
     */
    public  List<TreeNode> getTreeListAsTreeNode(List<String> appid,List<String> checkedIds) {
        EntityWrapper wrapper = new EntityWrapper<Menu>();
        wrapper.eq("menu_type","CORP");
        wrapper.eq("type","APP");
        if(appid !=null && appid.size()>0){
            wrapper.in("id",appid);
        }
        List<Menu> apps = this.selectList(wrapper);
        List<TreeNode> treeNodes =menuListToTreeNodeList(apps,checkedIds);
        List<TreeNode> trees = getSubListAsTreeNode(treeNodes,checkedIds);
        return trees;
    }

    /**
     * 递归渲染树
     * @param rootTreeNodes  树的根节点
     * @param checkedIds 选中节点的id集合
     * @return
     */
    private   List<TreeNode> getSubListAsTreeNode(List<TreeNode> rootTreeNodes,List<String> checkedIds) {
        for (TreeNode rootTreeNode:rootTreeNodes) {
            List<Menu> firstMenus = this.selectList(new EntityWrapper<Menu>().eq("pid",rootTreeNode.getId()).eq("menu_type","CORP"));
            if(firstMenus != null && firstMenus.size()>0){
                List<Menu> secondMenus= this.selectList(new EntityWrapper<Menu>().eq("pid",rootTreeNode.getId()).eq("menu_type","CORP"));
                List<TreeNode> treeNodes = menuListToTreeNodeList(secondMenus,checkedIds);
                rootTreeNode.setChildren(treeNodes);
                getSubListAsTreeNode(rootTreeNode.getChildren(),checkedIds);
            }
        }
        return rootTreeNodes;
    }

    /**
     * 菜单集合转为树集合 以便 ztree渲染
     * @param menus
     * @param checkedIds 选中节点的id集合
     * @return
     */
    private List<TreeNode> menuListToTreeNodeList(List<Menu> menus,List<String> checkedIds){
        List<TreeNode> treeNodes = new ArrayList<>();
       menus.forEach(menu -> {
           treeNodes.add(setCheckStatus(menuToTreeNode(menu),checkedIds));
       });
        return  treeNodes;
    }

    /**
     * 菜单 转为树节点 以便ztree渲染
     * @param menu
     * @return
     */
    private TreeNode menuToTreeNode(Menu menu){
        TreeNode treeNode = new TreeNode();
        if (Objects.equals(menu.getType(),UkConstant.MENU_TYPE_BUTTON)){
            treeNode.setLeaf(true);
            treeNode.setParent(false);
        }else {
            treeNode.setParent(true);
            treeNode.setLeaf(false);
        }
        treeNode.setpId(menu.getPid());
        treeNode.setId(menu.getId());
        treeNode.setName(menu.getTitle());
        treeNode.setOpen("true");
        treeNode.setOrderNum(menu.getShowOrder());
        return treeNode;
    }

    /**
     *   根据选中节点的集合设置树节点的选中状态
     * @param treeNode
     * @param checkedIds
     * @return
     */
    private TreeNode setCheckStatus(TreeNode treeNode,List<String> checkedIds){
        if (checkedIds != null && checkedIds.size()>0){
            if (checkedIds.contains(treeNode.getId())){
                treeNode.setChecked(true);
            }
        }
        return  treeNode;
    }

经过一番思考 ，由于在递归中查询数据库，性能较差，改变思路

由于菜单表是树结构，表中有10个树的根节点，先查询出所有数据 

封装成10棵树，然后再去掉不需要的树 代码如下

 

 @Override
    public List<TreeNode> getMenuByCorpCodeAndRoleId(String corpCode, String roleId) {
        Assert.notNull(corpCode);
        Assert.notNull(roleId);
        //获取所有应用
        List<Menu> apps = this.selectList(new EntityWrapper<Menu>().eq("menu_type", UkConstant.CUSTOMER_TYPE_CORP).eq("type", UkConstant.MENU_TYPE_APP).orderBy("show_order"));
        //获取该公司购买的应用
        EntityWrapper<MenuCorp> wrapper = new EntityWrapper<MenuCorp>();
        wrapper.eq(ExecutionContext.CORP_CODE, corpCode).gt("expire_time", DateUtil.convert(new Date(), DateUtil.DATE_PATTERN_yyyy_MM_dd_HH_MM_ss));
        List<MenuCorp> corpApps = menuCorpService.selectList(wrapper);
        List<Menu> buyApps = new ArrayList<>();
        List<String> buyAppIds = new ArrayList<>(buyApps.size());
        for (MenuCorp corpApp : corpApps) {
            buyAppIds.add(corpApp.getMenuId());
        }

        for (Menu menu : apps) {
            if (buyAppIds.contains(menu.getId())) {
                buyApps.add(menu);
            }
        }

        List<RoleMenu> roleMenus = roleMenuService.selectList(new EntityWrapper<RoleMenu>().eq("role_id", roleId));
        List<String> checkIds = new ArrayList<>();
        for (RoleMenu rolemenu : roleMenus) {
            checkIds.add(rolemenu.getMenuId());
        }
        List<Menu> allMenus = this.selectList(new EntityWrapper<Menu>().orderBy("show_order"));
        List<TreeNode> treeNodes = menuToTreeNodes(allMenus,checkIds);
        List<TreeNode> tree = TreeUtils.formatTree(treeNodes,true,false);
        List<TreeNode> result = new ArrayList<>(buyAppIds.size());
        tree.forEach( treeNode -> {
            if (buyAppIds.contains(treeNode.getId())){
               result.add(treeNode);
            }
        });
        return result;
    }


   

    /**
     * 菜单集合转为树集合 以便 ztree渲染
     *
     * @param menus
     * @param checkedIds 选中节点的id集合
     * @return
     */
    private List<TreeNode> menuToTreeNodes(List<Menu> menus, List<String> checkedIds) {
        List<TreeNode> treeNodes = new ArrayList<>();
        menus.forEach(menu -> {
            treeNodes.add(menuToTreeNode(menu,checkedIds));
        });
        return treeNodes;
    }

    /**
     * 菜单 转为树节点 以便ztree渲染
     *
     * @param menu
     * @return
     */
    private TreeNode menuToTreeNode(Menu menu,List<String> checkedIds) {
        TreeNode treeNode = new TreeNode();
        if (Objects.equals(menu.getType(), UkConstant.MENU_TYPE_BUTTON)) {
            treeNode.setLeaf(true);
            treeNode.setParent(false);
        } else {
            treeNode.setParent(true);
            treeNode.setLeaf(false);
        }

        treeNode.setpId(menu.getPid());
        treeNode.setId(menu.getId());
        treeNode.setName(menu.getTitle());
        treeNode.setOpen("true");
        treeNode.setOrderNum(menu.getShowOrder());
        if (CollectionUtils.isNotEmpty(checkedIds) && checkedIds.contains(treeNode.getId())) {
            treeNode.setChecked(true);
        }
        return treeNode;
    }