모델 프로그래밍

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

답변형 게시판 만들기

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;

package net.jeongsam.prjpkg;
      import java.sql.Connection;
      import java.sql.PreparedStatement;
      import java.sql.ResultSet;
      import java.sql.SQLException;
      import java.util.ArrayList;
      import java.util.Iterator;
      import java.util.List;
      public class ContentDAO {
       private Connection conn = null;
       private PreparedStatement pstmt = null;
       private ResultSet rs = null;
       private String sql = null;
       
       private ContentDAO() {
        conn = ConnectionManager.getConnection();
       }
       
       public static ContentDAO getInstance() {
        return new ContentDAO();
       }
       
       /**
        * 글을 데이터베이스에 삽입한다. 이때 태그는 별도의 테이블에 목록을 만들어 저장한다.
        * 태그의 경우 리스트를 만들어 리스트의 목록을 차례대로 태그가 저장된 테이블의 데이터와 비교하여
        * 기존 태그의 카운트를 증가하거나 새로운 태그를 저장한다.
        * ※ 현재 구현만 해두었고 코드를 다듬어서 최적화시킬 필요가 있다.
        * @param content
        */
       public void insertArticle(ContentDomain content) {
        sql = "INSERT INTO cont (num, title, content, tag_list) ";
        sql += "VALUES (seq_cont.NEXTVAL, ?, ?, ?)";
        
        try {
         pstmt = conn.prepareStatement(sql);
         pstmt.setString(1, content.getTitle());
         pstmt.setString(2, content.getContent());
         pstmt.setString(3, content.getTags());
         pstmt.executeUpdate();
         
         Iterator tagList = content.toTagList().iterator();
         while (tagList.hasNext()) {
          String tag = tagList.next();
          sql = "SELECT tag_name FROM tag_list ";
          sql += "WHERE tag_name LIKE ?";
          pstmt = conn.prepareStatement(sql);
          pstmt.setString(1, "%" + tag + "%");
          if (pstmt.executeUpdate() > 0) {
           sql = "UPDATE tag_list SET tag_count = tag_count + 1 ";
           sql += "WHERE tag_name = ?";
           pstmt = conn.prepareStatement(sql);
           pstmt.setString(1, tag);
           pstmt.executeUpdate();
          } else {
           sql = "INSERT INTO tag_list (num, tag_name, tag_count) ";
           sql += "VALUES (seq_tag_list.NEXTVAL, ?, ?)";
           pstmt = conn.prepareStatement(sql);
           pstmt.setString(1, tag);
           pstmt.setString(2, "1");
           pstmt.executeUpdate();
          }
         }
        } catch (SQLException e) {
         e.printStackTrace();
        } finally {
         try {
          if (pstmt != null) pstmt.close();
          if (conn != null) conn.close();
         } catch (SQLException e) {
          e.printStackTrace();
         }
        }
       }
       
       public ContentDomain getArticle(int num) {
        // 데이터베이스에서 글 읽어오기
        return new ContentDomain();
       }
       
       public List getTagList() {
        // 데이터베이스에서 태그 목록 리턴
        return new ArrayList();
       }
       
       public static void main(String[] args) {
        ContentDomain article = new ContentDomain();
        ContentDAO cdao = getInstance();
        Iterator itrArticle = null;
        
        article.setTitle("두번째 글");
        article.setContent("테스트 글입니다.\n테스트 글입니다.");
        article.setTags("태그, test, 두번째글, second");
        
        itrArticle = article.toTagList().iterator();
        while (itrArticle.hasNext())
         System.out.println(itrArticle.next());
        cdao.insertArticle(article);
       }
      }

 /**
       * 문자열 컬렉션 타입 지정
       * 태그 배열을 파라메터로 받기 위해 선언
       */
      CREATE OR REPLACE TYPE type_tag_list IS TABLE OF VARCHAR2(100) NOT NULL;
      /**
       * 태그 배열을 파라메터로 받아서 태그 카운트를 갱신하거나
       * 신규 태그를 등록한다.
       */
      CREATE OR REPLACE PROCEDURE insert_tag (tags IN type_tag_list)
      IS
       idx INTEGER;
      BEGIN
       idx := tags.FIRST;     -- 컬렉션 인텍스 시작값으로 초기화
       LOOP
           EXIT WHEN idx IS NULL;   -- 컬렉션 데이터가 없으면 LOOP 빠져나감
             
              UPDATE tag_list     -- 태그가 존재하면 태그 카운트 증가
              SET tag_count = tag_count + 1
              WHERE tag_name = tags(idx);
             
              IF SQL%NOTFOUND THEN   -- UPDATE의 WHERE 조건 만족하는 row가 없을 때
               INSERT INTO tag_list  -- 태그 추가
         (num, tag_name, tag_count)
         VALUES (seq_tag_list.NEXTVAL, tags(idx), '1');
              END IF;
                                 
              idx := tags.NEXT(idx);
          END LOOP;
      END;

