我的标题真冗长:P
最近一直在考虑这个问题,如何在现在常用的关系型数据库(如mysql,mssql)中存储含有层次结构(例如xml或者树型结构)的数据,我最终要实现的结果是一个比较好的页面三级联动菜单。比较了一下现有的三级联动,无非有两种解决方案,1、将所有数据写成静态的,这样的缺点在于对更新封闭;2、每改变一次去数据库中获取数据,填充到下一级菜单,这个缺点是对数据库的开销比较大,查询一次数据要对数据库做三次操作,而且数据库设计的比较丑陋。上述两种方法都让我比较不能接受。
我想的是一种比较优雅的解决方案:在数据库中存储的数据不要冗余,便于查询,不要产生递归,对数据库的开销要尽可能的小,尽量做到一次将所有数据都读出来,用户也要有比较好的体验。我最初的解决方案就是用xml来存储联动的数据,但是对于数据库操作来说,最后还是要落实到对文件的I/O操作,从这方面来讲,使用xml和数据库并没有什么本质的不同。另外一种解决方案就是在关系数据库中存类xml的数据了。google了半天,最后发现了一片自己学院一位学长翻译的文章www.nirvanastudio.org/category/database,讲得就是如何在关系型数据库存储这种层次数据的。文中提出了两种解决方案,一是“邻接列表模型”或称为“递归方法”,这种存在着数据冗余,而且递归出于众所周知的原因,效率不高。第二种方法,也就是我现在采用的方法是"前序优先遍历",表结构也很简单,没有冗余
id
name
left
right
我觉得应该属于一种深度优先。通过每个节点的左右两个属性获得其子女的属性,好处就在于获得一个或几个节点只需要一次查询,而在更新的时候速度会比较慢,因为要更新多个后继节点,不过话说回来,更新相对于获取来说次数要少的多,可以忽略。
下面是我的解决方案:
java 代码
importjava.io.IOException;
importjava.sql.Connection;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
importjava.util.ArrayList;
importcom.netkc.struts.datasource.DataSource;
importcom.netkc.struts.mapping.District;
/**
* @author SONG Yihan
* @version 1.0
* @date: Monday, April 09 2007
*/
publicclassDistrictTree {
privateArrayListtree;
privateStatement stmt1 =null;
privateStatement stmt2 =null;
privateConnection conn =null;
privateResultSet rs =null;
publicDistrictTree() {
try{
conn = DataSource.getConnection();
} catch(IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch(ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch(InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch(IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
publicDistrictTree(Connection conn) {
this.conn = conn;
}
publicbooleanaddNode(String parentId, String districtId, String districtName) {
try{
String sql = "SELECT * FROM district WHERE districtId='"+ parentId +"';";
conn.setAutoCommit(false);
stmt1 = conn.createStatement();
rs = stmt1.executeQuery(sql);
if(rs.next()) {
intright = rs.getInt("rgt");
sql = "UPDATE district SET lft=lft+2 WHERE lft>="+ right;
stmt2.addBatch(sql);
sql = "UPDATE district SET rgt=rgt+2 WHERE rgt>="+ right;
stmt2.addBatch(sql);
sql = "INSERT INTO district (districtId, districtName, lft, rgt) VALUES ('"+districtId +"', '"+ districtName +"', "+
(right) + ", "+ (right+1) +");";
stmt2.addBatch(sql);
int[] flag = stmt2.executeBatch();
if(flag[flag.length -1] ==1) {
conn.commit();
returntrue;
} else{
conn.rollback();
returnfalse;
}
}
} catch(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
}
returnfalse;
}
publicbooleandeleteNode(String districtId) {
String sql = "SELECT * FROM district WHERE districtId='"+ districtId +"';";
try{
conn.setAutoCommit(false);
rs = stmt1.executeQuery(sql);
if(rs.next())
{
intleft = rs.getInt("lft");
intright = rs.getInt("rgt");
intcount = (right - left -1) /2+1;
intminus = count *2;
sql = "DELETE FROM district WHERE lft BETWEEN "+ left +" AND "+ right +";";
stmt2.addBatch(sql);
sql = "UPDATE district SET lft=lft-"+ minus +" WHERE lft>"+right;
stmt2.addBatch(sql);
sql = "UPDATE district SET rgt=rgt-"+ minus +" WHERE rgt>"+right;
stmt2.addBatch(sql);
intflag = stmt2.executeBatch()[0];
if(flag ==1) {
conn.commit();
returntrue;
} else{
conn.rollback();
returnfalse;
}
}
} catch(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
returnfalse;
}
publicvoiddisplayTree(String parentId) {
}
publicArrayListgetTree(String parentId) {
String sql = "SELECT * FROM district WHERE districtId='"+ parentId +"';";
try{
stmt1 = conn.createStatement();
rs = stmt1.executeQuery(sql);
if(rs.next()) {
intleft = rs.getInt("lft");
intright = rs.getInt("rgt");
stmt2 = conn.createStatement();
sql = "SELECT * FROM district WHERE lft BETWEEN "+ left +" AND "+ right +" ORDER BY lft ASC";
rs = stmt2.executeQuery(sql);
tree = newArrayList();
District district = null;
while(rs.next()) {
district = newDistrict();
district.setDistrictId(rs.getString("districtId"));
district.setDistrictName(rs.getString("districtName"));
district.setLeft(rs.getInt("lft"));
district.setRight(rs.getInt("rgt"));
tree.add(district);
}
returntree;
}
} catch(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
try{
if(rs !=null) {
rs.close();
}
stmt1.close();
stmt2.close();
conn.close();
} catch(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
returnnull;
}
publicArrayListgetChildren(String parentId) {
if(tree ==null) {
tree = getTree(parentId);
}
ArrayListchildren = newArrayList();
intindex =this.indexof(parentId);
if(index != -1) {
District d = tree.get(index);
finalintcount = (d.getRight() - d.getLeft() -1) /2;
for(inti = index +1; i <= index + count; i++) {
d = tree.get(i);
children.add(d);
i += (d.getRight() - d.getLeft() - 1) /2;
}
}
returnchildren;
}
privateintindexof(String districtId) {
for(intindex =0; index
if(districtId.equals(tree.get(index).getDistrictId())) {
returnindex;
}
}
return-1;
}
publicbooleanupdateNode(String districtId, String districtName) {
String sql = "UPDATE district SET districtName='"+ districtName +"' WHERE districtId='"+ districtId +"';";
try{
conn.setAutoCommit(false);
stmt1 = conn.createStatement();
intflag = stmt1.executeUpdate(sql);
if(flag ==1) {
conn.commit();
returntrue;
}
else{
conn.rollback();
returnfalse;
}
} catch(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
}
returnfalse;
}
}
这个最遗憾的地方就是在其中混杂了java代码,这让我及其不爽,但是我没有找到向js传值的好办法:(
js 代码
functionDistrict(id, name, left, right) {
this.id=id;
this.name=name;
this.left=left;
this.right=right;
};
vartree =newArray();
for(District d : tree) {
%> tree.push(newDistrict('', '', '', ''));
Array.prototype.indexof = function(id) {
for(varindex = 0; index
if(id == tree[index].id) {
returnindex;
}
}
return-1;
};
functioncitychanged(id){
document.getElementById('district').length = 0;
document.getElementById('area').length = 0;
vardistricts = getChildren(id);
for(vari = 0; i
document.getElementById('district').options[i] = newOption(districts[i].name, districts[i].id);
}
};
functiondistrictchanged(id) {
document.getElementById('area').length = 0;
vararea = getChildren(id);
for(vari = 0; i
document.getElementById('area').options[i] = newOption(area[i].name, area[i].id);
}
};
functiongetChildren(id) {
if(tree.length == 0) {
}
varchildren =newArray();
varindex = tree.indexof(id);
if(index != -1) {
vard = tree[index];
varcount = (d.right - d.left - 1) / 2;
for(vari = index + 1; i <= index + count; i++) {
d = tree[i];
children.push(d);
i += (d.right - d.left - 1) / 2;
}
}
returnchildren;
};
jsp 页面 代码
"/fastfoodAct?method=query"styleId="queryFoodForm">
searchType :
city :
district :
area :
restaurantName :
searchType :
foodName :
以上就是我解决三级联动和层次结构的一点想法:)