今天,我做了一个关于web树的结构,老师讲过之后,我听明白了,写出了一个Demo,在这里跟大家分享一下,这个web树的shi实现主要是shu'数据结构算法设计的巧妙,理解了这种数据库结构结构,也就会写各种树状结构了。废话不多说,操作操作步骤如下:
1.创建web工程,做实体,创建数据库和表,搭建好环境,做出Utils工具类,可以获取JDBC连接。
create table category
(
id varchar(40) primary key,
name varchar(100),
lft int,
rgt int
);
insert into category values('1','汽车',1,20);
insert into category values('2','奥迪',2,7);
insert into category values('3','奔驰',8,13);
insert into category values('4','兰博基尼',14,19);
insert into category values('5','奥迪A4',3,4);
insert into category values('6','奥迪A6',5,6);
insert into category values('7','宝马E系列',9,10);
insert into category values('8','宝马S系列',11,12);
insert into category values('9','兰博基尼suv',15,16);
insert into category values('10','兰博基尼Aventador',17,18);
问题:为了在页面中显示树状结构,需要得到所有结点,以及每个结点在树中的层次:
解决思路:
- 要得到结点的层次,就是看节点有几个父亲,例如奥迪A4有2个父亲,则它所在层次就为2。
- 如何知道每一个节点有几个父亲呢?这个表有个特点,父亲和孩子都在同一个表中,为得到父亲所有的孩子,可以把这张表想像成两张表,一张用于保存父亲,一张表保存孩子,如下所示:
select * from category parent,category child;
父亲下面的孩子有个特点,它的左值>父亲的左值,并且<父亲的右值,如下所示
select * from category parent,category child where child.lft>=parent.lft and child.rgt<=parent.rgt;
- 以上语句会得到父亲下面所有的孩子。
- 对父亲所有孩子的姓名进行归组,然后使用count统计函数,这时就会知道合并了几个孩子,合并了几个孩子姓名,这个孩子就有几个父亲,从而知道它所在的层次
select 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;
- 最后根据左值排序即可
select 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;
2.根据三层开发思想设计,Dao、Service和Servlet。
3.在Dao中定义方法,service进行业务逻辑处理,Servlet进行实现。
图示如下:
代码如下:
(1) ListTreeServlet
package com.nyist.cn.Servlet;
import java.io.IOException;
import java.util.List;import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import com.nyist.cn.Service.TreeService;
import com.nyist.cn.model.Tree;public class ListTreeServlet extends HttpServlet{
@Override
public void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
TreeService service = new TreeService();
List<Tree> list = service.getAllTree();
request.getSession().setAttribute("list",list);
request.getRequestDispatcher("/listtree.jsp").forward(request, response);
}
}(2)TreeDao
public class TreeDao {
public List<Tree> getAll(){
try {
QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
String sql="select child.id,child.name,child.lft,child.rgt,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;";
List<Tree> list = (List<Tree>)runner.query(sql, new BeanListHandler(Tree.class));
return list;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException();
}
}
}3.listree.jsp
<html>
<head>
<title>显示树</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath }/css/xtree.css">
<script type="text/javascript" src="${pageContext.request.contextPath }/js/xtree.js"></script>
</head>
<body>
<script type="text/javascript">
<c:forEach var="c" items="${list}">
<c:if test='${c.depth==1 }'>
var tree = new WebFXTree('${c.name}');
</c:if>
<c:if test='${c.depth==2}'>
var node${c.depth} = new WebFXTreeItem('${c.name}');
tree.add(node${c.depth});
</c:if>
<c:if test="${c.depth>2}">
var node${c.depth} = new WebFXTreeItem('${c.name}');
node${c.depth-1}.add(node${c.depth});
</c:if>
</c:forEach>
document.write(tree);
</script>
</body>
</html>
效果如下图:
*注意:其中需要导入css和js空间,可以可以去我的资源里边下载,我上传了
源码下载:下载