[게시판 만들기] 11. 댓글달기

이번 포스팅에서는 게시글에 댓글을 달 수 있게 만들것이다. 

이렇게!!!!!! 

<이렇게 만들으려면 필요한 것>

1. 댓글 테이블 

2. 댓글에 대댓글이 달릴 때를 대비한 쿼리문!!!

쿼리문을 작성하기 위해서는 아래 내용을 이해해야한다.

사실이건너무어려웠당.........

1) ARTICLE_ID이 ? 인 것에 대한 댓글 리스트를 모두 가져오기 위한 쿼리문

2) PARENT_REPLY_ID 가 ? 인 것에 해당하는 댓글 갯수

3) REPLY_ID 가 ? 인 것부터 시작해서 PARENT_REPLY_ID와 REPLY_ID가 같은 것들을 순서대로 가져오기 (ORDER_NO로 내림차순 정렬하면은 가장 큰 ORDER_NO를 얻어올 수 있다)

4) REPLY_ID 가 ? 인 ORDER_NO 가져오기

---

이제 얘내들을 코드로 보자.

ReplyVO.java

 
package com.ktds.smahn.reply.vo;
 
import com.ktds.smahn.article.vo.ArticleVO;
 
public class ReplyVO extends ArticleVO {
 
    private int replyId;
    private String description;
    private int groupId;
    private int parentReplyId;
    private int depth;
    private int orderNo;
 
    public int getDepth() {
        return depth;
    }
 
    public void setDepth(int depth) {
        this.depth = depth;
    }
 
    public int getReplyId() {
        return replyId;
    }
 
    public void setReplyId(int replyId) {
        this.replyId = replyId;
    }
 
    public String getDescription() {
        return description;
    }
 
    public void setDescription(String description) {
        this.description = description;
    }
 
    public int getGroupId() {
        return groupId;
    }
 
    public void setGroupId(int groupId) {
        this.groupId = groupId;
    }
 
    public int getParentReplyId() {
        return parentReplyId;
    }
 
    public void setParentReplyId(int parentReplyId) {
        this.parentReplyId = parentReplyId;
    }
 
    public int getOrderNo() {
        return orderNo;
    }
 
    public void setOrderNo(int orderNo) {
        this.orderNo = orderNo;
    }
 
 
}
 

ReplyDAO.java

package com.ktds.smahn.reply.dao;
 
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;
 
import com.ktds.smahn.member.dao.Const;
import com.ktds.smahn.reply.vo.ReplyVO;
import com.ktds.smahn.util.xml.XML;
 
public class ReplyDAO {
    
