(3-2) [JSP&JDBC] 학생관리 프로그램 만들기 (추가,수정,삭제)

씩씩한 조약돌·2023년 1월 15일
0

미니프로젝트🤹

목록 보기
7/21

(2) 추가(INSERT)

1. StuDAO클래스 - insertMethod() 생성

  • sql구문을 처리할 PreparedStatment객체 pstmt 생성
    (SELECT는 Statment / INSERT, UPDATE, DELETE는 PreparedStatment)
  • int chk : pstmt.executeUpdate() 수행결과로 Int 타입의 값을 반환
  • INSERT INTO student(num, name, kor, eng, math) VALUES(stu_num_seq.nextval,?,?,?,?) : name, kor, eng, math값은 입력받아와야 하기 때문에 ?(placeholder)를 사용
  • pstmt.setString(1, dto.getName()) : 1번째 ?(placeholder)에 입력받아온 dto.getName을 넣기
  • finally - close(pstmt) : DB 사용 후 연결 끊기
  • 리턴값 : chk (Insert된 행의 갯수를 리턴)
	public int insertMethod(Connection conn, StuDTO dto) {
		
		PreparedStatement pstmt = null;
		int chk = 0;
		
		
		try {
			String sql = "INSERT INTO student(num, name, kor, eng, math) VALUES(stu_num_seq.nextval,?,?,?,?)";
			pstmt=conn.prepareStatement(sql);
			
			pstmt.setString(1, dto.getName());
			pstmt.setInt(2, dto.getKor());
			pstmt.setInt(3, dto.getEng());
			pstmt.setInt(4, dto.getMath());
			
			chk = pstmt.executeUpdate();
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcTemplate.close(pstmt);
		}
		
		return chk;
	}//insertMethod()

2. StuService 클래스 - getInsert()
테이블에 데이터 추가(INSERT) 처리 (Connection연결 및 종료)

  • Connection conn = JdbcTemplate.getConnection(); : 드라이버와 연결
  • conn.setAutoCommit(false); : 자동커밋 해제
  • chk = dao.insertMethod(conn, dto); : INSERT된 횟수 가져오기
  • 작업 성공하면 commit(conn) / 실패하면 rollback(conn) / 작업 종료되면 close(conn)
  • 리턴값 : chk
	public int getInsert(StuDTO dto) {
		int chk = 0;
		
		Connection conn = JdbcTemplate.getConnection();
		
		try {
			conn.setAutoCommit(false);
			chk = dao.insertMethod(conn, dto);
			JdbcTemplate.commit(conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			JdbcTemplate.rollback(conn);
		} finally {
			JdbcTemplate.close(conn);
		}
		
		return chk;
	}//getInsert()

3. InsertStuController클래스 생성
3-1. 초기설정 (extends HttpServlet)

package servletdemo.part04;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;

@WebServlet("/stuinsert")
public class InsertStuController extends HttpServlet {
	
	
	
}//InsertStuController

3-2. StuService, insert.jsp연결

  • 기본생성자 : StuService객체 service 생성
  • doGet() 오버라이딩
  • jsp연결(정적인페이지 html)
package servletdemo.part04;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/stuinsert")
public class InsertStuController extends HttpServlet {
	
	private StuService service;
	
	public InsertStuController() {
		service = new StuService();
	}//기본생성자
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		String path = "/servletview/part04/insert.jsp";
		RequestDispatcher dis = req.getRequestDispatcher(path);
		dis.forward(req, resp);
	}	
	
}//InsertStuController

4. insert.jsp 생성

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert</title>
</head>
<body>

<form action ="stuinsert" method="post">
	<table>
		<tr>
			<th>이름</th><td><input type="text" name="name" /></td>
		</tr>
		
		<tr>
			<th>국어</th><td><input type="text" name="kor" /></td>
		</tr>
		
		<tr>
			<th>영어</th><td><input type="text" name="eng" /></td>
		</tr>
		<tr>
			<th>수학</th><td><input type="text" name="math" /></td>
		</tr>		
		
		<tr>
			<td colspan ="2"><input type="submit" value="저장"></td>
		</tr>
		
	</table>
</form>
</body>
</html>

5. StuDTO클래스 수정

  • num을 제외한 매개변수가 4개인 생성자 추가
	public StuDTO(String name, int kor, int eng, int math) {
		super();
		this.name = name;
		this.kor = kor;
		this.eng = eng;
		this.math = math;
	}

