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

2024-09-05 00:20:20

字體：大中小

來源：轉載

供稿：網友

本文來源于網頁設計愛好者web開發社區http://www.html.org.cn收集整理，歡迎訪問。

在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 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();
}
}

}
%>

<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="增加部門" >
      </p>
    </th>
    <th width="33%" align="center" nowrap>
      <p align="center">
      <input id=cmdaddpeople name="addpeople" type=button value="增加用戶" >
      </p>
    </th>
    <th width="33%" align="center" nowrap>
      <p align="center">
      <input id=cmddelete name="delete" type=button value=" 刪除 " disabled>
      </p>
    </th>
</tr>
<tr>
    <td width="273" height="8" colspan="2"> 

    </td>
</tr>
</table>
</body>
<div >
<script type="text/javascript">
document.write(tree);
</script>
</div>
</html>