JSP | 웹스토어 게시판 추가 모델2 방식 적용 - 4 | 게시글 삭제 추가 | 게시글 넘버링 수정 | 게시글 비로그인 예외처리 | 관리자 게시글 삭제 추가

    Front-end/JSP / / 2021. 2. 16. 11:16

    kjh95.tistory.com/298

     

    JSP | 웹스토어 게시판 추가 모델2 방식 적용 - 3 | 글 상세정보 | 게시글 수정하기

    kjh95.tistory.com/293 JSP | 웹스토어 게시판 추가 모델2 방식 적용 - 2 | 글쓰기 기능 추가 kjh95.tistory.com/291 contriller 받음(유용한 패턴참고) model1 model2 command를 기능에 따라 분리한다. 계시판의..

    kjh95.tistory.com

    프런트 컨트롤러 수정

    view.jsp

    boardDeleteAction.do 삭제 경로를 컨트롤러에서 받아준다.

    package kr.gov.mvc.controller;

import java.io.IOException;

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

import kr.gov.mvc.command.BCommand;
import kr.gov.mvc.command.BDeleteCommand;
import kr.gov.mvc.command.BListCommand;
import kr.gov.mvc.command.BUpdateCommand;
import kr.gov.mvc.command.BViewCommand;
import kr.gov.mvc.command.BWriteCommand;
import kr.gov.mvc.command.BWriteFormCommand;

/*@WebServlet("/BoardController")*/
public class BoardController extends HttpServlet {
	private static final long serialVersionUID = 1L;
 
    public BoardController() {
        
    }


	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		System.out.println("doGet");
		actionDo(request, response);		
	}

	
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		System.out.println("doGet");
		actionDo(request, response);
	}
	
	
	private void actionDo(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		System.out.println("actionDo");
		
		String uri = request.getRequestURI();
		System.out.println("URI : " + uri);
		
		String contextPath = request.getContextPath();
		System.out.println("contextPath : " + contextPath);
		
		String command = uri.substring(contextPath.length());
		System.out.println("command : " + command);
		
		response.setContentType("text/html; charse=utf-8");
		request.setCharacterEncoding("UTF-8");
		
		BCommand com = null;
		String viewPage = null;
		
		//command 패턴에 따라서 분기함
		if(command.equals("/boardListAction.do")) {				//DB에 저장되어 있는 모든 게시글 출력
			System.out.println("--------------------------------");
			System.out.println("/boardListAction.do 페이지 호출");
			System.out.println("--------------------------------");
			
			//다시 위임 한것
			com = new BListCommand();
			com.execute(request, response);
			viewPage = "./board/list.jsp";
		}
		else if(command.equals("/boardWriteForm.do")) {			//회원의 로그인 정보 가져오는 부분
			System.out.println("--------------------------------");
			System.out.println("/boardWriteForm.do 페이지 호출");
			System.out.println("--------------------------------");
			
			com = new BWriteFormCommand();
			com.execute(request, response);
			
			viewPage = "./board/writeForm.jsp";
		}
		else if(command.equals("/boardWriteAction.do")) { 		//게시글을 쓰고 DB에 저장하는 부분
			System.out.println("--------------------------------");
			System.out.println("/boardWriteAction.do 페이지 호출");
			System.out.println("--------------------------------");
			
			com = new BWriteCommand();
			com.execute(request, response);
			
			viewPage = "/boardListAction.do";
		}
		else if(command.equals("/boardViewAction.do")) {		//게시판에 있는 게시물 제목 클릭하여 상세내용보는 부분
			System.out.println("--------------------------------");
			System.out.println("/boardViewAction.do 페이지 호출");
			System.out.println("--------------------------------");
			
			com = new BViewCommand();
			com.execute(request, response);
			
			System.out.println("boardViewAction의 execute()실행 완료");
			
			viewPage = "./board/view.jsp";
		}
		else if(command.equals("/boardUpdateAction.do")) {		//게시글 수정
			System.out.println("--------------------------------");
			System.out.println("/boardUpdateAction.do 페이지 호출");
			System.out.println("--------------------------------");
			
			com = new BUpdateCommand();
			com.execute(request, response);
			
			System.out.println("boardUpdateAction의 execute()실행 완료");
			
			viewPage = "/boardListAction.do";
		}
		else if(command.equals("/boardDeleteAction.do")) {	   //게시글 삭제
			System.out.println("--------------------------------");
			System.out.println("/boardDeleteAction.do 페이지 호출");
			System.out.println("--------------------------------");
			
			com = new BDeleteCommand();
			com.execute(request, response);
			
			viewPage = "/boardListAction.do";
		}
		
				
		//위의 분기문에서 설정된 view(.jsp)파일로 페이지 이동
		RequestDispatcher rDispatcher = request.getRequestDispatcher(viewPage);
		rDispatcher.forward(request, response);
	}
}

    커맨드 만들기 - get으로 받아온 파라미터 변수로 저장

    BDeleteCommand.java

     

    package kr.gov.mvc.command;

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

