(22.12.02)Database : DB에서 정보를 꺼내오고 추가하는 웹 프로그래밍(게시판 글 찾기,추가,수정,삭제 CRUD)

●●게시판 CRUD●●

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
 
@WebServlet(name = "DBBoardServlet", urlPatterns = { "/DBBoard" })
public class BoardServlet extends HttpServlet {
 
    protected void service(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException 
    {
        String view = new BoardService(request, response).exec();
          if(view!=null) {
              getServletContext().getRequestDispatcher(view).forward(request, response);
          }
    }
}

import java.sql.Date;
 
public class BoardVO {
    private int boardid;
    private String title;
    private String author;
    private java.sql.Date regdate;
    private String contents;
    private int hitcnt;
    private int parentid;
    
    
    public BoardVO() {}
    
    public BoardVO(int boardid) {
        this.boardid = boardid;
    }
    
    public BoardVO(int boardid, String title, String author, Date regdate, String contents, int hitcnt, int parentid) {
        super();
        this.boardid = boardid;
        this.title = title;
        this.author = author;
        this.regdate = regdate;
        this.contents = contents;
        this.hitcnt = hitcnt;
        this.parentid = parentid;
    }
 
    public int getBoardid() {
        return boardid;
    }
 
    public void setBoardid(int boardid) {
        this.boardid = boardid;
    }
 
    public String getTitle() {
        return title;
    }
 
    public void setTitle(String title) {
        this.title = title;
    }
 
    public String getAuthor() {
        return author;
    }
 
    public void setAuthor(String author) {
        this.author = author;
    }
 
    public java.sql.Date getRegdate() {
        return regdate;
    }
 
    public void setRegdate(java.sql.Date regdate) {
        this.regdate = regdate;
    }
 
    public String getContents() {
        return contents;
    }
 
    public void setContents(String contents) {
        this.contents = contents;
    }
 
    public int getHitcnt() {
        return hitcnt;
    }
 
    public void setHitcnt(int hitcnt) {
        this.hitcnt = hitcnt;
    }
 
    public int getParentid() {
        return parentid;
    }
 
    public void setParentid(int parentid) {
        this.parentid = parentid;
    }
}

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

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 * FROM board";
            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;
    }
    
    private void closeAll()
    {
        try {
            if(rs!=null) rs.close();
            if(pstmt!=null) pstmt.close();
            if(conn!=null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

<%@ page contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!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', cursive;/*웹 폰트 지정*/
        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;
    min-height: 100vh;
    }
    main { width:fit-content; margin:1em auto;}
    main h1{ text-align: center; }
    table { border:1px solid black; border-spacing:0; border-collapse: collapse; 
        padding:0.5em; text-align: center; }
    td,th { border-bottom:1px dashed black; padding:0.3em 1em; 
        border-right: 1px solid black;
    }
    th { background-color:rgb(248,242,24);}
    tr {background-color:rgb(255,255,255);}
    div.links { width:fit-content; margin:1em auto; }
</style>
</head>
<body style="background-color: rgb(254,223,255);">
<div class="wrapper">
<main class="main-font">
<h1 class="jm-font">😀게시글 목록😀</h1>
<table>
<tr><th>1️⃣글번호<th>2️⃣제목</th><th>3️⃣작성자</th><th>4️⃣히트수</th><th>5️⃣작성일</th></tr>
<c:forEach var="board" items="${list}">
   <tr>
     <td>${board.boardid}</td>
     <td> <a href="DBBoard?cmd=findById&boardid=${board.boardid}">${board.title}</a></td>
     <td>${board.author} </td>
     <td>${board.hitcnt} </td>
     <td>${board.regdate} </td>
   </tr>
</c:forEach>
</table>
<div class="links">
<a href="DBBoard?cmd=addBoard">[게시글 추가]</a>
</div>
</main>
</div>
</body>
</html>

<%@ 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', cursive;
        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:3em; 
        background-color:rgb(248,242,24); text-align:center; padding:0.5em;
    }
    div { margin-top:0.5em; }
</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 addBoard()
{
    var obj = $('#addForm').serialize();
    $.ajax({
        url : 'DBBoard',
        method: 'post',
        data: obj,
        cache : false,
        dataType:'json',
        success:function(res){
            alert(res.added ? '추가 성공' : '추가 실패');
            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="addForm" onsubmit="return addBoard();">
    <input type="hidden" name="cmd" value="addResult">
    <div>
        <label for="title">제목</label> 
        <input id="title" type="text" name="title" value="">
    </div>
    <div>
        <label for="author">작성자</label> 
        <input id="author" type="text" name="author" value="">
    </div>
    <div>
        <label for="contents">내용</label>
        <textarea id="contents" name="contents" rows="5" cols="50"></textarea>
    </div>
    <div class="btn">
        <button type="reset">취소</button>
        <button type="submit">저장</button>
    </div>
</form>
</main>
</span>
</body>
</html>

<%@ 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 deleteboard(boardid)
    {
        if(!confirm("정말로 현재 게시글을 삭제하겠습니까?")) return;
 
        $.ajax({
            url:'DBBoard', 
            method:'post',
            data: {'cmd':'deleteBoard','boardid':boardid},
            cache : false,
            dataType: 'json',
            success: function(res){ 
                if(res.deleted){
                    alert(res.deleted ? '삭제 성공':'에러'); 
                    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>
    <div>
        <label>1️⃣글번호</label> 
        ${board.boardid}
    </div>
    <div>
        <label>2️⃣글제목</label> 
        ${board.title}
    </div>
    <div>
        <label>3️⃣작성자</label>
        ${board.author}
    </div>
    <div>
        <label>4️⃣히트수</label>
        ${board.hitcnt}
    </div>
    <div>
        <label>5️⃣글내용</label>
        ${board.contents}
    </div>
    <div>
        <label>6️⃣작성일</label>
        ${board.regdate}
    </div>
<a href="DBBoard?cmd=list">[게시글 목록가기]</a>    
<a href="DBBoard?cmd=editBoard&boardid=${board.boardid}">[수정]</a>
<a href="javascript:deleteboard(${board.boardid});">[삭제]</a>
</main>
</span>
</body>
</html>

<%@ 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.updated ? '수정 성공' : '수정 실패');
            location.href="DBBoard?cmd=findById&boardid=${board.boardid}";
        },
        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="updateBoard">
<input type="hidden" name="boardid" value="${board.boardid}">
    <div>
        <label>1️⃣글번호</label> 
        ${board.boardid}
    </div>
    <div>
        <label>2️⃣글제목</label> 
        <input id="title" type="text" name="title" value="${board.title}">
    </div>
    <div>
        <label>3️⃣작성자</label>
        ${board.author}
    </div>
    <div>
        <label>4️⃣히트수</label>
        ${board.hitcnt}
    </div>
    <div>
        <label>5️⃣글내용</label>
        <textarea id="contents" name="contents" rows="5" cols="50" >${board.contents}</textarea>
    </div>
    <div>
        <label>6️⃣작성일</label>
        ${board.regdate}
    </div>
<div class="btn">
    <a href="DBBoard?cmd=list">[게시글 목록가기]</a>    
    <button type="reset">취소</button>
    <button type="submit">저장</button>
</div>
</form>
</main>
</span>
</body>
</html>

실행 결과 :