[Web] 31. 회원 관리 & 게시판 만들기 ⑥ : 게시글 조회, 삭제, 수정

https://github.com/upcake/Class_Examples

교육 중에 작성한 예제들은 깃허브에 올려두고 있습니다. 

gif 파일은 클릭해서 보는 것이 정확합니다.

---

 - 회원 관리 & 게시판 만들기 ⑥ :  게시글 조회, 삭제, 수정 -

▲게시글 조회, 삭제, 수정 작동 화면

 

package com.board.action;
import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.commons.action.Action;
import com.commons.action.ActionForward;

@WebServlet("/BoardFrontController.bo")
public class BoardFrontController extends HttpServlet {
	protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String uri = request.getRequestURI();
		String context = request.getContextPath();
		String command = uri.substring(context.length());
		//System.out.println(command);	//콘솔 창에 /memberLogin.me 등이 출력된다.
		
		Action action = null;
		ActionForward forward = null;
		
		if(command.equals("/boardList.bo")) {
			action = new BoardListAction();
			forward = action.execute(request, response);
		} else if(command.equals("/boardWrite.bo")) {
			forward = new ActionForward();
			forward.setPath("board/board_write.jsp");
			forward.setRedirect(false);
			//가져갈게 있으면 true(forward) 없으면 false(sendRedirect)
			//URL 바뀌면 true(boardWrite.bo로 바뀌어서 안됨) 안바뀌면 false
		} else if(command.equals("/boardAddAction.bo")) {
			action = new BoardAddAction();
			forward = action.execute(request, response);
		} else if(command.equals("/boardDetailAction.bo")) {
			action = new BoardDetailAction();
			forward = action.execute(request, response);
		} else if(command.equals("/boardDeleteAction.bo")) {
			action = new BoardDeleteAction();
			forward = action.execute(request, response);
		} else if(command.equals("/boardModifyView.bo")) {
			action = new BoardModifyView();
			forward = action.execute(request, response);
		} else if(command.equals("/boardModifyAction.bo")) {
			action = new BoardModifyAction();
			forward = action.execute(request, response);
		}
		
		if(forward != null) {
			if(forward.isRedirect()) {	//true : sendRedirect() 전환
				response.sendRedirect(forward.getPath());
			} else {					//false : forward() 전환
				RequestDispatcher rd = request.getRequestDispatcher(forward.getPath());
				rd.forward(request, response);
			}
		}
	}
}

▲BoardFrontController.java

 

package com.board.action;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.board.study.BoardDAO;
import com.board.study.BoardDTO;
import com.commons.action.Action;
import com.commons.action.ActionForward;

public class BoardDetailAction implements Action {
	@Override
	public ActionForward execute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		int board_num = Integer.parseInt(request.getParameter("board_num"));
		HttpSession session = request.getSession();
		String id = (String) session.getAttribute("id");
		
		BoardDAO dao = new BoardDAO();
		BoardDTO dto = dao.getDetail(board_num);
		if(!id.equals(dto.getBoard_id())) {	//작성자와 로그인 id가 같을경우 조회수가 증가하지 않음
			dao.readCount(board_num);	//조회수 증가
		}
		dto = dao.getDetail(board_num);	//증가한 조회수를 가져옴
		request.setAttribute("dto", dto);
		
		ActionForward forward = new ActionForward();
		forward.setPath("board/board_view.jsp");
		forward.setRedirect(false);
		return forward;
	}

}

▲BoardDetailAction.java

 