6. InsertStuController 클래스 - doPost()

  • doGet() : stuinsert페이지를 보여줌
  • doPost() : get(insert.jsp)에서 받은 정보를 '저장'버튼을 누르면 post로 받아옴
  • req.getParameter("")로 받은 값은 StuDTO dto객체에 담아서 service.getInsert(dto);로 처리됨 : MemDAO에서 만든 PreparedStatement pstmt에 넣어주고 / 성공하면 StuSevice에서 commit(conn)됨
  • chk가 0보다 크면 (insert가 성공하면) / 화면에 stulist(list.jsp)띄워주기
package servletdemo.part04;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/stuinsert")
public class InsertStuController extends HttpServlet {
	
	private StuService service;
	
	public InsertStuController() {
		service = new StuService();
	}//기본생성자
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		String path = "/servletview/part04/insert.jsp";
		RequestDispatcher dis = req.getRequestDispatcher(path);
		dis.forward(req, resp);
	}//doGet()
	
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		//post방식 한글깨짐 해결
		req.setCharacterEncoding("UTF-8");
		
		String name = req.getParameter("name");
		int kor = Integer.parseInt(req.getParameter("kor"));
		int eng = Integer.parseInt(req.getParameter("eng"));
		int math = Integer.parseInt(req.getParameter("math"));
		
		StuDTO dto = new StuDTO(name, kor, eng, math);
		
		int chk = service.getInsert(dto);
		
		if(chk>0) {
			//insert가 성공하면
			//서버에서 클라이언트에 stulist(list.jsp)페이지를 보여주도록 설정
			resp.sendRedirect("stulist");
		} else {
			System.out.println("저장 실패");
		}
	}//doPost()
	
}//InsertStuController

(3) 수정(UPDATE)

1. StuDAO클래스 - updateMethod() 생성

	public int updateMethod(Connection conn, HashMap<String, Object> map) {

		PreparedStatement pstmt = null;
		int chk = 0;


		try {
			String sql = "UPDATE student SET name=?, kor=?, eng=?, math=? WHERE num=?";
			pstmt=conn.prepareStatement(sql);

			pstmt.setString(1, map.get("name").toString());
			pstmt.setInt(2, Integer.parseInt(map.get("kor").toString()));
			pstmt.setInt(3, Integer.parseInt(map.get("eng").toString()));
			pstmt.setInt(4, Integer.parseInt(map.get("math").toString()));
			pstmt.setInt(5, Integer.parseInt(map.get("num").toString()));

			chk = pstmt.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcTemplate.close(pstmt);
		}

		return chk;
	}//updateMethod()

2. StuService 클래스 - getUpdate()

	public int getUpdate(HashMap<String, Object> map) {
		int chk = 0;
		
		Connection conn = JdbcTemplate.getConnection();
		
		try {
			conn.setAutoCommit(false);
			chk = dao.updateMethod(conn, map);
			JdbcTemplate.commit(conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			JdbcTemplate.rollback(conn);
		} finally {
			JdbcTemplate.close(conn);
		}
		
		return chk;
	}//getUpdate()

3. UpdateStuController클래스 생성 - doGet(

package servletdemo.part04;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/stuupdate")
public class UpdateStuController extends HttpServlet{

	private StuService service;
	
	public UpdateStuController() {
		service = new StuService();
	}
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		String path = "/servletview/part04/update.jsp";
		RequestDispatcher dis = req.getRequestDispatcher(path);
		dis.forward(req, resp);
	}//doGet()
	
}

4. insert.jsp 생성

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>update</title>
</head>
<body>

<form action ="stuupdate" method="post">
	<h2> 수정할 내용을 전부 입력해주세요. </h2>
	<table>
		<tr>
			<th>번호</th><td><input type="text" name="num" /></td>
		</tr>		
		
		<tr>
			<th>이름</th><td><input type="text" name="name" /></td>
		</tr>
		
		<tr>
			<th>국어</th><td><input type="text" name="kor" /></td>
		</tr>
		
		<tr>
			<th>영어</th><td><input type="text" name="eng" /></td>
		</tr>
		<tr>
			<th>수학</th><td><input type="text" name="math" /></td>
		</tr>		
		
		<tr>
			<td colspan ="2"><input type="submit" value="수정"></td>
		</tr>
		
	</table>
	
</body>
</html>

5. UpdateStuController 클래스 - doPost()

package servletdemo.part04;

import java.io.IOException;
import java.util.HashMap;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/stuupdate")
public class UpdateStuController extends HttpServlet{

	private StuService service;
	
	public UpdateStuController() {
		service = new StuService();
	}
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		String path = "/servletview/part04/update.jsp";
		RequestDispatcher dis = req.getRequestDispatcher(path);
		dis.forward(req, resp);
	}//doGet()
	
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		HashMap<String, Object> map = new HashMap<String, Object>();
		
		//post방식 한글깨짐 해결
		req.setCharacterEncoding("UTF-8");
		
		int num = Integer.parseInt(req.getParameter("num"));
		String name = req.getParameter("name");
		int kor = Integer.parseInt(req.getParameter("kor"));
		int eng = Integer.parseInt(req.getParameter("eng"));
		int math = Integer.parseInt(req.getParameter("math"));
		
		map.put("num", num);
		map.put("name", name);
		map.put("kor", kor);
		map.put("eng", eng);
		map.put("math", math);
		
		int chk = service.getUpdate(map);
		
		if(chk>0) {
			//insert가 성공하면
			//서버에서 클라이언트에 stulist(list.jsp)페이지를 보여주도록 설정
			resp.sendRedirect("stulist");
		} else {
			System.out.println("수정 실패");
		}
	}//doPost()
	
}//UpdateStuController

