有关读数据库的无刷新的三级联动以及层次数据(树形结构)在关系数据库的存储...

我的标题真冗长：P

最近一直在考虑这个问题，如何在现在常用的关系型数据库（如mysql，mssql）中存储含有层次结构（例如xml或者树型结构）的数据，我最终要实现的结果是一个比较好的页面三级联动菜单。比较了一下现有的三级联动，无非有两种解决方案，1、将所有数据写成静态的，这样的缺点在于对更新封闭;2、每改变一次去数据库中获取数据，填充到下一级菜单，这个缺点是对数据库的开销比较大，查询一次数据要对数据库做三次操作，而且数据库设计的比较丑陋。上述两种方法都让我比较不能接受。

我想的是一种比较优雅的解决方案：在数据库中存储的数据不要冗余，便于查询，不要产生递归，对数据库的开销要尽可能的小，尽量做到一次将所有数据都读出来，用户也要有比较好的体验。我最初的解决方案就是用xml来存储联动的数据，但是对于数据库操作来说，最后还是要落实到对文件的I/O操作，从这方面来讲，使用xml和数据库并没有什么本质的不同。另外一种解决方案就是在关系数据库中存类xml的数据了。google了半天，最后发现了一片自己学院一位学长翻译的文章www.nirvanastudio.org/category/database，讲得就是如何在关系型数据库存储这种层次数据的。文中提出了两种解决方案，一是“邻接列表模型”或称为“递归方法”，这种存在着数据冗余，而且递归出于众所周知的原因，效率不高。第二种方法，也就是我现在采用的方法是"前序优先遍历"，表结构也很简单，没有冗余

id

name

left

right

我觉得应该属于一种深度优先。通过每个节点的左右两个属性获得其子女的属性，好处就在于获得一个或几个节点只需要一次查询，而在更新的时候速度会比较慢，因为要更新多个后继节点，不过话说回来，更新相对于获取来说次数要少的多，可以忽略。

下面是我的解决方案：

java 代码

 

