[Servlet] CRUD

gimyeondong 2020. 4. 27. 11:58

2020. 4. 27. 11:58

creat

read

update

delete

4가지는 어떤 상황에서도 만들 수 있어야 한다.

입력 / 출력

Create / Read

숙제풀이.zip

1.98MB

1. html - index / input / error

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Index</title>
        <script src="https://code.jquery.com/jquery-3.5.0.js"></script>
        <style>   
            fieldset{
                text-align: center;
            }
        </style>
        <script>
            $(function(){
                $("#toInput").on("click",function(){
                    location.href="input.html";
                })
                $("#toOutput").on("click",function(){
                    location.href="Output";
                })

            })
        </script>
    </head>
    <body>
        <fieldset>
            <legend>Index</legend>
            <button id="toInput">toInput</button>
            <button id="toOutput">toOutput</button>
        </fieldset>
    </body>
</html>

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Input</title>
        <script src="https://code.jquery.com/jquery-3.5.0.js"></script>
        <style>
            fieldset{
                text-align: center;
            }
        </style>
    </head>
    <body>
        <form action="InputProc" method="post">
            <fieldset>
                <legend>Input</legend>
                <input type=text name=name placeholder="Input name"><br>
                <input type=text name=msg placeholder="Input msg"><br>
                <input type=submit>
            </fieldset>
        </form>
    </body>    
</html>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Error</title>
<script src="https://code.jquery.com/jquery-3.5.0.js"></script>
</head>
<body>
	<fieldset>
		<legend>Error</legend>
		에러가 발생했습니다.<br>
		문제가 반복될 시 관리자에게 연락해주세요.<br>
		email : admin@naver.com<br>
		고객센터 : 02-1234-1234
	</fieldset>
</body>
</html>

2. 서블릿 - InputProc / Output

package kh.backend.servlet;

import java.io.IOException;
import java.io.PrintWriter;

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

import kh.backend.dao.MessagesDAO;
import kh.backend.dto.MessagesDTO;


@WebServlet("/InputProc")
public class InputProc extends HttpServlet {
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String name = request.getParameter("name");
		String msg = request.getParameter("msg");
		
		MessagesDAO dao = new MessagesDAO();
		try {
			int result = dao.insert(new MessagesDTO(0,name,msg,null));
			
			if(result > 0) {
				PrintWriter pw = response.getWriter();
				pw.append("<html>");
				pw.append("<head>");
				pw.append("</head>");
				pw.append("<body>");
				pw.append("<script>");
				pw.append("alert('Input Complete');");
				pw.append("location.href='index.html';");
				pw.append("</script>");
				pw.append("</body>");
				pw.append("</html>");
			}
		}catch(Exception e) {
			e.printStackTrace();
			response.sendRedirect("error.html");
		}
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

}

package kh.backend.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

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

import kh.backend.dao.MessagesDAO;
import kh.backend.dto.MessagesDTO;


@WebServlet("/Output")
public class Output extends HttpServlet {
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		MessagesDAO dao = new MessagesDAO();
		PrintWriter pw = response.getWriter();
		try {
			List<MessagesDTO> result = dao.selectAll();
			pw.append("<html>");
			pw.append("<head>");
			pw.append("</head>");
			pw.append("<body>");
			pw.append("<table border=1 align=center>");
			pw.append("<tr>");
			pw.append("<td>Seq");
			pw.append("<td>Writer");
			pw.append("<td>Contents");
			pw.append("<td>Date");
			pw.append("</tr>");
			for(MessagesDTO dto : result) {
				pw.append("<tr>");
				pw.append("<td>"+dto.getSeq());
				pw.append("<td>"+dto.getName());
				pw.append("<td>"+dto.getMsg());
				pw.append("<td>"+dto.getWrite_date());
				pw.append("</tr>");
			}
			pw.append("</table>");
			pw.append("<button id=back>back</button>");
			pw.append("<script>document.getElementById('back').onclick = function(){location.href='index.html';}</script>");
			pw.append("</body>");
			pw.append("</html>");
		}catch(Exception e) {
			e.printStackTrace();
			response.sendRedirect("error.html");
		}
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

}

3. DTO

package kh.backend.dto;

import java.sql.Timestamp;

public class MessagesDTO {
	private int seq;
	private String name;
	private String msg;
	private Timestamp write_date;
	