package com.board.study;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class BoardDAO {
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;
	
	public Connection getConn() {
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
		String user = "hanul";
		String password = "0000";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("getConn() Exception!!!");
		}
		return conn;
	} //getConn()

	//게시글 등록
	public int boardInsert(BoardDTO dto) {
		conn = getConn();
		String sql = "";
		int b_num = 0;
		int succ = 0;
		try {
			//글 번호를 검색한 후 등록할 글 번호(b_num)를 결정
			sql = "SELECT MAX(board_num) FROM memberBoard";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			if(rs.next()) { //글이 있으면 (글 갯수 + 1)번
				b_num = rs.getInt(1);
				b_num += 1;
			} else { // 글이 없으면 1번
				b_num = 1;
			}
			
			//글 등록
			sql = "INSERT INTO memberBoard VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, sysdate)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, b_num);
			ps.setString(2, dto.getBoard_id());
			ps.setString(3, dto.getBoard_subject());
			ps.setString(4, dto.getBoard_content());
			ps.setString(5, dto.getBoard_file());
			ps.setInt(6, b_num); //댓글을 쓰기 위한 그룹 번호
			ps.setInt(7, 0); //댓글이 없으니 0
			ps.setInt(8, 0);
			ps.setInt(9, 0);
			succ = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("boardInsert() Exception!!!");
		} finally {
			dbClose();
		}
		return succ;
	} //boardInsert()
	
	//등록된 글의 총 개수
	public int getListCount() {
		conn = getConn();
		String sql = "SELECT COUNT(*) FROM memberBoard";
		//NULL이 들어갈 수 있는 필드도 있으므로 모든 필드를 센 다음에 가장 많이 나온 필드를 기준으로 한다.
		int listCount = 0;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
				listCount = rs.getInt(1);	//board_num 필드는 NULL값이 들어올 수 없으니 board_num을 가져온다.
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("getListCount() Exception!!!");
		} finally {
			dbClose();
		}
		return listCount;
	} //getListCount()
	
	//전체 글 목록 조회(Feat : 페이징 
	public ArrayList<BoardDTO> getBoardList(int page, int limit) {
		conn = getConn();
		String sql = "SELECT * FROM";
		sql += " (SELECT ROWNUM rnum, board_num, board_id,";
		sql	+= " board_subject, board_content, board_file, board_re_ref,";
		sql += " board_re_lev, board_re_seq, board_readcount, board_date";
		sql	+= " FROM (SELECT * FROM memberBoard ORDER BY";
		sql += " board_num DESC, board_re_seq ASC))";
		sql += "WHERE rnum >= ? and rnum <= ?";
		int startRow = (page - 1) * 10 + 1; //읽기 시작할 rownum
		int endRow = startRow + limit - 1;	//읽을 마지막 rownum
		ArrayList<BoardDTO> list = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, startRow);
			ps.setInt(2, endRow);
			rs = ps.executeQuery();
			
			while(rs.next()) {
				BoardDTO dto = new BoardDTO();
				dto.setBoard_num(rs.getInt("board_num"));
				dto.setBoard_id(rs.getString("board_id"));
				dto.setBoard_subject(rs.getString("board_subject"));
				dto.setBoard_content(rs.getString("board_content"));
				dto.setBoard_file(rs.getString("board_file"));
				dto.setBoard_re_ref(rs.getInt("board_re_ref"));
				dto.setBoard_re_lev(rs.getInt("board_re_lev"));
				dto.setBoard_re_seq(rs.getInt("board_re_seq"));
				dto.setBoard_readcount(rs.getInt("board_readcount"));
				dto.setBoard_date(rs.getString("board_date"));
				list.add(dto);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("getBoardList() Exception!!!");
		} finally {
			dbClose();
		}
		return list;
		
	}

	//글 내용 보기
	public BoardDTO getDetail(int board_num) {
		conn = getConn();
		String sql = "SELECT * FROM memberBoard WHERE board_num = ?";
		BoardDTO dto = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, board_num);
			rs = ps.executeQuery();
			
			if(rs.next()) {
				dto = new BoardDTO();
				dto.setBoard_num(rs.getInt("board_num"));
				dto.setBoard_id(rs.getString("board_id"));
				dto.setBoard_subject(rs.getString("board_subject"));
				dto.setBoard_content(rs.getString("board_content"));
				dto.setBoard_file(rs.getString("board_file"));
				dto.setBoard_re_ref(rs.getInt("board_re_ref"));
				dto.setBoard_re_lev(rs.getInt("board_re_lev"));
				dto.setBoard_re_seq(rs.getInt("board_re_seq"));
				dto.setBoard_readcount(rs.getInt("board_readcount"));
				dto.setBoard_date(rs.getString("board_date"));
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("getDetail() Exception!!!");
		} finally {
			dbClose();
		}
		return dto;
	} //getDetail()
	
	//조회수 증가
	public void readCount(int board_num) {
		conn =getConn();
		String sql = "UPDATE memberBoard SET board_readcount = ";
		sql += "board_readcount + 1 WHERE board_num = ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, board_num);
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("readCount() Excetption!!!");
		} finally {
			dbClose();
		}
	} //readCount()
	
	//작성자 확인
	public boolean isBoardWriter(int board_num, String id) {
		conn = getConn();
		String sql = "SELECT * FROM memberBoard WHERE board_num = ?";
		boolean result = false;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, board_num);
			rs = ps.executeQuery();
			
			if(rs.next()) {
				if(id.equals(rs.getString("board_id"))) {
					result = true;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("isBoardWriter() Exception!!!");
		} finally {
			dbClose();
		}
		return result;
	} //isBoardWriter()

	//글 삭제
	public int boardDelete(int board_num) {
		conn = getConn();
		String sql = "DELETE FROM memberBoard WHERE board_num = ?";
		int succ = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, board_num);
			succ = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("boardDelete() Exception!!!");
		} finally {
			dbClose();
		}
		return succ;
	} //boardDelete()
	
	//글 수정
	public int boardUpdate(BoardDTO dto) {
		conn = getConn();
		String sql = "UPDATE memberBoard SET board_subject = ?, ";
		sql += "board_content = ? WHERE board_num = ?";
		int succ = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, dto.getBoard_subject());
			ps.setString(2, dto.getBoard_content());
			ps.setInt(3, dto.getBoard_num());
			succ = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("boardUpdate() Exception!!!");
		} finally {
			dbClose();
		}
		return succ;
	} //boardUpdate()

	//DB 종료
		public void dbClose() {
			try {
				if(rs != null) rs.close();
				if(ps != null) ps.close();
				if(conn != null) conn.close();
			} catch (Exception e) {
				e.printStackTrace();
				System.out.println("dbClose() Exception!!!");
			}
		} //dbClose()



} //class

