계층형 게시판 로직

우리가 자주 사용하는 게시판은 두 가지로 나눌 수 있다. 하나는 일반형 게시판으로 글을 삽입한 순서대로 순차적으로 보여주는 형태이다. 이러한 예로는 뉴스, 자료실 등이 있다. 이외에 다른 하나는 계층형 게시판이다. 답변 글을 쓰면 원본 글 아래로 삽입이 되는 형태로 트리 구조를 가지고 있다. 계층형 게시판에 관련된 로직은 실수를 이용한 방법, 문자로 처리하는 방법 등 다양한 방법이 있겠으나 여기서는 까오기 보드에 관련된 로직을 설명하겠다.

계층형 게시판에 사용되는 필드의 구성

까오기 보드에서는 두 개의 필드를 통해 계층형 게시판을 구현하고 있다.


re_level
re_level은 새 글을 등록했을 때 0의 값을 가지며 답변 시 1씩 더해지는 값이다. 이를 통해 목록보기에서 들여 쓰기 등의 방법으로 현재 글의 level를 보여 준다. 이외에 글 삭제 시 이 칼럼을 참고하여 이 글의 답변 글이 있는지를 확인할 수 있다.

re_step
re_step은 글의 순서를 나타내는 칼럼이다.
이 값은 답변이 아닐 때는 100씩 증가하는 값으로 뒤에 2자리를 여유공간으로 가지고 있다. 답변 글을 쓰면 이 곳에 저장이 되며 답변이 2자리 이상일 때는 1000단위로 증가하게끔 처리하면 된다.

100자리	여유공간
3	99
2	99
1	99

DB에 저장하기

1. 새 글일 때

위에서 보면 새 글일 때는 199, 299, 399 이런 식으로 값이 증가를 한다. 목록보기에서 글이 순차적으로 보이기 위해서는 새 글일 때 뒤에 두 자리의 값이 0 ~ 99의 값 중 가장 높은 값인 99를 가지며 이 후 답변 글은 이 값에서 1씩 빼나간다.

새 글일 때 최대 re_step 구하기
a. 해당 테이블의 최대 re_step 값을 구한다.
String query = "select max(re_step) re_step from kkaok";
b. 최대 re_step값에 100을 더 해준다. ⇒ 만약 1499라면 1599가 된다.

아래는 실제 최대 값을 구하는 소스부분이다.

String sql="select re_step from kkaok order by re_step desc limit 1"; int re_step = 199; // DB안에 아무 값도 없을 때는 199이다. ResultSet rs = null; PreparedStatement pstmt = null; Connection conn = null; try{ conn = DBManager.getConnection(); pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); if(rs.next()) re_step=rs.getInt("re_step") + 100; }finally{ DBManager.close(rs,pstmt,conn); }

새 글일 때 re_level은 항상 "0"이다.

2. 답변일 때


글을 저장한 순서

1번 글 ⇒ 2번 글 ⇒ 3번 글 ⇒ 2_1번 글 ⇒ 2_1_1번 글 ⇒ 2_2번 글 ⇒ 2_2_1번 글

위에서 보면 알 수 있듯이 re_step은 목록보기의 순서와 일치가 된다. 답변을 하면 원본 글의 아래에 저장이 되며 같은 레벨에서 답변은 상위에 저장이 된다.
답변 글을 저장할 때는 re_step은 원본 글의 값에서 1을 빼고 re_level은 원본 글의 값에 1을 더하면 된다.

	re_step	re_level
원본글	299	0
답변글	298(-1)	1(+1)

이때 원본 글과 답변 사이에 또 다른 답변이 삽입이 되면 원본 글보다 작고 여유공간 범위 안에 있는 값들을 모두 1씩 빼줘야 한다.
원본 re_step이 297이라고 가정 할 때 297보다 작고 200보다 큰 값은 모두 1씩 빼줘야 한다.
update kkaok set re_step=re_step-1 where re_step > 200 and re_step < 297

	re_step	re_level
원본글	297	2
이 공간에 답변글이 들어간다.
답변글	295(-1)	1
답변글	294(-1)	2

