数据库_jdbc_web树

最新推荐文章于 2022-05-21 10:25:44 发布

pre_eminent

最新推荐文章于 2022-05-21 10:25:44 发布

阅读量1.3k

点赞数

分类专栏：数据库文章标签：数据库 web树

本文链接：https://blog.csdn.net/pre_eminent/article/details/17678165

版权

数据库专栏收录该内容

18 篇文章 1 订阅

订阅专栏

概述

WebTree的实现原理:
Web树的数据库表设计:
		mysql -uroot -proot
		set character_set_client=gb2312;
		set character_set_results=gb2312;
		use day17;
		create table category
		(
			id varchar(40) primary key,
			name varchar(100),
			lef int,
			rig int
		);
		insert into category values('1','商品',1,18);
		insert into category values('2','平板电视',2,7);
		insert into category values('3','冰箱',8,11);
		insert into category values('4','笔记本',12,17);
		insert into category values('5','长虹',3,4);
		insert into category values('6','索尼',5,6);
		insert into category values('7','西门子',9,10);
		insert into category values('8','thinkpad',13,14);
		insert into category values('9','Dell',15,16);
查询语句:
		select * from category parent,category child ;
		返回9X9=81行数据
		select * from category parent,category child where parent.lef<child.lef and parent.rig>child.rig
		返回13行数据
		select * from category parent,category child where parent.lef<=child.lef and parent.rig>=child.rig
		返回22行数据
		select child.*,count(child.name) depth from category parent,category child 
		where parent.lef<=child.lef and parent.rig>=child.rig group by child.name order by child.lef;
		返回9条数据分别是:
		ID,name,左值,右值,depth		
		"1","商品",1,18,1
		"2","平板电视",2,7,2
		"5","长虹",3,4,3
		"6","索尼",5,6,3
		"3","冰箱",8,11,2
		"7","西门子",9,10,3
		"4","笔记本",12,17,2
		"8","thinkpad",13,14,3
		"9","Dell",15,16,3
		mysql> select child.*,count(child.name) depth from category parent,category child where parent.lef<=child.lef and parent.rig>=child.rig group by child.name order by child.lef;
		+----+----------+------+------+-------+
		| id | name     | lef  | rig  | depth |
		+----+----------+------+------+-------+
		| 1  | 商品        |    1 |   18 |     1 |
		| 2  | 平板电视        |    2 |    7 |     2 |
		| 5  | 长虹         |    3 |    4 |     3 |
		| 6  | 索尼         |    5 |    6 |     3 |
		| 3  | 冰箱         |    8 |   11 |     2 |
		| 7  | 西门子         |    9 |   10 |     3 |
		| 4  | 笔记本       |   12 |   17 |     2 |
		| 8  | thinkpad |   13 |   14 |     3 |
		| 9  | Dell     |   15 |   16 |     3 |
      +----+----------+------+------+-------+
      9 rows in set (0.00 sec)
新建一个类:Category接收上面查询的语句:
      成员String id,String name,int lef,int rig,int depth
      分别生成getter setter方法
ListWebTreeServlet调用Cservice,Cservice调用CategoryDao,
      CategoryDao利用QueryRunner 将sql发送数据库,取出所有归组,排序后的记录逐一存入Category对象,
      最后添加到list集合,最终由ListWebTreeServlet存入request域,转发给listwebtree.jsp显示
      jsp显示树,生成树,可以使用jquery实现,也可以使用xtree这个js控件
重点在listwebtree.jsp,导入jstl标签库(两个jar),taglib引入,拷贝xtree.js到js文件夹,xtree.css到css文件夹,
      拷贝要用到的images到images文件夹
      更改xtree.js中图片的引用路径
      在jsp<head>标签中引入xtree.css和xtree.js
      在<body>中的javasrcipt代码中用foreach迭代
       <body>
    <script type="text/javascript">
        
     <c:forEach var="cat" items="${list}">
           //如果层次为1,则为商品类别的根节点!
           <c:if test="${cat.depth==1}">
              var tree = new WebFXTree('${cat.name }');
           </c:if>
           //如果层次为2,则添加在根节点下面
           <c:if test="${cat.depth==2}">
              var node${cat.depth}= new WebFXTreeItem('${cat.name }');
              tree.add(node${cat.depth});
              
           </c:if>
           //如果层次为大于2,则添加在其父节点的下面
           <c:if test="${cat.depth>2}">
              var node${cat.depth} = new WebFXTreeItem('${cat.name }');
              node${cat.depth-1}.add(node${cat.depth});              
           </c:if>           
     </c:forEach>
     //迭代完成将树写入文档
     document.write(tree);
  </script>
  </body>

Category位于domain包

package cn.itcast.domain;
//select child.*,count(child.name) depth from category parent,category child 
//where parent.lef<=child.lef and parent.rig>=child.rig group by child.name order by child.lef;
public class Category {
	private String id;
	private String name;
	private int lef;
	private int rig;
	private int depth;
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getLef() {
		return lef;
	}
	public void setLef(int lef) {
		this.lef = lef;
	}
	public int getRig() {
		return rig;
	}
	public void setRig(int rig) {
		this.rig = rig;
	}
	public int getDepth() {
		return depth;
	}
	public void setDepth(int depth) {
		this.depth = depth;
	}
}

JdbcUtils位于utils包

