나도 답변형 게시판을 만들어보자 ! (2)

DB 테이블을 생성해 보자.

각 처리 분야별 역할은

서비스 : 게시판 관련 로직을 수행한다. 

ListArticleService - 게시글 목록 제공

ReadArticleService - 게시글 읽기 기능

WriteArticleService - 게시글 쓰기 기능

ReplyArticleService - 답변 쓰기 기능

UpdateArticleService - 게시글 수정 기능

DeleteArticleService - 게시글 삭제 기능

DAO : DB 테이블에 대한 CRUD작업을 수행.

모델 : Article(게시글 데이터), ArticleListModel (게시글 목록 화면을 생성할 때 필요한 데이터 저장)

Article 모델

import java.util.Date;
 
public class Article {
 
    private int id;
    private int groupId;
    private String sequenceNumber;
    private Date postingDate;
    private int readCount;
    private String writerName;
    private String password;
    private String title;
    private String content;
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getGroupId() {
        return groupId;
    }
    public void setGroupId(int groupId) {
        this.groupId = groupId;
    }
    public String getSequenceNumber() {
        return sequenceNumber;
    }
    public void setSequenceNumber(String sequenceNumber) {
        this.sequenceNumber = sequenceNumber;
    }
    public Date getPostingDate() {
        return postingDate;
    }
    public void setPostingDate(Date postingDate) {
        this.postingDate = postingDate;
    }
    public int getReadCount() {
        return readCount;
    }
    public void setReadCount(int readCount) {
        this.readCount = readCount;
    }
    public String getWriterName() {
        return writerName;
    }
    public void setWriterName(String writerName) {
        this.writerName = writerName;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public String getContent() {
        return content;
    }
    public void setContent(String content) {
        this.content = content;
    }
 
    public int getLevel() {
        if (sequenceNumber == null) {
            return -1;
        }
        if (sequenceNumber.length() != 16) {
            return -1;
        }
        if (sequenceNumber.endsWith("999999")) {
            return 0;
        }
        if (sequenceNumber.endsWith("9999")) {
            return 1;
        }
        if (sequenceNumber.endsWith("99")) {
            return 2;
        }
        return 3;
    }
    
}
 

ArticleDao

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
 
import mvjsp.chap17.board.model.Article;
import mvjsp.jdbc.JdbcUtil;
 
public class ArticleDao {
 
    private static ArticleDao instance = new ArticleDao();
    public static ArticleDao getInstance() {
        return instance;
    }
 
    private ArticleDao() {
    }
 
    public int selectCount(Connection conn) throws SQLException {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select count(*) from article");
            rs.next();
            return rs.getInt(1);
        } finally {
            JdbcUtil.close(rs);
            JdbcUtil.close(stmt);
        }
    }
 
    public List<Article> select(Connection conn, int firstRow, int endRow)
            throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement("select article_id, "
                    + "group_id, sequence_no, posting_date, "
                    + "read_count, writer_name, password, title "
                    + "from article order by sequence_no desc limit ?, ?");
            pstmt.setInt(1, firstRow - 1);
            pstmt.setInt(2, endRow - firstRow + 1);
            rs = pstmt.executeQuery();
            if (!rs.next()) {
                return Collections.emptyList();
            }
            List<Article> articleList = new ArrayList<Article>();
            do {
                Article article = makeArticleFromResultSet(rs, false);
                articleList.add(article);
            } while (rs.next());
            return articleList;
        } finally {
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
        }
    }
 
    private Article makeArticleFromResultSet(ResultSet rs, boolean readContent)
            throws SQLException {
        Article article = new Article();
        article.setId(rs.getInt("article_id"));
        article.setGroupId(rs.getInt("group_id"));
        article.setSequenceNumber(rs.getString("sequence_no"));
        article.setPostingDate(rs.getTimestamp("posting_date"));
        article.setReadCount(rs.getInt("read_count"));
        article.setWriterName(rs.getString("writer_name"));
        article.setPassword(rs.getString("password"));
        article.setTitle(rs.getString("title"));
        if (readContent) {
            article.setContent(rs.getString("content"));
        }
        return article;
    }
 
    public int insert(Connection conn, Article article) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement("insert into article "
                    + "(group_id, sequence_no, posting_date, read_count, "
                    + "writer_name, password, title, content) "
                    + "values (?, ?, ?, 0, ?, ?, ?, ?)");
            pstmt.setInt(1, article.getGroupId());
            pstmt.setString(2, article.getSequenceNumber());
            pstmt.setTimestamp(3, new Timestamp(article.getPostingDate()
                    .getTime()));
            pstmt.setString(4, article.getWriterName());
            pstmt.setString(5, article.getPassword());
            pstmt.setString(6, article.getTitle());
            pstmt.setString(7, article.getContent());
            int insertedCount = pstmt.executeUpdate();
 
            if (insertedCount > 0) {
                stmt = conn.createStatement();
                rs = stmt.executeQuery("select last_insert_id() from article");
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
            return -1;
        } finally {
            JdbcUtil.close(rs);
            JdbcUtil.close(stmt);
            JdbcUtil.close(pstmt);
        }
    }
 
    public Article selectById(Connection conn, int articleId)
            throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement("select * from article "
                    + "where article_id = ?");
            pstmt.setInt(1, articleId);
            rs = pstmt.executeQuery();
            if (!rs.next()) {
                return null;
            }
            Article article = makeArticleFromResultSet(rs, true);
            return article;
        } finally {
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
        }
    }
 
    public void increaseReadCount(Connection conn, int articleId)
            throws SQLException {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("update article "
                    + "set read_count = read_count + 1 "
                    + "where article_id = ?");
            pstmt.setInt(1, articleId);
            pstmt.executeUpdate();
        } finally {
            JdbcUtil.close(pstmt);
        }
    }
 
    public String selectLastSequenceNumber(Connection conn,
            String searchMaxSeqNum, String searchMinSeqNum) 
    throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn
                    .prepareStatement("select min(sequence_no) from article "
                            + "where sequence_no < ? and sequence_no >= ?");
            pstmt.setString(1, searchMaxSeqNum);
            pstmt.setString(2, searchMinSeqNum);
            rs = pstmt.executeQuery();
            if (!rs.next()) {
                return null;
            }
            return rs.getString(1);
        } finally {
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
        }
    }
 
    public int update(Connection conn, Article article) throws SQLException {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("update article "
                    + "set title = ?, content = ? where article_id = ?");
            pstmt.setString(1, article.getTitle());
            pstmt.setString(2, article.getContent());
            pstmt.setInt(3, article.getId());
            return pstmt.executeUpdate();
        } finally {
            JdbcUtil.close(pstmt);
        }
    }
 
    public void delete(Connection conn, int articleId) throws SQLException {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("delete from article "
                    + "where article_id = ?");
            pstmt.setInt(1, articleId);
            pstmt.executeUpdate();
        } finally {
            JdbcUtil.close(pstmt);
        }
    }
 
}