(22.12.06)Database : MVC방식 게시판 답글 달기 (Oracle 계층 쿼리 활용)

●●게시판 답글 달기- 계층구조 질의(hierarchycal Query)●●

 

 

 

오라클에서 제공해주는 계층구조 질의(hierarchycal Query)을 활용하여 게시판에 답글 넣기

 

기존 게시판 [(22.12.02) 게시판 글 참고!!!] 에서  답글 달기 기능 만들기

M : BoardVO.java , BoardDAO.java

V : boardList.jsp , boardAdd.jsp , boardDetail.jsp , boardEdit.jsp , boardReple.jsp

C : 서블릿

MVC Service : BoardService.java

 현재 글에 없는 java, jsp 파일은 (22.12.02)글 참고

 

 

BoardService.java

import java.io.IOException;
import java.io.PrintWriter;
import java.util.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.json.simple.JSONObject;
 
public class BoardService 
{
    private HttpServletRequest request;
    private HttpServletResponse response;
    private String viewPath = "/WEB-INF/jsp/board";
    
    public BoardService(HttpServletRequest request, HttpServletResponse response) 
    {
        this.request = request;
        this.response = response;
        
    }
    
    public String exec() 
    {
        String cmd = request.getParameter("cmd");
        if(cmd==null || cmd.equals("list"))
        {
            List<BoardVO> list = getList();
            request.setAttribute("list", list);
            return viewPath + "/boardList.jsp";
        }
        else if(cmd.equals("addBoard"))
        {
            return viewPath + "/boardAdd.jsp";
        }
        else if(cmd.equals("addResult"))
        {
            boolean added = addBoard();
            JSONObject jsObj = new JSONObject();
            Map<String, Object> map = new HashMap<>();
            map.put("added", added);
            sendJSONStr(map);
        }
        else if(cmd.equals("findById"))
        {
            int boardid = Integer.valueOf(request.getParameter("boardid"));
            BoardVO board = findById(boardid);
            request.setAttribute("board", board);
            return viewPath + "/boardDetail.jsp";
        }
        else if(cmd.equals("editBoard"))
        {
            int boardid = Integer.valueOf(request.getParameter("boardid"));
            BoardVO board = findById(boardid);
            request.setAttribute("board", board);
            return viewPath + "/boardEdit.jsp";
        }
        else if(cmd.equals("updateBoard"))
        {
            boolean updated = updateResult();
            JSONObject jsObj = new JSONObject();
            Map<String, Object> map = new HashMap<>();
            map.put("updated", updated);
            sendJSONStr(map);
        }
        else if(cmd.equals("deleteBoard"))
        {
            boolean deleted = deleteResult();
            JSONObject jsObj = new JSONObject();
            Map<String, Object> map = new HashMap<>();
            map.put("deleted", deleted);
            sendJSONStr(map);
        
        }else if(cmd.equals("repleBoard"))
        {
            int boardid = Integer.valueOf(request.getParameter("boardid"));
            BoardVO board = findById(boardid);
            request.setAttribute("board", board);
            return viewPath + "/boardReple.jsp";
        }
        else if(cmd.equals("repleAddBoard"))
        {
            boolean repleAdded = repleAddResult();
            JSONObject jsObj = new JSONObject();
            Map<String, Object> map = new HashMap<>();
            map.put("repleAdded", repleAdded);
            sendJSONStr(map);
        }
        return null;
    }
    public List<BoardVO> getList()
    {
        BoardDAO dao = new BoardDAO();
        List<BoardVO> list = dao.getList();
        return list;
    }
    public boolean addBoard() {
        String title = request.getParameter("title");
        String author = request.getParameter("author");
        String contents = request.getParameter("contents");
        
        BoardVO board = new BoardVO();
        board.setTitle(title);
        board.setAuthor(author);
        board.setContents(contents);
        
        BoardDAO dao = new BoardDAO();
        return dao.addBoard(board);
    }
    public BoardVO findById(int boardid)
    {
        BoardDAO dao = new BoardDAO();
        BoardVO board = dao.findById(boardid);
        return board;
    }
    public boolean updateResult() {
        int boardid = Integer.valueOf(request.getParameter("boardid"));
        String title = request.getParameter("title");
        String contents = request.getParameter("contents");
        
        BoardVO board = new BoardVO();
        board.setBoardid(boardid);
        board.setTitle(title);
        board.setContents(contents);
        
        BoardDAO dao = new BoardDAO();
        return dao.updateBoard(board);
    }
    public boolean repleAddResult()
    {    
        int boardid = Integer.valueOf(request.getParameter("boardid"));
        String title = request.getParameter("title");
        String contents = request.getParameter("contents");
        String author = request.getParameter("author");
        
        
        BoardVO board = new BoardVO();
        board.setParentid(boardid);
        board.setTitle(title);
        board.setAuthor(author);
        board.setContents(contents);
        
        BoardDAO dao = new BoardDAO();
        return dao.reply(board);
    }
    public boolean deleteResult()
    {    
        int boardid = Integer.valueOf(request.getParameter("boardid"));
        BoardDAO dao = new BoardDAO();
        return dao.deleteBoard(boardid);
    }
    public void sendJSONStr(Map<String, Object> map){
        JSONObject jsobj = new JSONObject(map);
        String jsStr = jsobj.toJSONString();
        
        try {
            PrintWriter out = response.getWriter();
            out.print(jsStr);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

72 ~ 78 행

	else if(cmd.equals("repleBoard"))
    {
        int boardid = Integer.valueOf(request.getParameter("boardid"));
        BoardVO board = findById(boardid);
        request.setAttribute("board", board);
        return viewPath + "/boardReple.jsp";
    }

boardDetail.jsp 뷰에서 [답글달기] 링크를 누르면 form을 전송하면서 cmd는 repleBoard로 전송한다. 서비스에서 cmd가 repleBoard인 것을 인지하여 같이 hidden으로 넘어온 boardid에 맞는 board 객체를 찾아서 모든 데이터를 boardReple.jsp에 넘긴다. 그리고 boardReple.jsp 뷰로 넘어가게 된다.

 

 

79~86 행

	else if(cmd.equals("repleAddBoard"))
    {
       boolean repleAdded = repleAddResult();
       JSONObject jsObj = new JSONObject();
       Map<String, Object> map = new HashMap<>();
       map.put("repleAdded", repleAdded);
       sendJSONStr(map);
    }

boardReple.jsp 에서 정보를 기입하고 '저장' 버튼을 누르면 cmd와 boardid가 넘어오게 된다. repleAddBoard 와 같기 때문위 로직이 돌아간다. repleAddResult() 메소드에서 리턴된 값을 boolean 변수에 저장하고 map 자료구조에 담아 json 문자열로 response에 보내주게 된다. 이 response는  boardReple.jsp success:function의 파라미터로 들어가서 true면 답글달기 성공을 false면 답글 달기 실패를 alert창으로 보여주게된다.

 

 

127 ~ 143 행

public boolean repleAddResult()
    {    
        int boardid = Integer.valueOf(request.getParameter("boardid"));
        String title = request.getParameter("title");
        String contents = request.getParameter("contents");
        String author = request.getParameter("author");
        
        BoardVO board = new BoardVO();
        board.setParentid(boardid);
        board.setTitle(title);
        board.setAuthor(author);
        board.setContents(contents);
        
        BoardDAO dao = new BoardDAO();
        return dao.reply(board);
    }

요청에서 넘어온 파라미터 값들을  각각 맞는 변수에 저장한다.  보드객체를 새로 만들어 데이터들을 set해주는데 이때 parentid를 넘어온 boardid와 같게 해준다. 나중에 BoardDAO.java에서 sql 쿼리로서 parentid=0부터 정렬하여 boardid와 parentid가 계층구조를 이루게끔 하기 위함이다.

BoardDAO 객체에 데이터를 set한 board객체를 파라미터로 보내주어 넘어온 값을 리턴한다.

 

 

BoardDAO.java

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 {
    private Connection conn;
    private PreparedStatement pstmt;
    private ResultSet rs;
    
    private Connection getConn()
    {
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            conn = DriverManager.getConnection(
                    "jdbc:oracle:thin:@localhost:1521:xe", "SCOTT", "TIGER");
            this.conn = conn;
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public List<BoardVO> getList()
    {
        getConn();
        
        try {
            String sql = "SELECT boardid, LPAD('　',  (LEVEL-1)*2,'　')||title AS title,"
                    + " author, hitcnt, regdate, contents, parentid"
                    + " FROM board START WITH parentid=0"
                    + " CONNECT BY PRIOR boardid=parentid";
            this.pstmt = conn.prepareStatement(sql);
            this.rs = pstmt.executeQuery();
 
            
            List<BoardVO> list = new ArrayList<>();
            while(rs.next())
            {
                int boardid = rs.getInt("boardid");
                String title = rs.getString("title");
                String author = rs.getString("author");
                java.sql.Date regdate = rs.getDate("regdate");
                String contents = rs.getString("contents");
                int hitcnt = rs.getInt("hitcnt");
                int parentid = rs.getInt("parentid");
                
                BoardVO board = new BoardVO();
                board.setBoardid(boardid);
                board.setTitle(title);
                board.setAuthor(author);
                board.setRegdate(regdate);
                board.setContents(contents);
                board.setHitcnt(hitcnt);
                board.setParentid(parentid);
                
                list.add(board);
            }
            return list;
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    public boolean addBoard(BoardVO board)
    {
        getConn();
        
        try {
            String sql = "INSERT INTO board "
                    + "(boardid, title, contents, author) VALUES(DB_ID_SEQ.NEXTVAL,?,?,?)";
            this.pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, board.getTitle());
            pstmt.setString(2, board.getContents());
            pstmt.setString(3, board.getAuthor());
            
            
            int rows = pstmt.executeUpdate();
 
            return rows>0 ? true : false;
 
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return false;
    }
    public BoardVO findById(int boardid)
    {
        getConn();
        
        try {
            String sql = "UPDATE board SET hitcnt= hitcnt+1 WHERE boardid=?";
            this.pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, boardid);
            pstmt.executeUpdate();
            
            String sql2 = "SELECT * FROM board WHERE boardid=?";
            this.pstmt = conn.prepareStatement(sql2);
            pstmt.setInt(1, boardid);
            this.rs = pstmt.executeQuery();
 
            if(rs.next())
            {
                int insertboardid = rs.getInt("BOARDID");
                String title = rs.getString("TITLE");
                String author = rs.getString("AUTHOR");
                String contents = rs.getString("CONTENTS");
                int hitcnt = rs.getInt("HITCNT");
                java.sql.Date regdate = rs.getDate("REGDATE");
                
                BoardVO board = new BoardVO();
                board.setBoardid(insertboardid);
                board.setTitle(title);
                board.setAuthor(author);;
                board.setContents(contents);
                board.setHitcnt(hitcnt);
                board.setRegdate(regdate);
                return board;
            }
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    public boolean updateBoard(BoardVO board)
    {
        getConn();
        
        try {
            String sql = "UPDATE board SET title=?, contents=? WHERE boardid=?";
            this.pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, board.getTitle());
            pstmt.setString(2, board.getContents());
            pstmt.setInt(3, board.getBoardid());
 
            int rows = pstmt.executeUpdate();
 
            return rows>0 ? true : false;
 
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return false;
    }
    public boolean deleteBoard(int boardid)
    {
        getConn();
        
        try {
            String sql = "DELETE FROM board WHERE boardid=?";
            this.pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,boardid);
 
            int rows = pstmt.executeUpdate();
 
            return rows>0 ? true : false;
 
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return false;
    }
    public boolean reply(BoardVO board)
    {
        getConn();
        
        try {
            String sql = "INSERT INTO board "
                    + "(boardid, title, contents, author, parentid)"
                    + " VALUES(DB_ID_SEQ.NEXTVAL,?,?,?,?)";
            this.pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "💌Re:"+board.getTitle());
            pstmt.setString(2, board.getContents());
            pstmt.setString(3, board.getAuthor());
            pstmt.setInt(4, board.getParentid());
            
            
            int rows = pstmt.executeUpdate();
 
            return rows>0 ? true : false;
 
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return false;
    }
    
    private void closeAll()
    {
        try {
            if(rs!=null) rs.close();
            if(pstmt!=null) pstmt.close();
            if(conn!=null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

28 ~ 70 행

public List<BoardVO> getList()
    {
        getConn();
        
        try {
            String sql = "SELECT boardid, LPAD('　',  (LEVEL-1)*2,'　')||title AS title,"
                    + " author, hitcnt, regdate, contents, parentid"
                    + " FROM board START WITH parentid=0"
                    + " CONNECT BY PRIOR boardid=parentid";
            this.pstmt = conn.prepareStatement(sql);
            this.rs = pstmt.executeQuery();

            
            List<BoardVO> list = new ArrayList<>();
            while(rs.next())
            {
                int boardid = rs.getInt("boardid");
                String title = rs.getString("title");
                String author = rs.getString("author");
                java.sql.Date regdate = rs.getDate("regdate");
                String contents = rs.getString("contents");
                int hitcnt = rs.getInt("hitcnt");
                int parentid = rs.getInt("parentid");
                
                BoardVO board = new BoardVO();
                board.setBoardid(boardid);
                board.setTitle(title);
                board.setAuthor(author);
                board.setRegdate(regdate);
                board.setContents(contents);
                board.setHitcnt(hitcnt);
                board.setParentid(parentid);
                
                list.add(board);
            }
            return list;
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }

위 로직은 기존에 있던 리스트를 보여주는 로직에서 이젠 계층구조를 써서 답글이  boardid가 즉 글번호가 늦어도 원글에 바로 아래 가게 끔 해주기 위하여 바꿔주었다. 핵심적인 로직이 있는 SQL 쿼리를 바래 SQL developer에서 나타내면 다음과 같다.

220행 ~ 221행 : parentid 가 0인 글부터 시작하는 대신에 그 다음글엔 원글의 boardid와 parentid가 같은걸 오겠끔 해준다.

222 행  : 만약 parentid가 같은 형제 글이라면 boardid 순서대로 나열한다.

218 행 : 계층구조를 더 잘보이게끔 들여쓰기를 해주기 위한 로직이다. 레벨의 -1에서 곱하기 2만큼 빈 공백(ㄱ 한자키 1번)을 띄우게 설정했고 그걸 title이라고 명시했다. 나머지 컬럼은 board 테이블의 컬럼들이다.

 

 

BoardDAO.java의 177 행 ~21 행

public boolean reply(BoardVO board)
    {
        getConn();
        
        try {
            String sql = "INSERT INTO board "
                    + "(boardid, title, contents, author, parentid)"
                    + " VALUES(DB_ID_SEQ.NEXTVAL,?,?,?,?)";
            this.pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "💌Re:"+board.getTitle());
            pstmt.setString(2, board.getContents());
            pstmt.setString(3, board.getAuthor());
            pstmt.setInt(4, board.getParentid());
            
            
            int rows = pstmt.executeUpdate();

            return rows>0 ? true : false;

        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return false;
    }

BoardService.java의  repleAddResult() 메소드에서 넘어온 board 객체에서 데이터를 get 해와서 preparestatment에 set 해준다. 만약 성공적으로 저장이 됐다면 행의 변화가 생기게 되고 행의 변화를 1을 리턴해주기 때문에 0보다 크면 잘 수행됐다는 true를 실패했다면 false를 리턴하게 된다.

 

boardReple.jsp

<%@ page contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>게시글 답글달기</title>
<style type="text/css">
    @import url(//fonts.googleapis.com/earlyaccess/jejumyeongjo.css);
 
    .jm-font{
        font-family: 'Jeju Myeongjo', serif;
        color: orange;
        font-size:1.5em;
    }
    .main-font{
        font-family: 'Jeju Myeongjo', serif;/*웹 폰트 지정*/
        color: black;
        font-size:1.5em;
    }
    .wrapper {
    display: flex;
    justify-content: center;
    align-items: center;
    }
    main { width: fit-conent; margin:1em auto;}
    main h1 { text-align: center; }
    label { display:inline-block; margin-right:1em; width:fit-conent;
        background-color:rgb(248,242,24); text-align:center; padding:0.5em;
    }
    div { margin-top:0.5em; width: fit-conent; }
</style>
<script src="https://code.jquery.com/jquery-3.6.1.min.js" integrity=
"sha256-o88AwQnZB+VDvE9tvIXrMQaPlFFSUTR+nldQm1LuPXQ=" crossorigin="anonymous">
</script>
<script type="text/javascript">
function updateBoard()
{
    
    var obj = $('#updateForm').serialize();
    
    $.ajax({
        url : 'DBBoard',
        method: 'post',
        data: obj,
        cache : false,
        dataType:'json',
        success:function(res){
            alert(res.repleAdded ? '답글달기 성공' : '답글달기 실패');
            location.href="DBBoard?cmd=list";
        },
        error : function(xhr,status,err){
            alert('에러:' + err);
        }
    });
    return false;
}
</script>
</head>
<body style="background-color: rgb(254,223,255);">
<span class="wrapper">
<main class="main-font">
<h1 class="jm-font">게시글 답글달기</h1>
<form id="updateForm" onsubmit="return updateBoard();">
<input type="hidden" name="cmd" value="repleAddBoard">
<input type="hidden" name="boardid" value="${board.boardid}">
    
    <div>
        <label>1️⃣글제목</label> 
        <input id="title" type="text" name="title" value="${board.title}" style="height:40px">
    </div>
    <div>
        <label>2️⃣작성자</label>
        <input id="author" type="text" name="author" value="" style="height:40px">
    </div>
    <div>
        <label>3️⃣글내용</label>
        <textarea id="contents" name="contents" rows="5" cols="50" style="height:150px"></textarea>
    </div>
<div class="btn">
    <a href="DBBoard?cmd=list">[게시글 목록가기]</a>    
    <button type="reset">취소</button>
    <button type="submit">저장</button>
</div>
</form>
</main>
</span>
</body>
</html>

BoardService.java 에서 넘어온 response의 repleAdded가 true면 답글달기 성공을 false면 답글달기 실패를 alert창에 띄운다.

 

 

실행결과 : 

첫 실행 화면

 

 

TEST 글에 답글달기

 

 

답글 달기 성공

 

 

글번호와 상관 없이 TEST글의 답글이므로 바로 아래 나타나는 TEST답글

 

 

답글 내용

 

 

답글에 답글 달기

 

 

답글에 답글을 단 결과

 

 

답글의 답글 상세보기