在web页面上实现树状结构,有点麻烦.
在最近的一个MIS系统的开发中,我们项目组大量用到了树结构:比如人员的选择,单位的选择等待.
这个MIS系统所用的数据库是oracle 9i. oracle 9i 的sql支持迭代查询.我们的树是由牛人彭越写的,不过
也参照了网络上比较著名的xtree(可以到此下载:/),他的树算法支持无限级的树结构,不过性能好像
很慢.我持保留态度.
他用到的关键技术就是这句话:
String sql = "select dwxh,dwbh,dwmc,dwfxh,level cc from xt_dw connect by prior dwxh = dwfxh start with dwfxh = 0";
可是许多数据库不支持迭代查询,并且迭代查询速度真是不能忍受.有什么更好的办法呢.下面说说我的解决方案.
一:需求的提出
1:客户需要一个关于部门人员的树结构,数据库为mysql4.1
2:java实现
二:建表:
1:
用户信息表:
各字段为:用户序号,用户编号,用户名称,单位序号,密码,用户登陆号
create table XT_YH
(
YHXH INT(9) NOT NULL auto_increment PRIMARY KEY,
YHBH VARCHAR(30),
YHMC VARCHAR(30),
DWXH INT(9),
PWD VARCHAR(20),
YHDLH VARCHAR(30)
)
--插入三条测试数据:
--insert into xt_yh(yhbh,yhmc,dwxh,pwd,yhdlh) values('licl','李春雷',2,'password','licl')
--insert into xt_yh(yhbh,yhmc,dwxh,pwd,yhdlh) values('fengx','冯欣',2,'password','fengx')
--insert into xt_yh(yhbh,yhmc,dwxh,pwd,yhdlh) values('wangqx','王庆香',6,'password','wangqx')
2:
单位部门表
各字段为:单位序号,单位编号,单位名称,单位父序号
create table XT_DW
(
DWXH int(9) NOT NULL auto_increment PRIMARY KEY,
DWBH VARCHAR(10),
DWMC VARCHAR(30),
DWFXH int(9)
)
--插入5条测试数据
--insert into xt_dw(dwbh,dwmc,dwfxh) values('0100000000','武汉科技局',0);
--insert into xt_dw(dwbh,dwmc,dwfxh) values('0101000000','人事处',1);
--insert into xt_dw(dwbh,dwmc,dwfxh) values('0102000000','后勤处',1);
--insert into xt_dw(dwbh,dwmc,dwfxh) values('0101010000','人事处son1',2);
--insert into xt_dw(dwbh,dwmc,dwfxh) values('0101020000','人事处son2',2);
--insert into xt_dw(dwbh,dwmc,dwfxh) values('0102010000','后勤处son1',3);
注意:
为了实现快速的树结构实现,我需要充分利用单位编号DWBH,DWBH才有10位编码,其中,第一第二位表示一级单位,第三第四位表示二级单位,
第五六位表示三级单位...那么10位编码就可以实现五级单位的树结构.
比如:测试数据的树结构如下:
1 武汉科技局:
2 人事处
3 人事处son1
3 人事处son2
2 后勤处
3后勤处son1
其实XT_DW表中的父序号是多余的.不过如果你要用迭代算法来实现,就是必须的
才有10位编码,我只需要一句简单快速的sql语句就可以实现树结构:
String sql = "select dwxh,dwbh,dwmc,dwfxh from xt_dw order by dwbh"
这句sql在几乎所有的数据库平台都能执行,速度也快.
下面贴出采用xtree,用10位编码而不是迭代算法实现的树:
/*******Constants.java**********/
package mon;
public class Constants {
public static final String DBDRIVER = "com.mysql.jdbc.Driver"; //MYSQL驱动
public static final String DBUrl="jdbc:mysql://localhost/beauoa"; //数据库url
public static final String USERNAME="root"; //数据库用户名
public static final String PASSWORD="root"; //数据库密码
}
/**********DbAccess.java****************/
package mon;
import java.sql.*;
import ng.*;
/**
* @author 李春雷
*
* TODO 要更改此生成的类型注释的模板，请转至
* 数据库访问类
*/
public class DbAccess
{
String strDBDriver = Constants.DBDRIVER;
String strDBUrl = Constants.DBUrl;
String username = Constants.USERNAME;
String password = Constants.PASSWORD;
private Connection conn = null;
private Statement stmt = null;
ResultSet rs=null;
//注册数据库驱动程序
public DbAccess()
{
try
{
Class.forName(strDBDriver);
}
//异常处理
catch( ng.ClassNotFoundException e)
{
System.err.println("DbAccess():"+e.getMessage());
}
}
//建立数据库连接及定义数据查询
public ResultSet executeQuery(String sql)
{
rs=null;
try
{
conn=DriverManager.getConnection(strDBUrl,username,password);
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
}
catch(SQLException ex)
{
System.err.println("ap.executeQuery:"+ex.getMessage());
}
return rs;
}
//定义数据操库作
public void executeUpdate(String sql)
{
stmt=null;
rs=null;
try
{
conn=DriverManager.getConnection(strDBUrl,username,password);
stmt=conn.createStatement();
stmt.executeQuery(sql);
stmt.close();
conn.close();
}
catch(SQLException ex)
{
System.err.println("ap.executeQuery:"+ex.getMessage());
}
}
//关闭数据库
public void closeStmt()
{
try
{
stmt.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
public void closeConn()
{
try
{
conn.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
public static void main(String[] args){
System.out.println("hello,it's test");
DbAccess dbaccess = new DbAccess();
String sql = "select * from xt_yh";
ResultSet rs = dbaccess.executeQuery(sql);
try
{
while(rs.next()){
System.out.print(rs.getString(1)+rs.getString(2)+rs.getString(3)+rs.getString(4)+rs.getString(5)+rs.getString(6));
System.out.println();
}
dbaccess.closeStmt();
dbaccess.closeConn();
}
catch (SQLException e)
{
// TODO 自动生成 catch 块
e.printStackTrace();
}
}
}
/*********DepEmplConfig.jsp************/
<%@ page contentType="text/html; charset=gb2312" language="java" import="java.sql.*,mon.*" errorPage="" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>无标题文档</title>
<HEAD>
<script type="text/javascript" src="../resources/xDataTree.js"></script>
<link type="text/css" rel="stylesheet" href="../resources/xtree.css" />
<style type="text/css">
body {
background: white;
color: black;
}
</style>
<TITLE> New Document </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">
</HEAD>
<script type="text/javascript">
webFXTreeConfig.rootIcon = "../resources/images/xp/folder.png";
webFXTreeConfig.openRootIcon = "../resources/images/xp/openfolder.png";
webFXTreeConfig.folderIcon = "../resources/images/xp/folder.png";
webFXTreeConfig.openFolderIcon = "../resources/images/xp/openfolder.png";
webFXTreeConfig.fileIcon = "../resources/images/xp/file.png";
webFXTreeConfig.lMinusIcon = "../resources/images/xp/Lminus.png";
webFXTreeConfig.lPlusIcon = "../resources/images/xp/Lplus.png";
webFXTreeConfig.tMinusIcon = "../resources/images/xp/Tminus.png";
webFXTreeConfig.tPlusIcon = "../resources/images/xp/Tplus.png";
webFXTreeConfig.iIcon = "../resources/images/xp/I.png";
webFXTreeConfig.lIcon = "../resources/images/xp/L.png";
webFXTreeConfig.tIcon = "../resources/images/xp/T.png";
webFXTreeConfig.blankIcon = "../resources/images/blank.png";
var tree = new WebFXTree("单位人员基本情况","R0");
var child;
var nodeToAddPerson;
function addDeptTreeNode(preNodeLevel,curNodeLevel,dispLabel,sKey,sTag) {
if(curNodeLevel==1) {
child = tree.add(new WebFXTreeItem(dispLabel,sKey,sTag));
}
else {
if(curNodeLevel==preNodeLevel) {
if(child.parentNode)
child = child.parentNode.add(new WebFXTreeItem(dispLabel,sKey,sTag));
}
if(curNodeLevel>preNodeLevel) {
child = child.add(new WebFXTreeItem(dispLabel,sKey,sTag));
}
if(curNodeLevel<preNodeLevel) {
for(i=0;i<preNodeLevel-curNodeLevel+1;i++)
child = child.parentNode;
child = child.add(new WebFXTreeItem(dispLabel,sKey,sTag));
}
}
return child;
}
function treeClick() {
if(tree.getSelected()) {
if(tree.getSelected().childNodes.length==0&&tree.getSelected().key!="R0")
cmdDelete.disabled = false;
else
cmdDelete.disabled = true;
if(tree.getSelected().key.substr(0,2)=="RZ") {
cmdAddDept.disabled = true;
cmdAddPeople.disabled = true;
var strYhxh;
strYhxh = tree.getSelected().key.substr(2);
//window.open("../userAdm/editYh.do?yhxh="+strYhxh,"main");
}
else if(tree.getSelected().key.substr(0,2)=="RB") {
cmdAddDept.disabled = false;
cmdAddPeople.disabled = false;
var strDwxh;
strDwxh = tree.getSelected().key.substr(2);
//window.open("../userAdm/editBm.do?dwxh="+strDwxh,"main");
}
else {
cmdAddDept.disabled = false;
cmdAddPeople.disabled = true;
//window.open("yhroot.jsp","main");
}
}
}
function addPeople() {
var strDwxh;
if(tree.getSelected()) {
if (tree.getSelected().key.substr(0,2)=="RB") {
strDwxh = tree.getSelected().key.substr(2);
//window.open("../userAdm/addYh.do?dwxh="+strDwxh,"main");
alert("addPeople");
}
}
}
function addDept() {
var strDwxh;
if(tree.getSelected()) {
if (tree.getSelected().key.substr(0,2)=="RB") {
strDwfxh = tree.getSelected().key.substr(2);
//window.open("../userAdm/addBm.do?dwfxh="+strDwfxh,"main");
alert("addDept");
}
else if(tree.getSelected().key=="R0") {
//window.open("../userAdm/addBm.do?dwfxh=0","main");
alert("addDept");
}
}
}
function deleSelected() {
if(!confirm("确认删除该节点吗？"))
return;
if(tree.getSelected()) {
if(tree.getSelected().key.substr(0,2)=="RB") {
var strDwxh;
strDwxh = tree.getSelected().key.substr(2);
//window.open("../userAdm/delBm.do?dwxh="+strDwxh,"main");
alert("deleSelected");
}
else if(tree.getSelected().key.substr(0,2)=='RZ') {
var strYhxh,strYhbh;
strYhxh = tree.getSelected().key.substr(2);
strYhbh = tree.getSelected().tag;
//window.open("../userAdm/delYh.do?yhxh="+strYhxh+"&yhbh="+strYhbh,"main");
alert("deleSelected");
}
}
}
function removeNode() {
if(tree.getSelected()) {
var node = tree.getSelected();
node.remove();
}
}
function addPeopleNode(strParentKey,strKey,strText,strTag) {
if(tree.getSelected()) {
var node = tree.getSelected();
var childNode;
//node.expand();
childNode = node.add(new WebFXTreeItem(strText,strKey,strTag,"","","../resources/images/people1.png"));
node.expand(); //why I do so? I dont want to tell you,hah!
childNode.focus();
treeClick();
}
}
function addDeptNode(strParentKey,strKey,strText,strTag) {
if(tree.getSelected()) {
var node = tree.getSelected();
var childNode;
childNode = node.add(new WebFXTreeItem(strText,strKey,strTag));
node.expand();
childNode.focus();
treeClick();
}
}
function updateDeptNode(strTag,strText) {
if(tree.getSelected()) {
var node = tree.getSelected();
node.text = strText;
node.tag = strTag;
node.focus();
}
}
function updatePeopleNode(strTag,strText) {
if(tree.getSelected()) {
var node = tree.getSelected();
node.text = strText;
node.tag = strTag;
node.focus();
}
}
</script>
<%
int dwxh;
int dwfxh;
int yhxh;
String dwbh = null;
String dwmc = null;
String yhmc = null;
String yhbh = null;
int preLevel =1;
int level = 1;
DbAccess dbaccess = new DbAccess();
String sql = "select dwxh,dwbh,dwmc,dwfxh from xt_dw order by dwbh";
ResultSet rs = dbaccess.executeQuery(sql);
try
{
while(rs.next())
{
dwxh = rs.getInt(1);
dwbh = rs.getString(2);
dwmc = rs.getString(3);
dwfxh = rs.getInt(4);
//通过单位编号计算level
String last = dwbh.substring(9,10);
int i = 9;
while(last.equals("0") && i>0){
i--;
last = dwbh.substring(i,i+1);
}
if(i==0 || i==1) level =1;
if(i==2 || i==3) level =2;
if(i==4 || i==5) level =3;
if(i==6 || i==7) level =4;
if(i==8 || i==9) level =5;
//
%>
<script type="text/javascript">
nodeToAddPerson = addDeptTreeNode(<%=preLevel%>,<%=level%>,"<%=dwmc%>","RB<%=dwxh%>","<%=dwbh%>");
</script>
<%
preLevel = level;
String subsql = "select yhxh,yhmc,yhbh from xt_yh where dwxh = "+Integer.toString(dwxh);
ResultSet subRs = dbaccess.executeQuery(subsql);
while(subRs.next()) {
yhxh = subRs.getInt(1);
yhmc = subRs.getString(2);
yhbh = subRs.getString(3);
%>
<script type="text/javascript">
nodeToAddPerson.add(new WebFXTreeItem("<%=yhmc%>","RZ<%=yhxh%>","<%=yhbh%>","","","../resources/images/people1.png"));
</script>
<%
}
}
dbaccess.closeStmt();
dbaccess.closeConn();
}
catch(Exception e)
{
}
%>
<base target="_self">
<META HTTP-EQUIV="PRAGMA" CONTENT="NO-CACHE">
</head>
<body>
<table border="0" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td width="273" colspan="2">
<font face="宋体" size="3">
</font>
</td>
</tr>
<tr>
<th width="33%" align="center" nowrap>
<p align="center">
<INPUT id=cmdAddDept name="AddDept" type=button value="增加部门" onclick="addDept()" style="FONT-FAMILY: 楷体_GB2312; FONT-SIZE: 12pt; FONT-WEIGHT: bold; HEIGHT: 24px; WIDTH: 80px" >
</p>
</th>
<th width="33%" align="center" nowrap>
<p align="center">
<INPUT id=cmdAddPeople name="AddPeople" type=button value="增加用户" onclick="addPeople()" style="FONT-FAMILY: 楷体_GB2312; FONT-SIZE: 12pt; FONT-WEIGHT: bold; HEIGHT: 24px; WIDTH: 80px" >
</p>
</th>
<th width="33%" align="center" nowrap>
<p align="center">
<INPUT id=cmdDelete name="Delete" type=button value=" 删除 " onclick="deleSelected()" style="FONT-FAMILY: 楷体_GB2312; FONT-SIZE: 12pt; FONT-WEIGHT: bold; HEIGHT: 24px; WIDTH: 80px" disabled>
</p>
</th>
</tr>
<tr>
<td width="273" height="8" colspan="2">
</td>
</tr>
</table>
</body>
<div onclick="treeClick()">
<script type="text/javascript">
document.write(tree);
</script>
</div>
</HTML>
//其中jsp页面上的几个javascript函数为同事牛人彭越所写,我没改动,在此说明.