package cn.itcast.utils;
import java.io.InputStream;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
/**演示开源数据库连接池DBCP的使用
DBCP内部增强Connection的close方法使用的是装饰模式!
1,导入两个jar包到工程下的lib目录,变成奶瓶
commons-dbcp-1.2.2.jar
commons-pool.jar
2,设置src下的dbcpconfig.properties配置文件信息如库名!
3,新建一个工具类如:JdbcUtils_DBCP
4,定义成员记住DBCP创建出来的数据源(即连接池)
5,静态代码块中用BasicDataSourceFactory创建数据源(即连接池)
6,定义获取连接的方法
7,定义释放连接的方法
*/
public class JdbcUtils {
	private static DataSource ds;
	static{
	      try {
	         String pro_name="dbcpconfig.properties";
	          InputStream in=JdbcUtils.class.getClassLoader().getResourceAsStream(pro_name);
	          Properties pro = new Properties();
	          pro.load(in);
	        //DBCP连接池--固定代码:由工厂创建数据源(即连接池)
	          BasicDataSourceFactory factory=new BasicDataSourceFactory();
	          //用类成员记住根据配置文件创建出来的连接池!
	          ds=factory.createDataSource(pro);
	      } catch (Exception e) {
	         throw new ExceptionInInitializerError(e);
	      }
	}
	public static DataSource getDataSource(){
		//Dbutils工具构造函数需要一个连接池
		return ds;
	}
}

CategoryDao位于dao包

package cn.itcast.dao;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import cn.itcast.domain.Category;
import cn.itcast.utils.JdbcUtils;
public class CategoryDao {
	//webTree方法1:获得所有的商品节点,并保存到集合中!
	public List<Category> getAll() throws SQLException{
		//不管三七二十一,先new个QueryRunner
		QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
		String sql="select child.*,count(child.name) depth from category parent,category child where parent.lef<=child.lef and parent.rig>=child.rig group by child.name order by child.lef";
		List<Category> list=(List<Category>) qr.query(sql, new BeanListHandler(Category.class));
		return list;
	}
}

Cservice位于service包

package cn.itcast.service;
import java.sql.SQLException;
import java.util.List;

import cn.itcast.dao.CategoryDao;
import cn.itcast.domain.Category;
public class Cservice {	
	//薄薄的业务层,调用CategoryDao,获取存有所有商品节点的集合
	public List<Category> getAllCategory() throws SQLException{
		CategoryDao dao=new CategoryDao();
		return dao.getAll();
	}
}

ListWebTreeServlet位于web.controller包

package cn.itcast.web.controller;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.itcast.domain.Category;
import cn.itcast.service.Cservice;
public class ListWebTreeServlet extends HttpServlet {
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		try {
			Cservice service=new Cservice();
			List<Category> list=service.getAllCategory();
			request.setAttribute("list", list);
			request.getRequestDispatcher("/listwebtree.jsp").forward(request, response);
		} catch (SQLException e) {			
			e.printStackTrace();
		}
	}
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doGet(request, response);
	}
}

dbcpconfig.properties位于src目录

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day17
#url=jdbc:mysql://localhost:3306/day16
#url=jdbc:mysql://localhost:3306/day14_customer
username=root
password=root
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 即等1分钟后仍没连接,这时才告诉人家,呆会再来,暂无连接! -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;] 
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=utf8
#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=
#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_COMMITTED

listwebtree.jsp位于WebRoot

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>商品树</title>
     <script src="${pageContext.request.contextPath }/js/xtree.js"></script>
	<link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath }/css/xtree.css" />
  </head>
  <body>
    <script type="text/javascript">
  		
	  <c:forEach var="cat" items="${list}">
	  		//如果层次为1,则为商品类别的根节点!
	  		<c:if test="${cat.depth==1}">
	  			var tree = new WebFXTree('${cat.name }');
	  		</c:if>
	  		//如果层次为2,则添加在根节点下面
	  		<c:if test="${cat.depth==2}">
	  			var node${cat.depth}= new WebFXTreeItem('${cat.name }');
	  			tree.add(node${cat.depth});
	  			
	  		</c:if>
	  		//如果层次为大于2,则添加在其父节点的下面
	  		<c:if test="${cat.depth>2}">
	  			var node${cat.depth} = new WebFXTreeItem('${cat.name }');
	  			node${cat.depth-1}.add(node${cat.depth});	  			
	  		</c:if>	  		
	  </c:forEach>
	  //迭代完成将树写入文档
	  document.write(tree);
  </script>
  </body>
</html>

xtree_demo位于WebRoot

<%@ page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>xtree_demo1</title>
     <script src="${pageContext.request.contextPath }/js/xtree.js"></script>
	<link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath }/css/xtree.css">
  </head>
  
  <body>
  <script type="text/javascript">
  		 var tree = new WebFXTree('Root');
		  tree.add(new WebFXTreeItem('Tree Item 1'));
		  tree.add(new WebFXTreeItem('Tree Item 2'));
		  tree.add(new WebFXTreeItem('Tree Item 3'));
		  document.write(tree);
  		
  		
  		
  		
  		
	  <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 node2 = new WebFXTree('${c.name }');
	  			tree.add(node2);
	  		</c:if>
	  </c:forEach>
	  document.write(tree);
  </script>
    This is my JSP page. <br>
  </body>
</html>

pre_eminent

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库_jdbc_web树

概述WebTree的实现原理:Web树的数据库表设计: mysql -uroot -proot set character_set_client=gb2312; set character_set_results=gb2312; use day17; create table category ( id varchar(40) primary key,
复制链接

扫一扫