Messages DB input output 만들기

Servlet, JSP/JSP

Messages DB input output 만들기

구리Guri 2020. 4. 27. 17:52

index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script src="https://code.jquery.com/jquery-3.5.0.js"></script>
<style>
fieldset{width:500px;margin:auto;}
</style>
</head>
<body>
	<fieldset>
		<legend>Index</legend>
		<button id="toInput">toInput</button>
		<button id="toOutput">toOutput</button>
	</fieldset>
	
	<script>
	
		$("#toInput").on("click",function(){
			location.href ="input.jsp";
		})
		
		$("#toOutput").on("click",function(){
			location.href ="output.jsp";
		})
	</script>
</body>
</html>

input.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script src="https://code.jquery.com/jquery-3.5.0.js"></script>
<style>
fieldset{width:500px;margin:auto;}
</style>
</head>
<body>
	<form action="inputProc.jsp" 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>

inputProc.jsp

<%@page import="backend.dao.MessagesDAO"%>
<%@page import="backend.dto.MessagesDTO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script src="https://code.jquery.com/jquery-3.5.0.js"></script>
</head>
<body>
	<%
		String name = request.getParameter("name");
		String msg = request.getParameter("msg");
		MessagesDAO dao= new MessagesDAO();
		int result=dao.insert(new MessagesDTO(0,name,msg,null));
		
		//scriptlet의 성질을 이용하여서 Scriptlet을 열고 닫아서 처리하면
		//이용자는 자신에게 해당하는 부분만 받게 됨.
		if(result>0){
			%>
			<script>
				alert("Input Complete");
				location.href="index.jsp";
			</script>
			<%
		}else{
			%>
			<script>
				alert("Input Failed");
				location.href="index.jsp";
			</script>
			<%
		}
	%>
	<!-- 
	<script>
	if(< % =result% > >0){
		//result 안에 있는 값이 0 보다 크다면~
		//불필요한 코드가 클라이언트에게 가는 거라서 그다지 선호되는 방법은 아님 (상황에 따라서는 사용할 수도 있음.)
		alert("Input complete");
	}else{
		alert("Input Fail.");
	}
	</script>
	 -->
</body>
</html>

output.jsp

<%@page import="java.util.List"%>
<%@page import="backend.dto.MessagesDTO"%>
<%@page import="java.io.PrintWriter"%>
<%@page import="backend.dao.MessagesDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script src="https://code.jquery.com/jquery-3.5.0.js"></script>
</head>
<body>
<table border=1 align=center>
<tr>
<td>Seq</td>
<td>Writer</td>
<td>Contents</td>
<td>Date</td>
</tr>
<% 
MessagesDAO dao = new MessagesDAO();
PrintWriter pw = response.getWriter();
try{
List<MessagesDTO> result = dao.selectAll();
for(MessagesDTO dto:result){
%>
<tr>
<td> 
<%= dto.getSeq() %> 
</td> 
<td> 
<%= dto.getName() %> 
</td>
<td> 
<%= dto.getMsg() %> 
</td>
<td> 
<%= dto.getWrite_date() %> 
</td>
</tr>
<% 
}
}catch(Exception e){
	e.printStackTrace();
%>
	<script> alert('result is null');location.href='index.jsp';</script>
<%
}
%>

<tr><td colspan=4><button id=back>back</button>
<script>
document.getElementById('back').onclick=function(){
	location.href='index.jsp';
}</script>
</td></tr>
</table>
</body>
</html>

MessagesDAO.java

package 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 = "";
		String pw = "";
		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;
		}
	}
	
	public int delete(int seq) throws Exception {
		String sql = "delete from messages where seq=?";
		
		try(Connection con = this.getConnection();
			PreparedStatement pstat = con.prepareStatement(sql);
		){
			pstat.setInt(1,seq);
			int result = pstat.executeUpdate();
			con.commit();
			return result;
		}	
	}
	
	public int modify(int 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.setInt(3, seq);
			int result = pstat.executeUpdate();
			con.commit();
			return result;
		}
		
				
		
	}
}

MessagesDTO.java

package 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;
	}
	
}