JSP tree structure quickly implemented without Iterative Algorithms

Implementing the tree structure on the Web page is a little troublesome.
In the development of a recent MIS system, our project team used a large number of tree structures, such as staff selection and unit selection waiting.
The database used by this MIS system is the SQL statement of Oracle 9i. Oracle 9i that supports iterative query. Our tree is written by Peng Yue, Niu Ren,
Also referred to the network's more famous xtree (can download here: http://webfx.eae.net/), his tree algorithm supports an infinite tree structure, but the performance seems
Very slow. I am reserved.
The key technology he used is this sentence:
String SQL = "select dwxh, dwbh, dwmc, dwfxh, level CC from xt_dw connect by prior dwxh = dwfxh start with dwfxh = 0 ";
However, many databases do not support iterative queries. What is the better solution? My solution is described below.

I. Proposal of requirements
1: the customer needs a tree structure for the Department staff. The database is mysql4.1.
2: JAVA Implementation
Ii. Table creation:
1:
User information table:
The fields are user serial numbers, user serial numbers, user names, organization serial numbers, passwords, and user logon numbers.
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 ('feng', 'feng xin', 2, 'Password', 'feng ')
-- Insert into xt_yh (yhbh, yhmc, dwxh, PWD, yhdlh) values ('wangqx', 'wang qingxiang ', 6, 'Password', 'wangqx ')
2:
Organization and department table
Each field is: Unit serial number, unit serial number, unit name, unit parent serial 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 records
-- Insert into xt_dw (dwbh, dwmc, dwfxh) values ('20140901', 'wuhan Science and Technology authorization', 0 );
-- Insert into xt_dw (dwbh, dwmc, dwfxh) values ('000000', 'personnel division ', 1 );
-- Insert into xt_dw (dwbh, dwmc, dwfxh) values ('20140901', 'logistics Department ', 1 );
-- Insert into xt_dw (dwbh, dwmc, dwfxh) values ('000000', 'personnel son1', 2 );
-- Insert into xt_dw (dwbh, dwmc, dwfxh) values ('000000', 'personnel Department son2', 2 );
-- Insert into xt_dw (dwbh, dwmc, dwfxh) values ('000000', 'attendance son1', 3 );

Note:
In order to achieve a fast tree structure, we need to make full use of the unit number dwbh, dwbh has only 10 bits of encoding. The first and second bits represent the level-1 unit, and the third and fourth bits represent the level-2 unit,
The fifth and sixth bits represent three-level units... then the 10-bit encoding can implement a 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 Department
3 personnel department son1
3 personnel department son2
2 Logistics
3 Logistics Department son1

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

/******* 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 Li chunlei
*
* To change the template of the type comment generated by todo, go
* Database category
*/
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 ();
Rs1_stmt.exe cutequery (SQL );
}
Catch (sqlexception ex)
{
System. Err. println ("ap.exe cutequery:" + ex. getmessage ());
}
 
Return Rs;
}
// Define database operations
Public void executeupdate (string SQL)
{
Stmt = NULL;
Rs = NULL;
Try
{
Conn = drivermanager. getconnection (strdburl, username, password );
Stmt = conn. createstatement ();
Stmt.exe cutequery (SQL );
Stmt. Close ();
Conn. Close ();
}
Catch (sqlexception ex)
{
System. Err. println ("ap.exe cutequery:" + 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 = new dbaccess ();
String SQL = "select * From xt_yh ";
Resultset rs = dbaccess.exe cutequery (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 catch Blocks
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 staff information", "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 ")
Optional Delete. Disabled = false;
Else
Optional Delete. Disabled = true;
If (tree. getselected (). Key. substr (0, 2) = "RZ "){
Required adddept. Disabled = true;
Required addpeople. 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 "){
Required adddept. Disabled = false;
Required addpeople. Disabled = false;
VaR strdwxh;
Strdwxh = tree. getselected (). Key. substr (2 );
// Window. Open ("../useradm/editbm. do? Dwxh = "+ strdwxh," Main ");
}
Else {
Required adddept. Disabled = false;
Required addpeople. 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 () = '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 = new dbaccess ();
String SQL = "select dwxh, dwbh, dwmc, dwfxh from xt_dw order by dwbh ";
Resultset rs = dbaccess.exe cutequery (SQL );
Try
{
While (Rs. Next ())
{
Dwxh = Rs. getint (1 );
Dwbh = Rs. getstring (2 );
Dwmc = Rs. getstring (3 );
Dwfxh = Rs. getint (4 );
// Calculate the level by the 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.exe cutequery (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>
& Lt; TD width = "273" colspan = "2" & gt;
<Font face = "" size = "3">
</Font>
</TD>
</Tr>
<Tr>
& Lt; th width = "33%" align = "center" nowrap & gt;
<P align = "center">
<Input id = adddept name = "adddept" type = button value = "add Department" onclick = "adddept ()" style = "font-family: _ gb2312; font-size: 12pt; font-weight: bold; Height: 24px; width: 80px ">
</P>
</Th>
& Lt; th width = "33%" align = "center" nowrap & gt;
<P align = "center">
<Input id = addpeople name = "addpeople" type = button value = "Add User" onclick = "addpeople ()" style = "font-family: _ gb2312; font-size: 12pt; font-weight: bold; Height: 24px; width: 80px ">
</P>
</Th>
& Lt; th width = "33%" align = "center" nowrap & gt;
<P align = "center">
<Input id = Your Delete name = "delete" type = button value = "delete" 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>

// Several JavaScript Functions on the JSP page are written by Peng Yue, a colleague. I have not changed them.