    public List<ReplyVO> getReplyListByArticleId(int articleId){
        
        loadOracleDriver();
 
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
 
        try {
 
            conn = DriverManager.getConnection(Const.DB_URL, Const.DB_ID, Const.DB_PASSWORD);
 
            String query = XML.getNodeString("//query/reply/getReplyListByArticleId/text()");
            stmt = conn.prepareStatement(query);
 
            stmt.setInt(1, articleId);
            rs = stmt.executeQuery();
            
            List<ReplyVO> replyList = new ArrayList<ReplyVO>();
            
            ReplyVO reply = null;
            
            while ( rs.next() ) {
                reply = new ReplyVO();
                reply.setReplyId(rs.getInt("REPLY_ID"));
                reply.setArticleId(rs.getInt("ARTICLE_ID"));
                reply.setMemberId(rs.getString("MEMBER_ID"));
                reply.setNickName(rs.getString("NICK_NAME"));
                reply.setDescription(rs.getString("DESCRIPTION"));
                reply.setGroupId(rs.getInt("GROUP_ID"));
                reply.setParentReplyId(rs.getInt("PARENT_REPLY_ID"));
                reply.setDepth(rs.getInt("DEPTH"));
                reply.setOrderNo(rs.getInt("ORDER_NO"));
                
                replyList.add(reply);
            }
            
            if( reply == null ){
                
                return null;
            }
            
            return replyList;
 
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(), e);
        } finally {
            closeDB(conn, stmt, rs);
        }            
        
        
    }
    
    public int insertReply(ReplyVO reply){
        
        loadOracleDriver();
 
        Connection conn = null;
        PreparedStatement stmt = null;    
    
        
        try {
            conn = DriverManager.getConnection(Const.DB_URL, Const.DB_ID, Const.DB_PASSWORD);
            
            String query = XML.getNodeString("//query/reply/insertReply/text()");
            
            if( reply.getParentReplyId() > 0 ){
                query = XML.getNodeString("//query/reply/insertReReply/text()");
            }
            
            stmt = conn.prepareStatement(query);
            stmt.setInt(1, reply.getArticleId());
            stmt.setString(2, reply.getMemberId());
            stmt.setString(3, reply.getDescription());
 
            
            if( reply.getParentReplyId() > 0 ){
                stmt.setInt(4, reply.getGroupId());
                stmt.setInt(5, reply.getParentReplyId());
                stmt.setInt(6, reply.getDepth());
                stmt.setInt(7, reply.getOrderNo());
            }
            
            
            return stmt.executeUpdate();
            
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(), e);
        } finally {
            closeDB(conn, stmt, null);
        }    
    }
    
    public int selectCountParentReplyId(ReplyVO reply) {
        
        loadOracleDriver();
 
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
 
        try {
 
            conn = DriverManager.getConnection(Const.DB_URL, Const.DB_ID, Const.DB_PASSWORD);
 
            String query = XML.getNodeString("//query/reply/selectCountParentReplyId/text()");
            stmt = conn.prepareStatement(query);
 
            stmt.setInt(1, reply.getParentReplyId());
            
            
            rs = stmt.executeQuery();
            
            rs.next();
            return rs.getInt(1);
 
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(), e);
        } finally {
            closeDB(conn, stmt, rs);
        }            
        
    }
 
    public int selectMaxOrderNoByParentReplyId(ReplyVO reply) {
        loadOracleDriver();
 
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
 
        try {
 
            conn = DriverManager.getConnection(Const.DB_URL, Const.DB_ID, Const.DB_PASSWORD);
 
            String query = XML.getNodeString("//query/reply/selectMaxOrderNoByParentReplyId/text()");
            stmt = conn.prepareStatement(query);
 
            // 대댓글까지 가능한 쿼리
            //stmt.setInt(1, reply.getParentReplyId());
            //stmt.setInt(2, reply.getDepth() - 1);
            
            /*
             SELECT    ORDER_NO
            FROM    (
                        SELECT    *
                        FROM    (
                                        SELECT  *
                                        FROM    REPLIES
                                        START   WITH GROUP_ID = ?
                                        CONNECT BY PARENT_REPLY_ID = REPLY_ID
                                        ORDER   BY ORDER_NO DESC
                        )
                    START    WITH DEPTH > ?
                    CONNECT    BY PARENT_REPLY_ID = REPLY_ID
                    ORDER    BY ORDER_NO DESC
                    )
            WHERE    ROWNUM = 1
             */
        
        
            
            // 대대댓글 가능한 쿼리
            stmt.setInt(1, reply.getReplyId());
            
            rs = stmt.executeQuery();
            
            if(rs.next()){
                return rs.getInt(1);
            }
            else{
                return 0;
            }
            
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(), e);
        } finally {
            closeDB(conn, stmt, rs);
        }            
    }
 
    public int selectLatestOrderNoByParentReplyId(ReplyVO reply) {
        loadOracleDriver();
 
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
 
        try {
 
            conn = DriverManager.getConnection(Const.DB_URL, Const.DB_ID, Const.DB_PASSWORD);
 
            String query = XML.getNodeString("//query/reply/selectLatestOrderNoByParentReplyId/text()");
            stmt = conn.prepareStatement(query);
 
            stmt.setInt(1, reply.getParentReplyId());
            rs = stmt.executeQuery();
            
            rs.next();
            return rs.getInt(1);
 
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(), e);
        } finally {
            closeDB(conn, stmt, rs);
        }
    }
 
    public void updateOrderNoByGroupId(ReplyVO reply) {
        loadOracleDriver();
 
        Connection conn = null;
        PreparedStatement stmt = null;
 
        try {
 
            conn = DriverManager.getConnection(Const.DB_URL, Const.DB_ID, Const.DB_PASSWORD);
 
            String query = XML.getNodeString("//query/reply/updateOrderNoByGroupId/text()");
            stmt = conn.prepareStatement(query);
 
            stmt.setInt(1, reply.getOrderNo());
            stmt.setInt(2, reply.getGroupId());
            
            stmt.executeUpdate();
            
 
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(), e);
        } finally {
            closeDB(conn, stmt, null);
        }
    }
    
    private void loadOracleDriver() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }
 
    private void closeDB(Connection conn, PreparedStatement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
            }
        }
 
    }
 
 
 
}
 

ReplyBiz.java

 
package com.ktds.smahn.reply.biz;
 
import com.ktds.smahn.reply.dao.ReplyDAO;
import com.ktds.smahn.reply.vo.ReplyVO;
 
public class ReplyBiz {
 
    private ReplyDAO replyDAO;
 
    public ReplyBiz() {
 
        replyDAO = new ReplyDAO();
 
    }
 
    public boolean addNewReplyDepthOne(ReplyVO reply) {
        
        
        int orderNo = this.getNewOrderNo(reply);
        reply.setOrderNo(orderNo);
        
        if( orderNo > 0 ){
            replyDAO.updateOrderNoByGroupId(reply);
        }
        
        return replyDAO.insertReply(reply) > 0;
    }
 