▲BoardDAO.java

 

<%@page import="com.board.study.BoardDTO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%
String id = (String) (session.getAttribute("id"));
BoardDTO dto = (BoardDTO) request.getAttribute("dto");

String board_content = dto.getBoard_content();
String replaceContent = board_content.replaceAll("\r\n", "<br/>");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>board view jsp</title>
<script type="text/javascript">
function fnUpdate(num) {
	if(confirm("수정하시겠습니까?")) {
		location.href = "boardModifyView.bo?board_num=" + num;
	}
	return false;
}

function fnDelete(num) {
	if(confirm("정말 삭제하시겠습니까?")) {
		location.href = "boardDeleteAction.bo?board_num=" + num;
	}
	return false;
}

</script>
</head>
<body>
<div align="center">
	<h3>[상세 글 보기]</h3>
	<table border="1">
		<tr>
			<th>제목</th>
			<td><%=dto.getBoard_subject() %></td>		
			<th>조회수</th>
			<td align="center"><%=dto.getBoard_readcount() %></td>
		</tr>
		<tr>
			<th>내용</th>
			<td colspan="3" width="500"><%=replaceContent %></td>
		</tr>
		<tr>
			<th>첨부 파일</th>
			<td colspan="3" >
				<%if(dto.getBoard_file() == null) { %>
					첨부된 파일이 없습니다.
				<%} else { %>
					<a href="boardupload/<%=dto.getBoard_file()%>"><%=dto.getBoard_file() %></a>
				<%} %>
			</td>
		</tr>
		<tr align="center">
			<td colspan="4">
				<input type="button" value="수정하기" onclick="fnUpdate('<%=dto.getBoard_num() %>')" />
				<input type="button" value="삭제하기" onclick="fnDelete('<%=dto.getBoard_num() %>')" />
				<input type="button" value="목록보기" onclick="location.href='boardList.bo'"/>
			</td>
		</tr>
	</table>
</div>
</body>
</html>

▲board_view.jsp

 

package com.board.action;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.board.study.BoardDAO;
import com.commons.action.Action;
import com.commons.action.ActionForward;

public class BoardDeleteAction implements Action {
	@Override
	public ActionForward execute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		int board_num = Integer.parseInt(request.getParameter("board_num"));
		HttpSession session = request.getSession();
		String id = (String) session.getAttribute("id");
		
		BoardDAO dao = new BoardDAO();
		boolean result = dao.isBoardWriter(board_num, id);	//작성자 확인
		
