[실습문제] Day81 - MVC Model2 문제 및 해답

Web/JSP

2020. 10. 22. 17:06

Q. MVC Model 2로 회원형 게시판 만들어보기

-BCommand.java // 커맨드 참고해서 만들어보기

package com.tistory.coderbear.command;

import java.io.IOException;

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

public interface BCommand {
	void excute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException;
}

 

A.

- MVCBoard.sql

create table mvcboard(
	no int auto_increment primary key,
	name varchar(20) not null,
	title varchar(100) not null,
	contents text not null,
	hit int not null default 0,
	wtime timestamp not null default CURRENT_TIMESTAMP,
	groupnum int not null default 0,
	stepnum int not null default 0,
	indentnum int not null default 0
);

[ view ]

- list.jsp

<%@page import="com.tistory.coderbear.dto.BoardDTO"%>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html lang="ko-kr">
<head>
<meta charset="UTF-8">
<meta name="description" content="HTML Study">
<meta name="keywords" content="HTML,CSS,XML,JavaScript">
<meta name="author" content="coderbear">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>목록</title>
</head>
<body>

<%
	ArrayList<BoardDTO> list = (ArrayList<BoardDTO>)request.getAttribute("list");
%>
<table border="1">
	<tr>
		<td>번호</td>
        <td>이름</td>
        <td>제목</td>
        <td>날짜</td>
        <td>조회수</td>
	</tr>
<%
	for(int i=0;list != null && i<list.size();i++)
	{
		BoardDTO dto = list.get(i);
%>
	<tr>
		<td><%=dto.getNum() %></td>
		<td><%=dto.getName() %></td>
		<td>
			<%
				int indentNum = dto.getIndentNum();
				for(int j=0;j<indentNum;j++)
					out.println("-");
			%>
			<a href="view.do?num=<%=dto.getNum()%>"><%=dto.getTitle() %></a>
		</td>
		<td><%=dto.getWtime() %></td>
		<td><%=dto.getHit() %></td>
	</tr>	
<%
	}
%>
	<tr>
		<td colspan="5"><a href="write.do">글작성</a></td>
	</tr>
</table>
</body>
</html>

- write.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<!DOCTYPE html>
<html lang="ko-kr">
<head>
<meta charset="UTF-8">
<meta name="description" content="HTML Study">
<meta name="keywords" content="HTML,CSS,XML,JavaScript">
<meta name="author" content="coderbear">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Insert title here</title>
</head>
<body>
	<form method="post" action="writeOK.do">
		<table border="1">
			<tr>
				<td>이름</td><td><input type="text" name="name" /></td>
			</tr>
			<tr>
				<td>제목</td><td><input type="text" name="title" /></td>
			</tr>
			<tr>
				<td>내용</td><td><textarea name="contents" cols="100" rows="15"></textarea></td>
			</tr>
			<tr>
				<td colspan="2"><input type="submit" value="입력"> <a href="list.do">목록</a></td>
			</tr>
		</table>
	</form>
</body>
</html>

- view.jsp // 상세보기

<%@page import="com.tistory.coderbear.dto.BoardDTO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<!DOCTYPE html>
<html lang="ko-kr">
<head>
<meta charset="UTF-8">
<meta name="description" content="HTML Study">
<meta name="keywords" content="HTML,CSS,XML,JavaScript">
<meta name="author" content="coderbear">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Insert title here</title>
</head>
<body>
<%
	BoardDTO dto = (BoardDTO)request.getAttribute("dto");
%>
	<form method="post" action="modifyOK.do">
		<table border="1">
			<tr>
				<td>번호</td><td><%=dto.getNum() %></td>
			</tr>
			<tr>
				<td>조회수</td><td><%=dto.getHit() %></td>
			</tr>
			<tr>
				<td>이름</td><td><input type="text" name="name" value="<%=dto.getName() %>"/></td>
			</tr>
			<tr>
				<td>제목</td><td><input type="text" name="title" value="<%=dto.getTitle() %>"/></td>
			</tr>
			<tr>
				<td>내용</td><td><textarea name="contents" cols="100" rows="15"><%=dto.getContents() %></textarea></td>
			</tr>
			<tr>
				<td colspan="2">
					<input type="hidden" name="num" value="<%=dto.getNum() %>">
					<input type="submit" value="수정"> 
					<a href="list.do">목록</a>
					<a href="delete.do?num=<%=dto.getNum() %>">삭제</a>
					<a href="reply.do?num=<%=dto.getNum() %>">답변</a>
				</td>
			</tr>
		</table>		
	</form>
