[JSP] Delete_WEB에서 DB자원 삭제하기

목차

1. DB
2. VO
3. DAO_selectList/delect
4. WEB.jsp
5. Delete.jsp

---

---

1. DB

* DB 자원삭제를 위해서는 자원하나의 특성을 묶어주는 인덱스가 반드시 필요하다(CREATE SEQUENCE student_seq;)

CREATE SEQUENCE student_seq;

CREATE table grade(
	idx NUMBER(3) primary key,
	name VARCHAR2(40),
	java NUMBER(10),
	c NUMBER(10),
	grade NUMBER(10)
);

INSERT INTO grade VALUES(student_seq.nextval, '사나',99,99,4);
INSERT INTO grade VALUES(student_seq.nextval, '미나',88,88,3);
INSERT INTO grade VALUES(student_seq.nextval, '모모',77,77,2);
INSERT INTO grade VALUES(student_seq.nextval, '쯔위',66,66,1);
commit;

 

---

2. VO

public class VO {
	private int idx;
	private String name; 
	private int java;
	private int c;
	private int grade;
	
	
	public int getIdx() {
		return idx;
	}
	public void setIdx(int idx) {
		this.idx = idx;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getJava() {
		return java;
	}
	public void setJava(int java) {
		this.java = java;
	}
	public int getC() {
		return c;
	}
	public void setC(int c) {
		this.c = c;
	}
	public int getGrade() {
		return grade;
	}
	public void setGrade(int grade) {
		this.grade = grade;
	}
}

---

3. DAO_selectList/delete

package dao;

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

import service.DBService;
import vo.VO;

public class DAO {	
		//DAO(Data Access Object) : 데이터 접근을 목표로 하는 클래스
		//single-ton pattern: 해당 클래스의 인스턴스가 1개만 생성됨	
		static DAO single = null;
		public static DAO getInstance() {
			//생성되지 않았으면 생성
			if (single == null)
				single = new DAO();
			//생성된 객체정보를 반환
			return single;
		}
		
		//grade테이블 조회
		public List<VO> selectList() {

			List<VO> list = new ArrayList<VO>();
			Connection conn = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			String sql = "SELECT * From grade";

			try {
				//1.Connection얻어온다
				conn = DBService.getInstance().getConnection();
				//2.명령처리객체정보를 얻어오기
				pstmt = conn.prepareStatement(sql);

				//3.결과행 처리객체 얻어오기
				rs = pstmt.executeQuery();

				while (rs.next()) {
					VO vo = new VO();
					//현재레코드값=>Vo저장
					vo.setIdx(rs.getInt("idx"));
					vo.setName(rs.getString("name"));
					vo.setJava(rs.getInt("java"));
					vo.setC(rs.getInt("c"));
					vo.setGrade(rs.getInt("grade"));
					
					list.add(vo);
				}
			} 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) {
					e.printStackTrace();
				}
			}
			return list;
		}	
		
		public int insert(VO vo) {
			int res = 0;

			Connection conn = null;
			PreparedStatement pstmt = null;

			String sql =
			"INSERT INTO grade VALUES(student_seq.nextval, ?,?,?,?)";

			try {
				//1.Connection획득
				conn = DBService.getInstance().getConnection();
				//2.명령처리객체 획득
				pstmt = conn.prepareStatement(sql);

				//3.pstmt parameter 채우기
				pstmt.setString(1, vo.getName());
				pstmt.setInt(2, vo.getJava());
				pstmt.setInt(3, vo.getC());
				pstmt.setInt(4, vo.getGrade());				

				//4.DB로 전송(res:처리된행수)
				res = pstmt.executeUpdate();

			} catch (Exception e) {			
				e.printStackTrace();
			} finally {

				try {
					if (pstmt != null)
						pstmt.close();
					if (conn != null)
						conn.close();
				} catch (SQLException e) {					
					e.printStackTrace();
				}
			}
			return res;
		}
		
		public int delete(int idx) {
			// TODO Auto-generated method stub
			int res = 0;

			Connection conn = null;
			PreparedStatement pstmt = null;

			String sql = "DELETE FROM grade WHERE idx=?";

			try {
				//1.Connection획득
				conn = DBService.getInstance().getConnection();
				//2.명령처리객체 획득
				pstmt = conn.prepareStatement(sql);
				
				//3.pstmt parameter 채우기
				pstmt.setInt(1, idx);

				//4.DB로 전송(res:처리된행수)
				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;
		}
}