(4) 삭제(DELETE)

1. StuDAO클래스 - deleteMethod() 생성

	public int deleteMethod(Connection conn, int num) {

		PreparedStatement pstmt = null;
		int chk = 0;


		try {
			String sql = "DELETE FROM student WHERE num=?";
			pstmt=conn.prepareStatement(sql);

			pstmt.setInt(1, num);

			chk = pstmt.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcTemplate.close(pstmt);
		}

		return chk;
	}//deleteMethod()

2. StuService 클래스 - getDelete()

	public int getDelete(int num) {
		int chk = 0;
		
		Connection conn = JdbcTemplate.getConnection();
		
		try {
			conn.setAutoCommit(false);
			chk = dao.deleteMethod(conn, num);
			JdbcTemplate.commit(conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			JdbcTemplate.rollback(conn);
		} finally {
			JdbcTemplate.close(conn);
		}
		
		return chk;
	}//getDelete()

3. DeleteStuController클래스 생성 - doGet()

package servletdemo.part04;

import java.io.IOException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/studelete")
public class DeleteStuController extends HttpServlet{

	private StuService service;
	
	public DeleteStuController() {
		service = new StuService();
	}
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		String path = "/servletview/part04/delete.jsp";
		RequestDispatcher dis = req.getRequestDispatcher(path);
		dis.forward(req, resp);
	}//doGet()

	
}//DeleteStuController

4. delete.jsp 생성

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Delete</title>
</head>
<body>

<form action ="studelete" method="post">
	<h2> 삭제할 번호를 입력해주세요. </h2>
	<table>
		<tr>
			<th>번호</th><td><input type="text" name="num" /></td>
		</tr>				
			<tr>
			<td colspan ="2"><input type="submit" value="삭제"></td>
		</tr>
		
	</table>
</form>
</body>
</html>

5. InsertStuController 클래스 - doPost()

package servletdemo.part04;

import java.io.IOException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/studelete")
public class DeleteStuController extends HttpServlet{

	private StuService service;
	
	public DeleteStuController() {
		service = new StuService();
	}
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		String path = "/servletview/part04/delete.jsp";
		RequestDispatcher dis = req.getRequestDispatcher(path);
		dis.forward(req, resp);
	}//doGet()
	
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		int num = Integer.parseInt(req.getParameter("num"));
		
		int chk = service.getDelete(num);
		
		if(chk>0) {
			//insert가 성공하면
			//서버에서 클라이언트에 stulist(list.jsp)페이지를 보여주도록 설정
			resp.sendRedirect("stulist");
		} else {
			System.out.println("저장 실패");
		}
	}//doPost()

	
}//DeleteStuController

profile
씩씩한 조약돌
씩씩하게 공부중 (22.11~)
이전 포스트

(3-1) [JSP&JDBC] 학생관리 프로그램 만들기 (전체 데이터 조회)

다음 포스트

(3-3 完) [JSP&JDBC] 학생관리 프로그램 만들기 (페이지 이동)

0개의 댓글