不用迭代算法而快速实现的jsp树结构-jsp-IT落伍者

在web页面上实现树状结构有点麻烦

在最近的一个MIS系统的开发中我们项目组大量用到了树结构:比如人员的选择单位的选择等待

这个MIS系统所用的数据库是oracle i oracle i 的sql支持迭代查询我们的树是由牛人彭越写的不过

也参照了网络上比较着名的xtree(可以到此下载他的树算法支持无限级的树结构不过性能好像

很慢我持保留态度

他用到的关键技术就是这句话:

String sql = "select dwxhdwbhdwmcdwfxhlevel cc from xt_dw connect by prior dwxh = dwfxh start with dwfxh = ";

可是许多数据库不支持迭代查询并且迭代查询速度真是不能忍受有什么更好的办法呢下面说说我的解决方案

一:需求的提出

:客户需要一个关于部门人员的树结构数据库为mysql

:java实现

二:建表:

用户信息表:

各字段为:用户序号用户编号用户名称单位序号密码用户登陆号

create table XT_YH

(

YHXH INT() NOT NULL auto_increment PRIMARY KEY

YHBH VARCHAR()

YHMC VARCHAR()

DWXH INT()

PWD VARCHAR()

YHDLH VARCHAR()

)

插入三条测试数据:

insert into xt_yh(yhbhyhmcdwxhpwdyhdlh) values(licl李春雷passwordlicl)

insert into xt_yh(yhbhyhmcdwxhpwdyhdlh) values(fengx冯欣passwordfengx)

insert into xt_yh(yhbhyhmcdwxhpwdyhdlh) values(wangqx王庆香passwordwangqx)

单位部门表

各字段为:单位序号单位编号单位名称单位父序号

create table XT_DW

(

DWXH int() NOT NULL auto_increment PRIMARY KEY

DWBH VARCHAR()

DWMC VARCHAR()

DWFXH int()

)

插入条测试数据

insert into xt_dw(dwbhdwmcdwfxh) values(武汉科技局);

insert into xt_dw(dwbhdwmcdwfxh) values(人事处);

insert into xt_dw(dwbhdwmcdwfxh) values(后勤处);

insert into xt_dw(dwbhdwmcdwfxh) values(人事处son);

insert into xt_dw(dwbhdwmcdwfxh) values(后勤处son);

注意:

为了实现快速的树结构实现我需要充分利用单位编号DWBHDWBH才有位编码其中第一第二位表示一级单位第三第四位表示二级单位

第五六位表示三级单位那么位编码就可以实现五级单位的树结构

比如:测试数据的树结构如下:

武汉科技局:

人事处

人事处son

后勤处

后勤处son

其实XT_DW表中的父序号是多余的不过如果你要用迭代算法来实现就是必须的

才有位编码我只需要一句简单快速的sql语句就可以实现树结构:

String sql = "select dwxhdwbhdwmcdwfxh from xt_dw order by dwbh"

这句sql在几乎所有的数据库平台都能执行速度也快

下面贴出采用xtree用位编码而不是迭代算法实现的树:

/*******Constantsjava**********/

package comlclcommon;

public class Constants {

public static final String DBDRIVER = "commysqljdbcDriver"; //MYSQL驱动

public static final String DBUrl="jdbc:mysql://localhost/beauoa"; //数据库url

public static final String USERNAME="root"; //数据库用户名

public static final String PASSWORD="root"; //数据库密码

/**********DbAccessjava****************/

package comlclcommon;

import javasql*;

import javalang*;

/**

* @author 李春雷

* TODO 要更改此生成的类型注释的模板请转至

* 数据库访问类

public class DbAccess

{

String strDBDriver = ConstantsDBDRIVER;

String strDBUrl = ConstantsDBUrl;

String username = ConstantsUSERNAME;

String password = ConstantsPASSWORD;

private Connection conn = null;

private Statement stmt = null;

ResultSet rs=null;

//注册数据库驱动程序

public DbAccess()

{

try

{

ClassforName(strDBDriver);

}

//异常处理

catch( javalangClassNotFoundException e)

{

Systemerrprintln("DbAccess():"+egetMessage());

}

//建立数据库连接及定义数据查询

public ResultSet executeQuery(String sql)

{

rs=null;

try

{

conn=DriverManagergetConnection(strDBUrlusernamepassword);

stmt=conncreateStatement();

rs=stmtexecuteQuery(sql);

}

catch(SQLException ex)

{

Systemerrprintln("apexecuteQuery:"+exgetMessage());

}

return rs;

}

//定义数据操库作

public void executeUpdate(String sql)

{

stmt=null;

rs=null;

try

{

conn=DriverManagergetConnection(strDBUrlusernamepassword);

stmt=conncreateStatement();

stmtexecuteQuery(sql);

stmtclose();

connclose();

}

catch(SQLException ex)

{

Systemerrprintln("apexecuteQuery:"+exgetMessage());

}

//关闭数据库

public void closeStmt()

{

try

{

stmtclose();

}

catch(SQLException e)

{

eprintStackTrace();

}

public void closeConn()

{

try

{

connclose();

}

catch(SQLException e)

{

eprintStackTrace();

}

public static void main(String[] args){

Systemoutprintln("helloits test");

DbAccess dbaccess = new DbAccess();

String sql = "select * from xt_yh";

ResultSet rs = dbaccessexecuteQuery(sql);

try

{

while(rsnext()){

Systemoutprint(rsgetString()+rsgetString()+rsgetString()+rsgetString()+rsgetString()+rsgetString());

Systemoutprintln();

}

dbaccesscloseStmt();

dbaccesscloseConn();

}

catch (SQLException e)

{

// TODO 自动生成 catch 块

eprintStackTrace();

}

/*********DepEmplConfigjsp************/

<%@ page contentType="text/html; charset=gb" language="java" import="javasql*comlclcommon*" errorPage="" %>

<!DOCTYPE HTML PUBLIC "//WC//DTD HTML Transitional//EN" "

<html>

<head>

<title>无标题文档</title>

<HEAD>

body {

background:white;

color:black;

}

</style>

<TITLE> New Document </TITLE>

</HEAD>

webFXTreeConfigrootIcon= "/resources/images/xp/folderpng";

webFXTreeConfigopenRootIcon= "/resources/images/xp/openfolderpng";

webFXTreeConfigfolderIcon= "/resources/images/xp/folderpng";

webFXTreeConfigopenFolderIcon= "/resources/images/xp/openfolderpng";

webFXTreeConfigfileIcon= "/resources/images/xp/filepng";

webFXTreeConfiglMinusIcon= "/resources/images/xp/Lminuspng";

webFXTreeConfiglPlusIcon= "/resources/images/xp/Lpluspng";

webFXTreeConfigtMinusIcon= "/resources/images/xp/Tminuspng";

webFXTreeConfigtPlusIcon= "/resources/images/xp/Tpluspng";

webFXTreeConfigiIcon= "/resources/images/xp/Ipng";

webFXTreeConfiglIcon= "/resources/images/xp/Lpng";

webFXTreeConfigtIcon= "/resources/images/xp/Tpng";

webFXTreeConfigblankIcon = "/resources/images/blankpng";

var tree = new WebFXTree("单位人员基本情况""R");

var child;

var nodeToAddPerson;

function addDeptTreeNode(preNodeLevelcurNodeLeveldispLabelsKeysTag) {

if(curNodeLevel==) {

child = treeadd(new WebFXTreeItem(dispLabelsKeysTag));

}

else {

if(curNodeLevel==preNodeLevel) {

if(childparentNode)

child = childparentNodeadd(new WebFXTreeItem(dispLabelsKeysTag));

}

if(curNodeLevel>preNodeLevel) {

child = childadd(new WebFXTreeItem(dispLabelsKeysTag));

}

if(curNodeLevel<preNodeLevel) {

for(i=;i<preNodeLevelcurNodeLevel+;i++)

child = childparentNode;

child = childadd(new WebFXTreeItem(dispLabelsKeysTag));

}

return child;

}

function treeClick() {

if(treegetSelected()) {

if(treegetSelected()childNodeslength==&&treegetSelected()key!="R")

cmdDeletedisabled = false;

else

cmdDeletedisabled = true;

if(treegetSelected()keysubstr()=="RZ") {

cmdAddDeptdisabled = true;

cmdAddPeopledisabled = true;

var strYhxh;

strYhxh = treegetSelected()keysubstr();

//windowopen("/userAdm/editYhdo?yhxh="+strYhxh"main");

}

else if(treegetSelected()keysubstr()=="RB") {

cmdAddDeptdisabled = false;

cmdAddPeopledisabled = false;

var strDwxh;

strDwxh = treegetSelected()keysubstr();

//windowopen("/userAdm/editBmdo?dwxh="+strDwxh"main");

}

else {

cmdAddDeptdisabled = false;

cmdAddPeopledisabled = true;

//windowopen("yhrootjsp""main");

}

function addPeople() {

var strDwxh;

if(treegetSelected()) {

if (treegetSelected()keysubstr()=="RB") {

strDwxh = treegetSelected()keysubstr();

//windowopen("/userAdm/addYhdo?dwxh="+strDwxh"main");

alert("addPeople");

}

function addDept() {

var strDwxh;

if(treegetSelected()) {

if (treegetSelected()keysubstr()=="RB") {

strDwfxh = treegetSelected()keysubstr();

//windowopen("/userAdm/addBmdo?dwfxh="+strDwfxh"main");

alert("addDept");

}

else if(treegetSelected()key=="R") {

//windowopen("/userAdm/addBmdo?dwfxh=""main");

alert("addDept");

}

function deleSelected() {

if(!confirm("确认删除该节点吗？"))

return;

if(treegetSelected()) {

if(treegetSelected()keysubstr()=="RB") {

var strDwxh;

strDwxh = treegetSelected()keysubstr();

//windowopen("/userAdm/delBmdo?dwxh="+strDwxh"main");

alert("deleSelected");

}

else if(treegetSelected()keysubstr()==RZ) {

var strYhxhstrYhbh;

strYhxh = treegetSelected()keysubstr();

strYhbh = treegetSelected()tag;

//windowopen("/userAdm/delYhdo?yhxh="+strYhxh+"&yhbh="+strYhbh"main");

alert("deleSelected");

}

function removeNode() {

if(treegetSelected()) {

var node = treegetSelected();

noderemove();

}

function addPeopleNode(strParentKeystrKeystrTextstrTag) {

if(treegetSelected()) {

var node = treegetSelected();

var childNode;

//nodeexpand();

childNode = nodeadd(new WebFXTreeItem(strTextstrKeystrTag"""""/resources/images/peoplepng"));

nodeexpand(); //why I do so? I dont want to tell youhah!

childNodefocus();

treeClick();

}

function addDeptNode(strParentKeystrKeystrTextstrTag) {

if(treegetSelected()) {

var node = treegetSelected();

var childNode;

childNode = nodeadd(new WebFXTreeItem(strTextstrKeystrTag));

nodeexpand();

childNodefocus();

treeClick();

}

function updateDeptNode(strTagstrText) {

if(treegetSelected()) {

var node = treegetSelected();

nodetext = strText;

nodetag = strTag;

nodefocus();

}

function updatePeopleNode(strTagstrText) {

if(treegetSelected()) {

var node = treegetSelected();

nodetext = strText;

nodetag = strTag;

nodefocus();

}

</script>

int dwxh;

int dwfxh;

int yhxh;

String dwbh = null;

String dwmc = null;

String yhmc = null;

String yhbh = null;

int preLevel =;

int level = ;

DbAccess dbaccess = new DbAccess();

String sql = "select dwxhdwbhdwmcdwfxh from xt_dw order by dwbh";

ResultSet rs = dbaccessexecuteQuery(sql);

try

{

while(rsnext())

{

dwxh = rsgetInt();

dwbh = rsgetString();

dwmc = rsgetString();

dwfxh = rsgetInt();

//通过单位编号计算level

String last = dwbhsubstring();

int i = ;

while(lastequals("") && i>){

last = dwbhsubstring(ii+);

}

if(i== || i==) level =;

nodeToAddPerson = addDeptTreeNode(<%=preLevel%><%=level%>"<%=dwmc%>""RB<%=dwxh%>""<%=dwbh%>");

</script>

preLevel = level;

String subsql = "select yhxhyhmcyhbh from xt_yh where dwxh = "+IntegertoString(dwxh);

ResultSet subRs = dbaccessexecuteQuery(subsql);

while(subRsnext()) {

yhxh = subRsgetInt();

yhmc = subRsgetString();

yhbh = subRsgetString();

nodeToAddPersonadd(new WebFXTreeItem("<%=yhmc%>""RZ<%=yhxh%>""<%=yhbh%>""""""/resources/images/peoplepng"));

</script>

}

dbaccesscloseStmt();

dbaccesscloseConn();

}

catch(Exception e)

{

}

</head>

<body>

<tr>

</font>

</td>

</tr>

<tr>

</p>

</th>

</p>

</th>

</p>

</th>

</tr>

<tr>

</td>

</tr>

</table>

</body>

documentwrite(tree);

</script>

</div>

</HTML>

//其中jsp页面上的几个javascript函数为同事牛人彭越所写我没改动在此说明