程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> 網頁編程 >> JSP編程 >> 關於JSP >> 不用迭代算法而快速實現的jsp樹結構

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

編輯:關於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函數為同事牛人彭越所寫,我沒改動,在此說明.

     

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved