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字符串即可满足很多树形控件的条件。类似于下图的树形结构。多选框其实为叶子节点。根据项目需要才将叶子节点当做多选框展示并能勾选。