public class BDeleteCommand implements BCommand {

	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) {
		// TODO Auto-generated method stub
		
		int num = Integer.parseInt(request.getParameter("num"));
	}
	
}

     

    다시 프런트 컨트롤러에서 커맨드 객체를 불러오고 view page를 만든다.

     

    view.jsp

    num(게시글 번호)를 커맨드 객체에 변수로 저장함.

     

     

    DAO 수정 - 커맨드 호출 DB연결 실행

    deleteBoard 메서드 만들기 - 선택된 글 삭제하는 메서드

    package kr.gov.mvc.model;

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

import kr.gov.mvc.database.DBConnection;

public class BoardDAO {
	
	private Connection conn = null;		//DB접속시 필요한 객체
	private PreparedStatement pstmt= null;
	private ResultSet rs = null;
	private ArrayList<BoardDTO> dtos = null;
	
	private static BoardDAO instance;
	
	public BoardDAO() {
		// TODO Auto-generated constructor stub
	}
	
	public static BoardDAO getInstance() { 			//싱글톤 패턴으로 BoardDAO객체 하나만 만들어서 리턴.
		if(instance == null) {
			instance = new BoardDAO();
		}
		return instance;
	}
	
	//board 테이블에 레코드 가져오는 메서드
	//page : 게시물 페이지 숫자, limit : 페이지당 게시물 수, items : 제목, 본문, 글쓴이, text : 검색어
	public ArrayList<BoardDTO> getBoardList(int page, int limit, String items, String text) {
		
		int totalRecord = getListCount(items, text); 	//board테이블의 전체 레코드 개수
		int start = (page - 1) * limit;					//선택 page이전까지의 레코드 개수
		int index = start + 1; 							//선택 page 시작 레코드(게시물)
		
		String sql = "";
		dtos = new ArrayList<BoardDTO>();
		
		if(items == null && text == null) {				//파라미터로 넘어오는 검색기능이 두군데 모두 아무값이 없는 경우
			sql = "select * from board order by num desc";
		}
		else { 
			sql = "select * from board where " +items+ " like '%"+text+"%' order by num desc";  //매개변수가 파라미터로 넘어오는 값으로 검색
		} 
		
		try {
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			rs = pstmt.executeQuery();
			
			while(rs.absolute(index)) {		//가령 6페이지를 보고있다가 1페이지를 클릭하게되면 -> 마이너스값이 되면안됨. 
				BoardDTO board = new BoardDTO();
				board.setNum(rs.getInt("num"));
				board.setId(rs.getString("id"));
				board.setName(rs.getString("name"));
				board.setSubject(rs.getString("subject"));
				board.setContent(rs.getString("content"));
				board.setRegistDay(rs.getString("registDay"));
				board.setHit(rs.getInt("hit"));
				board.setIp(rs.getString("ip"));
				
				dtos.add(board);
				
				//인덱스가 가져올 데이터건수 보다 작다면 
				if(index < (start + limit) && index <= totalRecord) {
					index ++;
				}
				else {
					break;
				}
				
			}
			
		}catch (SQLException e){
			System.out.println("getBoardList() 예외발생 : " + e.getMessage());
			e.printStackTrace();
		}finally {
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				System.out.println("getBoardList()의 close() 호출 예외" + e2.getMessage());
				e2.printStackTrace();
			}
		}
		