    private int getNewOrderNo(ReplyVO reply) {
 
        if( replyDAO.selectCountParentReplyId(reply) > 0){
            int newOrderNo = replyDAO.selectMaxOrderNoByParentReplyId(reply);
            return newOrderNo + 1;
        }
        else {
            if (reply.getParentReplyId() != 0 ){
                int newOrderNo = replyDAO.selectLatestOrderNoByParentReplyId(reply);
                return newOrderNo + 1 ;
            }
        }
        return 0;
    }
 
 
}
 

DoWriteReplyServlet.java

 
package com.ktds.smahn.reply.web;
 
import java.io.IOException;
 
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
 
import com.ktds.smahn.member.vo.MemberVO;
import com.ktds.smahn.reply.biz.ReplyBiz;
import com.ktds.smahn.reply.vo.ReplyVO;
import com.ktds.smahn.util.Root;
 
/**
 * Servlet implementation class DoWriteReplyServlet
 */
public class DoWriteReplyServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    private ReplyBiz replyBiz;
    
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public DoWriteReplyServlet() {
        super();
        replyBiz = new ReplyBiz();
    }
 
    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.sendError(HttpServletResponse.SC_FORBIDDEN, "잘못된 요청입니다.");
    }
 
    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
        int articleId = Integer.parseInt(request.getParameter("articleId"));
        int depth = Integer.parseInt(request.getParameter("depth"));
        int parentReplyId = Integer.parseInt(request.getParameter("parentReplyId"));
        int groupId = Integer.parseInt(request.getParameter("groupId"));
        int orderNo = Integer.parseInt(request.getParameter("orderNo"));
        int replyId = Integer.parseInt(request.getParameter("replyId"));
        
        String reply = request.getParameter("description");
        reply = reply.replaceAll("\n", "<br/>");
        
        System.out.println("depth : " + depth);
        System.out.println("GROUP_ID : "+groupId + " / PARENT_REPLY_ID : "+parentReplyId);
                
        ReplyVO replyInfo = new ReplyVO();
        replyInfo.setArticleId(articleId);
        replyInfo.setDepth(depth);
        replyInfo.setParentReplyId(parentReplyId);
        replyInfo.setGroupId(groupId);
        replyInfo.setOrderNo(orderNo);
        replyInfo.setDescription(reply);
        replyInfo.setReplyId(replyId);
        
        HttpSession session = request.getSession();
        MemberVO member = (MemberVO) session.getAttribute("_MEMBER_");
        
        if( member != null ){
            replyInfo.setMemberId(member.getMemberId());
            
        }
        
        replyBiz.addNewReplyDepthOne(replyInfo);
        
        response.sendRedirect(Root.get(this) + "/detail?articleId=" + articleId);
        
    }
 
}
 

detail.jsp

 
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri = "http://java.sun.com/jsp/jstl/core" %>    
<jsp:include page="/WEB-INF/view/common/header.jsp"></jsp:include>
    
<script type="text/javascript">
    
    $(document).ready( function(){
        
        $(".hide").hide();
        
        $("#increaseRecommends").click( function(){
            
            var form = $("#increaseRecommendsForm");
            
            form.attr("method", "POST");
            form.attr("action", "<c:url value="/increaseRecommends" />");
 
            form.submit(); 
        });
        
        
        $("#writeReplyBtn").click( function(){
            var form = $("#writeReplyForm");
            
            form.attr("method", "POST");
            form.attr("action", "<c:url value="/doWriteReply" />");
 
            form.submit(); 
            
        });
        
        /*새로 생긴 엘리먼트에 접근하는 방법*/
        $("body").on("click", "#writeReplyBtn", function(){
            var form = $("#writeReplyForm");
            
            form.attr("method", "POST");
            form.attr("action", "<c:url value="/doWriteReply" />");
 
            form.submit(); 
 
        });
        
        $(".writeReReply").click(function() {
            // 현재 클릭한 것(this)의 부모, 부모, 부모 하면은 table이 나온다.
            var table = $(this).parent().parent().parent();
            
            var groupId= table.children(":eq(1)").children(":eq(0)").html();
            var parentReplyId = table.children(":eq(1)").children(":eq(1)").html();
            var depth = table.children(":eq(2)").children(":eq(0)").html();
            var orderNo = table.children(":eq(2)").children(":eq(1)").html();
            var replyId = table.children(":eq(3)").children(":eq(0)").html();
            
            $("#depth").val(parseInt(depth) + 1);
            $("#parentReplyId").val(replyId);
            $("#groupId").val(groupId);
            $("#orderNo").val(orderNo);
            $("#replyId").val(replyId);
            
            var form = $("#formWrapper").html();
            $("#formWrapper").detach();
            
            /*댓글달기 눌렀다가 다른 댓글달기 눌렀을 때 활성화*/
            if( form == undefined ) {
                $(".formAppender").each(function(index, data) {
                    if( data.innerHTML != "" ) {
                        form = data.innerHTML;
                    }
                });
                $(".formAppender").html("");
            }
            
            var formAppender = table.parent().parent().children(":eq(1)");
            formAppender.html(form);
            formAppender.show();
        });
        
    });
 
 