DB에 처리할 때는 최대 re_step의 값은 원본 값으로 하고 최소 값은 원본 re_step의 값을 이용하여 구할 수 있다.
먼저 원본 값을 백으로 나누고 ⇒ 297/100 = 2.97
그 값을 int로 형변환하여 소수점을 버린다. ⇒ (int)(2.97) = 2
결과값에 다시 100을 곱하면 최소값을 구할 수 있다. ⇒ 2*100

String sql="update kkaok set re_step=re_step-1 where re_step > ? and re_step < ? "; PreparedStatement pstmt = null; Connection conn = null; try{ conn = DBManager.getConnection(); pstmt = conn.prepareStatement(sql); pstmt.setInt(1,(int)(re_step/100)*100); pstmt.setInt(2,re_step); pstmt.executeUpdate(); }finally{ DBManager.close(pstmt,conn); }

이와 같은 처리를 하였을 때 좋은점

re_step은 고유한 값을 가지며 list의 순서와 일치가 된다. 따라서 primary key로 사용이 가능하다. primary key로 설정을 하면 unique 속성을 가지며 물리적으로 정렬(clustered index 속성을 가짐)이 되기 때문에 index를 부여하는 것보다 빠른 엑세스가 가능하다. 또한 order by 절에서 re_step만으로 정렬을 시킬 수 있다.

목록보기 쿼문의 작성

이해를 위해 다음과 같이 가정을 하고 설명을 하겠습니다.
int gotoPage : 현재의 페이지 값, 2page로 가정한다.
int pageSize : 보여주려고 하는 게시물의 수, 15로 가정한다.
int recordCount : 전체 레코드 수, 57로 가정한다.

1. mysql

select seq,name,title from kkaok order by re_step desc limit ?,?

첫 번째 바인드변수의 값 : pageSize*(gotoPage-1) ⇒ 15*(2-1) = 15
두 번째 바인드변수의 값 : pageSize ⇒ 15
15번째 게시물부터 15개를 불러온다.

사용되는 소스 예제

public List getListData(String tableName,int gotoPage, int pageSize,int recordCount) throws Exception { int start = pageSize*(gotoPage-1); List listData = new ArrayList(); String query = "select seq,name,title,email,readnum,writeday,re_level, relativeCnt from "+tableName+" order by re_step desc limit ?,?"; BoardTable bTable = null; ResultSet rs = null; PreparedStatement pstmt = null; Connection conn = null; try{ conn = DBManager.getConnection(); pstmt = conn.prepareStatement(query); pstmt.setInt(1,start); pstmt.setInt(2,pageSize); rs = pstmt.executeQuery(); while (rs.next()){ bTable = new BoardTable(); bTable.setSeq(rs.getInt("seq")); bTable.setName(rs.getString("name")); bTable.setTitle(Utility.getTitleLimit( ReplaceUtil.encodeHTMLSpecialChar(rs.getString("title"),14) ,40,rs.getInt("re_level"))); bTable.setEmail(rs.getString("email")); bTable.setReadnum(rs.getInt("readnum")); bTable.setWriteday(rs.getTimestamp("writeday")); bTable.setRe_level(rs.getInt("re_level")); bTable.setRelativeCnt(rs.getInt("relativeCnt")); listData.add(bTable); } } finally { DBManager.close(rs,pstmt,conn); } return listData; }

2. mssql

int start = pageSize*gotoPage;
if (start > recordCount) pageSize -= (start-recordCount);
만약 4페이지라면 start는 60이며 이 값은 전체 레코드 수 보다 크다. 그때는 불러오는 pageSize는 15가 아니고 12가 된다.
pageSize = 15-(60-57)
현재는 2페이지로 가정하였으므로 start는 30이다.

select top "+start+" * from kkaok order by re_step desc

이렇게 하면 re_step을 내림차순으로 정렬하여 30개를 가져온다.

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31, 32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57

가져온 값을 다시 오름차순으로 해서 pageSize 만큼만 뽑아낸다.

select top "+pageSize+" * from (select top "+start+" * from kkaok order by re_step desc) as DERIVEDTBL order by re_step

28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54, 55,56,57

이제 가져온 값을 다시 내림차순으로 정렬한다.

select * from (select top "+pageSize+" * from (select top "+start+" * from kkaok order by re_step desc) as DERIVEDTBL order by re_step) as DERIVEDTBL order by re_step desc