1. import java.io.IOException;  
2. import java.sql.Connection;  
3. import java.sql.ResultSet;  
4. import java.sql.SQLException;  
5. import java.sql.Statement;  
6. import java.util.ArrayList;  
7.   
8. import com.netkc.struts.datasource.DataSource;  
9. import com.netkc.struts.mapping.District;  
10.   
11. /** 
12.  * @author SONG Yihan 
13.  * @version 1.0 
14.  * @date: Monday, April 09 2007 
15.  */  
16. public class DistrictTree {  
17.       
18.     private ArrayList<district> tree;  </district>
19.     private Statement stmt1 = null;  
20.     private Statement stmt2 = null;  
21.     private Connection conn = null;  
22.     private ResultSet rs = null;  
23.       
24.     public DistrictTree() {  
25.         try {  
26.             conn = DataSource.getConnection();  
27.         } catch (IOException e) {  
28.             // TODO Auto-generated catch block  
29.             e.printStackTrace();  
30.         } catch (SQLException e) {  
31.             // TODO Auto-generated catch block  
32.             e.printStackTrace();  
33.         } catch (ClassNotFoundException e) {  
34.             // TODO Auto-generated catch block  
35.             e.printStackTrace();  
36.         } catch (InstantiationException e) {  
37.             // TODO Auto-generated catch block  
38.             e.printStackTrace();  
39.         } catch (IllegalAccessException e) {  
40.             // TODO Auto-generated catch block  
41.             e.printStackTrace();  
42.         }   
43.     }  
44.       
45.     public DistrictTree(Connection conn) {  
46.         this.conn = conn;  
47.     }  
48.   
49.     public boolean addNode(String parentId, String districtId, String districtName) {  
50.         try {  
51.             String sql = "SELECT * FROM district WHERE districtId='" + parentId + "';";  
52.             conn.setAutoCommit(false);  
53.             stmt1 = conn.createStatement();  
54.             rs = stmt1.executeQuery(sql);  
55.             if(rs.next()) {  
56.                 int right = rs.getInt("rgt");  
57.                 sql = "UPDATE district SET lft=lft+2 WHERE lft>=" + right;  
58.                 stmt2.addBatch(sql);  
59.                 sql = "UPDATE district SET rgt=rgt+2 WHERE rgt>=" + right;  
60.                 stmt2.addBatch(sql);  
61.                 sql = "INSERT INTO district (districtId, districtName, lft, rgt) VALUES ('"+districtId + "', '" + districtName + "', " +  
62.                 (right) + ", " + (right+1) +");";  
63.                 stmt2.addBatch(sql);  
64.                 int[] flag = stmt2.executeBatch();  
65.                 if(flag[flag.length - 1] == 1) {  
66.                     conn.commit();  
67.                     return true;  
68.                 } else {  
69.                     conn.rollback();  
70.                     return false;  
71.                 }             
72.             }  
73.               
74.         } catch (SQLException e) {  
75.             // TODO Auto-generated catch block  
76.             e.printStackTrace();  
77.         } finally {  
78.   
79.   
80.         }  
81.         return false;  
82.     }  
83.       
84.     public boolean deleteNode(String districtId) {  
85.         String sql = "SELECT * FROM district WHERE districtId='" + districtId + "';";  
86.         try {  
87.             conn.setAutoCommit(false);  
88.             rs = stmt1.executeQuery(sql);  
89.             if(rs.next())  
90.             {  
91.                 int left = rs.getInt("lft");  
92.                 int right = rs.getInt("rgt");  
93.                 int count = (right - left - 1) / 2 + 1;  
94.                 int minus = count * 2;  
95.                 sql = "DELETE FROM district WHERE lft BETWEEN " + left + " AND " + right + ";";  
96.                 stmt2.addBatch(sql);  
97.                 sql = "UPDATE district SET lft=lft-" + minus + " WHERE lft>"+right;  
98.                 stmt2.addBatch(sql);  
99.                 sql = "UPDATE district SET rgt=rgt-" + minus + " WHERE rgt>"+right;  
100.                 stmt2.addBatch(sql);  
101.                 int flag = stmt2.executeBatch()[0];  
102.                 if(flag == 1) {  
103.                     conn.commit();  
104.                     return true;  
105.                 } else {  
106.                     conn.rollback();  
107.                     return false;  
108.                 }  
109.                   
110.             }  
111.         } catch (SQLException e) {  
112.             // TODO Auto-generated catch block  
113.             e.printStackTrace();  
114.         }  
115.         return false;  
116.     }  
117.       
118.     public void displayTree(String parentId) {  
119.           
120.           
121.     }  
122.       
123.     public ArrayList<district> getTree(String parentId) {  </district>
124.         String sql = "SELECT * FROM district WHERE districtId='" + parentId + "';";  
125.         try {  
126.             stmt1 = conn.createStatement();  
127.             rs = stmt1.executeQuery(sql);  
128.             if(rs.next()) {  
129.                 int left = rs.getInt("lft");  
130.                 int right = rs.getInt("rgt");  
131.                 stmt2 = conn.createStatement();  
132.                 sql = "SELECT * FROM district WHERE lft BETWEEN " + left + " AND " + right + " ORDER BY lft ASC";  
133.                 rs = stmt2.executeQuery(sql);  
134.                 tree = new ArrayList<district>();  </district>
135.                 District district = null;  
136.                 while(rs.next()) {  
137.                     district = new District();  
138.                     district.setDistrictId(rs.getString("districtId"));  
139.                     district.setDistrictName(rs.getString("districtName"));  
140.                     district.setLeft(rs.getInt("lft"));  
141.                     district.setRight(rs.getInt("rgt"));  
142.                       
143.                     tree.add(district);  
144.                 }     
145.                 return tree;  
146.                   
147.             }  
148.         } catch (SQLException e) {  
149.             // TODO Auto-generated catch block  
150.             e.printStackTrace();  
151.         } finally {  
152.             try {  
153.                 if (rs != null) {  
154.                     rs.close();  
155.                 }  
156.                 stmt1.close();  
157.                 stmt2.close();  
158.                 conn.close();  
159.             } catch (SQLException e) {  
160.                 // TODO Auto-generated catch block  
161.                 e.printStackTrace();  
162.             }  
163.         }  
164.           
165.         return null;  
166.     }  
167.       
168.     public ArrayList<district> getChildren(String parentId) {  </district>
169.         if(tree == null) {  
170.             tree = getTree(parentId);  
171.         }  
172.         ArrayList<district> children = new ArrayList<district>();  </district></district>
173.         int index = this.indexof(parentId);  
174.         if(index != -1) {  
175.             District d = tree.get(index);  
176.             final int count = (d.getRight() - d.getLeft() - 1) / 2;  
177.             for(int i = index + 1; i <= index + count; i++) {  
178.                 d = tree.get(i);  
179.                 children.add(d);  
180.                 i += (d.getRight() - d.getLeft() - 1) / 2;  
181.             }  
182.         }  
183.               
184.         return children;  
185.     }  
186.       
187.     private int indexof(String districtId) {  
188.         for(int index = 0; index < tree.size(); index++) {  
189.             if(districtId.equals(tree.get(index).getDistrictId())) {  
190.                 return index;  
191.             }  
192.         }  
193.         return -1;  
194.     }  
195.       
196.     public boolean updateNode(String districtId, String districtName) {  
197.         String sql = "UPDATE district SET districtName='" + districtName + "' WHERE districtId='" + districtId + "';";  
198.         try {  
199.             conn.setAutoCommit(false);  
200.             stmt1 = conn.createStatement();  
201.             int flag = stmt1.executeUpdate(sql);  
202.             if(flag == 1) {  
203.                 conn.commit();  
204.                 return true;  
205.             }  
206.             else {  
207.                 conn.rollback();  
208.                 return false;  
209.             }  
210.                   
211.         } catch (SQLException e) {  
212.             // TODO Auto-generated catch block  
213.             e.printStackTrace();  
214.         } finally {  
215.               
216.         }  
217.         return false;  
218.     }  
219. }

