Java树形结构

        最近公司有一个需求，在页面展示一个树形菜单栏，里面取的数据不是原数据库存在的已维护的数据，而是在某几张特定表中的表结构数据。取的是表结构中的列名与列注释。类似下图表结构：

        本来是想在mysql中使用类似于oracle的rownum取某部分行数据的,取出列名在11行以后且不为83的列与注释

SELECT column_name,column_comment FROM (SELECT @rownum:=@rownum+1 rownum,column_name,column_comment FROM (SELECT @rownum:=0 rownum,column_name, column_comment,table_name FROM information_schema.COLUMNS WHERE table_schema = '数据库名' AND table_name ='表名' ) t) t WHERE rownum >11 AND rownum <> 83  

        但是这段sql代码在用jdbc使用时一直报错,我一直没找到原因.但是在sql中是可以执行的.最后我们项目负责人说列所在的顺序也是不确定的。新加的列是可以在任意一个位置。但是列开头的为A_,B_,C_...。所以就打算用正则匹配。代码如下，BINARY为强制匹配大写字母，不加的话大小写都能匹配上

SELECT column_name, column_comment FROM information_schema.COLUMNS WHERE table_schema = '数据库名' AND table_name ='表名'  AND column_name  REGEXP BINARY '^[A-Z]\\_'

        添加到此处,我原以为菜单就完成了。但是问题来了，在页面显示太多列对用户体验不好,决定使用树形结构解决这个问题。以"__"进行分割组成树形菜单。

        两种思路，第一个是在程序中使用递归解决,第二种是操作数据库得到树形结构。正是在数据库这个坑中呆了一天,发现无法解决(能力有限)。接着产品经理用C#写了个递归,我将它转为了java代码。看着挺简单的,但是就是没有想到。

        当时想到了几种树组件，如zTree,但是好像需要传入Id与parentId这种东西。一般都是有维护表上下级关系才使用该种树形控件。果断查找jquery无规则的树形控件。

直接上代码

public class RuleTree {
    /// <summary>
    /// 节点列表
    /// </summary>
    private List<RuleTreeNode> NodeList ;

    public RuleTree()
    {
        NodeList = new ArrayList<>();
    }

    /// <summary>
    /// 从数据库构建规则树
    /// </summary>
    /// <param name="dt"></param>
    public static RuleTree CreateRuleTree(List<IndexCustom> dt)
    {
        RuleTree rt = new RuleTree();

        for (IndexCustom row: dt)
        {
            String colName = row.getFieldName();
            String colComment = row.getFieldComment();
            RuleTreeNode node = RuleTreeNode.CreateRuleTreeNode(colName, colComment);
            rt.InsertNodeWithMerge(node);
        }
        return rt;
    }

    /// <summary>
    /// 用合并的方法插入一个节点
    /// </summary>
    /// <param name="node"></param>
    public void InsertNodeWithMerge(RuleTreeNode newNode)
    {
        for (RuleTreeNode existNode : NodeList)
        {
            if (RuleTreeNode.IsSameRoot(existNode, newNode))
            {
                existNode.Merge(newNode);
                return;
            }
        }

        // 没有相同根的节点
        NodeList.add(newNode);
    }

    /// <summary>
    /// 打印
    /// </summary>
    /// <param name="ruleTree"></param>
    public static void PrintRuleTreeNodeList(List<RuleTreeNode> nodeList , int nodeLevel)
    {
        nodeLevel = 0;
        for(RuleTreeNode node : nodeList)
        {
            StringBuilder sb = new StringBuilder();
            sb.append(' '+nodeLevel + 4);
//            sb.Append(' ', nodeLevel + 4);
        }
    }

    public List<RuleTreeNode> getNodeList() {
        return NodeList;
    }

    public void setNodeList(List<RuleTreeNode> nodeList) {
        NodeList = nodeList;
    }
}

public class RuleTreeNode {

    private String name ;

    private List<RuleTreeNode> Children ;

    public RuleTreeNode(String name) {
        this.name = name;
        Children = new ArrayList<>();
    }

    public RuleTreeNode()
    {
        // 空树
        Children = new ArrayList<>();
    }

    /// <summary>
    ///  构建树的节点
    /// </summary>
    /// <param name="leaveName">最后节点的名称</param>
    /// <param name="pathString">路径支付串</param>
    /// <returns></returns>
    public static RuleTreeNode CreateRuleTreeNode(String leaveName, String pathString)
    {
        String[] pathGroup = pathString.split("__");

        return BuildRuleTreeNode(leaveName, pathGroup, 0);
    }

    protected static RuleTreeNode BuildRuleTreeNode(String leaveName, String[] pathGroup, int nodeIndex)
    {
        // 如果没有树路径使用叶作为节点
        if (nodeIndex >= pathGroup.length) return new RuleTreeNode(leaveName);

        RuleTreeNode node = new RuleTreeNode();
        node.name = pathGroup[nodeIndex];

        RuleTreeNode subNode = BuildRuleTreeNode(leaveName, pathGroup, nodeIndex + 1);
        node.Children.add(subNode);

        return node;
    }

    /// <summary>
    /// 把这个节点的子节点和自己的子几点和在一起
    /// </summary>
    /// <param name="newNode"></param>
    public void Merge(RuleTreeNode newNode)
    {

        for (RuleTreeNode subNode : newNode.Children)
        {
            RuleTreeNode existNode = FindSameRootNode(Children, subNode);
            if (existNode == null)
            {
                // 不存在相同的子节点,该节点作为新节点加入
                this.Children.add(subNode);
            }
            else
            {
                // 合并子节点
                existNode.Merge(subNode);
            }
        }
    }

    public RuleTreeNode FindSameRootNode(List<RuleTreeNode> nodeList, RuleTreeNode expectNode)
    {
        for (RuleTreeNode existNode : nodeList)
        {
            if (IsSameRoot(existNode, expectNode)) return existNode;
        }
        return null;
    }

    public static Boolean IsSameRoot(RuleTreeNode existNode, RuleTreeNode newNode)
    {
        return (existNode.name).equals(newNode.name);
    }

    public String getName() {
        return name;
    }

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

    public List<RuleTreeNode> getChildren() {
        return Children;
    }

    public void setChildren(List<RuleTreeNode> children) {
        this.Children = children;
    }
}

调用CreateRuleTree(List<IndexCustom> dt) 传入数据库查到的集合，再将它转为json字符串即可满足很多树形控件的条件。类似于下图的树形结构。多选框其实为叶子节点。根据项目需要才将叶子节点当做多选框展示并能勾选。