</script>
</head>
<body>
 
<div align="center">
    <table style="width:60%;" >
    <tr>
        <th>글 번호</th>
        <th>글 제목</th>
        <th>글쓴이</th>
        <th>글쓴이의 ID</th>
        <th>글 내용</th>
    </tr>
    
    <tr>
        <td>${article.articleId}</td>
        <td>${article.title}</td>
        <td>${article.nickName}</td>
        <td>${article.memberId}</td>
        <td>${article.description}</td>    
    </tr>
    
    <tr>
        <c:forEach items="${article.fileList}" var="file">
            <td colspan="6">
                <a href="<c:url value="/download?fileId=${file.fileId}&articleId=${article.articleId}" />">
                ${file.fileName}
                </a>
            </td>
        </c:forEach>
    </tr>
 
    </table>
    
    
    <form id="increaseRecommendsForm">    
    <br/>
    조회수 ${article.hits} |
    <img src="<c:url value="/resource/img/like.png" />" style="width: 20px;">  ${article.recommends}
    <input type="hidden" name="articleId" value="${article.articleId}" />
    <input type="button" id="increaseRecommends" value="추천하기" />
    <a href="<c:url value="/list" />">  |  목록으로 가기</a>
    </form>
    
    
    <br/>
    
    <!-- httpSession에 있는 것을 가지고옴
    ${sessionScope._MEMBER_.memberId}
    ${sessionScope._MEMBER_.nickName}
    ${sessionScope._MEMBER_.password}
    ${sessionScope._MEMBER_.email}  -->
    
    
    <!-- 로그인한 사용자와 글을 쓴 사용자가 같을때만 삭제버튼이 보여진다.
    eq 는 ==와 동일한 의미
    c:set 을 써서 el도 변수로 만들 수 있다
    내가 지우려는 게시글 번호가 내가 쓴 글인지부터 체크 -->
    <c:set var ="loginId" value="${ sessionScope._MEMBER_.memberId }" />
    <c:set var ="isAdmin" value="${ sessionScope._MEMBER_.isAdmin() }" />
    
    
    <c:if test="${ loginId eq article.memberId || isAdmin }">    
    <a href="<c:url value="/delete?articleId=${article.articleId}" />"><img src="<c:url value="/resource/img/delete.png" />" style="width: 20px;">삭제하기</a>   
    <a href="<c:url value="/modify?articleId=${article.articleId}" />"><img src="<c:url value="/resource/img/modify.png" />" style="width: 20px; margin-left:100px;">수정하기</a>
 
    </c:if>
    
</div>
 
        
    <div align="center" id="replyDiv">
        <div id="formWrapper">            
            <form id="writeReplyForm">
                <input type="hidden" id="articleId" name="articleId" value="${article.articleId}" />
                <input type="hidden" id="depth" name="depth" value="0" />
                <input type="hidden" id="parentReplyId" name="parentReplyId" value="0" />
                <input type="hidden" id="groupId" name="groupId" value="0" />
                <input type="hidden" id="orderNo" name="orderNo" value="0" />
                <input type="hidden" id="replyId" name="replyId" value="0" />
                
                <textarea id="descrption" name="description" style="width:800px;"></textarea>
                <input type="button" id="writeReplyBtn" value="등록" />    
            </form>
        </div>
        <c:forEach items="${article.replyList}" var="reply">
        
            <div style="padding-left: ${reply.depth * 40 }px;">
                <table id="replyTable">
                
                    <tr>
                        <td style="width:50px;">${reply.nickName}</td>
                        <td>
                            ${reply.description}
                            <!--<c:if test="${ reply.depth eq 0 }">
                                <span class="writeReReply" style="cursor: pointer;">댓글 달기</span>
                            </c:if>
                            -->
                            <span class="writeReReply" style="cursor: pointer;">댓글 달기</span>
                        </td>
                    </tr>
                    <tr class="hide">
                        <td class="groupId">${reply.groupId}</td>
                        <td class="parentReplyId">${reply.parentReplyId}</td>
                    </tr>
                    <tr class="hide">
                        <td class="depth">${reply.depth}</td>
                        <td class="orderNo">${reply.orderNo}</td>
                    </tr>        
                    <tr class="hide">
                        <td colspan="2" class="replyId">${reply.replyId}</td>
                    </tr>                        
                </table>
                <div class="hide formAppender"></div>
            </div>
        </c:forEach>
        
    </div>
 
<jsp:include page="/WEB-INF/view/common/footer.jsp"></jsp:include>