		return dtos;
	}
	
	//board 테이블에 레코드 개수를 가져오는 메서드
	public int getListCount(String items, String text) {
		
		int count = 0;
		String sql = "";
		
		//파라미터로 넘어오는 검색기능 두군데 모두 아무값도 없는 경우
		if(items == null && text == null) {
			sql = "select count(*) from board";
		}
		else {
			sql = "select count(*) from board where " +items+ "like '%"+text+"%' "; //파라미터로 넘어오는 값으로 검색
		}
		
	
		try {
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				count = rs.getInt(1);
			}
			
			
		} catch (SQLException e) {
			System.out.println("getListCount() 예외발생 : " + e.getMessage());
			e.printStackTrace();
		}finally {
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				System.out.println("getListCount()의 close() 호출 예외" + e2.getMessage());
				e2.printStackTrace();
			}
		}
			
		return count;
	}
	
	//member테이블에서 인증된 id의 사용자명 가져오기
	public String getLoginName(String id) {
		String name = null;
		String sql = "select * from member where id = ?";
		
		try {
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				name = rs.getString("name");
			}
			
		} catch (Exception e) {
			System.out.println("getLoginName() 예외발생 : " + e.getMessage());
			e.printStackTrace();
		} finally {
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				System.out.println("getLoginName()의 close() 호출 예외 : " + e2.getMessage());
				e2.printStackTrace();
			}
		}
		
		return name;
	}
	
	//board 테이블에 새로운 글 저장하는 메서드 
	public void insertBoard(BoardDTO boardDTO) {
		/*
		  	num                     int not null auto_increment,  -- 게시글 순번
			id		                varchar(10) not null,		  -- 회원 아이디
			name 				    varchar(20) not null,		  -- 회원 이름
			subject				    varchar(100) not null,        -- 게시글 제목
			content					text         not null,		  -- 게시글 내용 
			registDay				varchar(30),				  -- 게시글 등록 일자
			hit						int,						  -- 게시글 조회수
			ip						varchar(20),				  -- 게시글 등록 시 IP
		*/
		
		try {
			String sql = "insert into board values(?, ?, ?, ?, ?, ?, ?, ?)";
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, boardDTO.getNum());
			pstmt.setString(2, boardDTO.getId());
			pstmt.setString(3, boardDTO.getName());
			pstmt.setString(4, boardDTO.getSubject());
			pstmt.setString(5, boardDTO.getContent());
			pstmt.setString(6, boardDTO.getRegistDay());
			pstmt.setInt(7, boardDTO.getHit());
			pstmt.setString(8, boardDTO.getIp());
			
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			System.out.println("insertBoard() 예외발생 : " + e.getMessage());
			e.printStackTrace();
		} finally {
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				System.out.println("insertBoard()의 close() 호출 예외 : " + e2.getMessage());
				e2.printStackTrace();
			}
		}	
	}
	
	public BoardDTO getBoardByNum(int boardNum, int pageNum) {		//선택된 게시글의 상세 내용을 가져오는 메서드
		
		BoardDTO boardDTO = null;
		String sql = "select * from board where num = ?";
		
		try {
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, boardNum);
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				boardDTO = new BoardDTO();
				boardDTO.setNum(rs.getInt("num"));
				boardDTO.setId(rs.getString("id"));
				boardDTO.setName(rs.getString("name"));
				boardDTO.setSubject(rs.getString("subject"));
				boardDTO.setContent(rs.getNString("content"));
				boardDTO.setRegistDay(rs.getString("registDay"));
				boardDTO.setHit(rs.getInt("hit"));
				boardDTO.setIp(rs.getString("ip"));
			}
			System.out.println("getBoardByNum 수행완료");
			
		} catch (Exception e) {
			System.out.println("getBoardByNum() 예외발생 : " + e.getMessage());
			e.printStackTrace();
		}finally {
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				System.out.println("getBoardByNum()의 close() 호출 예외 : " + e2.getMessage());
				e2.printStackTrace();
			}
		}
		
		updateHit(boardNum);	//조회수 증가시키는 메서드
		
		return boardDTO;
	}

	public void updateHit(int boardNum) {	//조회수 증가시키는 메서드
		
		String sql = "select hit from board where num = ?";
		int hit = 0;
		
		try {
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, boardNum);
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				hit = rs.getInt("hit") + 1;
			}
			
			sql = "update board set hit = ? where num = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, hit);
			pstmt.setInt(2, boardNum);
			pstmt.executeUpdate();
			
			System.out.println("updateHit 수행완료");
			
		} catch (Exception e) {
			System.out.println("updateHit() 예외발생 : " + e.getMessage());
			e.printStackTrace();
		}finally {
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				System.out.println("updateHit()의 close() 호출 예외 : " + e2.getMessage());
				e2.printStackTrace();
			}
		}
	}
	
	public void updateBoard(BoardDTO boardDTO) {		//게시글을 수정한 내용 DB에 저장하는 메서드
		
		String sql = "update board set subject = ?, content = ?, registDay = ?, ip = ? where num = ?";
		
		try {
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, boardDTO.getSubject());
			pstmt.setString(2, boardDTO.getContent());
			pstmt.setString(3, boardDTO.getRegistDay());
			pstmt.setString(4, boardDTO.getIp());
			pstmt.setInt(5, boardDTO.getNum());
			
			pstmt.executeUpdate();
			
			System.out.println("updateBoard() 수행 완료");
			
		} catch (Exception e) {
			System.out.println("updateBoard() 예외발생 : " + e.getMessage());
			e.printStackTrace();
		}finally {
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				System.out.println("updateBoard()의 close() 호출 예외 : " + e2.getMessage());
				e2.printStackTrace();
			}
		}
	}
	
	public void deleteBoard(int num) {				//선택된 글 삭제
		
		String sql = "delete from board where num = ?";
		
		try {
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			
			pstmt.executeUpdate();
			System.out.println("deleteBoard() 수행 완료");
			
		} catch (Exception e) {
			System.out.println("deleteBoard() 예외발생 : " + e.getMessage());
			e.printStackTrace();
		}finally {
			try {
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				System.out.println("deleteBoard()의 close() 호출 예외 : " + e2.getMessage());
				e2.printStackTrace();
			}
		}
	}
	
}

    커맨드에서 DAO 호출 - 파라미터 num을 받아옴

    BDeleteCommand.java

    package kr.gov.mvc.command;

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

