Bulletin Boards with replies

This section describes:

  1. How to make bulletin boards with replies using databases that have not Hierarchical Queries
  2. Hierarchical Queries in Oracle SQL
  3. Using Oracle Hierarchical Query to create bulletin boards with replies

Below is a typical bulletin board with replies.

no title wdate
29 29 posts 2007/12/09
27 27 posts 2007/11/27
32 ↳ First reply in 27 posts 2007/12/22
26 26 posts 2007/11/25
30 ↳ Second reply in 26 posts 2007/12/20
31 ↳ First reply in 30 posts 2007/12/21
33 ↳ First reply in 31 posts 2007/12/23
28 ↳ First reply in 26 posts 2007/12/01
25 posts 1 2007/11/20

How to make bulletin boards with replies using databases that have not Hierarchical Queries

The 25th post, the 26th post, the 27th post, the29th post are original posts, not replies. So these become the top post of their group.

Fields to add for bulletin boards with replies

Each post should have the following information:

  1. which group it belongs to
  2. how far below it is from the top of the group
  3. how much its title should be indented on the list

Add a column named family for 1. Add a column named depth for 2. Add a column named indent for 3.

Column Type Description
family number Group number
depth number Order from the top of the group
indent number Indentation level

When you delete the 30th posts, you must also delete the 31st and 33rd posts. But you can't do this with just the family, depth, and indent fields. You need information about replies' parents.

Add a column named parent.

no parent title wdate
29 0 29 posts 2007/12/09
27 0 27 posts 2007/11/27
32 27   ↳ First reply in 27 posts 2007/12/22
26 0 26 posts 2007/11/25
30 26   ↳ Second reply in 26 posts 2007/12/20
31 30     ↳ First reply in 30 posts 2007/12/21
33 31       ↳ First reply in 31 posts 2007/12/23
28 26   ↳ First reply in 26 posts 2007/12/01
25 0 25 posts 2007/11/20

In conclusion, the columns needed for bulletin boards with replies are:

Column Type Description
family number Group number
parent number The no column value of the parent posts
depth number Order from the top of the group
indent number Indentation level

bulletin boards with replies algorithm

New posts

A new post creates a group.
The column values are determined as follows.

Field Value
family Same as no column value
parent 0
depth 0
indent 0

Reply

The column values are determined as follows.