---

4. WEB.jsp

* 삭제버튼을 누르면 idx가 매개변수로 del()로 넘어가고 cofirm()을 통해 웹사용자의 허가가 떨어지면 Grade_del.jsp로 idx매개변수와 함께 넘어간다.

<%@page import="vo.VO"%>
<%@page import="java.util.List"%>
<%@page import="dao.DAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	DAO dao = DAO.getInstance();
	List<VO> list = dao.selectList();
%>      

<!DOCTYPE html>
<html>
	<head>
		<meta charset="UTF-8">
		<title>Insert title here</title>
		<script>
			function send(f){
				//name 값을 가져옴
				var name = f.name.value;
				var java = f.java.value;
				var c = f.c.value;
				var grade = f.grade.value;
				
				//유효성 체크			
				if (name=="") {
					alert("학생이름을 확인해주세요");
					return;
				}
				if(java=="" ||java < 0 || java > 100){
					alert("0~100점까지를 입력해주세요");
					return;
				}
				if(c==""||c < 0 || c > 100){
					alert("0~100점까지를 입력해주세요");
					return;
				}
				if(grade=="" ||grade < 0 || grade > 4.5){
					alert("GPA는 0~4.5점까지를 입력해주세요");
					return;
				}				
				/*유효성체크에서도 문제가 없다면 var로 선언된 값들을 
				grade_register.jsp로 보낸다.*/
				f.action = "Grade_register.jsp";
				f.method = "post";
				f.submit();
			}
			
			function del( idx ) {
				if(!confirm("정말로 삭제하시겠습니까?")){
					return;
				}						
				location.href="Grade_del.jsp?idx="+idx;
			}
			
		</script>
	</head>	
	<body>
		<table border='1' style='border-collapse: collapse;' align="center">
			<tr>
				<th> 이름 </th>
				<th> 자바 </th>
				<th> C계열 </th>
			    <th> GPA </th>			
				<th> 기능 </th>
						
			</tr>
			<%
				for(int i=0; i<list.size(); i++){
					VO vo = list.get(i);
			%>
			<tr style="text-align: center;">
				<td><%= vo.getName() %> </td>
				<td><%= vo.getJava() %> </td>
				<td><%= vo.getC() %> </td>
				<td><%= vo.getGrade() %> </td>		
				<td>
					<input type="button" value="삭제" onclick="del('<%=vo.getIdx()%>');">
				<td>
			</tr>					
			<%}%>		
		</table>
		
		<!--학생입력  -->
		<div id="input">
			<form>
				<h3 style="text-align: center;"> 학생 추가 </h3><br>
				<table border="1" align="center" style="border-collapse: collapse;">
					<tr>
						<th>이름 </th>
						<td><input name="name"></td>
					</tr>
					<tr>
						<th>JAVA </th>
						<td><input name="java"></td>
					</tr>
					<tr>
						<th>C계열 </th>
						<td><input name="c"></td>
					</tr>
					<tr>
						<th>GPA </th>
						<td><input name="grade"></td>
					</tr>
						
					<tr>
						<td colspan="2" align="center">
							<input type="button" value="등록"
                            onclick="send(this.form);">
						</td>
					</tr>										 
				</table>
			</form>		
		</div>	
	</body>
</html>

---

5. Delete.jsp

<%@page import="dao.DAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	request.setCharacterEncoding("utf-8");
	int idx = Integer.parseInt(request.getParameter("idx"));
	int res = DAO.getInstance().delete(idx);
	
	if(res != 0){//삭제 성공 시 res != 0이 되며 Grade.jsp로 이동
		response.sendRedirect("Grade.jsp");
	}
%>

    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

</body>
</html>