A fast implementation of JSP tree structure without iterative algorithm

js | Algorithm
Implementing a tree structure on a web page is a bit cumbersome.
In the recent development of a MIS system, our project team used a lot of tree structures: such as selection of personnel, waiting for selection of units.
The database used by this MIS system is oracle 9i. The oracle 9i sql supports iterative query. Our tree was written by Niu Peng Yue, but
It also refers to the more famous xtree on the network (you can download it here: http://webfx.eae.net/). His tree algorithm supports infinite tree structure, but the performance seems
Very slow. I am reserved.
The key technology he used was this sentence:
String sql = "select dwxh, dwbh, dwmc, dwfxh, level cc from xt_dw connect by prior dwxh = dwfxh start with dwfxh = 0";
But many databases do not support iterative query, and the speed of iterative query is really intolerable. Is there any better way? Let me talk about my solution below.

One: the demand
1: The client needs a tree structure about the department staff, the database is mysql4.1
2: java implementation
Two: build a table:
1:
User Information Form:
The fields are: user number, user number, user name, unit number, password, user login number
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 three test data:
--insert into xt_yh (yhbh, yhmc, dwxh, pwd, yhdlh) values ('licl', 'Li Chunlei', 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:
Unit department table
The fields are: unit number, unit number, unit name, unit parent number
create table XT_DW
(
  DWXH int (9) NOT NULL auto_increment PRIMARY KEY,
  DWBH VARCHAR (10),
  DWMC VARCHAR (30),
  DWFXH int (9)
)
--Insert 5 test data
--insert into xt_dw (dwbh, dwmc, dwfxh) values ('0100000000', 'Wuhan Science and Technology Bureau', 0);
--insert into xt_dw (dwbh, dwmc, dwfxh) values ('0101000000', 'Personnel Office', 1);
--insert into xt_dw (dwbh, dwmc, dwfxh) values ('0102000000', 'Logistics', 1);
--insert into xt_dw (dwbh, dwmc, dwfxh) values ('0101010000', 'personnel office son1', 2);
--insert into xt_dw (dwbh, dwmc, dwfxh) values ('0101020000', 'personnel office son2', 2);
--insert into xt_dw (dwbh, dwmc, dwfxh) values ('0102010000', 'Logistics son1', 3);

note:
In order to achieve a fast tree structure implementation, I need to make full use of the unit number DWBH. DWBH has a 10-bit code.Among them, the first and second digits represent first-level units, and the third and fourth digits represent second-level units.
The fifth and sixth digits represent three-level units ... then 10-bit encoding can achieve the tree structure of five-level units.
For example: The tree structure of the test data is as follows:
  1 Wuhan Science and Technology Bureau:
 2 Personnel Office
  3 Personnel Office son1
  3 Personnel Office son2
 2 Logistics
  3 logistics office son1

In fact, the parent serial number in the XT_DW table is redundant. However, if you want to implement iterative algorithm, it is necessary
Only 10-bit encoding, I only need a simple and fast sql statement to achieve the tree structure:
String sql = "select dwxh, dwbh, dwmc, dwfxh from xt_dw order by dwbh"
This sql can be executed on almost all database platforms and is fast.
The following shows a tree that uses xtree and uses 10-bit encoding instead of an iterative algorithm:

/*******Constants.java**********/

package com.lcl.common;

public class Constants {
 
 public static final String DBDRIVER = "com.mysql.jdbc.Driver"; // MYSQL driver
 
 public static final String DBUrl = "jdbc: mysql: // localhost / beauoa"; // Database URL
 
 public static final String USERNAME = "root"; // Database username
 
 public static final String PASSWORD = "root"; // Database password
 
 
}


/**********DbAccess.java********************/

package com.lcl.common;

import java.sql. *;
import java.lang. *;

/ **
 * @author 李春雷
 *
 * TODO To change the template of this generated type annotation, go to
 * Database access class
 * /
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;
 // Register the database driver
 public DbAccess ()
 {
  try
  {
   Class.forName (strDBDriver);
  }
  // Exception handling
  catch (java.lang.ClassNotFoundException e)
  {
   System.err.println ("DbAccess ():" + e.getMessage ());
  }
 }
 // Establish database connection and define data query
 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;
 }
 // Define data manipulation library operation
 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 ());
  }
 }
 // Close the database
 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 automatically generates a catch block
   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> Untitled document </ 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 ("Basic situation of unit personnel", "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 ("Are you sure you want to delete this node?"))
      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);
// Calculate level by unit number
  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 = "Add department" style = "FONT-FAMILY: italic_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 = "Add User" style = "FONT-FAMILY: italic_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 = "delete" style = "FONT-FAMILY: italic_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>
<script type = "text / javascript">
 document.write (tree);
</ script>
</ div>
</ HTML>

// The several javascript functions on the jsp page were written by my colleague Niu Peng Yue.I haven't changed them, and I will explain here.