42,41,40,39,38,37,36,35,34,33,32,31,30,29,28


사용되는 소스 예제

public List getListData(String tableName,int gotoPage, int pageSize,int recordCount) throws Exception { int start = pageSize*gotoPage; if (start > recordCount) pageSize -= (start-recordCount); StringBuffer query = new StringBuffer(); query.append("select seq,name,title,email,readnum,writeday,re_level,"); query.append("relativeCnt from (select top "+pageSize+" seq,name,title,"); query.append("email,readnum,writeday,re_level,relativeCnt,re_step from "); query.append("(select top "+start+" seq,name,title,email,readnum,writeday"); query.append(",re_level,relativeCnt,re_step from "+tableName+" order by "); query.append("re_step desc) as DERIVEDTBL order by re_step)as DERIVEDTBL "); query.append("order by re_step desc"); List listData = new ArrayList(); BoardTable bTable = null; ResultSet rs = null; PreparedStatement pstmt = null; Connection conn = null; try{ conn = DBManager.getConnection(); pstmt = conn.prepareStatement(query.toString()); rs = pstmt.executeQuery(); while(rs.next()){ bTable = new BoardTable(); bTable.setSeq(rs.getInt("seq")); bTable.setName(rs.getString("name")); bTable.setTitle(Utility.getTitleLimit( ReplaceUtil.encodeHTMLSpecialChar(rs.getString("title"),14), 40,rs.getInt("re_level"))); bTable.setEmail(rs.getString("email")); bTable.setReadnum(rs.getInt("readnum")); bTable.setWriteday(rs.getTimestamp("writeday")); bTable.setRe_level(rs.getInt("re_level")); bTable.setRelativeCnt(rs.getInt("relativeCnt")); listData.add(bTable); } } finally{ DBManager.close(pstmt,conn); } return listData; }

3. oracle

int start = pageSize*gotoPage;
if (start > recordCount) pageSize -= (start-recordCount);

select /*+ index_desc(kkaok kkaok_pk) */ *,rownum as rnum from "+tableName+" where rownum <= ? order by rnum desc

여기서는 hint로 order by를 대신하여 정렬을 하고 rownum을 이용하여 start 만큼 가져온 후 다시 결과물을 내림차순으로 정렬을 한다.

57,56,55,54,53,52,51,50,49,48,47,46,45,44,43,42,41,40,39,38,37,36,35,34,33,32, 31,30,29,28

가져온 값을 pageSize 만큼만 뽑아낸다.

select * from (select /*+ index_desc(kkaok kkaok_pk) */ *,rownum as rnum from "+tableName+" where rownum <= ? order by rnum desc) where rownum <= ? order by rnum

42,41,40,39,38,37,36,35,34,33,32,31,30,29,28

사용되는 소스 예제

public List getListData(String tableName,int gotoPage, int pageSize,int recordCount) throws Exception { int start = pageSize*gotoPage; if (start > recordCount) pageSize -= (start-recordCount); StringBuffer query = new StringBuffer(); query.append("select seq,name,title,email,readnum,writeday,re_level,"); query.append("relativeCnt from (select /*+ index_desc("+tableName+" "); query.append("tableName+"_pk_re_step) */ seq,name,title,email,"); query.append("readnum,writeday,re_level,relativeCnt,rownum as rnum from "); query.append("tableName+" where rownum <= ? order by rnum desc) where "); query.append("rownum <= ? order by rnum"); List listData = new ArrayList(); BoardTable bTable = null; ResultSet rs = null; PreparedStatement pstmt = null; Connection conn = null; try{ conn = DBManager.getConnection(); pstmt = conn.prepareStatement(query.toString()); pstmt.setInt(1,start); pstmt.setInt(2,pageSize); rs = pstmt.executeQuery(); while (rs.next()){ bTable = new BoardTable(); bTable.setSeq(rs.getInt("seq")); bTable.setName(rs.getString("name")); bTable.setTitle(Utility.getTitleLimit( ReplaceUtil.encodeHTMLSpecialChar(rs.getString("title"),14), 40,rs.getInt("re_level"))); bTable.setEmail(rs.getString("email")); bTable.setReadnum(rs.getInt("readnum")); bTable.setWriteday(rs.getTimestamp("writeday")); bTable.setRe_level(rs.getInt("re_level")); bTable.setRelativeCnt(rs.getInt("relativeCnt")); listData.add(bTable); } } finally { DBManager.close(rs,pstmt,conn); } return listData; }