		response.setContentType("text/html; charset=utf-8");
		PrintWriter out = response.getWriter();
		if(id.equals("admin") || result == true) {
			int succ = dao.boardDelete(board_num);
			if(succ > 0) {
				out.println("<script>alert('삭제되었습니다!');");
				out.println("location.href='boardList.bo';</script>");
			} else {
				out.println("<script>alert('삭제 실패!');");
				out.println("location.href='boardList.bo';</script>");
			}
		} else if(!id.equals("admin") && result == false) {
			out.println("<script>alert('삭제 권한이 없습니다!');");
			//out.println("location.href='boardDetailAction.bo?board_num=" + board_num + "';</script>"); 작동은 되지만 조회수가 올라가서 적절치 않다.
			out.println("history.go(-1);</script>");
		}
		return null;
	}
}

▲BoardDeleteAction.java

 

package com.board.action;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.board.study.BoardDAO;
import com.board.study.BoardDTO;
import com.commons.action.Action;
import com.commons.action.ActionForward;

public class BoardModifyView implements Action {
	@Override
	public ActionForward execute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		int board_num = Integer.parseInt(request.getParameter("board_num"));
		HttpSession session = request.getSession();
		String id = (String) session.getAttribute("id");
		
		BoardDAO dao = new BoardDAO();
		boolean result = dao.isBoardWriter(board_num, id);	//작성자 확인
		response.setContentType("text/html; charset=utf-8");
		PrintWriter out = response.getWriter();
		if(result == false) {
			out.println("<script>alert('수정 권한이 없습니다!');");
			out.println("history.go(-1);</script>");
		} else {
			BoardDTO dto = dao.getDetail(board_num);
			request.setAttribute("dto", dto);
			
			ActionForward forward = new ActionForward();
			forward.setPath("board/board_modify.jsp");
			forward.setRedirect(false);
			return forward;
		}
		return null;
	}
}

▲BoardModifyView.java

 

<%@page import="com.board.study.BoardDTO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%
String id = (String) session.getAttribute("id");
BoardDTO dto = (BoardDTO) request.getAttribute("dto");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>board modify JSP</title>
</head>
<body>
<div align="center">
	<h3>[글 내용 수정하기]</h3>
	<form action="boardModifyAction.bo" method="post">
	<input type="hidden" name="board_num" value="<%=dto.getBoard_num()%>"/>
		<table border="1">
			<tr>
				<th>제목</th>
				<td>
					<input type="text" name="board_subject" value="<%=dto.getBoard_subject() %>" required="required"/>
				</td>
			</tr>
			<tr>
				<th>내용</th>
				<td>
					<textarea rows="15" cols="50" name="board_content" required="required"><%=dto.getBoard_content() %></textarea>
					<!-- textarea 태그 사이에서 들여쓰기를 하면 들여쓰기 만큼의 공백이 내용에 나와버린다. -->
				</td>
			</tr>
			<tr>
				<th>파일 첨부</th>
				<td><input type="file" name="board file" value="<%=dto.getBoard_file() %>" /></td>
			</tr>
			<tr>
				<td colspan="2" align="center">
					<input type="submit" value="수정하기" />
					<input type="button" value="뒤로가기" onclick="history.back()"/>
					<!-- back은 바로 이전, go(-1)은 안의 인수만큼 이동 -->
					<input type= "button" value="목록보기" onclick="location.href='boardList.bo'" />
				</td>
			</tr>
		</table>
	</form>
</div>
</body>
</html>

▲board_modify.jsp

 

package com.board.action;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.board.study.BoardDAO;
import com.board.study.BoardDTO;
import com.commons.action.Action;
import com.commons.action.ActionForward;

public class BoardModifyAction implements Action {
	@Override
	public ActionForward execute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		int board_num = Integer.parseInt(request.getParameter("board_num"));
		BoardDTO dto = new BoardDTO();
		dto.setBoard_num(board_num);
		dto.setBoard_subject(request.getParameter("board_subject"));
		dto.setBoard_content(request.getParameter("board_content"));
		
		BoardDAO dao = new BoardDAO();
		int succ = dao.boardUpdate(dto);
		
		response.setContentType("text/html; charset=utf-8");
		PrintWriter out = response.getWriter();
		if(succ > 0) {
			out.println("<script>alert('수정되었습니다!');");
			out.println("location.href='boardDetailAction.bo?board_num=" + board_num + "';</script>");
		} else {
			out.println("<script>alert('수정 실패!');");
			out.println("location.href='boardDetailAction.bo?board_num=" + board_num + "';</script>");
		}
		
		return null;
	}
}

▲BoardModifyAction.java