계층형 게시판

계층형 게시판이란 어떤 게시글과 그에 대한 답변 글이 계층관계를 가지는 게시판이다.

이 절에서는 데이터베이스가 계층형 쿼리를 지원하지 않을 때 계층형 게시판을 구현하는 실습을 한 후, 오라클 계층형 쿼리를 사용해서 계층형 게시판을 구현하는 실습을 한다.

계층형 쿼리를 사용하지 않고 계층형 게시판 구현하기

아래 표에서 '25 게시글' , '26 게시글' , '27 게시글', '29 게시글'은 글쓰기(write_form.jsp, BoardWriter.java)를 통해 등록된 글이다. 이들은 그룹을 생성하고 자신이 그룹에서 최상위 게시글이 된다. 답변 글은 답변쓰기(아직 구현하지 않음)를 통해 작성된 글이다. 표 헤더에 나오는 no, title, wdate는 board 테이블의 컬럼이다.

no	title	wdate
29	29 게시글	2007/12/09
27	27 게시글	2007/11/27
32	⌙ 27 게시글의 첫 번째 답변 글	2007/12/22
26	26 게시글	2007/11/25
30	⌙ 26 게시글의 두 번째 답변 글	2007/12/20
31	⌙ 30 게시글의 첫 번째 답변 글	2007/12/21
33	⌙ 31 게시글의 첫 번째 답변 글	2007/12/23
28	⌙ 26 게시글의 첫 번째 답변 글	2007/12/01
25	25 게시글	2007/11/20

계층형 게시판을 위해 추가할 필드

계층형 게시판을 구현하기 위해서 각 게시글은 아래와 같은 정보를 가지고 있어야 한다.

게시글은 자신이 어떤 그룹에 속해있는지에 대한 정보를 가지고 있어야 한다.
게시글은 그룹 최상위 게시글에서 자신이 몇 번째인지 대한 정보를 가지고 있어야 한다.
게시글은 목록 페이지에서 자신의 제목이 얼마나 들여써야 하는지에 대한 정보를 가지고 있어야 한다.

1을 위해 family 컬럼을 추가한다. 2를 위해 depth 컬럼을 추가한다. 3을 위해 indent 컬럼을 추가한다.

컬럼명	데이터 타입	설명
family	number	그룹번호
depth	number	그룹내 최상위 글로부터 매겨지는 순서
indent	number	들여쓰기 수준

충분해 보인다. 하지만, 글을 삭제할 경우를 생각해 보자. 게시판에서 30번 글을 삭제할 때 31번과 33번 글을 그대로 두면, 사용자는 버그라고 생각할 수 있다. 30번 글을 삭제할 때 31번과 33번 글도 같이 삭제하려면, family, depth, indent만 가지고 해결할 수 없다. 따라서, 답변 글을 자식 글이라고 하면 답변 게시글은 자신의 부모 글에 대한 대한 정보를 가지고 있어야 한다. 이 정보를 저장할 컬럼으로 parent를 추가한다.

no	parent	title	wdate
29	29	29 게시글	2007/12/09
27	27	27 게시글	2007/11/27
32	27	⌙ 27 게시글의 첫 번째 답변 글	2007/12/22
26	26	26 게시글	2007/11/25
30	26	⌙ 26 게시글의 두 번째 답변 글	2007/12/20
31	30	⌙ 30 게시글의 첫 번째 답변 글	2007/12/21
33	31	⌙ 31 게시글의 첫 번째 답변 글	2007/12/23
28	26	⌙ 26 게시글의 첫 번째 답변 글	2007/12/01
25	25	25 게시글	2007/11/20

위와 같이 부모글에 대한 정보를 parent 컬럼이 가지고 있다면 30을 삭제할 때 30이 부모인 31를 삭제하고, 이어서 31이 부모인 33을 삭제하는 식으로 삭제를 하면 답변 글이 있는 글에 대한 삭제가 가능하다. 게시판 정책이 답변 글이 있는 게시글은 삭제하지 못한다 하더라도 마찬가지이다. 삭제하려는 게시글을 부모로 가진 글을 parent 컬럼에서 확인할 수 있다. 결론적으로, 계층형 게시판을 위해 필요한 컬럼은 아래와 같다.

컬럼명	데이터 타입	설명
family	number	그룹번호
parent	number	부모 글
depth	number	그룹내 최상위 글로부터 매겨지는 순서
indent	number	들여쓰기 수준

계층형 게시판 알고리즘

글쓰기를 통해 등록되는 글의 컬럼값은 아래와 같이 삽입되도록 한다.

Field	Value
family	no 컬럼값과 같다.
parent	0
depth	0
indent	0

답변쓰기를 통해 등록되는 글의 컬럼값은 아래와 같이 삽입되도록 한다.

Field	Value
family	부모글의 family
parent	부모글의 no
depth	(부모글의 depth) + 1
indent	(부모글의 indent) + 1

계층형 게시판을 위한 board 테이블 변경

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

기존 게시판을 계층형 게시판으로 수정: 목록