	public MessagesDTO() {}
	public MessagesDTO(int seq, String name, String msg, Timestamp write_date) {
		super();
		this.seq = seq;
		this.name = name;
		this.msg = msg;
		this.write_date = write_date;
	}
	public int getSeq() {
		return seq;
	}
	public void setSeq(int seq) {
		this.seq = seq;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getMsg() {
		return msg;
	}
	public void setMsg(String msg) {
		this.msg = msg;
	}
	public Timestamp getWrite_date() {
		return write_date;
	}
	public void setWrite_date(Timestamp write_date) {
		this.write_date = write_date;
	}
	
}

4. DAO

package kh.backend.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import kh.backend.dto.MessagesDTO;

public class MessagesDAO {
	private Connection getConnection() throws Exception{
		String url = "jdbc:oracle:thin:@localhost:1521:xe";
		String id = "kh";
		String pw = "kh";
		Class.forName("oracle.jdbc.driver.OracleDriver");
		return DriverManager.getConnection(url,id,pw);
	}
	
	public int insert(MessagesDTO dto) throws Exception{
		String sql = "insert into messages values(messages_seq.nextval, ?,?,sysdate)";
		
		try(Connection con = this.getConnection();	
		PreparedStatement pstat = con.prepareStatement(sql)){
			pstat.setString(1, dto.getName());
			pstat.setString(2, dto.getMsg());
			int result = pstat.executeUpdate();
			con.commit();
			return result;
		}
	}
	public List<MessagesDTO> selectAll() throws Exception{
		String sql = "select * from messages";
		try(Connection con = this.getConnection();
			PreparedStatement pstat = con.prepareStatement(sql);
			ResultSet rs = pstat.executeQuery();){
			
			List<MessagesDTO> result = new ArrayList<>();
			while(rs.next()) {
				int seq = rs.getInt("seq");
				String name = rs.getString("name");
				String msg = rs.getString("msg");
				Timestamp write_date = rs.getTimestamp("write_date");
				MessagesDTO dto = new MessagesDTO(seq,name,msg,write_date);
				result.add(dto);
			}
			return result;
		}
	}
	
}

Delete

1. html 수정

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Index</title>
        <script src="https://code.jquery.com/jquery-3.5.0.js"></script>
        <style>   
            fieldset{
                text-align: center;
            }
        </style>
        <script>
            $(function(){
                $("#toInput").on("click",function(){
                    location.href="input.html";
                })
                $("#toOutput").on("click",function(){
                    location.href="Output";
                })
                $("#toDelete").on("click",function(){
                    location.href="Delete";
                })
            })
        </script>
    </head>
    <body>
        <fieldset>
            <legend>Index</legend>
            <button id="toInput">toInput</button>
            <button id="toOutput">toOutput</button>
            <button id="toDelete">toDelete</button>
        </fieldset>
    </body>
</html>

2. Delete 서블릿 생성

package kh.backend.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

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

import kh.backend.dao.MessagesDAO;
import kh.backend.dto.MessagesDTO;


@WebServlet("/Delete")
public class Delete extends HttpServlet {
	private static final long serialVersionUID = 1L;




	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		MessagesDAO dao = new MessagesDAO();
		PrintWriter pw = response.getWriter();
		try {
			List<MessagesDTO> result = dao.selectAll();
			pw.append("<html>");
			pw.append("<head>");
			pw.append("</head>");
			pw.append("<body>");
			pw.append("<table border=1 align=center>");
			pw.append("<tr>");
			pw.append("<td>Seq");
			pw.append("<td>Writer");
			pw.append("<td>Contents");
			pw.append("<td>Date");
			pw.append("</tr>");
			for(MessagesDTO dto : result) {
				pw.append("<tr>");
				pw.append("<td>"+dto.getSeq());
				pw.append("<td>"+dto.getName());
				pw.append("<td>"+dto.getMsg());
				pw.append("<td>"+dto.getWrite_date());
				pw.append("</tr>");
			}
			pw.append("</table>");
			pw.append("<button id=back>back</button>");
			pw.append("<script>document.getElementById('back').onclick = function(){location.href='index.html';}</script>");
			pw.append("<form>");
			pw.append("<input type=text name=input_seq placeholder=\"Input del seq_num\">");
			pw.append("<input type=submit>");
			pw.append("</form>");
			pw.append("</body>");
			pw.append("</html>");
			String seq = request.getParameter("input_seq");
			if(seq != null) {
				int del_result = dao.delete(seq);
				if(del_result>0) {
					pw.append("<script>");
					pw.append("alert('Delete Complete');");
					pw.append("location.href='index.html';");
					pw.append("</script>");
				}
			}
		}catch(Exception e) {
			e.printStackTrace();
			response.sendRedirect("error.html");
		}
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

}

3. DAO에 삭제기능 추가

