树结构在数据库中的实现

import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSetMetaData;
import javax.sql.DataSource;
import net.sourceforge.jtds.jdbcx.JtdsDataSource;
import javax.swing.tree.DefaultMutableTreeNode;
import javax.swing.tree.TreeNode;
import java.awt.event.ActionEvent;
import javax.swing.SpinnerNumberModel;
import javax.swing.JFrame;
import javax.swing.JButton;
import java.awt.event.ActionListener;
import javax.swing.JSpinner;
import javax.swing.JToolBar;
import javax.swing.JScrollPane;
import javax.swing.JTree;
import javax.swing.tree.DefaultTreeModel;
//一个把树结构展现在表中的实现
//使用SqlServer数据库jtds的jdbc驱动
//表名为:dbTree;
//字段1：int         nodeId (主键，自增列)
//字段2：float       nodeOrder            
//字段3：varchar(50) parentPath
//nodeId： 用来唯一标识树中每一个节点
//nodeOrder：用来标识树节点在同父兄弟节点中的排序位置，默认为1024.
//           每追加一个子节点其nodeOrder为前一nodeOrder*2;
//           在第一个子节点之前加入节点则nodeOrder为第一节点nodeOrder/2;
//           在两节点之间插入节点，则nodeOrder为前一节点与后一节点nodeOrder的平均值；
//           这个方法是叫两分法？
//parentPath:用来存贮所有祖先节点的nodeId依次用逗号分隔，直到父节点为止;
//范例数据创建脚本:dbTree
/*
CREATE TABLE [dbtree] (
         [nodeId] [int] IDENTITY (1, 1) NOT NULL ,
         [nodeOrder] [float] NULL ,
         [parentPath] [varchar] (64) COLLATE Chinese_PRC_CI_AS NULL ,
         [userObject] [int] NULL ,
         CONSTRAINT [PK_DbTree] PRIMARY KEY CLUSTERED
         (
                 [nodeId]
         ) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT INTO dbtree (nodeOrder,parentPath) values (1024.0 , null );
INSERT INTO dbtree (nodeOrder,parentPath) values (2048.0 , null );
INSERT INTO dbtree (nodeOrder,parentPath) values (4096.0 , null );
INSERT INTO dbtree (nodeOrder,parentPath) values (1024.0 , '1,' );
INSERT INTO dbtree (nodeOrder,parentPath) values (2048.0 , '1,' );
INSERT INTO dbtree (nodeOrder,parentPath) values (1024.0 , '1,4,' );
INSERT INTO dbtree (nodeOrder,parentPath) values (1024.0 , '2,' );

------------运行环境------------------------------
JTDS的SQLSERVER2000驱动需要加入CLASSPATH;
数据库是默认数据库的MyDb也许你的环境下需要一些改变；
数据库端口用的7788不是sqlServer默认的需要改。
------------代码简要介绍------------------------------
其中最主要的类是DefaultDbTreeNode继承了DefaultMutableTreeNode;
里边有一些具体功能的实现，大概就是加载自身信息，加载自身子节点信息，还有就是在判断isLeaf()时根据是否已加载子节点信息来实现第一次展开时加载。SUN的DefaultTreeModel设计的很精妙。重载的程序可以运行的很理想。
其他的类都是辅助类不用看。大致就是让数据库操作的代码更简单是，个人习惯。
-----------------------------------------
欢迎指教讨论更好的方法。
*/