list.jsp를 열고 레코드를 가져오는 쿼리문을 아래와 같이 수정한다.

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 컬럼을 이용해서 아래와 같이 들여쓰기하도록 list.jsp를 수정한다.

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("⌙");
	}
	
	//.. 이하 생략 ..

위 코드를 보면 indent 만큼   를 추가하여 들여쓰기를 한 다음 답변 글이면(즉, indent != 0 이면) "⌙" 를 제목 앞에 추가한다. 여기까지 수정하고 테스트해도 게시판은 정상적으로 동작한다.

기존 게시판을 계층형 게시판으로 수정: 글쓰기

BoardWriter.java에서 게시글을 등록하는 쿼리를 다음과 같이 수정한다.

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)";

기존 게시판을 계층형 게시판으로 수정: 상세보기

view.jsp 파일을 열고 답변쓰기 기능을 추가하기 위해 적절한 위치에 아래 코드를 삽입한다.

<a href="reply_form.jsp?no=<%=no %>&curPage=<%=curPage %>&keyword=<%=keyword %>">답변쓰기</a>

기존 게시판을 계층형 게시판으로 수정: 답변쓰기 기능 추가

답변쓰기 양식을 제공하는 JSP 페이지인 reply_form.jsp를 아래와 같이 만든다.

/board/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");

	//부모 게시글의 각 행마다 >를 추가한다.
	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>답변쓰기</title>
</head>
<body>
<h1>답변쓰기</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 %>" />
제목: <input type="text" name="title" size="45" value="<%=title %>" /><br />
<textarea name="content" rows="10" cols="60"><%=content %></textarea><br />
<input type="submit" value="전송" />
<input type="reset" value="취소" /><br />
</form>
<a href="view.jsp?no=<%=no %>&curPage=<%=curPage %>&keyword=<%=keyword %>">상세보기</a>
</body>
</html>

답변쓰기 페이지의 textarea에 부모 글의 내용이 디폴트가 되도록 한다. 이때 부모 글과 답변 글이 구별되도록 부모 글의 각 행마다 >를 추가한다. 전송 버튼을 클릭하면 no, family, depth, indent, title, content 파라미터가 BoardReplier 서블릿에 전달된다.

아래와 같이 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;
	
	//depth 갱신용 쿼리
	String sql1 = "UPDATE board SET depth = depth + 1 " + 
			"WHERE family = ? AND depth > ? ";
	
	//답변쓰기용 쿼리
	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);
		}

	}
}

새로운 서블릿을 만들었으므로 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>

web.xml이 변경했으니 톰캣을 재실행한다. 상세보기를 방문하고 답변쓰기를 클릭하여 답변을 작성해 본다.

기존 게시판을 계층형 게시판으로 수정: 삭제

먼저 답변이 있는 글은 삭제되지 않도록 구현한다. 삭제 대상 게시글의 no 값을 parent로 가지는 게시글이 있는지 검사한 후, 만약 그러한 게시글이 없다면 삭제한다.

삭제 코드 힌트 (답변이 있는 글은 삭제할 수 없도록)

String sql1 = "SELECT count(*) FROM board WHERE parent = ?";
String sql2 = "DELETE FROM board WHERE no = ?";

boolean check = false;//true면 삭제

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;//true면 삭제
		
		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);
		}
	}
}

자손 글 모두를 삭제

아래와 같이 게시글이 있다고 가정하자.

no	title
85	85 게시글
86	⌙ 85 게시글의 첫 번째 답변 글
87	⌙ 86 게시글의 첫 번째 답변 글
88	⌙ 87 게시글의 첫 번재 답변 글

85 글은 글쓰기를 사용해서 등록한 글이다. 글쓰기를 사용해서 등록한 글은 parent는 0이고 family는 no와 같다. 즉, family는 85라는 값을 가진다. 86 글은 85 글에 대한 답변 글이다. 따라서 86 글은 parent는 85이고 family는 85이다. 87 글은 86 글에 대한 답변 글로 parent는 86이고 family는 85이다. 88 글은 87 글에 대한 답변 글로 parent는 87이고 family는 85이다. 이를 표로 정리하면 다음과 같다.

no	parent	family	title
85	0	85	85 게시글
86	85	85	⌙ 85 게시글의 첫 번째 답변 글
87	86	85	⌙ 86 게시글의 첫 번재 답변 글
88	87	85	⌙ 87 게시글의 첫 번째 답변 글

자손 글까지 같이 삭제하는 삭제 알고리즘

삭제할 글의 고유번호를 삭제리스트에 추가한다
삭제 리스트에서 차례로 값을 하나씩 꺼내어 그 값을 parent로 가지고 있는 글을 찾아서 삭제 리스트에 추가한다
삭제 리스트에 저장된 글을 삭제한다

답변 글 삭제 알고리즘을 수행할 RecursiveDeleter.java

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>();//삭제 리스트
	int idx = 1; //삭제리스트에 맨 처음 추가된 번호 바로 다음을 재귀호출하도록
	
	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);
			}
		}
	}
}

자손 글까지 모두 삭제하기 위해서는 아래 순서로 메소드를 호출해야 한다.

addDeleteList(삭제할 게시글의 고유번호);
getDeleteList(삭제할 게시글의 고유번호);
delete();