</body>
</html>

- reply.jsp

<%@page import="com.tistory.coderbear.dto.BoardDTO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<!DOCTYPE html>
<html lang="ko-kr">
<head>
<meta charset="UTF-8">
<meta name="description" content="HTML Study">
<meta name="keywords" content="HTML,CSS,XML,JavaScript">
<meta name="author" content="coderbear">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Insert title here</title>
</head>
<body>
<%
	BoardDTO dto = (BoardDTO)request.getAttribute("dto");
%>
	<form method="post" action="replyOK.do">
		<table border="1">
			<tr>
				<td>번호</td><td><%=dto.getNum()%></td>
			</tr>
			<tr>
				<td>조회수</td><td><%=dto.getHit() %></td>
			</tr>
			<tr>
				<td>이름</td><td><input type="text" name="name" autofocus/></td>
			</tr>
			<tr>
				<td>제목</td><td><input type="text" name="title" value="re: <%=dto.getTitle()%>"/></td>
			</tr>
			<tr>
				<td>내용</td><td><textarea name="contents" cols="100" rows="15">&#10&#13<%="re:" + dto.getContents() %></textarea></td> // &#10 - 줄 생성 &#13 - 맨 마지막줄 처음으로 커서 이동
			</tr>
			<tr>
				<td colspan="2">
					<input type="submit" value="답변"> 
					<a href="list.do">목록</a>
				</td>
			</tr>
		</table>
		<input type="hidden" name="num" value="<%=dto.getNum()%>" />
		<input type="hidden" name="groupNum" value="<%=dto.getGroupNum() %>" />
		<input type="hidden" name="stepNum" value="<%=dto.getStepNum() %>" />
		<input type="hidden" name="indentNum" value="<%=dto.getIndentNum() %>" />
	</form>
	
	<!-- 
	&#10;     Line feed 
	&#13;     Carriage Return
	
	Line Feed (LF)

	아스키 코드: 16진수로 0A
	기능: 커서를 현재 행의 다음 행으로, 즉 아래로 내리기

	Carriage Return (CR)

	아스키 코드: 16진수로 0D
	기능: 커서를 현재 행의 맨 좌측으로 옮기기
-->
</body>
</html>

[ Controller ]

- FrontController.java // 요청받은 로직을 직접 처리하는 것이 아니라 커맨드에 전달해 수행하게 하는 역할

package com.tistory.coderbear.frontcontroller;

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.tistory.coderbear.command.Command;
import com.tistory.coderbear.command.DeleteCommand;
import com.tistory.coderbear.command.ListCommand;
import com.tistory.coderbear.command.ModifyOKCommand;
import com.tistory.coderbear.command.ReplyCommand;
import com.tistory.coderbear.command.ReplyOKCommand;
import com.tistory.coderbear.command.ViewCommand;
import com.tistory.coderbear.command.WriteOKCommand;