import kr.gov.mvc.model.BoardDAO;

public class BDeleteCommand implements BCommand {

	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) {
		// TODO Auto-generated method stub
		
		int num = Integer.parseInt(request.getParameter("num"));
		
		BoardDAO bDao = BoardDAO.getInstance();
		bDao.deleteBoard(num);
	}
	
}

     

    view.jsp 실행

    삭제 기능 확인, 카운팅 수정해야됨.

    비로그인 시 게시판 예외처리 - 로그인 팝업 

    list.jsp 수정 - 로그인을 해야 화면이 전환되어야 함 loginForm() 스크립트 추가

    <%@page import="kr.gov.mvc.model.BoardDTO"%>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<%
	String sessionId = (String)session.getAttribute("sessionId");
	ArrayList<BoardDTO> boardList = (ArrayList<BoardDTO>)request.getAttribute("boardList");
	int totalRecord = ((Integer)request.getAttribute("totalRecord")).intValue();
	int totalPage = ((Integer)request.getAttribute("totalPage")).intValue();
	int pageNum = ((Integer)request.getAttribute("pageNum")).intValue();
%>

<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Insert title here</title>
  <link rel="stylesheet" href="${pageContext.request.contextPath}/resources/css/bootstrap.min.css"> 
</head>
<body>
	<jsp:include page="../menu.jsp"/>
	
	<div class="jumbotron">
		<div class="container">
			<h1 class="display-3">게시판</h1>
		</div>
	</div>
	
	<div class="container">
		<form action='<c:url value="./boardListAction.do" />' method="post">
			<div>
				<div class="text-right">
					<h2><span class="badge badge-danger">전체 건수 : <%=totalRecord %></span></h2>
				</div>		
			</div>
			
			<div style="padding-top:50px">			<!-- 게시글 표시 -->
				<table class="table table-hover">
					<tr>
						<th>번호</th>
						<th>제목</th>
						<th>작성일</th>
						<th>조회</th>
						<th>글쓴이</th>
					</tr>
					<%
						for(int i=0; i<boardList.size(); i++) {
							BoardDTO notice = boardList.get(i);
					%>
					<tr>
						<td><%=notice.getNum() %></td>		
						<!-- 게시글 제목클릭하면 해당 게시글이 보일수 있도록 a태그 이용 -->
						<td>
							<%
								if(sessionId == null) {
							%>	
								<a href="#" onclick="loginForm()"><%=notice.getSubject() %></a></td>
								
							<%
								}
								else {
							%>
									<a href="./boardViewAction.do?num=<%=notice.getNum() %>&pageNum=<%=pageNum %>">
																		<%=notice.getSubject() %>
									</a></td>	
							<%
								}
							%>
							
						
						<td><%=notice.getRegistDay() %></td>
						<td><%=notice.getHit() %></td>
						<td><%=notice.getName() %></td>
					</tr>			
					<%	
						}
					%>
					
				</table>
			</div>
			
			<div align="center"> 		<!-- 페이지 수 표시 --> 
				<c:set var="pageNum" value="<%=pageNum %>" /> <!-- 페이지수를 jstl로 표시 -->
				<c:forEach var="i" begin="1" end="<%=totalPage %>" >
					<a href=' <c:url value="./boardListAction.do?pageNum=${i }" />'>
						<c:choose>
							<c:when test="${pageNum==i }">
								<font color="4C5317"><b>[${i }]</b></font>
							</c:when>
							<c:otherwise>
								<font color="4C5317">[${i }]</font>
							</c:otherwise>
						</c:choose>
					</a>
				</c:forEach>	
			</div>
			
			<div align="left">		<!-- 검색 -->
				<table>
					<tr>
						<td width="100%" align="left">&nbsp;&nbsp;
							<select name="items" class="txt">
								<option value="subject">제목</option>
								<option value="content">본문</option>	
								<option value="name">글쓴이</option>		
							</select>
							<input name="text" type="text" />
							<input type="submit" id="btnAdd" class="btn btn-primary btn-sm" value="검색">
						</td>
						<td width="100%" align="right">
							<!-- 로그인 된 회원들만 글쓰기 가능하도록 하기위해서 checkForm()함수추가 -->
							<h3><a href="#" onclick="checkForm()" class="badge badge-primary">글쓰기</a></h3>
						
						</td>
					</tr>
				</table>
			</div>
			
		</form>
		<hr/>
	</div>
	
	<jsp:include page="../footer.jsp" />
	<script type="text/javascript">
		function checkForm() {
			if(${sessionId == null}) {				/* 로그인여부 판단 */
				alert("로그인해야 작성할수 있습니다");
				return false;
			}
			
			//로그인되었다면
			location.href = "./boardWriteForm.do?id=<%=sessionId %>"	
		} 
		
		function loginForm() {
			if(${sessionId == null}) {				/* 로그인여부 판단 */
				alert("로그인해야 게시글을 볼수 있습니다");
				return false;
			}
		}
	</script>
