-
Database App with JDBC : 학생 정보를 업데이트하고 삭제하는 기능 만들기 (Update, Delete)Spring 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); } } }
db에서도 업데이트 된 것 확인할 수 있다. 이제 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>
버튼누르면 Prompt창에서 물어본다. 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 에서도 잘 삭제됐다.
'Spring' 카테고리의 다른 글
@Many to Many 혹은 연관관계에서 stackOverFlow 에러 발생시 (0) 2021.08.17 Social Media 만들기 - 19) socket io 사용하기 ( App.js 문제 해결!) (0) 2021.08.02 Database App with JDBC : 학생을 추가하는 기능 만들기 (Create) (0) 2021.06.19 Database App with JDBC : 학생 리스트 DB에서 가져오기 (Read) (0) 2021.06.18 MVC : MVC의 기본작용방법 (0) 2021.06.18