@WebServlet("*.do") // 확장자가 do인 모든 것을 이 페이지로 넘기겠다는 의미
public class FrontController extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public FrontController() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doAction(request, response); // get일 때 doAction()으로 보낼 수 있도록 만든 것
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doAction(request, response); // post일 때 doAction()으로 보낼 수 있도록 만든 것
	}
	
	public void doAction(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("utf-8"); // utf-8로 설정
		String commandName = request.getServletPath(); // 클라이언트 요청 주소 중 서블릿 주소만 가지고 오는 것
		String viewPage = null;
		Command command = null;
        int flag = 0;
		
		if(commandName.equals("/list.do")) { // 컨트롤러에 오는 각각의 요청들을 목적에 따라 나누어 처리하기 위함
			command = new ListCommand(); // execute 시킬 command 객체를 생성  
			command.excute(request, response); // request, response를 커맨드에 보내는 것
			viewPage = "list.jsp"; // 실행결과를 보여줄 페이지 지정
            flag = 1;
		} else if(commandName.equals("/write.do")) {
			viewPage = "write.jsp";
		} else if(commandName.equals("/writeOK.do")) {
			command = new WriteOKCommand();
			command.excute(request, response);
			viewPage = "list.do";
		} else if(commandName.equals("/view.do")) {
			command = new ViewCommand();
			command.excute(request, response);
			viewPage = "view.jsp";
            flag = 1;
		} else if(commandName.equals("/modifyOK.do")) {
			command = new ModifyOKCommand();
			command.excute(request, response);
			viewPage = "list.do";
		} else if(commandName.equals("/reply.do")) {
			command = new ReplyCommand();
			command.excute(request, response);
			viewPage = "reply.jsp";
            flag = 1;
		} else if(commandName.equals("/replyOK.do")) {
			command = new ReplyOKCommand();
			command.excute(request, response);
			viewPage = "list.do";
		} else if(commandName.equals("/delete.do")) {
			command = new DeleteCommand();
			command.excute(request, response);
			viewPage = "list.do";
		}

		if(flag==0) { 
			response.sendRedirect(viewPage);
		} else if(flag==1) {
			RequestDispatcher dispatcher = request.getRequestDispatcher(viewPage); // viewPage로 넘겨줌
			dispatcher.forward(request, response); // 넘어간 viewPage에서 request와 response를 사용할 수 있게하는 메소드			
		}		
	}
}

[ Model ]

- Command.java

package com.tistory.coderbear.command;

import java.io.IOException;

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

public interface Command {
	void excute(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException;
}

- ListCommand.java

package com.tistory.coderbear.command;

import java.io.IOException;
import java.util.ArrayList;

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

import com.tistory.coderbear.dao.BoardDAO;
import com.tistory.coderbear.dto.BoardDTO;

public class ListCommand implements Command {

	@Override
	public void excute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		BoardDAO dao = BoardDAO.getBoardDAO();
		ArrayList<BoardDTO> list = dao.listDAO();
		request.setAttribute("list", list);
	}
}

- writeOKCommand.java

package com.tistory.coderbear.command;

import java.io.IOException;

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

import com.tistory.coderbear.dao.BoardDAO;
import com.tistory.coderbear.dto.BoardDTO;

public class WriteOKCommand implements Command {

	@Override
	public void excute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		BoardDAO dao = BoardDAO.getBoardDAO();
		BoardDTO dto = new BoardDTO();
		dto.setName(request.getParameter("name"));
		dto.setTitle(request.getParameter("title"));
		dto.setContents(request.getParameter("contents"));
		dao.writeOKDAO(dto);		
	}
}

- ViewCommand.java

package com.tistory.coderbear.command;

import java.io.IOException;

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

import com.tistory.coderbear.dao.BoardDAO;
import com.tistory.coderbear.dto.BoardDTO;

public class ViewCommand implements Command {

	@Override
	public void excute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		BoardDAO dao = BoardDAO.getBoardDAO();
		BoardDTO dto = dao.viewDAO(Integer.parseInt(request.getParameter("num")));
		request.setAttribute("dto", dto);
	}
}

- ModifyOKCommand.java

package com.tistory.coderbear.command;

import java.io.IOException;

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

import com.tistory.coderbear.dao.BoardDAO;
import com.tistory.coderbear.dto.BoardDTO;

public class ModifyOKCommand implements Command {

	@Override
	public void excute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		BoardDAO dao = BoardDAO.getBoardDAO();
		BoardDTO dto = new BoardDTO();
		dto.setNum(Integer.parseInt(request.getParameter("num")));
		dto.setName(request.getParameter("name"));
		dto.setTitle(request.getParameter("title"));
		dto.setContents(request.getParameter("contents"));
		dao.modifyOK(dto);
	}
}

- ReplyCommand.java

package com.tistory.coderbear.command;

import java.io.IOException;

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

import com.tistory.coderbear.BoardDAO;
import com.tistory.coderbear.BoardDTO;

public class ReplyCommand implements Command {

	@Override
	public void excute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		BoardDAO dao = BoardDAO.getBoardDAO();
		BoardDTO dto = dao.replyDAO(Integer.parseInt(request.getParameter("num")));
		request.setAttribute("dto", dto);
	}
}

