不用迭代算法而快速實現的jsp樹結構

在web頁面上實現樹狀結構,有點麻煩.

在最近的一個MIS系統的開發中,我們項目組大量用到了樹結構:比如人員的選擇,單位的選擇等待.

這個MIS系統所用的數據庫是oracle 9i.  oracle 9i 的sql支持迭代查詢.我們的樹是由牛人彭越寫的,不過

也參照了網絡上比較著名的xtree(可以到此下載:http://webfx.eae.net/),他的樹算法支持無限級的樹結構,不過性能好像

很慢.我持保留態度.

他用到的關鍵技術就是這句話:

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 com.lcl.common;

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 com.lcl.common;

import java.sql.*;

import java.lang.*;

/**

 * @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( java.lang.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.*,com.lcl.common.*" errorPage="" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/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">&nbsp;

     

    </td>

  </tr>

</table>

</body>

<div onclick="treeClick()">

<script type="text/javascript"> 

 document.write(tree);

</script>

</div>

</HTML>

//其中jsp頁面上的幾個javascript函數為同事牛人彭越所寫,我沒改動,在此說明.