</body>
</html>

    게시글 번호 수정 DAO 수정(게시글 넘버링 수정)

    insertBoard() 메서드 수정 - auto_increment부분을 다시 1로 재설정 해줌

    package kr.gov.mvc.model;

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

import kr.gov.mvc.database.DBConnection;

public class BoardDAO {
	
	private Connection conn = null;		//DB접속시 필요한 객체
	private PreparedStatement pstmt= null;
	private ResultSet rs = null;
	private ArrayList<BoardDTO> dtos = null;
	
	private static BoardDAO instance;
	
	public BoardDAO() {
		// TODO Auto-generated constructor stub
	}
	
	public static BoardDAO getInstance() { 			//싱글톤 패턴으로 BoardDAO객체 하나만 만들어서 리턴.
		if(instance == null) {
			instance = new BoardDAO();
		}
		return instance;
	}
	
	//board 테이블에 레코드 가져오는 메서드
	//page : 게시물 페이지 숫자, limit : 페이지당 게시물 수, items : 제목, 본문, 글쓴이, text : 검색어
	public ArrayList<BoardDTO> getBoardList(int page, int limit, String items, String text) {
		
		int totalRecord = getListCount(items, text); 	//board테이블의 전체 레코드 개수
		int start = (page - 1) * limit;					//선택 page이전까지의 레코드 개수
		int index = start + 1; 							//선택 page 시작 레코드(게시물)
		
		String sql = "";
		dtos = new ArrayList<BoardDTO>();
		
		if(items == null && text == null) {				//파라미터로 넘어오는 검색기능이 두군데 모두 아무값이 없는 경우
			sql = "select * from board order by num desc";
		}
		else { 
			sql = "select * from board where " +items+ " like '%"+text+"%' order by num desc";  //매개변수가 파라미터로 넘어오는 값으로 검색
		} 
		
		try {
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			rs = pstmt.executeQuery();
			
			while(rs.absolute(index)) {		//가령 6페이지를 보고있다가 1페이지를 클릭하게되면 -> 마이너스값이 되면안됨. 
				BoardDTO board = new BoardDTO();
				board.setNum(rs.getInt("num"));
				board.setId(rs.getString("id"));
				board.setName(rs.getString("name"));
				board.setSubject(rs.getString("subject"));
				board.setContent(rs.getString("content"));
				board.setRegistDay(rs.getString("registDay"));
				board.setHit(rs.getInt("hit"));
				board.setIp(rs.getString("ip"));
				
				dtos.add(board);
				
				//인덱스가 가져올 데이터건수 보다 작다면 
				if(index < (start + limit) && index <= totalRecord) {
					index ++;
				}
				else {
					break;
				}
				
			}
			
		}catch (SQLException e){
			System.out.println("getBoardList() 예외발생 : " + e.getMessage());
			e.printStackTrace();
		}finally {
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				System.out.println("getBoardList()의 close() 호출 예외" + e2.getMessage());
				e2.printStackTrace();
			}
		}
		