- ReplyOKCommand.java

package com.tistory.coderbear.command;

import java.io.IOException;

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

import com.tistory.coderbear.dao.BoardDAO;
import com.tistory.coderbear.dto.BoardDTO;

public class ReplyOKCommand implements Command {

	@Override
	public void excute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		BoardDAO dao = BoardDAO.getBoardDAO();
		BoardDTO dto = new BoardDTO();
		dto.setName(request.getParameter("name"));
		dto.setTitle(request.getParameter("title"));
		dto.setContents(request.getParameter("contents"));
		dto.setNum(Integer.parseInt(request.getParameter("num")));
		dto.setGroupNum(Integer.parseInt(request.getParameter("groupNum")));
		dto.setStepNum(Integer.parseInt(request.getParameter("stepNum")));
		dto.setIndentNum(Integer.parseInt(request.getParameter("indentNum")));
		dao.replyOKDAO(dto);
	}
}

- DeleteCommand.java

package com.tistory.coderbear.command;

import java.io.IOException;

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

import com.tistory.coderbear.dao.BoardDAO;

public class DeleteCommand implements Command {

	@Override
	public void excute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		BoardDAO dao = BoardDAO.getBoardDAO();
		dao.deleteDAO(Integer.parseInt(request.getParameter("num")));
	}
}

- BoardDAO.java

package com.tistory.coderbear.dao;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.tistory.coderbear.dto.BoardDTO;

