1.数据库:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for article
-- ----------------------------
CREATE TABLE `article` (
`id` int(11) NOT NULL auto_increment,
`pid` int(11) default NULL,
`rootid` int(11) default NULL,
`title` varchar(255) default NULL,
`cont` text,
`pdate` datetime default NULL,
`isleaf` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `article` VALUES ('1', '0', '1', '蚂蚁大战大象', '蚂蚁大战大象', '2009-07-27 01:44:54', '1');
INSERT INTO `article` VALUES ('2', '1', '1', '大象被打趴下了', '大象被打趴下了', '2009-07-27 01:44:54', '1');
INSERT INTO `article` VALUES ('3', '2', '1', '蚂蚁也不好过', '蚂蚁也不好过', '2009-07-27 01:44:54', '0');
INSERT INTO `article` VALUES ('4', '2', '1', '瞎说', '瞎说', '2009-07-27 01:44:54', '1');
INSERT INTO `article` VALUES ('5', '4', '1', '没有瞎说', '没有瞎说', '2009-07-27 01:44:54', '0');
INSERT INTO `article` VALUES ('6', '1', '1', '怎么可能', '怎么可能', '2009-07-27 01:44:54', '1');
INSERT INTO `article` VALUES ('7', '6', '1', '怎么没有可能', '怎么没有可能', '2009-07-27 01:44:54', '0');
INSERT INTO `article` VALUES ('8', '6', '1', '可能性是很大的', '可能性是很大的', '2009-07-27 01:44:54', '0');
INSERT INTO `article` VALUES ('9', '2', '1', '大象进医院了', '大象进医院了', '2009-07-27 01:44:54', '1');
INSERT INTO `article` VALUES ('10', '9', '1', '护士是蚂蚁', '护士是蚂蚁', '2009-07-27 01:44:54', '0');
2.源代码:
3.运行结果:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for article
-- ----------------------------
CREATE TABLE `article` (
`id` int(11) NOT NULL auto_increment,
`pid` int(11) default NULL,
`rootid` int(11) default NULL,
`title` varchar(255) default NULL,
`cont` text,
`pdate` datetime default NULL,
`isleaf` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `article` VALUES ('1', '0', '1', '蚂蚁大战大象', '蚂蚁大战大象', '2009-07-27 01:44:54', '1');
INSERT INTO `article` VALUES ('2', '1', '1', '大象被打趴下了', '大象被打趴下了', '2009-07-27 01:44:54', '1');
INSERT INTO `article` VALUES ('3', '2', '1', '蚂蚁也不好过', '蚂蚁也不好过', '2009-07-27 01:44:54', '0');
INSERT INTO `article` VALUES ('4', '2', '1', '瞎说', '瞎说', '2009-07-27 01:44:54', '1');
INSERT INTO `article` VALUES ('5', '4', '1', '没有瞎说', '没有瞎说', '2009-07-27 01:44:54', '0');
INSERT INTO `article` VALUES ('6', '1', '1', '怎么可能', '怎么可能', '2009-07-27 01:44:54', '1');
INSERT INTO `article` VALUES ('7', '6', '1', '怎么没有可能', '怎么没有可能', '2009-07-27 01:44:54', '0');
INSERT INTO `article` VALUES ('8', '6', '1', '可能性是很大的', '可能性是很大的', '2009-07-27 01:44:54', '0');
INSERT INTO `article` VALUES ('9', '2', '1', '大象进医院了', '大象进医院了', '2009-07-27 01:44:54', '1');
INSERT INTO `article` VALUES ('10', '9', '1', '护士是蚂蚁', '护士是蚂蚁', '2009-07-27 01:44:54', '0');
2.源代码:
- package com.bjtu.jdbc;
- import java.sql.*;
- public class ArticleTree {
- public static void main(String[] args) {
- new ArticleTree().show();
- }
- public void show() {
- Connection conn = null;
- Statement stmt = null;
- ResultSet rs = null;
- try {
- Class.forName("com.mysql.jdbc.Driver");
- conn = DriverManager
- .getConnection("jdbc:mysql://localhost/bbs?user=root&password=admin");
- stmt = conn.createStatement();
- rs = stmt.executeQuery("select * from article where pid = 0"); //根节点
- while(rs.next()){
- System.out.println(rs.getString("cont"));
- tree(conn, rs.getInt("id"), 1);
- }
- } catch(ClassNotFoundException e) {
- e.printStackTrace();
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- try {
- if(rs != null) {
- rs.close();
- rs = null;
- }
- if(stmt != null) {
- stmt.close();
- stmt = null;
- }
- if(conn != null) {
- conn.close();
- conn = null;
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
- private void tree(Connection conn, int id, int level) {
- Statement stmt = null;
- ResultSet rs = null;
- StringBuffer strPre = new StringBuffer("");
- for(int i=0; i<level; i++) {
- strPre.append(" ");
- }
- try {
- stmt = conn.createStatement();
- String sql = "select * from article where pid = " + id;//父节点ID
- rs = stmt.executeQuery(sql);
- while(rs.next()) {
- System.out.println(strPre + rs.getString("cont"));
- if(rs.getInt("isleaf") != 0)
- tree(conn, rs.getInt("id"), level + 1);
- }
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- try {
- if(rs != null) {
- rs.close();
- rs = null;
- }
- if(stmt != null) {
- stmt.close();
- stmt = null;
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
- }
3.运行结果:
- 蚂蚁大战大象
- 大象被打趴下了
- 蚂蚁也不好过
- 瞎说
- 没有瞎说
- 大象进医院了
- 护士是蚂蚁
- 怎么可能
- 怎么没有可能
- 可能性是很大的