86 글을 삭제하려 한다면 addDeleteList() 메소드를 이용해서 우선 86을 삭제리스트에 추가한다. 삭제리스트에 86을 저장한 후 getDeleteList() 메소드를 호출한다. getDeleteList() 메소드는 삭제리스트에서 86을 꺼내어 86을 parent로 가지고 있는 87을 삭제리스트에 추가한다. 다음에 다시 자기 자신을 호출하면서 87을 삭제리스트에서 꺼내어 그 값을 parent로 가지고 있는 88을 삭제리스트에 추가한다. 다시 자기 자신을 호출하면서 삭제리스트에서 88을 꺼내어 그 값을 parent로 가지고 있는 글을 찾는다. 88을 parent로 가지고 있는 글이 없으므로 삭제리스트를 작성을 끝낸다. 다음 delete() 메소드를 호출해서 삭제리스트에 있는 글을 모두 삭제한다.

위에서 메소드 내에서 자기 자신을 부르는 재귀 메소드를 사용했다. 재귀 메소드를 호출할 때 삭제리스트에서 순서대로 차례로 하나씩 값을 꺼내야 한다. 또한 삭제리스트가 완성되면 재귀 메소드가 더 이상 호출되지 말아야 한다. 이를 위해서 idx라는 인덱스를 사용했다. getDeleteList() 재귀 메소드를 따라가면서 idx가 어떻게 변경되는지 확인해야 getDeleteList() 메소드를 완벽히 이해할 수 있다.

BoardDeleter.java (자손 글 모두 삭제)

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; //삭제리스트에 맨 처음 추가된 번호 바로 다음을 재귀호출하도록
		
		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)

emp 테이블은 사원 테이블이다. mgr 컬럼은 상사의 사번을 저장한다.

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

start with 절 다음에는 최상위 계층을 찾는 조건이 나온다. connect by 절은 부모와 자식노드들을 연결한다.

계층형 관계를 쉽게 파악하게 위해 계층형 쿼리에서 사용할 수 있는 level 가상 컬럼을 사용해 보자.

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

level을 이용하여 들여쓰기를 해 보자.

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

위 쿼리를 정렬하기 위해 ORDER BY empno DESC를 추가한다면 계층 관계가 깨진다. 계층 관계가 깨지지 않고 정렬을 하려면 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;

결과에서 강조된 부분으로 empno 내림차순으로 정렬되었음을 알 수 있다.

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

다음 쿼리는 상위 10개의 레코드만 추출한다.

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

오라클 계층형 쿼리를 이용하여 계층형 게시판을 구현한다면, 부모 글의 고유번호를 저장할 parent 컬럼만 추가하면 된다.

오라클 계층형 게시판을 위한 쿼리 힌트

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

계층형 쿼리를 사용하는 계층형 게시판으로 수정: list.jsp

계층형 쿼리를 사용하는 계층형 게시판으로 수정하려면 list.jsp에서 아래와 같이 목록 데이터를 구하는 쿼리를 바꾼다.

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 ?";
}

list.jsp에서 다음 부분을 수정한다.

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

여기까지 작업한 후 테스트해도 게시판은 정상적으로 동작한다. 하지만 성능을 좋게 하기 위해 해야 할 일이 남아있다.

계층형 쿼리를 사용하는 계층형 게시판으로 수정: board 테이블 수정

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

계층형 쿼리를 사용하는 계층형 게시판으로 수정: 글쓰기

부모가 없는 글은 parent 컬럼 값을 0으로, 부모가 있는 글은 parent 컬럼에 부모 글의 no 컬럼의 값이 입력되도록 한다.

BoardWriter.java에서 새 게시글을 등록하는 쿼리를 아래와 같이 수정한다.

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

계층형 쿼리를 사용하는 계층형 게시판으로 수정: 답변쓰기

/board/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");

	//부모글의 각 행마다 >를 추가한다.
	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>답변쓰기</title>
</head>
<body>
<h1>답변쓰기</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 %>" />
제목: <input type="text" name="title" size="45" value="<%=title %>" /><br />
<textarea name="content" rows="10" cols="60"><%=content %></textarea><br />
<input type="submit" value="전송" />
<input type="reset" value="취소" /><br />
</form>
<a href="view.jsp?no=<%=no %>&curPage=<%=curPage %>&keyword=<%=keyword %>">상세보기</a>
</body>
</html>

답변쓰기 페이지의 textarea는 부모 글의 내용이 디폴트가 되도록 한다. 이때 부모 글과 답변 글이 구별되도록 부모 글의 각 행마다 >를 추가한다. 전송 버튼을 클릭하면 no, title, content 파라미터가 BoardReplier 서블릿에 전달된다.

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;
	
	//답변쓰기용 쿼리
	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);
		}
	}
}

계층형 쿼리를 사용하는 계층형 게시판으로 수정: 삭제

대부분의 게시판이 답변 글이 있으면 삭제하지 못하도록 하고 있으니 그렇게 구현한다.

BoardDeleter 서블릿 - 답변 글이 있으면 삭제할 수 없도록

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;//true면 삭제
		
		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);
		}
	}
}