JDBC递归遍历ArticleTree

工具：Eclipse+Mysql

MySQL数据库建表

create database bbs;

use bbs;

create table article 
(
id int primary key auto_increment,
pid int,
rootid int,
title varchar(255),
cont text,
pdate datetime,
isleaf int 
);

insert into article values (null, 0, 1, '蚂蚁大战大象', '蚂蚁大战大象', now(), 1);
insert into article values (null, 1, 1, '大象被打趴下了', '大象被打趴下了',now(), 1);
insert into article values (null, 2, 1, '蚂蚁也不好过','蚂蚁也不好过', now(), 0);
insert into article values (null, 2, 1, '瞎说', '瞎说', now(), 1);
insert into article values (null, 4, 1, '没有瞎说', '没有瞎说', now(), 0);
insert into article values (null, 1, 1, '怎么可能', '怎么可能', now(), 1);
insert into article values (null, 6, 1, '怎么没有可能', '怎么没有可能', now(), 0);
insert into article values (null, 6, 1, '可能性是很大的', '可能性是很大的', now(), 0);
insert into article values (null, 2, 1, '大象进医院了', '大象进医院了', now(), 1);
insert into article values (null, 9, 1, '护士是蚂蚁', '护士是蚂蚁', now(), 0);

JAVA代码

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/*
 * 思路：
 * 1.先写递归方法，考虑参数 id，级别，jdbc程序要考虑conn
 * 2.
 */

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=tiger");
            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) {
            System.out.println("SQLException :" + e.getMessage());
            System.out.println("SQLState :" + e.getSQLState());
            System.out.println("VendorError :" + e.getErrorCode());
        } 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;
            rs = stmt.executeQuery(sql);
            while(rs.next()) {
                //此种方法strPre不变
                System.out.println(strPre + rs.getString("cont"));
                //用StringBuffer strPre.append会导致strPre一直变化。
                //System.out.println(strPre.append(rs.getString("cont")));
                if(rs.getInt("isleaf") != 0) {
                    tree(conn, rs.getInt("id"), level +1);
                }

            }
            //strPre.delete(0, strPre.length());

        } catch (SQLException e) {
            System.out.println("SQLException :" + e.getMessage());
            System.out.println("SQLState :" + e.getSQLState());
            System.out.println("VendorError :" + e.getErrorCode());
        } finally {
            try {
                if(rs != null) {
                    rs.close();
                    rs = null;
                }
                if(stmt != null) {
                    stmt.close();
                    stmt = null;
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}