論壇回覆的樹狀結構顯示是經典難題。本文採用「非遞歸、單次查詢、記憶體中組裝」的高效能演算法,配合 MySQL 資料表設計與 JSP 顯示邏輯,完整呈現實作細節。
| 欄位 | 型別 | 說明 |
|---|---|---|
| bbsID | INT AUTO_INCREMENT PK | 主鍵 |
| rootID | INT | 根文章 ID,根文章時 rootID = bbsID |
| fid | INT | 父文章 ID,根文章時 fid = 0 |
| depth | INT | 層級深度:根=0,回覆遞增 |
| bbsSubject | VARCHAR(100) | 主題 |
| bbsContent | TEXT | 內容 |
| bbsTime | VARCHAR(30) | 發文時間 |
| bbsRead | INT | 閱讀數 |
| bbsReply | INT | 回覆數 |
CREATE TABLE mybbslist (
forumID INT(20) NOT NULL,
bbsID INT AUTO_INCREMENT PRIMARY KEY,
rootID INT(20) NOT NULL,
fid INT(20) NOT NULL,
depth INT(20) NOT NULL,
userID INT(20) NOT NULL,
bbsUser VARCHAR(24) NOT NULL,
bbsSubject VARCHAR(100) NOT NULL,
bbsContent TEXT,
bbsTime VARCHAR(30),
bbsRead INT(20),
bbsReply INT(20),
INDEX forumID (forumID)
);
package netzero;
import java.sql.*;
public class mydb {
private String driver = "org.gjt.mm.mysql.Driver";
private String url = "jdbc:mysql://localhost/mybbs?user=root&password=how&useUnicode=true&characterEncoding=8859_1";
private Connection conn;
private Statement stmt;
private ResultSet rs;
public mydb() {
try { Class.forName(driver); }
catch (ClassNotFoundException e) { System.err.println(e.getMessage()); }
}
public ResultSet executeQuery(String sql) throws SQLException {
conn = DriverManager.getConnection(url);
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
return rs;
}
public boolean closeConn() {
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
return true;
} catch (SQLException e) {
System.err.println("closeConn: " + e.getMessage());
return false;
}
}
}
<%@ page contentType="text/html;charset=gb2312" %>
<%@ page import="java.sql.*" %>
<jsp:useBean id="mybbs" scope="session" class="netzero.mydb" />
<%
out.print("顯示論壇樹形結構<br><br>");
String sql = "SELECT * FROM mybbslist ORDER BY rootID DESC, depth, fid, bbsID";
ResultSet rs = mybbs.executeQuery(sql);
if (rs.next()) {
rs.last();
int total = rs.getRow();
out.print("論壇樹中有 " + total + " 個節點<br>");
rs.first();
int currentDepth = 0;
out.print("<ul>");
for (int i = 0; i < total; i++) {
int rowDepth = rs.getInt("depth");
// 關閉較深層級的 ul
if (rowDepth < currentDepth) {
for (int k = 0; k < currentDepth - rowDepth; k++) {
out.print("</ul>");
}
}
// 開啟較深層級的 ul
else if (rowDepth > currentDepth) {
out.print("<ul>");
}
out.print("<li>" + rs.getString("bbsSubject") + "</li>");
currentDepth = rowDepth;
rs.next();
}
// 關閉剩餘 ul
for (int k = 0; k <= currentDepth; k++) {
out.print("</ul>");
}
} else {
out.print("資料庫中無記錄");
}
%>
<%
try { mybbs.closeConn(); } catch (Exception e) { e.printStackTrace(); }
%>
ORDER BY rootID DESC, depth, fid, bbsID 確保同一棵樹的節點連續、由淺入深、同層按父子順序排列。depth,與前一行 currentDepth 比較:
</ul> 關閉多餘層級<ul> 開啟新層級<li>rootID 為單位分頁,避免單頁過長。