public class DbTreeDemo extends JFrame {
    public static void main(String[] args) {
        DbTreeDemo n = new DbTreeDemo();
        n.setSize(600, 400);
        n.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        n.setVisible(true);
    }
    JTree tree = new JTree();
    JScrollPane jsp = new JScrollPane(tree);
    JButton btnInsert = new JButton("Insert At");
    JButton btnDelete = new JButton("Delete");
    SpinnerNumberModel spinModel = new SpinnerNumberModel(1, 0, 10, 1);
    JSpinner jspin = new JSpinner(spinModel);
    JToolBar toolBar = new JToolBar();
    DefaultTreeModel model;
    public DbTreeDemo() {
        //init ui
        add(jsp, "Center");
        add(toolBar, "North");
        toolBar.add(btnInsert);
        toolBar.add(jspin);
        toolBar.add(btnDelete);
        SqlServerDB db = SqlServerDB.getInstance();
        DefaultDbTreeNode node = new DefaultDbTreeNode(db.getDataSource(),
                "dbtree");
        model = new DefaultTreeModel(node.getDefaultRootNode());
        tree.setModel(model);
        //init event
        btnInsert.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                System.out.println("有待你的实现！");
            }
        });
        btnDelete.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                System.out.println("有待你的实现！");
            }
        });
        String sql = "select * from dbtree";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = db.getInstance().getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            DB.showResultSet(rs);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            DB.closeResultSet(rs);
            DB.closeStatement(stmt);
            DB.closeConnection(conn);
        }
    }
    class DB {
        public static void closeConnection(Connection conn) {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                } finally {
                    conn = null;
                }
            }
        }
        public static void closeStatement(Statement stmt) {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                } finally {
                    stmt = null;
                }
            }
        }
        public static void closeResultSet(ResultSet rs) {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                } finally {
                    rs = null;
                }
            }
        }
        public static void showResultSet(ResultSet rs) {
            try {
                ResultSetMetaData meta = rs.getMetaData();
                System.out.println(
                        "----------------Result Set-------------------");
                System.out.println("Fields:");
                for (int i = 1; i <= meta.getColumnCount(); i++) {
                    System.out.print(meta.getColumnName(i) + " , ");
                }
                System.out.println();
                System.out.println("Data:");
                while (rs.next()) {
                    for (int i = 1; i <= meta.getColumnCount(); i++) {
                        System.out.print(rs.getString(i) + " , ");
                    }
                    System.out.println();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
    class SqlServerDB extends DB {
        static SqlServerDB _db = new SqlServerDB();
        String _dbUrl = "jdbc:jtds:sqlserver://127.0.0.1:7788";
        String _dbDriver = "net.sourceforge.jtds.jdbc.Driver";
        private SqlServerDB() {}

        public static SqlServerDB getInstance() {
            return _db;
        }

        DataSource _ds;
        public DataSource getDataSource() {
            if (_ds == null) {
                JtdsDataSource jds = new JtdsDataSource();
                jds.setServerName("127.0.0.1");
                jds.setPortNumber(7788);
                jds.setDatabaseName("MyDB");
                _ds = jds;
            }
            return _ds;
        }
        public Connection getConnection() {
            try {
                return getDataSource().getConnection();
            } catch (SQLException ex) {
                return null;
            }
        }
        public static void main(String[] args) throws SQLException {
            SqlServerDB db = SqlServerDB.getInstance();
            Connection conn = db.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from syscolumns");
            db.showResultSet(rs);
            db.closeResultSet(rs);
            db.closeStatement(stmt);
            db.closeConnection(conn);
        }
    }
    class DefaultDbTreeNode extends DefaultMutableTreeNode {
        public int _nodeId = -1;
        public float _nodeOrder = 1024;
        public String _parentPath = "";

        public DataSource _ds;
        public String _tableName;

        public DefaultDbTreeNode(DataSource ds, String tableName) {
            _ds = ds;
            _tableName = tableName;
        }
        /***自定义的关于树方法***/
        public TreeNode getDefaultRootNode() {
            DefaultDbTreeNode root = new DefaultDbTreeNode(_ds, _tableName);
            root.setParent(null);
            root._childrenLoaded = true;
            String sql = "select nodeId,nodeOrder,parentPath,userObject from " +
                         _tableName +
                         " where parentPath is null or parentPath=''";
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            try {
                conn = _ds.getConnection();
                stmt = conn.createStatement();
                rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    DefaultDbTreeNode cNode = new DefaultDbTreeNode(_ds,
                            _tableName);
                    cNode._nodeId = rs.getInt(FLD_ID);
                    cNode._nodeOrder = rs.getFloat(FLD_ORDER);
                    root.add(cNode);
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                DB.closeResultSet(rs);
                DB.closeStatement(stmt);
                DB.closeConnection(conn);
            }
            return root;
        }
        public void load() { //加载自身
            //System.out.println("Load:"+_nodeId);
            String sql =
                    "select nodeOrder,parentPath,userObject from dbtree where nodeId=" +
                    _nodeId;
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            try {
                conn = _ds.getConnection();
                stmt = conn.createStatement();
                rs = stmt.executeQuery(sql);
                _nodeOrder = rs.getFloat(FLD_ORDER);
                _parentPath = rs.getString(FLD_PATH);
                //TODO:user object dispath;
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                DB.closeResultSet(rs);
                DB.closeStatement(stmt);
                DB.closeConnection(conn);
            }
            loadChildren();
        }
        boolean _childrenLoaded = false;
        public void loadChildren() {
            //加载自身子节点
            //System.out.println("Load Children!:"+_nodeId);
            _childrenLoaded = true;
            String nodePath = getNodePath();
            String sql =
                    "select nodeId,nodeOrder,userObject from dbtree where parentPath='" +
                    nodePath + "' ";
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            removeAllChildren();
            try {
                conn = _ds.getConnection();
                stmt = conn.createStatement();
                rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    DefaultDbTreeNode cNode = new DefaultDbTreeNode(_ds,
                            _tableName);
                    cNode._nodeId = rs.getInt(FLD_ID);
                    cNode._nodeOrder = rs.getFloat(FLD_ORDER);
                    cNode._parentPath = nodePath;
                    //TODO: USER OBJECT DISPATHC HERE
                    cNode.loadChildren();
                    add(cNode);
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                DB.closeResultSet(rs);
                DB.closeStatement(stmt);
                DB.closeConnection(conn);
            }
        }
        public void refresh() {
            load();
            loadChildren();
        }
        public String getNodePath() {
            return _parentPath + _nodeId + ",";
        }
        public boolean isLeaf() {
            //System.out.println("isLeaf()"+_nodeId+" "+super.isLeaf());
            if (!_childrenLoaded) {
                loadChildren();
            }
            System.out.println(super.isLeaf());
            return super.isLeaf();
        }
        /***继承自Object的方法***/
        public String toString() {
            return "{DefaultDbTreeNode[id:" + _nodeId + "],[order:" +
                    _nodeOrder + "],[path:" + _parentPath + "],[userObject:" +
                    getUserObject() + "]}";
        }

        /****字段***/
        public static String FLD_ID = "nodeId";
        public static String FLD_ORDER = "nodeOrder";
        public static String FLD_PATH = "parentPath";
        public static String FLD_USEROBJECT = "userObject";
    }
}