public class BoardDAO {
	private static BoardDAO boardDAO = new BoardDAO(); // 싱글톤 패턴으로 자기 자신을 boardDAO를 만들어주고 필요한 곳에서 사용할 수 있게 함
	private String CONNECTION_POOL_RESOURCE_NAME = "jdbc/testdb";
	private final String TABLE_NAME = "mvcboard";
	private DataSource dataSource; 
	private final String GET_BOARD_DTO_SQL = "SELECT * FROM " + TABLE_NAME + " WHERE no = ?";
	private final String SELECT_ALL_BOARD_SQL = "SELECT * FROM " + TABLE_NAME + " order by GROUPNUM DESC, STEPNUM ASC";
//	private final String INSERT_BOARD_SQL = 
//			"INSERT INTO " + TABLE_NAME + " (NAME, TITLE, CONTENTS, GROUPNUM) values(?, ?, ?, ?)";
	private final String INSERT_BOARD_SQL = 
			"INSERT INTO " + TABLE_NAME + " (NAME, TITLE, CONTENTS) values(?, ?, ?)";	
//	private final String GET_CURRENT_NUM_SQL = 
//			"SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='testdb' " + 
//			" AND TABLE_NAME='" + TABLE_NAME + "'";
//	private final String GET_CURRENT_NUM_SQL = "SELECT MAX(no) from " + TABLE_NAME; // 글 작성 직후 저장 전에 해당 글의 no를 알게 하기 위해  
	private final String GET_CURRENT_NUM_SQL = "SELECT MAX(NO) FROM " + TABLE_NAME + " WHERE NAME = ?";
	private final String INCREASE_HIT_SQL = 
			"UPDATE " + TABLE_NAME + " SET HIT=HIT+1 WHERE no=?";
	private final String UPDATE_BOARD_SQL = 
			"UPDATE " + TABLE_NAME + " SET NAME=?, TITLE=?, CONTENTS=?, WTIME=NOW() WHERE no=?";
	private final String UPDATE_STEP_NUM_SQL = 
			"UPDATE " + TABLE_NAME + " SET STEPNUM=STEPNUM+1 WHERE GROUPNUM=? AND STEPNUM>=?"; // 답글 다는 시점
	private final String INSERT_REPLY_SQL = 
			"INSERT INTO " + TABLE_NAME + " (NAME, TITLE, CONTENTS, GROUPNUM, STEPNUM, INDENTNUM) values(?, ?, ?, ?, ?, ?)";
	private final String DELETE_SQL =
			"DELETE FROM " + TABLE_NAME + " WHERE no=?";
	private final String UPDATE_GROUP_NUM = "UPDATE " + TABLE_NAME + " SET GROUPNUM = ? WHERE NO = ?";
	
	
	private BoardDAO() {
		try {
			Context context = new InitialContext(); // tomcat과 연결 역할
			dataSource = (DataSource)context.lookup("java:comp/env/" + CONNECTION_POOL_RESOURCE_NAME);
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}
	
	public static BoardDAO getBoardDAO() {
		return boardDAO;
	}
	
	public Connection getConnection() {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public BoardDTO getBoardDTO(int no) {
		Connection conn = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		BoardDTO dto = new BoardDTO();
		try {
			ps = conn.prepareStatement(GET_BOARD_DTO_SQL);
			ps.setInt(1, no);
			rs = ps.executeQuery();
			if(rs.next())
			{
				dto.setNum(rs.getInt("no"));
				dto.setName(rs.getString("name"));
				dto.setTitle(rs.getString("title"));
				dto.setContents(rs.getString("contents"));
				dto.setHit(rs.getInt("hit"));
				dto.setWtime(rs.getString("wtime"));
				dto.setGroupNum(rs.getInt("groupNum"));
				dto.setStepNum(rs.getInt("stepNum"));
				dto.setIndentNum(rs.getInt("indentNum"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, ps, conn);
		}		
		return dto;
	}
	
	public void increaseHit(int no) {
		Connection conn = getConnection();
		PreparedStatement ps = null;
		int result = 0;
		try {
			ps = conn.prepareStatement(INCREASE_HIT_SQL);
			ps.setInt(1, no);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(ps, conn);
		}
	}
	
//	public int getCurrentNum() {
//		Connection conn = getConnection();
//		PreparedStatement ps = null;
//		ResultSet rs = null;
//		int curNum = 0;
//		try {
//			ps = conn.prepareStatement(GET_CURRENT_NUM_SQL);
//			rs = ps.executeQuery();
//			if(rs.next()) {
//				curNum = rs.getInt(1);
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		return curNum + 1;
//	}

	public int getCurrentNum(BoardDTO dto) {				
		Connection conn = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int curNum = 0;
		try {
			ps = conn.prepareStatement(GET_CURRENT_NUM_SQL);
			ps.setString(1, dto.getName());
			rs = ps.executeQuery();
			if(rs.next()) {
				curNum = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return curNum;
	}
	
	public void updateGroupNum(int curNum) {			
		Connection conn = getConnection();
		PreparedStatement ps = null;
		int result = 0;
		try {
			ps = conn.prepareStatement(UPDATE_GROUP_NUM);
			ps.setInt(1, curNum);
			ps.setInt(2, curNum);
			result = ps.executeUpdate();
		}catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(ps, conn);
		}
	}
	
	public void writeOKDAO(BoardDTO dto) {			
		Connection conn = getConnection();
		PreparedStatement ps = null;
		int curNum = 0;
		int result = 0;
		try {
			ps = conn.prepareStatement(INSERT_BOARD_SQL);
			ps.setString(1, dto.getName());
			ps.setString(2, dto.getTitle());
			ps.setString(3, dto.getContents());
			result = ps.executeUpdate(); // 기본값이 null인 상태
			curNum = getCurrentNum(dto); // 현재의 no를 넣어주기 위해(mysql이기 때문에 해주는 것)
			updateGroupNum(curNum);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(ps, conn);
		}
	}
	
	public void modifyOK(BoardDTO dto) {
		Connection conn = getConnection();
		PreparedStatement ps = null;
		int result = 0;
		try {
			ps = conn.prepareStatement(UPDATE_BOARD_SQL);
			ps.setString(1, dto.getName());
			ps.setString(2, dto.getTitle());
			ps.setString(3, dto.getContents());
			ps.setInt(4, dto.getNum());
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(ps, conn);
		}
	}
	
	public void updateStepNum(BoardDTO dto) {
		Connection conn = null;
		PreparedStatement ps = null;
		int result = 0;
		conn = getConnection();
		try {
			ps = conn.prepareStatement(UPDATE_STEP_NUM_SQL);
			ps.setInt(1, dto.getGroupNum());
			ps.setInt(2, dto.getStepNum()+1);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(ps, conn);
		}
	}
	
	public BoardDTO replyDAO(int no) {
		BoardDTO dto = getBoardDTO(no);
		return dto;
	}
	
	public void replyOKDAO(BoardDTO dto) {
		updateStepNum(dto);
		Connection conn = null;
		PreparedStatement ps = null;
		int result = 0;
		conn = getConnection();
		try {
			ps = conn.prepareStatement(INSERT_REPLY_SQL);
			ps.setString(1, dto.getName());
			ps.setString(2, dto.getTitle());
			ps.setString(3, dto.getContents());
			ps.setInt(4, dto.getGroupNum());
			ps.setInt(5, dto.getStepNum()+1);
			ps.setInt(6, dto.getIndentNum()+1);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(ps, conn);
		}		
	}
	
	public void deleteDAO(int no) {
		Connection conn = null;
		PreparedStatement ps = null;
		int result = 0;
		conn = getConnection();
		try {
			ps = conn.prepareStatement(DELETE_SQL);
			ps.setInt(1, no);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(ps, conn);
		}		
	}
	
	public void close(ResultSet rs, PreparedStatement ps, Connection conn){
		
			try {
				if(rs != null) rs.close();
				if(ps != null) ps.close();
				if(conn != null) conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}
	
	public void close(PreparedStatement ps, Connection conn){
		
		try {
			if(ps != null) ps.close();
			if(conn != null) conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public ArrayList<BoardDTO> listDAO(){
		ArrayList<BoardDTO> list = new ArrayList<BoardDTO>();
		Connection conn = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(SELECT_ALL_BOARD_SQL);
			rs = ps.executeQuery();
			while(rs.next()) {
				BoardDTO dto = new BoardDTO();
				dto.setNum(rs.getInt("no"));
				dto.setName(rs.getString("name"));
				dto.setTitle(rs.getString("title"));
				dto.setContents(rs.getString("contents"));
				dto.setHit(rs.getInt("hit"));
				dto.setWtime(rs.getString("wtime"));
				dto.setGroupNum(rs.getInt("groupNum"));
				dto.setStepNum(rs.getInt("stepNum"));
				dto.setIndentNum(rs.getInt("indentNum"));
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, ps, conn);
		}
		return list;
	}
	
	public BoardDTO viewDAO(int no) {
		increaseHit(no);
		BoardDTO dto = getBoardDTO(no);
		return dto;
	}
}

 

[ DTO ]

- BoardDTO.java

package com.tistory.coderbear.dto;

public class BoardDTO {
	private int num;
	private String name;
	private String title;
	private String contents;
	private int hit; // 조회 수
	private String wtime;
	private int groupNum; // 게시글 그룹
	private int stepNum; // 댓글의 최신 순으로 정렬하기 위함
	private int indentNum; // 몇 번째 댓글인가 ex) 대댓글
	
	public int getNum() {
		return num;
	}
	public void setNum(int num) {
		this.num = num;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getContents() {
		return contents;
	}
	public void setContents(String contents) {
		this.contents = contents;
	}
	public int getHit() {
		return hit;
	}
	public void setHit(int hit) {
		this.hit = hit;
	}
	public String getWtime() {
		return wtime;
	}
	public void setWtime(String wtime) {
		this.wtime = wtime;
	}
	public int getGroupNum() {
		return groupNum;
	}
	public void setGroupNum(int groupNum) {
		this.groupNum = groupNum;
	}
	public int getStepNum() {
		return stepNum;
	}
	public void setStepNum(int stepNum) {
		this.stepNum = stepNum;
	}
	public int getIndentNum() {
		return indentNum;
	}
	public void setIndentNum(int indentNum) {
		this.indentNum = indentNum;
	}
}
728x90
저작자표시

'Web > JSP' 카테고리의 다른 글

[필기정리] Day84-1 - EL(Expression Language), ContextParam 등  (0) 2020.10.26
[필기정리] Day83 - MIME, Proxy server, http 응답코드 등  (0) 2020.10.26
[필기정리]Day79-2 - MVC(Model, View, Controller)  (0) 2020.10.22
[필기정리]Day79-1 - Connection Pool, JNDI, Data Source 등  (0) 2020.10.20
[필기정리]Day78 - ActionTag, JavaBeans, DAO, DTO, VO 등  (0) 2020.10.20

티스토리툴바