Field Value
family Group number
parent parent posts's no
depth (parent's depth) + 1
indent (parent's indent) + 1

Changing the board table for bulletin boards with replies

alter table board 
add (family number, parent number, depth number, indent number)
/
update board set family = no, parent = 0, depth = 0, indent = 0
/

Modify list.jsp

Modify the following query to retrieve the records. 

if (keyword.equals("")) {
  sql = "SELECT no,family,parent,depth,indent,title,wdate " +
    "FROM (SELECT ROWNUM R, A.* FROM (" +
    "SELECT no,family,parent,depth,indent,title,wdate FROM board " + 
    "ORDER BY family DESC, depth ASC) A) " +
    "WHERE R BETWEEN ? AND ?";
} else {
  sql = "SELECT no,family,parent,depth,indent,title,wdate " +
    "FROM (SELECT ROWNUM R, A.* FROM (" +
    "SELECT no,family,parent,depth,indent,title,wdate FROM board " +
    "WHERE title LIKE '%" + keyword + "%' OR content LIKE '%" + keyword + "%' " + 
    "ORDER BY family DESC, depth ASC) A) " + 
    "WHERE R BETWEEN ? AND ?";
}

Indent as shown below. 

while (rs.next()) {
  int no = rs.getInt("no");
  String title = rs.getString("title");
  Date wdate = rs.getDate("wdate");
  int indent = rs.getInt("indent");
  for (int i = 0; i < indent; i++) {
    out.println("&nbsp;&nbsp;");
  }
  if(indent != 0) {
    out.println("↳");
  }
	
  //.. Omit ..
}

If it is an answer (indent! = 0), add &nbsp;&nbsp; as long as the indent column value and add "↳" before the title.

Modify BoardWriter.java

Open the BoardWriter servlet file and modify the query as shown below. 

String sql = "INSERT INTO board (no,title,content,wdate,family,parent,depth,indent) "
  + "VALUES (board_no_seq.nextval, ?, ?, sysdate, board_no_seq.nextval, 0, 0, 0)";

Modify detailed view page

Open the view.jsp file and insert the following code in the appropriate location: 

<a href="reply_form.jsp?no=<%=no %>&curPage=<%=curPage %>&keyword=<%=keyword %>">Reply</a>

Modify Reply form page

Create reply_form.jsp as shown below.

reply_form.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="net.java_school.util.*" %>
<%@ page import="net.java_school.db.dbpool.*" %>
<jsp:useBean id="dbmgr" scope="application" class="net.java_school.db.dbpool.OracleConnectionManager" />
<!DOCTYPE html>
<%! static final String LINE_SEPARATOR = System.getProperty("line.separator"); %> 
<%
request.setCharacterEncoding("UTF-8");
Log log = new Log();

int family = 0;
int indent = 0;
int depth = 0;

String title = null;
String content = null;
Date wdate = null;

int no = Integer.parseInt(request.getParameter("no"));
String curPage = request.getParameter("curPage");
String keyword = request.getParameter("keyword");

Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String sql = "SELECT no, family, depth, indent, title, content, wdate " +
    "FROM board " +
    "WHERE no = ?";

try {
  con = dbmgr.getConnection();
  stmt = con.prepareStatement(sql);
  stmt.setInt(1, no);
  rs = stmt.executeQuery();

  rs.next();
  no = rs.getInt("no");
  family = rs.getInt("family");
  depth = rs.getInt("depth");
  indent = rs.getInt("indent");
  title = rs.getString("title");
  content = rs.getString("content");

  //Add &gt; for each line of the parent text.
  content = content.replaceAll(LINE_SEPARATOR, LINE_SEPARATOR + ">");
  content = LINE_SEPARATOR + LINE_SEPARATOR +">" + content;
  wdate = rs.getDate("wdate");
} catch (SQLException e) {
  log.debug("Error Source : board/modify_form.jsp : SQLException");
  log.debug("SQLState : " + e.getSQLState());
  log.debug("Message : " + e.getMessage());
  log.debug("Oracle Error Code : " + e.getErrorCode());
  log.debug("sql : " + sql );
} finally {
  if (rs != null) {
    try {
      rs.close();
    } catch (SQLException e) {}
  }
  if (stmt != null) {
    try {
      stmt.close();
    } catch (SQLException e) {}
  }
  if (con != null) {
    try {
      con.close();
    } catch (SQLException e) {}
  }
}
%>
<html>
<head>
<meta charset="UTF-8" />
<title>Reply</title>
</head>
<body>
<h1>Reply</h1>
<form action="../servlet/BoardReplier" method="post">
<input type="hidden" name="no" value="<%=no %>" />
<input type="hidden" name="family" value="<%=family %>" />
<input type="hidden" name="indent" value="<%=indent %>" />
<input type="hidden" name="depth" value="<%=depth %>" />
<input type="hidden" name="curPage" value="<%=curPage %>" />
<input type="hidden" name="keyword" value="<%=keyword %>" />
Title: <input type="text" name="title" size="45" value="<%=title %>" /><br />
<textarea name="content" rows="10" cols="60"><%=content %></textarea><br />
<input type="submit" value="Submit" />
<input type="reset" value="Reset" /><br />
</form>
<a href="view.jsp?no=<%=no %>&curPage=<%=curPage %>&keyword=<%=keyword %>">View</a>
</body>
</html>

Create BoardReplier.java.

BoardReplier.java
package net.java_school.board;

import java.io.*;

import javax.servlet.*;
import javax.servlet.http.*;

import java.sql.*;

import net.java_school.db.dbpool.*;
import net.java_school.util.*;

public class BoardReplier extends HttpServlet {
  
  OracleConnectionManager dbmgr = null;
	
  //Query for depth update
  String sql1 = "UPDATE board SET depth = depth + 1 " + 
      "WHERE family = ? AND depth > ? ";
	
  //Queries for reply
  String sql2 = "INSERT INTO board " + 
      "(no, family, parent, depth, indent, title, content, wdate) " + 
      "VALUES (board_no_seq.nextval, ?, ?, ?, ?, ?, ?, sysdate)";
	
  @Override
  public void init() throws ServletException {
    ServletContext sc = getServletContext();
    dbmgr = (OracleConnectionManager)sc.getAttribute("dbmgr");
  }
	
  @Override
  public void doPost(HttpServletRequest req, HttpServletResponse resp) 
      throws ServletException, IOException {
		
    req.setCharacterEncoding("UTF-8");
    Log log = new Log();
		
    int parent = Integer.parseInt(req.getParameter("no"));
    int family = Integer.parseInt(req.getParameter("family"));
    int depth = Integer.parseInt(req.getParameter("depth"));
    int indent = Integer.parseInt(req.getParameter("indent"));
    String title = req.getParameter("title");
    String content = req.getParameter("content");
		
    String curPage = req.getParameter("curPage");
    String keyword = req.getParameter("keyword");
		
    Connection con = null;
    PreparedStatement stmt1 = null;
    PreparedStatement stmt2 = null;
		
    try {
      con = dbmgr.getConnection();
      con.setAutoCommit(false);
			
      stmt1 = con.prepareStatement(sql1);
      stmt1.setInt(1,family);
      stmt1.setInt(2,depth);
      stmt1.executeUpdate();
			
      stmt2 = con.prepareStatement(sql2);
      stmt2.setInt(1, family);
      stmt2.setInt(2, parent);
      stmt2.setInt(3, depth+1);
      stmt2.setInt(4, indent+1);
      stmt2.setString(5, title);
      stmt2.setString(6, content);
      stmt2.executeUpdate();
      con.commit();
    } catch (SQLException e) {
      try {
        con.rollback();
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
      log.debug("Error Source:BoardReplier.java : SQLException");
      log.debug("SQLState : " + e.getSQLState());
      log.debug("Message : " + e.getMessage());
      log.debug("Oracle Error Code : " + e.getErrorCode());
      log.debug("sql : " + sql2);
    } finally {
      if (stmt1 != null) {
        try {
          stmt1.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (stmt2 != null) {
        try {
          stmt2.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (con != null) {
        dbmgr.freeConnection(con);
      }
      log.close();
      String path = req.getContextPath();
      keyword = java.net.URLEncoder.encode(keyword,"UTF-8");
      resp.sendRedirect(path + "/board/list.jsp?curPage=" + curPage + "&keyword=" + keyword);
    }
  }

}

Add the following servlet definition and mapping to the web.xml. 

<servlet>
  <servlet-name>BoardReplier</servlet-name>
  <servlet-class>net.java_school.board.BoardReplier</servlet-class>
</servlet>
<servlet-mapping>
  <servlet-name>BoardReplier</servlet-name>
  <url-pattern>/servlet/BoardReplier</url-pattern>
</servlet-mapping>

Restart Tomcat and test a reply.

Delete a post

Solution when you want not to delete a post with replies
String sql1 = "SELECT count(*) FROM board WHERE parent = ?";
String sql2 = "DELETE FROM board WHERE no = ?";

boolean check = false;

Connection con = null;
PreparedStatement stmt1 = null;
PreparedStatement stmt2 = null;
ResultSet rs = null;

con = dbmgr.getConnection();
stmt1 = con.prepareStatement(sql1);
stmt1.setInt(1, no);
rs = stmt1.executeQuery();
rs.next();
int num = rs.getInt(1);
if (num == 0) {
  check = true;
}
if (check == true) {
  stmt2 = con.prepareStatement(sql2);
  stmt2.setInt(1, no);
  stmt2.executeUpdate();
}
BoardDeleter.java
package net.java_school.board;

import java.io.*;

import javax.servlet.*;
import javax.servlet.http.*;

import java.sql.*;

import net.java_school.db.dbpool.*;
import net.java_school.util.*;

public class BoardDeleter extends HttpServlet {

  private OracleConnectionManager dbmgr;
	
  private static final String SQL1 = "SELECT count(*) FROM board WHERE parent = ?";
  private static final String SQL2 = "DELETE FROM board WHERE no = ?";
	
  @Override
  public void init() throws ServletException {
    ServletContext sc = getServletContext();
    dbmgr = (OracleConnectionManager) sc.getAttribute("dbmgr");
  }
	
  @Override
  public void doGet(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
    doPost(req, resp);
  }
	
  @Override
  public void doPost(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
		
    req.setCharacterEncoding("UTF-8");
    Log log = new Log();
		
    int no = Integer.parseInt(req.getParameter("no"));
    String curPage = req.getParameter("curPage");
    String keyword = req.getParameter("keyword");
		
    Connection con = dbmgr.getConnection();
    PreparedStatement stmt1 = null;
    PreparedStatement stmt2 = null;
    ResultSet rs = null;
		
    boolean check = false;
		
    try {
      stmt1 = con.prepareStatement(SQL1);
      stmt1.setInt(1, no);
      rs = stmt1.executeQuery();
      rs.next();
      int num = rs.getInt(1);
      if (num == 0) {
        check = true;
      }
      if (check == true) {
        stmt2 = con.prepareStatement(SQL2);
        stmt2.setInt(1, no);
        stmt2.executeUpdate();
      }
    } catch (SQLException e) {
      log.debug("Error Source : BoardDeleter.java : SQLException");
      log.debug("SQLState : " + e.getSQLState());
      log.debug("Message : " + e.getMessage());
      log.debug("Oracle Error Code : " + e.getErrorCode());
      log.debug("sql1 : " + SQL1);
      log.debug("sql2 : " + SQL2);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (stmt1 != null) {
        try {
          stmt1.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (stmt2 != null) {
        try {
          stmt2.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (con != null) {
        dbmgr.freeConnection(con);
      }
      log.close();
      String path = req.getContextPath();
      keyword = java.net.URLEncoder.encode(keyword, "UTF-8");
      resp.sendRedirect(path + "/board/list.jsp?curPage=" + curPage + "&keyword=" + keyword);
    }
  }

}
Solution when you want to delete a post its all descendants
no parent family title
85 0 85 85 posts
86 85 85   ↳ First reply in 85 posts
87 86 85     ↳ First reply in 86 posts
88 87 85       ↳ First reply in 87 posts
  1. Add the no of the posts to delete to the deletion list
  2. Fetch one by one from the deletion list and find posts whose parent equals to the fetched value and add them to the deletion list
  3. Delete all posts stored in the deletion list
RecursiveDeleter.java (perform the delete algorithm)
public class RecursiveDeleter {
  String sql1 = "SELECT no FROM board WHERE parent = ?";
  String sql2 = "DELETE FROM board WHERE no = ?";
	
  Connection con;
	
  Vector<Integer> v = new Vector<Integer>();//delete list
  int idx = 1; //To recursively call the next to the first one added to the list
	
  public RecursiveDeleter(Connection con) {
    this.con = con;
  }
	
  public void addDeleteList(int no) {
    v.addElement(new Integer(no));
  }
	
  public void getDeleteList(int no) {
    PreparedStatement stmt = null;
    ResultSet rs = null;
		
    try {
      stmt = con.prepareStatement(sql1);
      stmt.setInt(1, no);
      rs = stmt.executeQuery();
      while (rs.next()) {
        int target = rs.getInt(1);
        v.addElement(new Integer(target));
      }
      if(idx < v.size()) {
        Integer value = (Integer) v.elementAt(idx++);
        getDeleteList(value.intValue());
      }
    } catch (SQLException e) {
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    }
  }
	 
  public void delete() {
    PreparedStatement stmt = null;
		
    try {
      stmt = con.prepareStatement(sql2);
      for(int i = 0; i < v.size(); i++) {
        Integer value = (Integer) v.elementAt(i);
        stmt.setInt(1, value.intValue());
        stmt.executeUpdate();
      }
    } catch (SQLException e) {
    } finally {
      v.removeAllElements();
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (con != null) {
        dbmgr.freeConnection(con);
      }
    }
  }

}

You must call the method in the following order.

  1. addDeleteList(no);
  2. getDeleteList(no);
  3. delete();

If you want to delete the 86, add the 86 to the deletion list using the addDeleteList() method and then call the getDeleteList() method.

The getDeleteList() method retrieves the 86 from the deletion list and adds the 87 whose parent is the 86 to the deletion list. Next, this method calls itself to fetch the 87 from the deletion list and add the 88 whose parent is the 87 to the deletion list. Again, it calls itself to retrieve the 88 from the deletion list and finds the posts whose parent is the 88. Since no post whose parent is the 88, it completes the deletion list.

Finally, delete all the posts in the deletion list by calling the delete() method.

Methods that call themselves, such as getDeleteList(), are called recursive methods.

When the deletion list is complete, the getDeleteList() method should no longer be called. To do this, it used the idx as an index. Therefore, you need to trace how the idx value changes to understand the getDeleteList() method entirely.

BoardDeleter.java - (Remove all descendant posts)
package net.java_school.board;

import java.io.*;
import java.net.URLEncoder;

import javax.servlet.*;
import javax.servlet.http.*;

import java.sql.*;
import java.util.Vector;

import net.java_school.db.dbpool.*;
import net.java_school.util.*;

public class BoardDeleter extends HttpServlet {
	
  private OracleConnectionManager dbmgr = null;
	
  class RecursiveDeleter {
    String sql1 = "SELECT no FROM board WHERE parent = ?";
    String sql2 = "DELETE FROM board WHERE no = ?";
		
    Connection con;
		
    Vector<Integer> v = new Vector<Integer>();
    int idx = 1; //To recursively call the next to the first one added to the list
		
    public RecursiveDeleter(Connection con) {
      this.con = con;
    }

    public void addDeleteList(int no) {
      v.addElement(new Integer(no));
    }
	
    public void getDeleteList(int no) {
      Log log = new Log();
      PreparedStatement stmt = null;
      ResultSet rs = null;
			
      try {
        stmt = con.prepareStatement(sql1);
        stmt.setInt(1, no);
        rs = stmt.executeQuery();
        while (rs.next()) {
          int target = rs.getInt(1);
          v.addElement(new Integer(target));
        }
        if(idx < v.size()) {
          Integer value = (Integer) v.elementAt(idx++);
          getDeleteList(value.intValue());
        }
      } catch (SQLException e) {
        log.debug("Error Source : BoardDeleter.getDeleteList() : SQLException");
        log.debug("SQLState : " + e.getSQLState());
        log.debug("Message : " + e.getMessage());
        log.debug("Oracle Error Code : " + e.getErrorCode());
        log.debug("sql : " + sql1);
      } finally {
        if (rs != null) {
          try {
            rs.close();
          } catch (SQLException e) {
            e.printStackTrace();
          }
        }
        if (stmt != null) {
          try {
            stmt.close();
          } catch (SQLException e) {
            e.printStackTrace();
          }
        }
        log.close();
      }
    }
		 
    public void delete() {
      Log log = new Log();
			
      PreparedStatement stmt = null;
			
      try {
        stmt = con.prepareStatement(sql2);
        for(int i = 0; i < v.size(); i++) {
          Integer value = (Integer) v.elementAt(i);
          stmt.setInt(1, value.intValue());
          stmt.executeUpdate();
        }
      } catch (SQLException e) {
        log.debug("Error Source : BoardDeleter.delete() : SQLException");
        log.debug("SQLState : " + e.getSQLState());
        log.debug("Message : " + e.getMessage());
        log.debug("Oracle Error Code : " + e.getErrorCode());
        log.debug("sql : " + sql2);
      } finally {
        v.removeAllElements();
        if (stmt != null) {
          try {
            stmt.close();
          } catch (SQLException e) {
            e.printStackTrace();
          }
        }
        if (con != null) {
          dbmgr.freeConnection(con);
        }
        log.close();
      }
    }
  }
	
  @Override
  public void init() throws ServletException {
    ServletContext sc = getServletContext();
    dbmgr = (OracleConnectionManager)sc.getAttribute("dbmgr");
  }
	
  @Override
  public void doGet(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
    doPost(req, resp);
  }
	
  @Override
  public void doPost(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
		
    req.setCharacterEncoding("UTF-8");
		
    int no = Integer.parseInt(req.getParameter("no"));
    String curPage = req.getParameter("curPage");
    String keyword = req.getParameter("keyword");
		
    Connection con = dbmgr.getConnection();
    RecursiveDeleter deleter = new RecursiveDeleter(con);
    deleter.addDeleteList(no);
    deleter.getDeleteList(no);
    deleter.delete();
		
    String path = req.getContextPath();
    keyword = URLEncoder.encode(keyword, "UTF-8");
    resp.sendRedirect(path + "/board/list.jsp?curPage=" + curPage + "&keyword=" + keyword);
  }

}

Oracle Hierarchical Query

Let's practice Oracle Hierarchical queries with the Scott account's emp table. The mgr column of the emp table stores the supervisor's employee number. 

SELECT job, empno, ename, mgr
FROM emp
START WITH mgr is null
CONNECT BY PRIOR empno = mgr;

JOB	       EMPNO ENAME	       MGR
--------- ---------- ---------- ----------
PRESIDENT	7839 KING
MANAGER 	7566 JONES	      7839
ANALYST 	7902 FORD	      7566
CLERK		7369 SMITH	      7902
MANAGER 	7698 BLAKE	      7839
SALESMAN	7499 ALLEN	      7698
SALESMAN	7521 WARD	      7698
SALESMAN	7654 MARTIN	      7698
SALESMAN	7844 TURNER	      7698
CLERK		7900 JAMES	      7698
MANAGER 	7782 CLARK	      7839
CLERK		7934 MILLER	      7782

The start with clause is followed by a condition to find the highest position. The connect by clause connects parent and child nodes.

To easily understand hierarchical relationships, let's use the level pseudo column used in hierarchical queries. 

SELECT level, job, empno, ename, mgr
FROM emp
START WITH mgr is null
CONNECT BY PRIOR  empno = mgr;

     LEVEL JOB		  EMPNO ENAME		  MGR
---------- --------- ---------- ---------- ----------
	 1 PRESIDENT	   7839 KING
	 2 MANAGER	   7566 JONES		 7839
	 3 ANALYST	   7902 FORD		 7566
	 4 CLERK	   7369 SMITH		 7902
	 2 MANAGER	   7698 BLAKE		 7839
	 3 SALESMAN	   7499 ALLEN		 7698
	 3 SALESMAN	   7521 WARD		 7698
	 3 SALESMAN	   7654 MARTIN		 7698
	 3 SALESMAN	   7844 TURNER		 7698
	 3 CLERK	   7900 JAMES		 7698
	 2 MANAGER	   7782 CLARK		 7839
	 3 CLERK	   7934 MILLER		 7782

Let's indent using the level column. 

col job format a20;

SELECT LPAD(' ', 2*(LEVEL-1)) || job job, empno, ename, mgr
FROM emp
START WITH mgr is null
CONNECT BY PRIOR  empno = mgr;

JOB			  EMPNO ENAME		  MGR
-------------------- ---------- ---------- ----------
PRESIDENT		   7839 KING
  MANAGER		   7566 JONES		 7839
    ANALYST		   7902 FORD		 7566
      CLERK		   7369 SMITH		 7902
  MANAGER		   7698 BLAKE		 7839
    SALESMAN		   7499 ALLEN		 7698
    SALESMAN		   7521 WARD		 7698
    SALESMAN		   7654 MARTIN		 7698
    SALESMAN		   7844 TURNER		 7698
    CLERK		   7900 JAMES		 7698
  MANAGER		   7782 CLARK		 7839
    CLERK		   7934 MILLER		 7782

If you add ORDER BY empno DESC to sort the above query, the hierarchical relationship is broken. To keep the hierarchical relationship, use ORDER SIBLINGS BY empno DESC. 

SELECT LPAD(' ', 2*(LEVEL-1)) || job job, empno, ename, mgr
FROM emp
START WITH mgr is null
CONNECT BY PRIOR  empno = mgr 
ORDER SIBLINGS BY empno DESC;

Notice that the highlighted part of the result is sorted in empno descending order. 

JOB			  EMPNO ENAME		  MGR
-------------------- ---------- ---------- ----------
PRESIDENT		   7839 KING
  MANAGER		   7782 CLARK		 7839
    CLERK		   7934 MILLER		 7782
  MANAGER		   7698 BLAKE		 7839
    CLERK		   7900 JAMES		 7698
    SALESMAN		   7844 TURNER		 7698
    SALESMAN		   7654 MARTIN		 7698
    SALESMAN		   7521 WARD		 7698
    SALESMAN		   7499 ALLEN		 7698
  MANAGER		   7566 JONES		 7839
    ANALYST		   7902 FORD		 7566
      CLERK		   7369 SMITH		 7902

The following query extracts only the top 10 records. 

SELECT job, empno, ename,mgr FROM
(SELECT ROWNUM R, A.* 
FROM 
(SELECT LPAD(' ', 2*(LEVEL-1)) || job job, empno, ename, mgr
FROM emp
START WITH mgr is null
CONNECT BY PRIOR  empno = mgr 
ORDER SIBLINGS BY empno DESC) A) 
WHERE R BETWEEN 1 AND 10;

JOB			  EMPNO ENAME		  MGR
-------------------- ---------- ---------- ----------
PRESIDENT		   7839 KING
  MANAGER		   7782 CLARK		 7839
    CLERK		   7934 MILLER		 7782
  MANAGER		   7698 BLAKE		 7839
    CLERK		   7900 JAMES		 7698
    SALESMAN		   7844 TURNER		 7698
    SALESMAN		   7654 MARTIN		 7698
    SALESMAN		   7521 WARD		 7698
    SALESMAN		   7499 ALLEN		 7698
  MANAGER		   7566 JONES		 7839

If you want to create bulletin boards with replies using Oracle hierarchical queries, you only need to add the parent column to store the unique number of the parent post.

Oracle hierarchical Query for bulletin boards with replies
SELECT no, indent, parent, title, wdate FROM 
(SELECT ROWNUM R, A.* 
FROM 
(SELECT no, level as indent, parent, title, wdate FROM board 
START WITH parent = 0 
CONNECT BY PRIOR no = parent 
ORDER SIBLINGS BY no DESC) A) 
WHERE R BETWEEN 1 AND 10

Modify list.jsp

Change the query that retrieves list data from as follows: 

if (keyword.equals("")) {
  sql = "SELECT no, indent, parent, title, wdate FROM " +
  "(SELECT ROWNUM R, A.* " +
  "FROM " +
  "(SELECT no, level as indent, parent, title, wdate FROM board " +
  "START WITH parent = 0 " +
  "CONNECT BY PRIOR no = parent " +
  "ORDER SIBLINGS BY no DESC) A) " +
  "WHERE R BETWEEN ? AND ?";
} else {
  sql = "SELECT no, indent, parent, title, wdate FROM " +
  "(SELECT ROWNUM R, A.* " +
  "FROM " +
  "(SELECT no, level as indent, parent, title, wdate FROM board " +
  "WHERE title LIKE '%" + keyword + "%' OR content LIKE '%" + keyword + "%' " +
  "START WITH parent = 0 " +
  "CONNECT BY PRIOR no = parent " +
  "ORDER SIBLINGS BY no DESC) A) " +
  "WHERE R BETWEEN ? AND ?";
}

Then modify the following in list.jsp: 

if(indent != 1) {
	out.println("↳");
}

The bulletin board usually works just fine.
The following columns are no longer needed, so let's remove them.

ALTER TABLE board

ALTER TABLE board DROP COLUMN family
/
ALTER TABLE board DROP COLUMN depth
/
ALTER TABLE board DROP COLUMN indent
/

New Post

If there is no parent, the parent column value is set to 0. Otherwise, the parent column value is set to the no column value of the parent post.

Edit the BoardWriter's new post-registration query as shown below. 

String sql = "INSERT INTO board (no, title, content, wdate, parent) "
  + "VALUES (board_no_seq.nextval, ?, ?, sysdate, 0)";

Reply

reply_form.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="net.java_school.util.*" %>
<%@ page import="net.java_school.db.dbpool.*" %>
<jsp:useBean id="dbmgr" scope="application" class="net.java_school.db.dbpool.OracleConnectionManager" />
<!DOCTYPE html>
<%! static final String LINE_SEPARATOR = System.getProperty("line.separator"); %> 
<%
request.setCharacterEncoding("UTF-8");
Log log = new Log();

int parent = 0;

String title = null;
String content = null;
Date wdate = null;

int no = Integer.parseInt(request.getParameter("no"));
String curPage = request.getParameter("curPage");
String keyword = request.getParameter("keyword");

Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;

String sql = "SELECT no, title, content, wdate " +
        "FROM board " +
        "WHERE no = ?";

try {
  con = dbmgr.getConnection();
  stmt = con.prepareStatement(sql);
  stmt.setInt(1, no);
  rs = stmt.executeQuery();

  rs.next();
  no = rs.getInt("no");
  title = rs.getString("title");
  content = rs.getString("content");

  //add > for each line of the parent post.
  content = content.replaceAll(LINE_SEPARATOR, LINE_SEPARATOR + ">");
  content = LINE_SEPARATOR + LINE_SEPARATOR +">" + content;
  wdate = rs.getDate("wdate");
} catch (SQLException e) {
  log.debug("Error Source : board/modify_form.jsp : SQLException");
  log.debug("SQLState : " + e.getSQLState());
  log.debug("Message : " + e.getMessage());
  log.debug("Oracle Error Code : " + e.getErrorCode());
  log.debug("sql : " + sql );
} finally {
  if (rs != null) {
    try {
      rs.close();
    } catch (SQLException e) {}
  }
  if (stmt != null) {
    try {
      stmt.close();
    } catch (SQLException e) {}
  }
  if (con != null) {
    try {
      con.close();
    } catch (SQLException e) {}
  }

}
%>
<html>
<head>
<meta charset="UTF-8" />
<title>Reply</title>
</head>
<body>
<h1>Reply</h1>
<form action="../servlet/BoardReplier" method="post">
<input type="hidden" name="no" value="<%=no %>" />
<input type="hidden" name="curPage" value="<%=curPage %>" />
<input type="hidden" name="keyword" value="<%=keyword %>" />
Title: <input type="text" name="title" size="45" value="<%=title %>" /><br />
<textarea name="content" rows="10" cols="60"><%=content %></textarea><br />
<input type="submit" value="Submit" />
<input type="reset" value="Reset" /><br />
</form>
<a href="view.jsp?no=<%=no %>&curPage=<%=curPage %>&keyword=<%=keyword %>">View</a>
</body>
</html>
BoardReplier.java
package net.java_school.board;

import java.io.*;

import javax.servlet.*;
import javax.servlet.http.*;

import java.sql.*;

import net.java_school.db.dbpool.*;
import net.java_school.util.*;

public class BoardReplier extends HttpServlet {
  
  OracleConnectionManager dbmgr = null;
	
  //Queries for reply
  String sql = "INSERT INTO board " + 
      "(no, parent, title, content, wdate) " + 
      "VALUES (board_no_seq.nextval, ?, ?, ?, sysdate)";
	
  @Override
  public void init() throws ServletException {
    ServletContext sc = getServletContext();
    dbmgr = (OracleConnectionManager)sc.getAttribute("dbmgr");
  }
	
  @Override
  public void doPost(HttpServletRequest req, HttpServletResponse resp) 
      throws ServletException, IOException {
		
    req.setCharacterEncoding("UTF-8");
    Log log = new Log();
		
    int parent = Integer.parseInt(req.getParameter("no"));
    String title = req.getParameter("title");
    String content = req.getParameter("content");
		
    String curPage = req.getParameter("curPage");
    String keyword = req.getParameter("keyword");
		
    Connection con = null;
    PreparedStatement stmt = null;
		
    try {
      con = dbmgr.getConnection();
      stmt = con.prepareStatement(sql);
      stmt.setInt(1, parent);
      stmt.setString(2, title);
      stmt.setString(3, content);
      stmt.executeUpdate();
    } catch (SQLException e) {
      log.debug("Error Source:BoardReplier.java : SQLException");
      log.debug("SQLState : " + e.getSQLState());
      log.debug("Message : " + e.getMessage());
      log.debug("Oracle Error Code : " + e.getErrorCode());
      log.debug("sql : " + sql);
    } finally {
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (con != null) {
        dbmgr.freeConnection(con);
      }
      log.close();
      String path = req.getContextPath();
      keyword = java.net.URLEncoder.encode(keyword,"UTF-8");
      resp.sendRedirect(path + "/board/list.jsp?curPage=" + curPage + "&keyword=" + keyword);
    }
  }

}

Delete a post

Solution when you don't want to delete a post with replies.

BoardDeleter.java
package net.java_school.board;

import java.io.*;

import javax.servlet.*;
import javax.servlet.http.*;

import java.sql.*;

import net.java_school.db.dbpool.*;
import net.java_school.util.*;

public class BoardDeleter extends HttpServlet {

  private OracleConnectionManager dbmgr;
	
  private static final String SQL1 = "SELECT count(*) FROM board WHERE parent = ?";
  private static final String SQL2 = "DELETE FROM board WHERE no = ?";
	
  @Override
  public void init() throws ServletException {
    ServletContext sc = getServletContext();
    dbmgr = (OracleConnectionManager) sc.getAttribute("dbmgr");
  }
	
  @Override
  public void doGet(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
    doPost(req, resp);
  }
	
  @Override
  public void doPost(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
		
    req.setCharacterEncoding("UTF-8");
    Log log = new Log();
		
    int no = Integer.parseInt(req.getParameter("no"));
    String curPage = req.getParameter("curPage");
    String keyword = req.getParameter("keyword");
		
    Connection con = dbmgr.getConnection();
    PreparedStatement stmt1 = null;
    PreparedStatement stmt2 = null;
    ResultSet rs = null;
		
    boolean check = false;
		
    try {
      stmt1 = con.prepareStatement(SQL1);
      stmt1.setInt(1, no);
      rs = stmt1.executeQuery();
      rs.next();
      int num = rs.getInt(1);
      if (num == 0) {
        check = true;
      }
      if (check == true) {
        stmt2 = con.prepareStatement(SQL2);
        stmt2.setInt(1, no);
        stmt2.executeUpdate();
      }
    } catch (SQLException e) {
      log.debug("Error Source : BoardDeleter.java : SQLException");
      log.debug("SQLState : " + e.getSQLState());
      log.debug("Message : " + e.getMessage());
      log.debug("Oracle Error Code : " + e.getErrorCode());
      log.debug("sql1 : " + SQL1);
      log.debug("sql2 : " + SQL2);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (stmt1 != null) {
        try {
          stmt1.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (stmt2 != null) {
        try {
          stmt2.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (con != null) {
        dbmgr.freeConnection(con);
      }
      log.close();
      String path = req.getContextPath();
      keyword = java.net.URLEncoder.encode(keyword, "UTF-8");
      resp.sendRedirect(path + "/board/list.jsp?curPage=" + curPage + "&keyword=" + keyword);
    }
  }

}