package net.jeongsam.prjpkg;
   import java.util.ArrayList;
      import java.util.Iterator;
      import java.util.List;
    import java.sql.CallableStatement;
      import java.sql.Connection;
      import java.sql.PreparedStatement;
      import java.sql.SQLException;
   import oracle.sql.ARRAY;
      import oracle.sql.ArrayDescriptor;
   public class ContentDAO {
       private ContentDAO() { }
       
       public static ContentDAO getInstance() {
        return new ContentDAO();
       }
       
       /**
        * 글을 데이터베이스에 삽입한다. 이때 태그는 별도의 테이블에 목록을 만들어 저장한다.
        * 태그의 경우 리스트를 만들어 리스트의 목록을 차례대로 태그가 저장된 테이블의 데이터와
        * 비교하여 기존 태그의 카운트를 증가하거나 새로운 태그를 저장한다.
        * @param content 글정보를 담은 JavaBean
        * @throws SQLException SQL Exception 처리
        */
       public void insertArticle(ContentDomain content) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        CallableStatement cstmt = null;
        String query = null;
        String qproc = null;
        
        query = "INSERT INTO cont (num, title, content, tag_list) ";
        query += "VALUES (seq_cont.NEXTVAL, ?, ?, ?)";
        
        qproc = "{CALL INSERT_TAG(:TAGS)}";
        
        try {
         conn = ConnectionManager.getConnection();
         // 글을 추가한다.
         pstmt = conn.prepareStatement(query);
         pstmt.setString(1, content.getTitle());
         pstmt.setString(2, content.getContent());
         pstmt.setString(3, content.getTags());
         pstmt.executeUpdate();
         
         // 태그 목록을 갱신, 추가하기 위해 Oracle Procedure 호출
         cstmt = conn.prepareCall(qproc);
         ArrayDescriptor desc1 =
          ArrayDescriptor.createDescriptor("TYPE_TAG_LIST",conn);
         ARRAY input =
          new ARRAY(desc1, conn, content.toTagList().toArray());
         cstmt.setArray(1, input);
         cstmt.executeUpdate();
        } finally {
         if (pstmt != null) pstmt.close();
         if (cstmt != null) cstmt.close();
         if (conn != null) conn.close();
        }
       }
       
       public ContentDomain getArticle(int num) {
        // 데이터베이스에서 글 읽어오기
        return new ContentDomain();
       }
       
       public List<;string> getTagList() {
        // 데이터베이스에서 태그 목록 리턴
        return new ArrayList <string>();
       }
       /**
        * 클래스 테스트 코드
        * insertArticel() 메서드 테스트
        * @param args 미사용 파라메터
        */
       public static void main(String[] args) {
        ContentDomain article = new ContentDomain();
        ContentDAO cdao = getInstance();
        Iterator <string> itrArticle = null;
        
        article.setTitle("일곱번째 글");
        article.setContent("테스트 글입니다.\n테스트 글입니다.");
        article.setTags("일곱번째 글, 태그, 7th, example");
        
        itrArticle = article.toTagList().iterator();
        while (itrArticle.hasNext())
         System.out.println(itrArticle.next());
        try {
         cdao.insertArticle(article);
        } catch (SQLException e) {
         e.printStackTrace();
        }
       }
      }