		return dtos;
	}
	
	//board 테이블에 레코드 개수를 가져오는 메서드
	public int getListCount(String items, String text) {
		
		int count = 0;
		String sql = "";
		
		//파라미터로 넘어오는 검색기능 두군데 모두 아무값도 없는 경우
		if(items == null && text == null) {
			sql = "select count(*) from board";
		}
		else {
			sql = "select count(*) from board where " +items+ "like '%"+text+"%' "; //파라미터로 넘어오는 값으로 검색
		}
		
	
		try {
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				count = rs.getInt(1);
			}
			
			
		} catch (SQLException e) {
			System.out.println("getListCount() 예외발생 : " + e.getMessage());
			e.printStackTrace();
		}finally {
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				System.out.println("getListCount()의 close() 호출 예외" + e2.getMessage());
				e2.printStackTrace();
			}
		}
			
		return count;
	}
	
	//member테이블에서 인증된 id의 사용자명 가져오기
	public String getLoginName(String id) {
		String name = null;
		String sql = "select * from member where id = ?";
		
		try {
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				name = rs.getString("name");
			}
			
		} catch (Exception e) {
			System.out.println("getLoginName() 예외발생 : " + e.getMessage());
			e.printStackTrace();
		} finally {
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				System.out.println("getLoginName()의 close() 호출 예외 : " + e2.getMessage());
				e2.printStackTrace();
			}
		}
		
		return name;
	}
	
	//board 테이블에 새로운 글 저장하는 메서드 
	public void insertBoard(BoardDTO boardDTO) {
		/*
		  	num                     int not null auto_increment,  -- 게시글 순번
			id		                varchar(10) not null,		  -- 회원 아이디
			name 				    varchar(20) not null,		  -- 회원 이름
			subject				    varchar(100) not null,        -- 게시글 제목
			content					text         not null,		  -- 게시글 내용 
			registDay				varchar(30),				  -- 게시글 등록 일자
			hit						int,						  -- 게시글 조회수
			ip						varchar(20),				  -- 게시글 등록 시 IP
		*/
		
		//auto_increment부분을 다시 1로 재설정 해줌
		String sql = "alter table board auto_increment = 1";
		try {
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
			
			sql = "insert into board values(?, ?, ?, ?, ?, ?, ?, ?)";
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, boardDTO.getNum());
			pstmt.setString(2, boardDTO.getId());
			pstmt.setString(3, boardDTO.getName());
			pstmt.setString(4, boardDTO.getSubject());
			pstmt.setString(5, boardDTO.getContent());
			pstmt.setString(6, boardDTO.getRegistDay());
			pstmt.setInt(7, boardDTO.getHit());
			pstmt.setString(8, boardDTO.getIp());
			
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			System.out.println("insertBoard() 예외발생 : " + e.getMessage());
			e.printStackTrace();
		} finally {
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				System.out.println("insertBoard()의 close() 호출 예외 : " + e2.getMessage());
				e2.printStackTrace();
			}
		}	
	}
	
	public BoardDTO getBoardByNum(int boardNum, int pageNum) {		//선택된 게시글의 상세 내용을 가져오는 메서드
		
		BoardDTO boardDTO = null;
		String sql = "select * from board where num = ?";
		
		try {
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, boardNum);
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				boardDTO = new BoardDTO();
				boardDTO.setNum(rs.getInt("num"));
				boardDTO.setId(rs.getString("id"));
				boardDTO.setName(rs.getString("name"));
				boardDTO.setSubject(rs.getString("subject"));
				boardDTO.setContent(rs.getNString("content"));
				boardDTO.setRegistDay(rs.getString("registDay"));
				boardDTO.setHit(rs.getInt("hit"));
				boardDTO.setIp(rs.getString("ip"));
			}
			System.out.println("getBoardByNum 수행완료");
			
		} catch (Exception e) {
			System.out.println("getBoardByNum() 예외발생 : " + e.getMessage());
			e.printStackTrace();
		}finally {
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				System.out.println("getBoardByNum()의 close() 호출 예외 : " + e2.getMessage());
				e2.printStackTrace();
			}
		}
		
		updateHit(boardNum);	//조회수 증가시키는 메서드
		
		return boardDTO;
	}

	public void updateHit(int boardNum) {	//조회수 증가시키는 메서드
		
		String sql = "select hit from board where num = ?";
		int hit = 0;
		
		try {
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, boardNum);
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				hit = rs.getInt("hit") + 1;
			}
			
			sql = "update board set hit = ? where num = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, hit);
			pstmt.setInt(2, boardNum);
			pstmt.executeUpdate();
			
			System.out.println("updateHit 수행완료");
			
		} catch (Exception e) {
			System.out.println("updateHit() 예외발생 : " + e.getMessage());
			e.printStackTrace();
		}finally {
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				System.out.println("updateHit()의 close() 호출 예외 : " + e2.getMessage());
				e2.printStackTrace();
			}
		}
	}
	
	public void updateBoard(BoardDTO boardDTO) {		//게시글을 수정한 내용 DB에 저장하는 메서드
		
		String sql = "update board set subject = ?, content = ?, registDay = ?, ip = ? where num = ?";
		
		try {
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, boardDTO.getSubject());
			pstmt.setString(2, boardDTO.getContent());
			pstmt.setString(3, boardDTO.getRegistDay());
			pstmt.setString(4, boardDTO.getIp());
			pstmt.setInt(5, boardDTO.getNum());
			
			pstmt.executeUpdate();
			
			System.out.println("updateBoard() 수행 완료");
			
		} catch (Exception e) {
			System.out.println("updateBoard() 예외발생 : " + e.getMessage());
			e.printStackTrace();
		}finally {
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				System.out.println("updateBoard()의 close() 호출 예외 : " + e2.getMessage());
				e2.printStackTrace();
			}
		}
	}
	
	public void deleteBoard(int num) {				//선택된 글 삭제
		
		String sql = "delete from board where num = ?";
		
		try {
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			
			pstmt.executeUpdate();
			System.out.println("deleteBoard() 수행 완료");
			
		} catch (Exception e) {
			System.out.println("deleteBoard() 예외발생 : " + e.getMessage());
			e.printStackTrace();
		}finally {
			try {
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				System.out.println("deleteBoard()의 close() 호출 예외 : " + e2.getMessage());
				e2.printStackTrace();
			}
		}
	}
	
}

    관리자가 게시물 삭제가능하게 만들기

    view.jsp admin 추가

    view.jsp

    <%@page import="kr.gov.mvc.model.BoardDTO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<%
	BoardDTO notice = (BoardDTO)request.getAttribute("boardDTO");
	int num = (Integer)request.getAttribute("num");
	int nowPage = (Integer)request.getAttribute("pageNum");
	
