Spring
Database App with JDBC : 학생 정보를 업데이트하고 삭제하는 기능 만들기 (Update, Delete)
dodop
2021. 6. 19. 17:45
update먼저 해보자.
먼저 업데이트 링크를 만들어준다.
여기서는 먼저 로드해서 데이터를 업데이트폼화면에 보여주도록 LOAD 커멘드라우트를 만들어 줄 것이다.
<%@ taglib uri = "http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<title>Student Tracker App</title>
<link type="text/css" rel="stylesheet" href = "css/style.css">
</head>
<body>
<div id = "wrapper">
<div id = "header">
<h2>FooBar University</h2>
</div>
</div>
<div id = "container">
<div id = "content">
<!-- put new button : Add Student -->
<input type="button" value="Add Student"
onClick="window.location.href='add-student-form.jsp'; return false;"
class = "add-student-button"
/>
<table>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Action</th>
</tr>
<c:forEach var = "tempStudent" items = "${STUDENT_LIST}">
<tr>
<!-- set up a link for each student -->
<c:url var = "templink" value="StudentControllerServlet">
<c:param name = "command" value="LOAD"/>
<c:param name = "studentId" value = "${tempStudent.id }" />
</c:url>
<td> ${tempStudent.firstName} </td>
<td> ${tempStudent.lastName} </td>
<td> ${tempStudent.email} </td>
<td> <a href="${templink }">Update</a> </td>
</tr>
</c:forEach>
</table>
</div>
</div>
</body>
</html>
여기서 Update의 링크를 복사해서 보면, 다음과 같이 보인다.
http://localhost:8080/web-student-tracker/StudentControllerServlet?command=LOAD&studentId=5
command와 studentId가 확인 되는 것을 볼 수 있다.
이제 command와 학생 아이디에 맞는 데이터를 업데이트 폼에 보이도록 servlet을 만들어보자.
먼저 데이터를 로드해서 보여주도록 , load커멘드라우트 를 작성해준다.
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
//read the "command" parameter
String theCommand = request.getParameter("command");
//if the command is missing, then default to listing students
if(theCommand==null) {
theCommand = "LIST";
}
//route to the appropriate method
switch(theCommand) {
case "LIST":
//list the students ... in MVC fashion
listStudents(request, response);
break;
case "ADD":
addStudents(request, response);
break;
case "LOAD":
loadStudents(request, response);
break;
default:
listStudents(request, response);
}
}catch(Exception exc) {
throw new ServletException(exc);
}
}
private void loadStudents(HttpServletRequest request, HttpServletResponse response) throws Exception {
// TODO Auto-generated method stub
//read student id from form data
String theStudentId = request.getParameter("studentId");
//get student from database (db util)
Student theStudent = studentDbUtil.getTheStudent(theStudentId);
//place student in the request attribute
request.setAttribute("THE_STUDENT", theStudent);
//send to jsp page: update-student-form.jsp
RequestDispatcher dispatcher = request.getRequestDispatcher("/update-student-form.jsp");
dispatcher.forward(request, response);
}
dbUtil
public Student getTheStudent(String theStudentId) throws Exception {
// TODO Auto-generated method stub
Student theStudent = null;
Connection myConn = null;
PreparedStatement myStmt = null;
ResultSet myRs = null;
int studentId;
try {
//convert student id to int
studentId = Integer.parseInt(theStudentId);
//get connection to database
myConn = dataSource.getConnection();
//create sql to get selected student
String sql = "select * from student where id = ?";
//create prepared statement
myStmt = myConn.prepareStatement(sql);
//set params
myStmt.setInt(1, studentId);
//execute statement
myRs = myStmt.executeQuery();
//retrieve data from result set row
if(myRs.next()) {
String firstName = myRs.getString("first_name");
String lastName = myRs.getString("last_name");
String email = myRs.getString("email");
//use the studentId during construction
theStudent = new Student(studentId, firstName, lastName, email);
}else {
throw new Exception("Could not find student id: " + studentId);
}
return theStudent;
}finally {
//clean p JDBC objects
close(myConn, myStmt, myRs);
}
}
이제 데이터를 화면에 보여주는 update-student-form.jsp를 만들자.
여기서 버튼을 누르면 업데이트 되도록 input타입도 업데이트로 바꿔주고, pram으로 studentId 도 준다.
<!DOCTYPE html>
<html>
<head>
<title>Update Student</title>
<link type="text/css" rel="stylesheet" href = "css/style.css">
<link type="text/css" rel="stylesheet" href = "css/add-student-style.css">
</head>
<body>
<div id = "wrapper">
<div id = "header">
<h2>FooBar University</h2>
</div>
</div>
<div id = "container">
<h3>Update Student</h3>
<form action="StudentControllerServlet" method="GET">
<input type="hidden" name="command" value = "UPDATE"/>
<input type="hidden" name="studentId" value = "${THE_STUDENT.id }"/>
<table>
<tbody>
<tr>
<td><label>First name : </label></td>
<td><input type="text" name = "firstName"
value="${THE_STUDENT.firstName}"/></td>
</tr>
<tr>
<td><label>Last name : </label></td>
<td><input type="text" name = "lastName"
value="${THE_STUDENT.lastName }"/></td>
</tr>
<tr>
<td><label>Email : </label></td>
<td><input type="text" name = "email"
value="${THE_STUDENT.email }"/></td>
</tr>
<tr>
<td><label></label></td>
<td><input type="submit" value = "Save" class="save" /></td>
</tr>
</tbody>
</table>
</form>
<div style= "clear: both;"></div>
<p>
<a href="StudentControllerServlet">Back to List</a>
</p>
</div>
</body>
</html>
실행해보면, update버튼 누르면 load studentid가 라우트로 전송되고 있는 것을 확인할 수 있다.
여기서 세이브 버튼을 누르면
업데이트 작동하도록 servlet과 dbutil을 작성하자.
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
//read the "command" parameter
String theCommand = request.getParameter("command");
//if the command is missing, then default to listing students
if(theCommand==null) {
theCommand = "LIST";
}
//route to the appropriate method
switch(theCommand) {
case "LIST":
//list the students ... in MVC fashion
listStudents(request, response);
break;
case "ADD":
addStudents(request, response);
break;
case "LOAD":
loadStudents(request, response);
break;
case "UPDATE":
updateStudents(request, response);
break;
default:
listStudents(request, response);
}
}catch(Exception exc) {
throw new ServletException(exc);
}
}
private void updateStudents(HttpServletRequest request, HttpServletResponse response) throws Exception{
// TODO Auto-generated method stub
//read student info from form data
int id = Integer.parseInt(request.getParameter("studentId"));
String firstName = request.getParameter("firstName");
String lastName = request.getParameter("lastName");
String email = request.getParameter("email");
//create a new student object
Student theStudent = new Student(id, firstName, lastName, email);
//perform update on database
studentDbUtil.updateStudent(theStudent);
//send them back to the "list students" page
listStudents(request, response);
}
dbUtil
public void updateStudent(Student theStudent)throws Exception {
// TODO Auto-generated method stub
Connection myConn = null;
PreparedStatement myStmt = null;
try {
//get db connection
myConn = dataSource.getConnection();
//create SQL update statement
String sql = "update student "
+ "set first_name = ?, last_name = ?, email=? "
+ "where id=?";
//prepare statement
myStmt = myConn.prepareStatement(sql);
//set params
myStmt.setString(1, theStudent.getFirstName());
myStmt.setString(2, theStudent.getLastName());
myStmt.setString(3, theStudent.getEmail());
myStmt.setInt(4, theStudent.getId());
//execute SQL statement
myStmt.execute();
}
finally {
close(myConn, myStmt, null);
}
}
}
이제 delete를 구현하자.
먼저 delete 링크를 추가하자.
<%@ taglib uri = "http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<title>Student Tracker App</title>
<link type="text/css" rel="stylesheet" href = "css/style.css">
</head>
<body>
<div id = "wrapper">
<div id = "header">
<h2>FooBar University</h2>
</div>
</div>
<div id = "container">
<div id = "content">
<!-- put new button : Add Student -->
<input type="button" value="Add Student"
onClick="window.location.href='add-student-form.jsp'; return false;"
class = "add-student-button"
/>
<table>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Action</th>
</tr>
<c:forEach var = "tempStudent" items = "${STUDENT_LIST}">
<tr>
<!-- set up a link for each student -->
<c:url var = "templink" value="StudentControllerServlet">
<c:param name = "command" value="LOAD"/>
<c:param name = "studentId" value = "${tempStudent.id }" />
</c:url>
<!-- set up a link for delete a student -->
<c:url var = "deletelink" value="StudentControllerServlet">
<c:param name = "command" value="DELETE"/>
<c:param name = "studentId" value = "${tempStudent.id }" />
</c:url>
<td> ${tempStudent.firstName} </td>
<td> ${tempStudent.lastName} </td>
<td> ${tempStudent.email} </td>
<td> <a href="${templink }">Update</a>
|
<a href="${deletelink}" onClick="if (!(confirm('Are you sure you want to delete this student?')))return false">Delete</a>
</td>
</tr>
</c:forEach>
</table>
</div>
</div>
</body>
</html>
delete 버튼의 링크를 copy해보면 다음과 같다 .
http://localhost:8080/web-student-tracker/StudentControllerServlet?command=DELETE&studentId=5
servlet에 만들자 .
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
//read the "command" parameter
String theCommand = request.getParameter("command");
//if the command is missing, then default to listing students
if(theCommand==null) {
theCommand = "LIST";
}
//route to the appropriate method
switch(theCommand) {
case "LIST":
//list the students ... in MVC fashion
listStudents(request, response);
break;
case "ADD":
addStudents(request, response);
break;
case "LOAD":
loadStudents(request, response);
break;
case "UPDATE":
updateStudents(request, response);
break;
case "DELETE":
deleteStudent(request, response);
break;
default:
listStudents(request, response);
}
}catch(Exception exc) {
throw new ServletException(exc);
}
}
private void deleteStudent(HttpServletRequest request, HttpServletResponse response) throws Exception{
// TODO Auto-generated method stub
//read student id from form data
String theStudentId = request.getParameter("studentId");
//delete student from database
studentDbUtil.deleteStudent(theStudentId);
//send them back to "list students" page
listStudents(request, response);
}
dbutil.
public void deleteStudent(String theStudentId) throws Exception{
// TODO Auto-generated method stub
Connection myConn = null;
PreparedStatement myStmt = null;
try {
//convert student id to int
int studentId = Integer.parseInt(theStudentId);
//get db connection
myConn = dataSource.getConnection();
//create SQL delete statement
String sql = "delete from student where id=?";
//prepare statement
myStmt = myConn.prepareStatement(sql);
//set params
myStmt.setInt(1, studentId);
//execute SQL statement
myStmt.execute();
}finally {
//clean up JDBC code
close(myConn, myStmt, null);
}
}
}
잘 삭제된다.
db 에서도 잘 삭제됐다.