위에서 보여지는 쿼리문은 만 건 미만일 때 효과적이다. 하지만 만 건이 넘어서면 서서히 신음소리를 내기 시작한다. 페이지가 점점 뒤로 갈수록 느려지는 것이 느껴지기 시작한다. 이때 쿼리문만으로 50만 건까지 극복할 수 있다. 물론 이런 방식이 검색을 할 때도 적용되는 건 아니다. 검색은 조금 다른 처리가 필요하다. 예를 들어 날짜로 제한을 주거나 검색 게시물의 수를 제한을 두는 방식을 사용할 수 있다.

방법은 간단하다. 자신이 보여줄 페이지의 첫 번째 값을 구한 다음, 페이지 사이즈만큼 불러오기를 하면 되는 것이다. 이렇게 하는 것이 빠른 이유는 위에 방법은 re_step 뿐 아니라 목록보기에 필요한 다른 데이터도 가져와서 필요한 것을 뽑지만 아래 방법은 re_step 하나의 필드에서 필요한 위치를 찾아서 불러오기 때문이다.

1. mysql

int start = pageSize*(gotoPage-1);
start는 15*(2-1), 15가 된다.

select re_step from kkaok order by re_step desc limit ?,1

바인드 변수에는 start 값이 들어간다. 즉 15번째부터 하나만 가져와라~~
이렇게 하면 2페이지의 첫 번째 re_step의 값을 알 수 있다.

select * from kkaok where re_step<= ? order by re_step desc limit ?

위에서 구한 값을 첫 번째 바인드변수에 넣고 뒤에는 pageSize를 넣어주면 된다. 그렇게 되면 첫 번째 값보다 작은 15개를 불러올 것이다.
사용되는 소스 예제

public List getListData(String tableName,int gotoPage, int pageSize) throws Exception { ResultSet rs = null; PreparedStatement pstmt = null; Connection conn = null; int start = pageSize*(gotoPage-1); int pageTopNum = 0; String query = "select re_step from "+tableName+" order by re_step desc limit ?,1"; try { conn = DBManager.getConnection(); pstmt = conn.prepareStatement(query); pstmt.setInt(1,start); rs = pstmt.executeQuery(); if(rs.next()) pageTopNum = rs.getInt("re_step"); else pageTopNum = 0; } finally { DBManager.close(rs,pstmt,conn); } List listData = new ArrayList(); query = "select seq,name,title,email,readnum,writeday,re_level, relativeCnt from "+tableName+" where re_step<= ? order by re_step desc limit ?"; BoardTable bTable = null; try{ conn = DBManager.getConnection(); pstmt = conn.prepareStatement(query); pstmt.setInt(1,pageTopNum); pstmt.setInt(2,pageSize); rs = pstmt.executeQuery(); while (rs.next()){ bTable = new BoardTable(); bTable.setSeq(rs.getInt("seq")); bTable.setName(rs.getString("name")); bTable.setTitle(Utility.getTitleLimit( ReplaceUtil.encodeHTMLSpecialChar(rs.getString("title"),14), 40,rs.getInt("re_level"))); bTable.setEmail(rs.getString("email")); bTable.setReadnum(rs.getInt("readnum")); bTable.setWriteday(rs.getTimestamp("writeday")); bTable.setRe_level(rs.getInt("re_level")); bTable.setRelativeCnt(rs.getInt("relativeCnt")); listData.add(bTable); } } finally { DBManager.close(rs,pstmt,conn); } return listData; }

2. mssql

int start = pageSize*(gotoPage-1)+1;
start는 16이 된다.(15*(2-1)+1=16)

첫 번째 값 구하기

select min(re_step) AS Expr1 from (select top "+start+" re_step from kkaok order by re_step desc) as DERIVEDTBL

게시물 목록 구하기

select top "+pageSize+" * from kkaok where re_step <= ? order by re_step desc

바인드 변수에는 위에서 구한 첫 번째 re_step 값을 넣는다.


사용되는 소스 예제

