게시판
-
(2) 오라클 데이터베이스를 이용한 답변형 게시판 작성하기2009.11.25
(2) 오라클 데이터베이스를 이용한 답변형 게시판 작성하기
2009. 11. 25. 00:53
모델 프로그래밍
package net.jeongsam.board;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class BoardDAO {
public static enum CLASS { basic, hire }
// private static String DATASOURCE_DB_NAME = "java:/comp/env/jdbc/testboards";
private static String _SQLBASIC = "SELECT id, p_id, subject, create_date "
+ "FROM (SELECT a.id, a.p_id, a.subject, a.create_date, ROWNUM AS rnum "
+ "FROM test_boards a WHERE ROWNUM <= :MAX_ROWS) "
+ "WHERE rnum >= :MIN_ROWS "
+ "ORDER BY id DESC";
private static String _SQLHIRE = "SELECT id, p_id, title, create_date "
+ "FROM (SELECT a.id, a.p_id, LPAD(' ', 2 * (LEVEL - 1)) || a.subject title, "
+ "a.create_date, ROWNUM AS rnum "
+ "FROM test_boards a "
+ "WHERE ROWNUM <= :MAX_ROWS "
+ "START WITH a.p_id IS NULL "
+ "CONNECT BY PRIOR a.id = a.p_id "
+ "ORDER SIBLINGS BY a.id DESC) "
+ "WHERE rnum >= :MIN_ROWS";
private static String _SQLREAD = "SELECT id, p_id, subject, content, create_date "
+ "FROM test_boards WHERE id = :ID";
private static String _SQLINSERT = "INSERT INTO test_boards VALUES ("
+ "(SELECT MAX(id) + 1 FROM test_boards), NULL, :SUBJECT, :CONTENT, SYSDATE)";
private static String _SQLREPLY = "INSERT INTO test_boards VALUES ("
+ "(SELECT MAX(id) + 1 FROM test_boards), :PNUM, "
+ "'[엮인글] ' || :SUBJECT, :CONTENT, SYSDATE)";
public BoardDAO() {
}
private Connection _getConnection() throws BoardDAOExcept {
Connection conn = null;
String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
String username = "test";
String password = "1234";
try {
Class.forName("oracle.jdbc.OracleDriver");
conn = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
throw new BoardDAOExcept(e);
} catch (SQLException e) {
throw new BoardDAOExcept(e);
}
return conn;
}
/**
* 글 목록 표시하기
* @param c 기본형과 답변형 선택
* @param startRow 표시할 시작 행 값
* @param endRow 표시할 마지막 행 값
* @return List<BoardTO> 글 목록
* @throws BoardDAOExcept
*/
private List<BoardTO> _getList(BoardDAO.CLASS c, int startRow, int endRow) throws BoardDAOExcept {
String sql = null;
ArrayList<BoardTO> articles = new ArrayList<BoardTO>();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
if (c == BoardDAO.CLASS.basic) {
sql = _SQLBASIC;
} else {
sql = _SQLHIRE;
}
try {
conn = _getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setInt(2, startRow);
pstmt.setInt(1, endRow);
rs = pstmt.executeQuery();
while (rs.next()) {
BoardTO article = new BoardTO();
article.setId(rs.getInt(1));
article.setPid(rs.getInt(2));
article.setSubject(rs.getString(3));
article.setCtime(rs.getTimestamp(4));
articles.add(article);
}
} catch (SQLException e) {
throw new BoardDAOExcept(e);
} finally {
if (rs != null)
try {
rs.close();
rs = null;
} catch(Exception e) {
throw new BoardDAOExcept(e);
}
if (pstmt != null) {
try {
pstmt.close();
pstmt = null;
} catch(Exception e) {
throw new BoardDAOExcept(e);
}
}
if (conn != null) {
try {
conn.close();
conn = null;
} catch(Exception e) {
throw new BoardDAOExcept(e);
}
}
}
return articles;
}
/**
* 지정된 페이지의 로우 출력
* @param c 게시판 종류 선택
* @param pageNum 페이지 번호
* @return List<BoardTO> 게시판 데이터
* @throws BoardDAOExcept
*/
public List<BoardTO> getList(BoardDAO.CLASS c, int pageNum) throws BoardDAOExcept {
int pagePerRows = 10;
int startRow = pagePerRows * (pageNum - 1) + 1;
int endRow = pagePerRows * pageNum;
return _getList(c, startRow, endRow);
}
/**
* 글 내용 읽기
* @param id 글 번호
* @return BoardTO 글 내용을 저장할 빈
* @throws BoardDAOExcept
*/
public BoardTO readArticle(int id) throws BoardDAOExcept {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
BoardTO article = null;
try {
conn = _getConnection();
pstmt = conn.prepareStatement(_SQLREAD);
pstmt.setInt(1, id);
rs = pstmt.executeQuery();
if (rs.next()) {
article = new BoardTO();
article.setId(rs.getInt(1));
article.setPid(rs.getInt(2));
article.setSubject(rs.getString(3));
article.setCtime(rs.getTimestamp(4));
}
} catch (BoardDAOExcept e) {
throw new BoardDAOExcept(e);
} catch (SQLException e) {
throw new BoardDAOExcept(e);
} finally {
if (rs != null)
try {
rs.close();
rs = null;
} catch(Exception e) {
throw new BoardDAOExcept(e);
}
if (pstmt != null) {
try {
pstmt.close();
pstmt = null;
} catch(Exception e) {
throw new BoardDAOExcept(e);
}
}
if (conn != null) {
try {
conn.close();
conn = null;
} catch(Exception e) {
throw new BoardDAOExcept(e);
}
}
}
return article;
}
/**
* 글 쓰기
* @param article 글 내용 저장 (BoardTO)
* @throws BoardDAOExcept
*/
public void insertArticle(BoardTO article) throws BoardDAOExcept {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = _getConnection();
pstmt = conn.prepareStatement(_SQLINSERT);
pstmt.setString(1, article.getSubject());
pstmt.setString(2, article.getContent());
pstmt.executeUpdate();
} catch (SQLException e) {
throw new BoardDAOExcept(e);
} finally {
if (pstmt != null) {
try {
pstmt.close();
pstmt = null;
} catch(Exception e) {
throw new BoardDAOExcept(e);
}
}
if (conn != null) {
try {
conn.close();
conn = null;
} catch(Exception e) {
throw new BoardDAOExcept(e);
}
}
}
}
/**
* 답변 쓰기
* @param article 글 내용 저장 (BoardTO)
* @throws BoardDAOExcept
*/
public void replyArticle(BoardTO article) throws BoardDAOExcept {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = _getConnection();
pstmt = conn.prepareStatement(_SQLREPLY);
pstmt.setInt(1, article.getPid());
pstmt.setString(2, article.getSubject());
pstmt.setString(3, article.getContent());
pstmt.executeUpdate();
} catch (SQLException e) {
throw new BoardDAOExcept(e);
} finally {
if (pstmt != null) {
try {
pstmt.close();
pstmt = null;
} catch(Exception e) {
throw new BoardDAOExcept(e);
}
}
if (conn != null) {
try {
conn.close();
conn = null;
} catch(Exception e) {
throw new BoardDAOExcept(e);
}
}
}
}
/**
* 전체 로우 수를 리턴
* @return 전체 로우 수
*/
public int getTotalRows() {
return 0;
}
}
(1) 오라클 데이터베이스를 이용한 답변형 게시판 작성하기
2009. 11. 24. 12:31
답변형 게시판 만들기
SQL (for oracle)
-- 글목록 출력 (등록순)
-- 게시판 페이지 알고리즘 Top-N Query 사용
SELECT id, p_id, subject, create_date
FROM (
SELECT a.id, a.p_id, a.subject, a.create_date,
ROWNUM AS rnum
FROM test_boards a
WHERE ROWNUM <= :MAX_ROWS)
WHERE rnum >= :MIN_ROWS
ORDER BY id DESC;
-- 글목록 출력 (답변형)
SELECT id, p_id, title, create_date
FROM (
SELECT a.id, a.p_id, LPAD(' ', 2 * (LEVEL - 1)) || a.subject title,
a.create_date, ROWNUM AS rnum
FROM test_boards a
WHERE ROWNUM <= :MAX_ROWS
START WITH a.p_id IS NULL
CONNECT BY PRIOR a.id = a.p_id
ORDER SIBLINGS BY a.id DESC)
WHERE rnum >= :MIN_ROWS;
-- 글올리기
INSERT INTO test_boards VALUES (
(SELECT MAX(id) + 1 FROM test_boards), NULL, :SUBJECT, :CONTENT, SYSDATE);
-- 글내용 보기
SELECT id, p_id, subject, content, create_date
FROM test_boards
WHERE id = :ID;
-- 답글쓰기
INSERT INTO test_boards VALUES (
(SELECT MAX(id) + 1 FROM test_boards), :PNUM,
'[엮인글] ' || :SUBJECT, :CONTENT, SYSDATE);
-- 글 삭제 (자식 글이 존재하면 삭제 불가)
SELECT CONNECT_BY_ISLEAF FROM test_boards
WHERE id = :ID
START WITH p_id IS NULL
CONNECT BY PRIOR id = p_id;
DELETE FROM test_boards WHERE id = :ID
-- 글 수정
UPDATE test_boards
SET subject = :SUBJECT, content = :CONTENT, create_date = SYSDATE
WHERE id = :ID;