웹 - JDBC
웹 - jdbc - 예제(방명록3 - 수정/삭제)
우가본
2024. 6. 21. 11:29
*)수정과 삭제를 위해선 비밀번호 검증이 필요하다
function del(f){
let pwd = f.pwd.value; // 게시물 비밀번호
let c_pwd = f.c_pwd.value.trim(); // 내가 입력한 비밀번호
if(pwd != c_pwd) {
alert('비밀번호가 일치하지 않습니다.');
f.c_pwd.value="";
f.c_pwd.focus();
return;
}
// 삭제확인
if(confirm("정말 삭제하시겠습니까?")==false) return;
// 삭제요청
f.action="delete.do";
f.submit();
})삭제
pwd와 c_pwd가 같은지 검증 후, 삭제 처리
수정후 visit.jsp 전체 코드
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!-- JSTL Library -->
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<%--현재 컨텍스트 경로 : ${ pageContext.request.contextPath }--%>
<link rel="stylesheet" href="../css/visit.css">
<script type="text/javascript">
function del(f){
let pwd = f.pwd.value; // 게시물 비밀번호
let c_pwd = f.c_pwd.value.trim(); // 내가 입력한 비밀번호
if(pwd != c_pwd) {
alert('비밀번호가 일치하지 않습니다.');
f.c_pwd.value="";
f.c_pwd.focus();
return;
}
// 삭제확인
if(confirm("정말 삭제하시겠습니까?")==false) return;
// 삭제요청
// 방법1: form통해서 전송
// f.action="delete.do";
// f.submit();
// 방법2: location전송(선택적으로 parameter 전송)
location.href = "delete.do?idx=" + f.idx.value;
}
</script>
</head>
<body>
<div id="box">
<h1 id="title">::::: 방명록 :::::</h1>
<div style="margin-bottom: 10px">
<input class="btn btn-primary" type="button" value="글쓰기"
onclick="location.href='insert_form.do'">
</div>
<!-- 내용이 없을 경우 -->
<c:if test="${ empty requestScope.list }">
<div id="empty_msg">등록된 게시물이 없습니다</div>
</c:if>
<!-- 내용 -->
<!-- for(VisitVo vo : list) 동일 -->
<c:forEach var="vo" items="${ requestScope.list }">
<!-- 코드 정렬 : ctrl + shift + f -->
<form class="form-inline">
<input type="hidden" name="pwd" value="${ vo.pwd }">
<input type="hidden" name="idx" value="${ vo.idx }">
<div class="panel panel-primary">
<div class="panel-heading">
<h4><b>${ vo.name }</b>님의 글: ${ vo.ip }</h4>
</div>
<div class="panel-body">
<div class="mycommon content">${ vo.content } </div>
<div class="mycommon regdate">작성일자 : ${ fn:substring(vo.regdate,0,16) }</div>
<div class="mycommon pwd">
비밀번호(${ vo.pwd }) : <input class="form-control" type="password" name="c_pwd">
<input class="btn btn-success" type="button" value="수정">
<input class="btn btn-danger" type="button" value="삭제"
onclick="del(this.form);">
</div>
</div>
</div>
</form>
</c:forEach>
</div>
</body>
</html>
)VisitDeleteAction
package action;
import java.io.IOException;
import dao.VisitDao;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
/**
* Servlet implementation class VisitDeleteAction
*/
@WebServlet("/visit/delete.do")
public class VisitDeleteAction extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#service(HttpServletRequest request, HttpServletResponse response)
*/
protected void service
(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
// /visit/delete.do?idx=5 - idx인자만 선택적으로 받기
// 1. 삭제할 idx 수신
int idx = Integer.parseInt(request.getParameter("idx"));
// 2. DB delete
int res = VisitDao.getInstance().delete(idx); //dao에 delete() 추가
// 3. 목록보기
response.sendRedirect("list.do");
}
}
dao - delete()
public int delete(int idx) {
// TODO Auto-generated method stub
int res = 0;
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "delete from visit where idx=?";
try {
//1.Connection 얻어오기
conn = DBService.getInstance().getConnection();
//2.PreparedStatement
pstmt = conn.prepareStatement(sql);
//3.pstmt parameter index 채우기
pstmt.setInt(1, idx);
//4.DB delete
res = pstmt.executeUpdate();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
//마무리 작업(열린역순으로 닫기)
try {
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return res;
}//end:delete()
-> 이후, 글 삭제시 번호 추가와 포지션 설정 (전체 코드에서 확인하기)
)수정
// 수정폼 띄우기
function modify_form(f) {
let pwd = f.pwd.value; // 게시물 비밀번호
let c_pwd = f.c_pwd.value.trim(); // 내가 입력한 비밀번호
if(pwd != c_pwd) {
alert('수정할 게시물의 비밀번호가 일치하지 않습니다.');
f.c_pwd.value="";
f.c_pwd.focus();
return;
}
// 수정폼 띄우기(요청)
location.href = "modify_form.do?idx=" + f.idx.value + "&no=" + f.no.value;
}
수정 후 전체 코드
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!-- JSTL Library -->
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<%--현재 컨텍스트 경로 : ${ pageContext.request.contextPath }--%>
<link rel="stylesheet" href="../css/visit.css">
<script type="text/javascript">
function del(f){
let pwd = f.pwd.value; // 게시물 비밀번호
let c_pwd = f.c_pwd.value.trim(); // 내가 입력한 비밀번호
if(pwd != c_pwd) {
alert('삭제할 게시물의 비밀번호가 일치하지 않습니다.');
f.c_pwd.value="";
f.c_pwd.focus();
return;
}
// 삭제확인
if(confirm("정말 삭제하시겠습니까?")==false) return;
// 삭제요청
// 방법1: form통해서 전송
// f.action="delete.do";
// f.submit();
// 방법2: location전송(선택적으로 parameter 전송)
location.href = "delete.do?idx=" + f.idx.value + "&no=" + f.no.value;
}// end - del()
// 수정폼 띄우기
function modify_form(f) {
let pwd = f.pwd.value; // 게시물 비밀번호
let c_pwd = f.c_pwd.value.trim(); // 내가 입력한 비밀번호
if(pwd != c_pwd) {
alert('수정할 게시물의 비밀번호가 일치하지 않습니다.');
f.c_pwd.value="";
f.c_pwd.focus();
return;
}
// 수정폼 띄우기(요청)
location.href = "modify_form.do?idx=" + f.idx.value + "&no=" + f.no.value;
}
</script>
</head>
<body>
<div id="box">
<h1 id="title">::::: 방명록 :::::</h1>
<div style="margin-bottom: 10px">
<input class="btn btn-primary" type="button" value="글쓰기"
onclick="location.href='insert_form.do'">
</div>
<!-- 내용이 없을 경우 -->
<c:if test="${ empty requestScope.list }">
<div id="empty_msg">등록된 게시물이 없습니다</div>
</c:if>
<!-- 내용 -->
<!-- for(VisitVo vo : list) 동일 -->
<c:forEach var="vo" items="${ requestScope.list }">
<!-- 코드 정렬 : ctrl + shift + f -->
<form class="form-inline">
<input type="hidden" name="pwd" value="${ vo.pwd }">
<input type="hidden" name="idx" value="${ vo.idx }">
<input type="hidden" name="no" value="${ vo.no }">
<div class="panel panel-primary" id="p_${ vo.no }">
<div class="panel-heading">
<h4><b>${ vo.name }</b>님의 글: ${ vo.ip }</h4>
</div>
<div class="panel-body">
<div class="mycommon content">${ vo.content } </div>
<div class="mycommon regdate">작성일자 : ${ fn:substring(vo.regdate,0,16) }</div>
<div class="mycommon pwd">
비밀번호(${ vo.pwd }) : <input class="form-control" type="password" name="c_pwd">
<input class="btn btn-success" type="button" value="수정"
onclick="modify_form(this.form);">
<input class="btn btn-danger" type="button" value="삭제"
onclick="del(this.form);">
</div>
</div>
</div>
</form>
</c:forEach>
</div>
</body>
</html>
)VisitModifyFormAction
package action;
import jakarta.servlet.RequestDispatcher;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import dao.VisitDao;
import db.vo.VisitVo;
/**
* Servlet implementation class VisitModifyFormAction
*/
@WebServlet("/visit/modify_form.do")
public class VisitModifyFormAction extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#service(HttpServletRequest request, HttpServletResponse response)
*/
protected void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//1. 수정할 게시물의 idx받는다
int idx = Integer.parseInt(request.getParameter("idx"));
//2. idx에 해당되는 세기물 1건 얻어오기
VisitVo vo = VisitDao.getInstance().selectOne(idx);
// textarea \n기능처리 : <br> -> \n
String content = vo.getContent().replaceAll("<br>", "\n");
vo.setContent(content);
//3. request Binding
request.setAttribute("vo", vo);
// Dispatcher형식으로 호출
String forward_page = "visit_modify_form.jsp";
RequestDispatcher disp = request.getRequestDispatcher(forward_page);
disp.forward(request, response);
}
}
)수정은 하나의 데이터를 뽑아와야하기 때문에 dao에 selectOne()을 가져와야 함
_selectOne()
// 일부만 조회
public VisitVo selectOne(int idx) {
VisitVo vo = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select * from visit_view where idx=?";
try {
//1.Connection 얻어오기
conn = DBService.getInstance().getConnection();
//2.PreparedStatement
pstmt = conn.prepareStatement(sql);
//3.pstmt parameter index채우기
pstmt.setInt(1, idx);
//4.ResultSet 얻어온다
rs = pstmt.executeQuery();
if (rs.next()) {
//저장객체 생성->레코드에서 읽은 값을 넣는다
vo = new VisitVo();
//rs가 가리키는 레코드값을 vo에 넣는다
vo.setNo(rs.getInt("no"));
vo.setIdx(rs.getInt("idx"));
vo.setName(rs.getString("name"));
vo.setContent(rs.getString("content"));
vo.setPwd(rs.getString("pwd"));
vo.setIp(rs.getString("ip"));
vo.setRegdate(rs.getString("regdate"));
} //end:if
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
//마무리 작업(열린역순으로 닫기)
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return vo;
}
)수정 폼 jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<style type="text/css">
#box{
width: 600px;
margin: auto;
margin-top: 100px;
}
textarea {
resize: none;
}
th {
width: 15%;
vertical-align: middle !important;
}
</style>
<script type="text/javascript">
function send(f) {
//입력값 검증
let name = f.name.value.trim();
let content = f.content.value.trim();
let pwd = f.pwd.value.trim();
if(name=='') {
alert('작성자명을 입력하시오.');
f.name.value = "";
f.name.focus();
return;
}
if(content=='') {
alert('내용을 입력하시오.');
f.content.value = "";
f.content.focus();
return;
}
if(pwd=='') {
alert('비밀번호를 입력하시오.');
f.pwd.value = "";
f.pwd.focus();
return;
}
//f.method = "post";
f.action = "modify.do"; // 전송대상(VisitModifyFormAction)
f.submit(); // 전송
}
</script>
</head>
<body>
<form>
<div id="box">
<!-- 코드 정렬 : ctrl + shift + f -->
<div class="panel panel-primary">
<div class="panel-heading"><h4>글 수정하기</h4></div>
<div class="panel-body">
<table class="table">
<tr>
<th>작성자</th>
<td><input class="form-control" name="name" value="${ vo.name }"></td>
</tr>
<tr>
<th>내용</th>
<td>
<textarea class="form-control" rows="6" name="content">${ vo.content }</textarea>
</td>
</tr>
<tr>
<th>비밀번호</th>
<td><input class="form-control" name="pwd" type="password" value="${ vo.pwd }"></td>
</tr>
<tr>
<td colspan="2" align="center">
<input class="btn btn-success" type="button" value="목록보기"
onclick="location.href='list.do'">
<input class="btn btn-primary" type="button" value="수정하기"
onclick="send(this.form);">
</td>
</tr>
</table>
</div>
</div>
</div>
</form>
</body>
</html>
)VisitModifyAction
package action;
import java.io.IOException;
import dao.VisitDao;
import db.vo.VisitVo;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
/**
* Servlet implementation class VisitModifyAction
*/
@WebServlet("/visit/modify.do")
public class VisitModifyAction extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#service(HttpServletRequest request, HttpServletResponse response)
*/
protected void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 0. 수신 인코딩 설정
request.setCharacterEncoding("utf-8");
// 1.parameter(전달인자) 받기
int idx = Integer.parseInt(request.getParameter("idx"));
String no = request.getParameter("no");
String name = request.getParameter("name");
String content = request.getParameter("content").replaceAll("\n", "<br>");
String pwd = request.getParameter("pwd");
// 2. ip정보 얻어오기
String ip = request.getRemoteAddr();
// 3. VisitVo 포장
VisitVo vo = new VisitVo(idx, name, content, pwd, ip);
// 4. DB insert
int res = VisitDao.getInstance().update(vo);
// 5. 목록보기 이동
response.sendRedirect("list.do");
}
}
_update
public int update(VisitVo vo) {
// TODO Auto-generated method stub
int res = 0;
Connection conn = null;
PreparedStatement pstmt = null;
// 1 2 3 4 5
String sql = "update visit set name=?, content=?, pwd=?, ip=?, regdate=sysdate where idx=? ";
try {
//1.Connection 얻어오기
conn = DBService.getInstance().getConnection();
//2.PreparedStatement
pstmt = conn.prepareStatement(sql);
//3.pstmt parameter index 채우기
pstmt.setString(1, vo.getName());
pstmt.setString(2, vo.getContent());
pstmt.setString(3, vo.getPwd());
pstmt.setString(4, vo.getIp());
pstmt.setInt(5, vo.getIdx());
//4.DB update
res = pstmt.executeUpdate();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
//마무리 작업(열린역순으로 닫기)
try {
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return res;
}//end:update()