public List getListData(String tableName,int gotoPage, int pageSize) throws Exception { int start = pageSize*(gotoPage-1)+1; ResultSet rs = null; PreparedStatement pstmt = null; Connection conn = null; String query = "select min(re_step) AS Expr1 from (select top "+start+" re_step from "+tableName+" order by re_step desc) as DERIVEDTBL"; int pageTopNum = 0; try{ conn = DBManager.getConnection(); pstmt = conn.prepareStatement(query); rs = pstmt.executeQuery(); if(rs.next()) pageTopNum = rs.getInt("Expr1"); } finally { DBManager.close(rs,pstmt,conn); } List listData = new ArrayList(); BoardTable bTable = null; query = "select top "+pageSize+" seq,name,title,email,readnum,writeday, re_level,relativeCnt from "+tableName+" where re_step <= ? order by re_step desc"; try{ conn = DBManager.getConnection(); pstmt = conn.prepareStatement(query); pstmt.setInt(1,pageTopNum); rs = pstmt.executeQuery(); while (rs.next()){ bTable = new BoardTable(); bTable.setSeq(rs.getInt("seq")); bTable.setName(rs.getString("name")); bTable.setTitle(Utility.getTitleLimit( ReplaceUtil.encodeHTMLSpecialChar(rs.getString("title"),14),40, rs.getInt("re_level"))); bTable.setEmail(rs.getString("email")); bTable.setReadnum(rs.getInt("readnum")); bTable.setWriteday(rs.getTimestamp("writeday")); bTable.setRe_level(rs.getInt("re_level")); bTable.setRelativeCnt(rs.getInt("relativeCnt")); listData.add(bTable); } } finally { DBManager.close(rs,pstmt,conn); } return listData; }

3. oracle

int start = pageSize*(gotoPage-1)+1;
start는 16이 된다.(15*(2-1)+1=16)

첫 번째 값 구하기

select min(re_step) re_step from (select /*+ index_desc(kkaok kkaok_pk) */ re_step from kkaok where rownum <=?)

바인드변수에는 start가 들어간다.
게시물 목록 구하기

select /*+ index_desc(kkaok kkaok_pk) */ * from kkaok where re_step <= ? and rownum <= ?

첫 번째 바인드 변수에는 위에서 구한 첫 번째 re_step 값을 넣고 두 번째는 pageSize를 넣어서 구한다.


사용되는 소스 예제

public List getListData(String tableName,int gotoPage, int pageSize) throws Exception { ResultSet rs = null; PreparedStatement pstmt = null; Connection conn = null; int start = pageSize*(gotoPage-1)+1; int pageTopNum = 0; String query = "select min(re_step) re_step from (select /*+ index_desc("+tableName+" "+tableName+"_pk_re_step) */ re_step from "+tableName+" where rownum <=?) DERIVEDTBL"; try { conn = DBManager.getConnection(); pstmt = conn.prepareStatement(query); pstmt.setInt(1,start); rs = pstmt.executeQuery(); if(rs.next()) pageTopNum = rs.getInt("re_step"); else pageTopNum = 0; } finally { DBManager.close(rs,pstmt,conn); } List listData = new ArrayList(); query = "select /*+ index_desc("+tableName+" "+tableName+"_pk_re_step) */ seq,name,title,email,readnum,writeday,re_level,relativeCnt from "+ tableName+" where re_step <= ? and rownum <= ? "; BoardTable bTable = null; try{ conn = DBManager.getConnection(); pstmt = conn.prepareStatement(query); pstmt.setInt(1,pageTopNum); pstmt.setInt(2,pageSize); rs = pstmt.executeQuery(); while (rs.next()){ bTable = new BoardTable(); bTable.setSeq(rs.getInt("seq")); bTable.setName(rs.getString("name")); bTable.setTitle(Utility.getTitleLimit( ReplaceUtil.encodeHTMLSpecialChar(rs.getString("title"),14),40, rs.getInt("re_level"))); bTable.setEmail(rs.getString("email")); bTable.setReadnum(rs.getInt("readnum")); bTable.setWriteday(rs.getTimestamp("writeday")); bTable.setRe_level(rs.getInt("re_level")); bTable.setRelativeCnt(rs.getInt("relativeCnt")); listData.add(bTable); } } finally { DBManager.close(rs,pstmt,conn); } return listData; }