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

我的标题真冗长：P

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

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

id

name

left

right

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

下面是我的解决方案：

java 代码

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.netkc.struts.datasource.DataSource;
import com.netkc.struts.mapping.District;

/**
 * @author SONG Yihan
 * @version 1.0
 * @date: Monday, April 09 2007
 */
public class DistrictTree {

    private ArrayList<district> tree;  </district>
    private Statement stmt1 = null;
    private Statement stmt2 = null;
    private Connection conn = null;
    private ResultSet rs = null;

    public DistrictTree() {
        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();
        }
    }

    public DistrictTree(Connection conn) {
        this.conn = conn;
    }

    public boolean addNode(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()) {
                int right = 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();
                    return true;
                } else {
                    conn.rollback();
                    return false;
                }
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {


        }
        return false;
    }

    public boolean deleteNode(String districtId) {
        String sql = "SELECT * FROM district WHERE districtId='" + districtId + "';";
        try {
            conn.setAutoCommit(false);
            rs = stmt1.executeQuery(sql);
            if(rs.next())
            {
                int left = rs.getInt("lft");
                int right = rs.getInt("rgt");
                int count = (right - left - 1) / 2 + 1;
                int minus = 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);
                int flag = stmt2.executeBatch()[0];
                if(flag == 1) {
                    conn.commit();
                    return true;
                } else {
                    conn.rollback();
                    return false;
                }

            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return false;
    }

    public void displayTree(String parentId) {


    }

    public ArrayList<district> getTree(String parentId) {  </district>
        String sql = "SELECT * FROM district WHERE districtId='" + parentId + "';";
        try {
            stmt1 = conn.createStatement();
            rs = stmt1.executeQuery(sql);
            if(rs.next()) {
                int left = rs.getInt("lft");
                int right = 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 = new ArrayList<district>();  </district>
                District district = null;
                while(rs.next()) {
                    district = new District();
                    district.setDistrictId(rs.getString("districtId"));
                    district.setDistrictName(rs.getString("districtName"));
                    district.setLeft(rs.getInt("lft"));
                    district.setRight(rs.getInt("rgt"));

                    tree.add(district);
                }
                return tree;

            }
        } 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();
            }
        }

        return null;
    }

    public ArrayList<district> getChildren(String parentId) {  </district>
        if(tree == null) {
            tree = getTree(parentId);
        }
        ArrayList<district> children = new ArrayList<district>();  </district></district>
        int index = this.indexof(parentId);
        if(index != -1) {
            District d = tree.get(index);
            final int count = (d.getRight() - d.getLeft() - 1) / 2;
            for(int i = index + 1; i <= index + count; i++) {
                d = tree.get(i);
                children.add(d);
                i += (d.getRight() - d.getLeft() - 1) / 2;
            }
        }

        return children;
    }

    private int indexof(String districtId) {
        for(int index = 0; index < tree.size(); index++) {
            if(districtId.equals(tree.get(index).getDistrictId())) {
                return index;
            }
        }
        return -1;
    }

    public boolean updateNode(String districtId, String districtName) {
        String sql = "UPDATE district SET districtName='" + districtName + "' WHERE districtId='" + districtId + "';";
        try {
            conn.setAutoCommit(false);
            stmt1 = conn.createStatement();
            int flag = stmt1.executeUpdate(sql);
            if(flag == 1) {
                conn.commit();
                return true;
            }
            else {
                conn.rollback();
                return false;
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {

        }
        return false;
    }
}

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

js 代码

function District(id, name, left, right) {
            this.id=id;
            this.name=name;
            this.left=left;
            this.right=right;
        };

        var tree = new Array();

        <%
        for(District d : tree) {
        %>  tree.push(new District('<%=d.getDistrictId()%>', '<%=d.getDistrictName()%>', '<%=d.getLeft()%>', '<%=d.getRight()%>'));
        <%}%>

        Array.prototype.indexof = function(id) {
            for(var index = 0; index < tree.length; index++) {
                if(id == tree[index].id) {
                    return index;
                }
            }
            return -1;
        };

        function citychanged(id){
            document.getElementById('district').length = 0;
            document.getElementById('area').length = 0;
            var districts = getChildren(id);
            for(var i = 0; i < districts.length; i++){
                document.getElementById('district').options[i] = new Option(districts[i].name, districts[i].id);
            }
        };

        function districtchanged(id) {
            document.getElementById('area').length = 0;
            var area = getChildren(id);
            for(var i = 0; i < area.length; i++){
                document.getElementById('area').options[i] = new Option(area[i].name, area[i].id);
            }
        };

        function getChildren(id) {
            if(tree.length == 0) {

            }
            var children = new Array();
            var index = tree.indexof(id);
            if(index != -1) {
                var d = tree[index];
                var count = (d.right - d.left - 1) / 2;
                for(var i = index + 1; i <= index + count; i++) {
                    d = tree[i];
                    children.push(d);
                    i += (d.right - d.left - 1) / 2;
                }
            }
            return children;
        };

 

jsp 页面 代码

    "/fastfoodAct?method=query" styleId="queryFoodForm">

        searchType :
        city :


        district :

        area :

        restaurantName :

        searchType :
        foodName :
        
    <html:submit>
    <html:cancel>
      
    </html:cancel>
    </html:submit>

 

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