/* 	System.out.println(notice.getName());
	System.out.println(num);
	System.out.println(nowPage); */
	
	String sessionId = (String)session.getAttribute("sessionId");
	String userId = notice.getId();
	
/* 	System.out.println(sessionId);
	System.out.println(userId); */
%>
<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>글 상세보기 - WebStore</title>
   <link rel="stylesheet" href="${pageContext.request.contextPath}/resources/css/bootstrap.min.css"> 
</head>
<body>
	<jsp:include page="../menu.jsp"/>
	
	<div class="jumbotron">
		<div class="container">
			<h1 class="display-3">게시판</h1>
		</div>
	</div>
	
	<div class="container">
		<form action="boardUpdateAction.do?num=<%=notice.getNum() %>&pageNum=<%=nowPage %>" name="newUpdate" class="form-horizontal" method="post">
			
			<div class="form-group row">
				<label class="col-sm-2 control-label">이름</label>
				<div class="col-sm-3">
					<input name="name" class="form-control" value="<%=notice.getName() %>" readonly> <!-- DB에 저장되어 있는 이름값 출력  -->
				</div>
			</div>
			
			<div class="form-group row">
				<label class="col-sm-2 control-label">제목</label>
				<div class="col-sm-3">
				
				<%
					if(sessionId.equals(userId)) {		/* 게시글 작성자가 맞다면 => 수정이 가능하도록 함 */
				%>
					<input name="subject" class="form-control" value="<%=notice.getSubject() %>"> <!-- DB에 저장되어 있는 제목 출력  -->
				<%
					}
					else {
				%>
					<input name="subject" class="form-control" value="<%=notice.getSubject() %>" readonly> <!-- DB에 저장되어 있는 제목 출력  -->
				<%
					}
				%>
				
					
				</div>
			</div>
			
			<div class="form-group row">
				<label class="col-sm-2 control-label">내용</label>
				<div class="col-sm-8" style="word-break:break-all">
				<%
					if(sessionId.equals(userId)) {
				%>
					<textarea rows="5" cols="50" name="content" class="form-control"><%=notice.getContent() %></textarea>
				<%				
					}
					else {
				%>
					<textarea rows="5" cols="50" name="content" class="form-control" readonly><%=notice.getContent() %></textarea>
				<%
					}
				%>
				
					
				</div>
			</div>
			
			<div class="form-group row">			<!-- 게시글 작성자가 맞다면, 수정과 삭제 가능 -->
				<div class="col-sm-offset-2 col-sm-10">
					<c:set var="userId" value="<%= notice.getId() %>" />
					<c:if test="${sessionId == userId || sessionId == 'admin'}">
						<p><a href="./boardDeleteAction.do?num=<%=notice.getNum() %>&pageNum=<%=nowPage %>"
									class="btn btn-danger">삭제</a>
						<input type="submit" class="btn btn-success" value="수정">			
					</c:if>
					<a href="./boardListAction.do?pageNum=<%=nowPage %>" class="btn btn-primary">목록</a>
				</div>
			</div>
			
		</form>
		<hr/>
	</div>
	
	<jsp:include page="../footer.jsp" />
</body>
</html>

    drive.google.com/file/d/1godLEVpcPkAbm2kk34sJOHVKgFtoDHek/view?usp=sharing

     

    Google Drive - 모든 파일을 한 곳에서

    하나의 계정으로 모든 Google 서비스를 Google Drive로 이동하려면 로그인하세요.

    accounts.google.com

    웹스토어 webStore.zip

    • 네이버 블러그 공유하기
    • 네이버 밴드에 공유하기
    • 페이스북 공유하기
    • 카카오스토리 공유하기
    loading

    티스토리툴바