论坛帖子的树状实现

百度贴吧等论坛在几年前推出了“楼中楼”系统，这意味着帖子分为三层：主题-楼层-楼中楼。这很显然是一个树状结构，我这几天试着用jsp实现了这种结构（无限层），顺便练一下新学的MySQL。

对于一个帖子，如何存储在数据库中？我想应该包含如下key：

id，帖子的编号

fid，“父帖子”的编号

rootid，根即帖子所在主题的编号

title，帖子标题（有点多余）

cont，内容

isleaf，是否为叶子

完成数据项的设计后，下面一个问题是：如何树状地展示这些帖子呢？

其实这是一个典型的树的先序遍历+bfs：先输出帖子本身，再逐层向下搜索，直到叶帖子为止。

回复很简单，就是添加结点，只要注意更新父帖子的子帖子数即可。

删除也是bfs，因此要向下一个页面传递父帖子编号，另外要注意更新isleaf。

部分代码：

<%!         String str = "";         private void tree(Connection conn, int id, int level) {                Statement stmt = null;ResultSet rs = null;String preStr = "";for (int i = 0; i < level; i++)preStr += "----";try {stmt = conn.createStatement();String sql = "select * from article where fid = " + id;rs = stmt.executeQuery(sql);while (rs.next()) {strDel = "<td><a href='delete.jsp?id=" + rs.getInt("id")+ "&fid=" + rs.getInt("fid") + "'>删除</a>";str += "<tr><td>" + rs.getInt("id") + "</td><td>" + preStr+ "<a href='showDetail.jsp?id=" + rs.getInt("id")+ "'>" + rs.getString("title") + "</a></td>" + strDel+ "</td></tr>";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();}}}%><%Class.forName("com.mysql.jdbc.Driver");String url = "jdbc:mysql://localhost/bbs?user=root&password=root";Connection conn = DriverManager.getConnection(url);Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("select * from article where fid = 0");String strDel = "";while (rs.next()) {strLogin = "<td><a href='delete.jsp?id=" + rs.getInt("id")+ "&fid=" + rs.getInt("fid") + "'>删除</a>";str += "<tr><td>" + rs.getInt("id") + "</td><td>"+ "<a href='showDetail.jsp?id=" + rs.getInt("id")+ "'>" + rs.getString("title") + "</a></td>" + strDel+ "</td></tr>";if (rs.getInt("isleaf") != 0) tree(conn, rs.getInt("id"), 1);}rs.close();stmt.close();conn.close();%>

删除帖子：

<%!public void del(Connection conn, int id) {Statement stmt = null;ResultSet rs = null;try {stmt = conn.createStatement();String sql = "select * from article where fid = " + id;rs = stmt.executeQuery(sql);while (rs.next()) {del(conn, rs.getInt("id"));}stmt.execute("delete from article where id = " + id);} 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();}}}%><%int id = Integer.parseInt(request.getParameter("id"));int fid = Integer.parseInt(request.getParameter("fid"));Class.forName("com.mysql.jdbc.Driver");String url = "jdbc:mysql://localhost/bbs?user=root&password=root";Connection conn = DriverManager.getConnection(url);conn.setAutoCommit(false);del(conn, id);Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("select count(*) from article where fid = " + fid);rs.next();int count = rs.getInt(1);rs.close();stmt.close();if(count <= 0) {Statement stmtUpdate = conn.createStatement();stmtUpdate.executeUpdate("update article set isleaf = 0 where id = " + fid);stmtUpdate.close();} conn.commit();conn.setAutoCommit(true);conn.close();response.sendRedirect("show.jsp");%>

缺点是：重复太多；面向对象体现得不够好

过几天会推出改进版。