	public int delete(String seq) throws Exception {

		String sql = "delete from messages where seq=?";
		try(Connection con = this.getConnection();	
				PreparedStatement pstat = con.prepareStatement(sql);) {
			pstat.setString(1, seq);
			int result = pstat.executeUpdate();
			con.commit();
			return result;
		}
	}

Update

1. index.html 수정

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Index</title>
        <script src="https://code.jquery.com/jquery-3.5.0.js"></script>
        <style>   
            fieldset{
                text-align: center;
            }
        </style>
        <script>
            $(function(){
                $("#toInput").on("click",function(){
                    location.href="input.html";
                })
                $("#toOutput").on("click",function(){
                    location.href="Output";
                })
                $("#toDelete").on("click",function(){
                    location.href="Delete";
                })
                 $("#toModify").on("click",function(){
                    location.href="Modify";
                })
            })
        </script>
    </head>
    <body>
        <fieldset>
            <legend>Index</legend>
            <button id="toInput">toInput</button>
            <button id="toOutput">toOutput</button>
            <button id="toDelete">toDelete</button>
            <button id="toModify">toModify</button>
        </fieldset>
    </body>
</html>

2. 서블릿 생성

package kh.backend.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

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

import kh.backend.dao.MessagesDAO;
import kh.backend.dto.MessagesDTO;


@WebServlet("/Modify")
public class Modify extends HttpServlet {
	private static final long serialVersionUID = 1L;


	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		MessagesDAO dao = new MessagesDAO();
		PrintWriter pw = response.getWriter();
		try {
			List<MessagesDTO> result = dao.selectAll();
			pw.append("<html>");
			pw.append("<head>");
			pw.append("</head>");
			pw.append("<body>");
			pw.append("<table border=1 align=center>");
			pw.append("<tr>");
			pw.append("<td>Seq");
			pw.append("<td>Writer");
			pw.append("<td>Contents");
			pw.append("<td>Date");
			pw.append("</tr>");
			for(MessagesDTO dto : result) {
				pw.append("<tr>");
				pw.append("<td>"+dto.getSeq());
				pw.append("<td>"+dto.getName());
				pw.append("<td>"+dto.getMsg());
				pw.append("<td>"+dto.getWrite_date());
				pw.append("</tr>");
			}
			pw.append("</table>");
			pw.append("<button id=back>back</button>");
			pw.append("<script>document.getElementById('back').onclick = function(){location.href='index.html';}</script>");

			pw.append("<form>");
			pw.append("<input type=text name=input_seq placeholder=\"Target seq_num\">");
			pw.append("<input type=text name=input_name placeholder=\"Modify name\">");
			pw.append("<input type=text name=input_msg placeholder=\"Modify msg\">");
			
			
			
			pw.append("<input type=submit>");
			pw.append("</form>");

			pw.append("</body>");
			pw.append("</html>");

			String seq = request.getParameter("input_seq");
			String name = request.getParameter("input_name");
			String msg = request.getParameter("input_msg");

			if(seq != null) {
				int del_result = dao.update(seq,name,msg);
				if(del_result>0) {
					pw.append("<script>");
					pw.append("alert('Update Complete');");
					pw.append("location.href='index.html';");
					pw.append("</script>");
				}
			}

		}catch(Exception e) {
			e.printStackTrace();
			response.sendRedirect("error.html");
		}

	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

}

3. DAO에 업데이트 기능 추가

	public int update(String seq, String name, String msg) throws Exception {

		String sql = 
				"update messages set name = ? , msg=?  where seq=?";
		try(Connection con = this.getConnection();	
				PreparedStatement pstat = con.prepareStatement(sql);) {
			pstat.setString(1, name);
			pstat.setString(2, msg);
			pstat.setString(3, seq);
			int result = pstat.executeUpdate();
			con.commit();
			return result;
		}
	}

'디지털 컨버전스 > JSP' 카테고리의 다른 글

[JSP] MVC 모델 (0)	2020.04.28
[JSP] Delete 기능 (0)	2020.04.28
[JSP] output 기능 (0)	2020.04.27
[JSP] input 기능 (0)	2020.04.27
[JSP] JSP 시작 (0)	2020.04.27

끄적끄적

[Servlet] CRUD

Create / Read

Delete

'디지털 컨버전스 > JSP' 카테고리의 다른 글

+ Recent posts

티스토리툴바