Backend/JAVA_WEB
06. JSP, DB연동해서 성적 테이블 CRUD 게시판 만들기 ② (성적 삭제, 수정)
개발개발빈이
2022. 7. 4. 22:49
○ 성적JSP페이지
- 참고 : 오라클 DB, 성적 테이블 SQL문
JSP, 성적 테이블 CRUD 게시판 만들기 ① (입력, 리스트, 상세보기)
○ 성적 삭제
- 성적 삭제 : sungjukDel.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<title>sungjukDel.jsp</title>
</head>
<body>
<h3>☆ 성적 삭제 ☆</h3>
<p>
<a href="sungjukForm.jsp">[성적쓰기]</a>
<a href="sungjukList.jsp">[성적목록]</a>
</p>
<%
int sno=Integer.parseInt(request.getParameter("sno"));
Connection con=null;
PreparedStatement pstmt=null;
try {
String url ="jdbc:oracle:thin:@localhost:1521:xe";
String user ="system";
String password ="1234";
String driver ="oracle.jdbc.driver.OracleDriver";
Class.forName(driver);
con =DriverManager.getConnection(url, user, password);
StringBuilder sql=new StringBuilder();
sql.append(" DELETE FROM sungjuk ");
sql.append(" WHERE sno=? ");
pstmt = con.prepareStatement(sql.toString());
pstmt.setInt(1, sno);
int cnt=pstmt.executeUpdate(); //삭제된 행의 개수가 나옴(0이면 삭제가 안된것)
if(cnt==0){
out.println("<p>성적 삭제가 실패했습니다</p>");
out.println("<p><a href='javascript:history.back()'>[다시시도]</a></p>");
}else{
out.println("<script>");
out.println(" alert('성적이 삭제되었습니다');");
out.println(" location.href='sungjukList.jsp';");
out.println("</script>");
}//if end
} catch (Exception e) {
out.println("오라클 DB 연결 실패 : " + e);
} finally {//자원반납(순서주의)
try {
if(pstmt!=null) {pstmt.close();}
} catch (Exception e) { }
try {
if(con!=null) {con.close();}
} catch (Exception e) { }
}//catch end
%>
</body>
</html>
○ 성적 수정
① 성적 수정 폼 : sungjukUpdate.jsp
- 기존에 입력했던 내용을 폼에 출력시킴
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<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.6.0/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<title>sungjukUpdate.jsp</title>
<style>
h3 {
font: 40px Montserrat, sans-serif;
font-weight: 500;
}
</style>
</head>
<body>
<div class="container-fluid text-center">
<h3>★ 성적 수정 ★</h3>
<%
//수정하고자 하는 행번호
int sno=Integer.parseInt(request.getParameter("sno"));
%>
<p>
<a href="sungjukRead.jsp?sno=<%=sno%>">[상세보기]</a>
<a href="sungjukList.jsp">[성적목록]</a>
</p>
<%
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
String url ="jdbc:oracle:thin:@localhost:1521:xe";
String user ="system";
String password ="1234";
String driver ="oracle.jdbc.driver.OracleDriver";
Class.forName(driver);
con =DriverManager.getConnection(url, user, password);
// 1단계) select문을 이용해서 수정할 행을 가져온다(sungjukRead.jsp참조)
StringBuilder sql=new StringBuilder();
sql.append(" SELECT uname, kor, eng, mat, addr");
sql.append(" FROM sungjuk ");
sql.append(" WHERE sno=? ");
pstmt = con.prepareStatement(sql.toString());
pstmt.setInt(1, sno);
rs = pstmt.executeQuery();
if(rs.next()){
// 2단계) 1단계의 내용을 폼에 출력 sungjukForm.jsp 참조
%>
<form method="post" action="sungjukUpdateProc.jsp">
<input type="hidden" name="sno" value="<%=sno%>"> <!-- 사용자에겐 보이지 않지만, sno을 전송함 -->
<table class="table">
<tr>
<th class="active">이름</th>
<td>
<input type="text" name="uname" class="form-control" maxlength="20" required
value="<%=rs.getString("uname")%>">
</td>
</tr>
<tr>
<th class="active">국어</th>
<td>
<input type="number" name="kor" class="form-control" size="5" min="0" max="100"
placeholder="숫자입력" value="<%=rs.getInt("kor")%>">
</td>
</tr>
<tr>
<th class="active">영어</th>
<td>
<input type="number" name="eng" class="form-control" size="5" min="0" max="100"
placeholder="숫자입력" value="<%=rs.getInt("eng")%>">
</td>
</tr>
<tr>
<th class="active">수학</th>
<td>
<input type="number" name="mat" class="form-control" size="5" min="0" max="100"
placeholder="숫자입력" value="<%=rs.getInt("mat")%>">
</td>
</tr>
<tr>
<th class="active">주소</th>
<td>
<% String addr=rs.getString("addr"); %>
<select name="addr" class="form-control">
<option value="Seoul" <%if (addr.equals("Seoul")) {out.print("selected");} %>>서울</option>
<option value="Jeju" <%if (addr.equals("Jeju")) {out.print("selected");} %>>제주</option>
<option value="Suwon" <%if (addr.equals("Suwon")) {out.print("selected");} %>>수원</option>
<option value="Busan" <%if (addr.equals("Busan")) {out.print("selected");} %>>부산</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="수정" class="btn btn-default btn-lg">
<input type="reset" value="취소" class="btn btn-default btn-lg">
</td>
</tr>
</table>
</form>
<%
}else {
out.println("해당 글 없음");
}//if end
} catch (Exception e) {
out.println("오라클 DB 연결 실패 : " + e);
} finally {//자원반납(순서주의)
try {
if(rs!=null) {rs.close();}
} catch (Exception e) { }
try {
if(pstmt!=null) {pstmt.close();}
} catch (Exception e) { }
try {
if(con!=null) {con.close();}
} catch (Exception e) { }
}//catch end
%>
</div>
</body>
</html>
② 성적 수정 진행 : sungjukUpdateProc.jsp
- 변경된 내용을 받아서 수정
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<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.6.0/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<title>sungjukUpdateProc.jsp</title>
</head>
<body>
<h3>☆ 성적 수정 페이지 ☆</h3>
<%
//한글 인코딩
request.setCharacterEncoding("UTF-8");
//사용자가 입력한 정보를 가져와서 변수에 담기
String uname = request.getParameter("uname").trim();
int kor = Integer.parseInt(request.getParameter("kor").trim());
int eng = Integer.parseInt(request.getParameter("eng").trim());
int mat = Integer.parseInt(request.getParameter("mat").trim());
String addr = request.getParameter("addr");
int sno = Integer.parseInt(request.getParameter("sno").trim());
//평균구하기
int aver = (kor+eng+mat)/3;
Connection con=null;
PreparedStatement pstmt=null;
try {
String url ="jdbc:oracle:thin:@localhost:1521:xe";
String user ="system";
String password ="1234";
String driver ="oracle.jdbc.driver.OracleDriver";
Class.forName(driver);
con =DriverManager.getConnection(url, user, password);
StringBuilder sql=new StringBuilder();
sql.append(" UPDATE sungjuk ");
sql.append(" SET uname=?, kor=?, eng=?, mat=?, aver=?, addr=?, wdate=sysdate ");
sql.append(" WHERE sno=? ");
pstmt = con.prepareStatement(sql.toString());
pstmt.setString(1, uname);
pstmt.setInt(2, kor);
pstmt.setInt(3, eng);
pstmt.setInt(4, mat);
pstmt.setInt(5, aver);
pstmt.setString(6, addr);
pstmt.setInt(7, sno);
int cnt=pstmt.executeUpdate(); //추가된 행의 개수가 나옴(0이면 추가가 안된것)
if(cnt==0){
out.println("<p>성적 수정이 실패했습니다</p>");
out.println("<p><a href='javascript:history.back()'>[다시시도]</a></p>");
}else{
out.println("<script>");
out.println(" alert('성적이 수정되었습니다');");
out.println(" location.href='sungjukRead.jsp?sno="+sno+"';");//상세페이지 이동
out.println("</script>");
}//if end
} catch (Exception e) {
out.println("오라클 DB 연결 실패 : " + e);
} finally {//자원반납(순서주의)
try {
if(pstmt!=null) {pstmt.close();}
} catch (Exception e) { }
try {
if(con!=null) {con.close();}
} catch (Exception e) { }
}//catch end
%>
</body>
</html>