这个最遗憾的地方就是在其中混杂了java代码，这让我及其不爽，但是我没有找到向js传值的好办法：（

js 代码

 

1. function District(id, name, left, right) {  
2.             this.id=id;  
3.             this.name=name;  
4.             this.left=left;  
5.             this.right=right;  
6.         };  
7.   
8.         var tree = new Array();   
9.               
10.         <%  
11.         for(District d : tree) {  
12.         %>  tree.push(new District('<%=d.getDistrictId()%>', '<%=d.getDistrictName()%>', '<%=d.getLeft()%>', '<%=d.getRight()%>'));  
13.         <%}%>  
14.   
15.         Array.prototype.indexof = function(id) {  
16.             for(var index = 0; index < tree.length; index++) {  
17.                 if(id == tree[index].id) {  
18.                     return index;  
19.                 }  
20.             }  
21.             return -1;  
22.         };  
23.           
24.         function citychanged(id){  
25.             document.getElementById('district').length = 0;  
26.             document.getElementById('area').length = 0;  
27.             var districts = getChildren(id);  
28.             for(var i = 0; i < districts.length; i++){  
29.                 document.getElementById('district').options[i] = new Option(districts[i].name, districts[i].id);  
30.             }  
31.         };  
32.           
33.         function districtchanged(id) {  
34.             document.getElementById('area').length = 0;  
35.             var area = getChildren(id);  
36.             for(var i = 0; i < area.length; i++){  
37.                 document.getElementById('area').options[i] = new Option(area[i].name, area[i].id);  
38.             }  
39.         };  
40.           
41.         function getChildren(id) {  
42.             if(tree.length == 0) {  
43.               
44.             }  
45.             var children = new Array();  
46.             var index = tree.indexof(id);  
47.             if(index != -1) {  
48.                 var d = tree[index];  
49.                 var count = (d.right - d.left - 1) / 2;  
50.                 for(var i = index + 1; i <= index + count; i++) {  
51.                     d = tree[i];  
52.                     children.push(d);  
53.                     i += (d.right - d.left - 1) / 2;  
54.                 }  
55.             }  
56.             return children;  
57.         };  

 

jsp 页面 代码

 

1.     "/fastfoodAct?method=query" styleId="queryFoodForm">  
2.   
3.         searchType : 
4.         city : 
5.             
6.             
     
7.         district : 
8.         
     
9.         area : 
10.         
      
11.         restaurantName : 
12.   
13.         searchType : 
14.         foodName : 
15.          <html:submit> <html:cancel>    </html:cancel> </html:submit>
16.       
17.   

 

以上就是我解决三级联动和层次结构的一点想法：）