无须递归的高性能树结构的设计（从使用mysql数据库储存到使用ztree网页输出）

最新推荐文章于 2022-12-04 15:48:31 发布

hao_vip

最新推荐文章于 2022-12-04 15:48:31 发布

阅读量3.5k

点赞数

本文链接：https://blog.csdn.net/hao_vip/article/details/79321137

版权

最后效果图是这样的（看起来有点丑，只是没有美化，效果还是不错的）：

一、树结构的数据库储存表设计：

　　树结构在数据库储存一般会使用加内关联键的方式，表看起来如下：

但是用这种表结构查询时总是要迭代，如果运用树结构的特点来建表，则查询和数据处理会好得多，具体表结构看起来如下：

　　数据库建表语句为：

create table category
(
    id varchar(30) primary key,
    name varchar(30),
    lft int unique,
    rgt int unique
);

　获取树结构的查询语句为：

select child.id,child.name,count(child.name) depth from category parent,category child where 
child.lft>=parent.lft and child.rgt<=parent.rgt group by child.name order by child.lft;

　得到的结果为：

　　让后将表格封装为对象，domain与dao的代码有：

class Category {
    private String id;
    private String name;
    private int lft;
    private int rgt;
    private int depth;
...........

public class CategoryDao {

    private QueryRunner qr = new QueryRunner(DaoUtils.getDataSource());

    public void add(Category category) {
        try {
            //先更新，使所有let与rgt>=category.lft的都加2，为插入提供位置
            String sql = "update category set rgt=rgt+2 where rgt>=?";
            qr.update(sql, category.getLft());
            sql = "update category set lft=lft+2 where lft>=?";
            qr.update(sql,category.getLft());
            //插入
            sql = "insert into category(id,name,lft,rgt) values(?,?,?,?)";
            qr.update(sql, category.getId(), category.getName(), category.getLft(), category.getRgt());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //删除一个节点，以及其下面所有子节点
    public void delete(String id) {
        try {
            Category category = find(id);
            String sql = "delete from category where lft>=? and rgt<=?";
            qr.update(sql, category.getLft(),category.getRgt());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public Category find(String id) {
        String sql = "select name,lft,rgt from category where id=?";
        try {
            Category category = qr.query(sql, new BeanHandler<Category>(Category.class), id);
            category.setId(id);
            return category;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public List<Category> getTree(){
        String sql = "select child.id,child.name,count(child.name) depth from category parent,category child where child.lft>=parent.lft and child.rgt<=parent.rgt group by child.name order by child.lft";
        try {
            List<Category> categories = qr.query(sql, new BeanListHandler<Category>(Category.class));
            return categories;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

最后sevlet调用jsp输出即可：

 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        List<Category> tree = new TreeService().getTree();
        request.setAttribute("tree",tree);
        request.getRequestDispatcher(request.getContextPath()+"/WEB-INF/jsp/tree.jsp").forward(request,response);
    }

<%@ page import="com.hao.domain.Category" %>
<%@ page import="java.util.List" %>
<%@ page import="java.util.ArrayList" %><%--
  Created by IntelliJ IDEA.
  User: 28602
  Date: 2018/2/10
  Time: 16:32
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<HTML>
<HEAD>
    <TITLE> ZTREE DEMO </TITLE>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
    <link rel="stylesheet" href="../css/zTreeStyle/zTreeStyle.css" type="text/css">
    <style>
        body {
            background-color: white;
            margin: 0;
            padding: 0;
            text-align: center;
        }

        div, p, table, th, td {
            list-style: none;
            margin: 0;
            padding: 0;
            color: #333;
            font-size: 12px;
            font-family: dotum, Verdana, Arial, Helvetica, AppleGothic, sans-serif;
        }

        #testIframe {
            margin-left: 10px;
        }
    </style>
    <script type="text/javascript" src="../js/jquery-1.4.4.min.js"></script>
    <script type="text/javascript" src="../js/jquery.ztree.core.js"></script>
    <SCRIPT type="text/javascript">

        var zTree;
        var demoIframe;

        var setting = {
            view: {
                dblClickExpand: false,
                showLine: true,
                selectedMulti: false
            },
            data: {
                simpleData: {
                    enable: true,
                    idKey: "id",
                    pIdKey: "pId",
                    rootPId: ""
                }
            },
            callback: {
                beforeClick: function (treeId, treeNode) {
                    var zTree = $.fn.zTree.getZTreeObj("tree");
                    if (treeNode.isParent) {
                        zTree.expandNode(treeNode);
                        return false;
                    } else {
                        demoIframe.attr("src", treeNode.file + ".html");
                        return true;
                    }
                }
            }
        };

        var zNodes = [
            <%
            //元素自上而下遍历，list作为储存可能插入元素的集合，其index为深度
            List<Category> list = new ArrayList<>();
            Category category = new Category();
            category.setId("0");
            list.add(0,category);
            request.setAttribute("list",list);
            %>
            <c:forEach var="ca" items="${tree}">
            <%
            Category ca =(Category) pageContext.getAttribute("ca");
            list.add(ca.getDepth(),ca);
            %>
            {id:${ca.id}, pId: ${list[ca.depth-1].id}, name: "${ca.name}", open: true},
            </c:forEach>
        ];

        $(document).ready(function () {
            var t = $("#tree");
            t = $.fn.zTree.init(t, setting, zNodes);
            demoIframe = $("#testIframe");
            demoIframe.bind("load", loadReady);
            var zTree = $.fn.zTree.getZTreeObj("tree");
            zTree.selectNode(zTree.getNodeByParam("id", 101));

        });

        function loadReady() {
            var bodyH = demoIframe.contents().find("body").get(0).scrollHeight,
                htmlH = demoIframe.contents().find("html").get(0).scrollHeight,
                maxH = Math.max(bodyH, htmlH), minH = Math.min(bodyH, htmlH),
                h = demoIframe.height() >= maxH ? minH : maxH;
            if (h < 530) h = 530;
            demoIframe.height(h);
        }

    </SCRIPT>
</HEAD>

<BODY>
<TABLE border=0 height=600px align=left>
    <TR>
        <TD width=500px align=left valign=top style="BORDER-RIGHT: #999999 ">
            <ul id="tree" class="ztree" style="width:260px; overflow:auto;"></ul>
        </TD>
    </TR>
</TABLE>

</BODY>
</HTML>

jsp输出使用到了ztree框架下载地址： zTree