JSP 강의 정리 15일차

[1] 테스트 환경

hardware

cpu : intel i7-7700, gpu : intel hd 630, ram : ddr4 16GB, mainboard : samsung DB400S7A-Z51, secondary storage : 256GB SSD, 1TB HDD

OS

windows pro 64bits

software

jdk version 1.8.0_301 eclipse version 2021-09 (4.21.0) apache-tomcat version 9.0.54

 

[2] 강의 정리

()

sql 문장

create table memo (
    memoId number(4) primary key,
    nickName nvarchar2(2) not null,
    contents nvarchar2(200) not null,
    regTime timestamp
);

commit;

create sequence memo_seq -- 시퀸스 (자동으로 증가하는 순번 객체)
START WITH 1 -- 시퀸스 값 1부터
INCREMENT BY 1 -- 1씩증가
NOMAXVALUE -- 시퀀스의 범위를 무한대로 지정
NOCACHE; -- 캐시값 없애기

commit;

 

com.jsp.memo

MemoDAO.java

package com.jsp.memo;

import java.sql.*;
import java.util.ArrayList;

public class MemoDAO {
	
	// 싱글톤 패턴으로 하기 
	// 전역 변수로 Connection PreparedStatement ResultSet 선언
	// 전역 변수로 url, id, pw, driver 선언하기
	
	private MemoDAO() {}
	
	private static MemoDAO instance = new MemoDAO();
	
	public static MemoDAO getInstance() {
		return instance;
	}
	
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;
	
	private String driver = "oracle.jdbc.OracleDriver";
	private String url = "jdbc:oracle:thin:@localhost:1521:xe";
	private String uid = "C##JSPUSER";
	private String upw = "jsp123";
	
	// 데이터를 넣는 메소드
	public int insertMemo(String nickName, String contents, Timestamp regTime) {
		int result = 0;
		
		String query = "insert into memo (memoId, nickName, contents, regTime) "
				+ "values (memo_seq.nextval, ?, ?, ?)";
		
		try {
			
			Class.forName(driver);
			conn = DriverManager.getConnection(url, uid, upw);
			pstmt = conn.prepareStatement(query);
			
			pstmt.setString(1, nickName);
			pstmt.setString(2, contents);
			pstmt.setTimestamp(3, regTime);
			
			result = pstmt.executeUpdate();
			
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			
			try {
				
				if(pstmt != null)pstmt.close();
				if(conn != null)conn.close();
				
			}catch(Exception e2) {}
			
		}
		
		
		return result;
	}
	
	
	// 데이터 얻는 메소드
	public ArrayList<MemoDTO> memoList(){
		
		ArrayList<MemoDTO> list = new ArrayList<MemoDTO>();
		
		String query = "select * from memo";
		
		try {
			
			Class.forName(driver);
			conn = DriverManager.getConnection(url, uid, upw);
			pstmt = conn.prepareStatement(query);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				
				int memoId = rs.getInt("memoId");
				String nickName = rs.getString("nickName");
				String contents = rs.getString("contents");
				Timestamp regTime = rs.getTimestamp("regTime");
				
				list.add(new MemoDTO(memoId, nickName, contents, regTime));
				
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			
			try {
				
				if(rs != null)rs.close();
				if(pstmt != null)pstmt.close();
				if(conn != null)conn.close();
				
			}catch(Exception e2) {}
			
		}
		
		return list;
	}
	
	//데이터를 삭제하는 메소드
	public int deleteMemo(int memoId) {
		int result = 0;
		
		String query = "delete from memo where memoId = ?";
				
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, uid, upw);
			pstmt = conn.prepareStatement(query);
			
			pstmt.setInt(1,  memoId);
			
			result = pstmt.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(pstmt != null)pstmt.close();
				if(conn != null)conn.close();
				
			}catch(Exception e2) {}
		}
	
	
	return result;
	
}
}

 

com.jsp.memo

MemoDTO.java

package com.jsp.memo;

import java.sql.Timestamp;

public class MemoDTO {
	
//	memoId number(4) primary key,
//	nickName nvarchar2(20) not null,
//	contents nvarchar2(200) not null,
//	regTime timestamp
	
	private int memoId;
	private String nickName;
	private String contents;
	private Timestamp regTime;
	
	public MemoDTO() {}

	public MemoDTO(int memoId, String nickName, String contents, Timestamp regTime) {
		super();
		this.memoId = memoId;
		this.nickName = nickName;
		this.contents = contents;
		this.regTime = regTime;
	}

	public int getMemoId() {
		return memoId;
	}

	public void setMemoId(int memoId) {
		this.memoId = memoId;
	}

	public String getNickName() {
		return nickName;
	}

	public void setNickName(String nickName) {
		this.nickName = nickName;
	}

	public String getContents() {
		return contents;
	}

	public void setContents(String contents) {
		this.contents = contents;
	}

	public Timestamp getRegTime() {
		return regTime;
	}

	public void setRegTime(Timestamp regTime) {
		this.regTime = regTime;
	}
	
	
	
}

 

memoForm.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>

<style>
	h2, form {
		text-align: center;
		margin: 0 auto;
	} 
</style>

</head>
<body>
	
	<!-- 
		form 태그로 메모를 입력하는 폼 만들기
		(닉네임, 메모작성글) 입력받기
		memoCheck.jsp로 전송하기 
	 -->
	
	<h2>나의 메모장</h2>
	<br>
	<form action="memoCheck.jsp" method="post">
		
		닉네임 <br> <input type="text" name="nickName" size="10"><br>
		<br>
		메모작성 <br>
		<textarea name="contents" rows="5" cols="20"></textarea>
		<br>
		
		<input type="submit" value="메모등록"> 
		
	</form>
	
</body>
</html>

 

memoCheck.jsp

<%@page import="com.jsp.memo.MemoDAO"%>
<%@page import="java.sql.Timestamp"%>
<%@ 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>
</head>
<body>
	
	<%--
		1. 넘어온 데이터 받기
		2. 현재시간으로 Timestamp 객체 생성하기
		3. DAO 객체 생성 후 insertMemo 메소드 호출하기
		4. 결과값을 받은 후 메모가 잘 들어갔다면 memoList 로 페이지 이동하기
		5. 잘 들어가지 않았다면 memoForm으로 페이지 이동하기
	 --%>
	
	<%
		request.setCharacterEncoding("UTF-8");
	
		String nickName = request.getParameter("nickName");
		String contents = request.getParameter("contents");
		
		Timestamp regTime = new Timestamp(System.currentTimeMillis());
		// 현재 시간으로 Timestamp 객체 생성
		
		MemoDAO dao = MemoDAO.getInstance();
		
		int result = dao.insertMemo(nickName, contents, regTime);
		
		if(result == 1){
	%>
			<script>
				alert('메모가 등록되었습니다.');
				window.location = 'memoList.jsp';
			</script>
	
	<%
		}else{
	%>
			<script>
				alert('메모가 등록되지 않았습니다.');
				window.location = 'memoForm.html';
			</script>
	<%
		}
	%>
	
	
</body>
</html>

 

memoList.jsp

<%@page import="java.text.SimpleDateFormat"%>
<%@page import="java.sql.Timestamp"%>
<%@page import="com.jsp.memo.MemoDTO"%>
<%@page import="java.util.ArrayList"%>
<%@page import="com.jsp.memo.MemoDAO"%>
<%@ 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>

<style type="text/css">

	table, th, td{
		border : 1px solid black;
		border-collapse: collapse;
		margin: 0 auto;
	}
	
	table{
		width: 700px;
	}
	
	th, td {
		text-align : center;
		height: 50px;
	}
	
	h1, h3{
		text-align : center;
	}
	
</style>

</head>
<body>
	
	<%--
		<table> 태그를 사용해서 메모장 만들기
		1. DAO객체 생성하기
		2. memoList 메소드 호출하여 데이터 얻기 (ArrayList)
		3. 닉네임 메모작성글 작성일자 (삭제) 
	 --%>
	
	<h1>나의 메모장</h1>
	
	<%
		MemoDAO dao = MemoDAO.getInstance();
		ArrayList<MemoDTO> list = dao.memoList();
	%>
	
	<table>
		
		<tr>
			<th>닉네임</th>
			<th>메모</th>
			<th>메모작성일</th>
			<th>메모삭제</th>
		</tr>
		
		<%
			for(int i = 0; i < list.size(); i++){
				
				MemoDTO dto = list.get(i);
				
				Timestamp regTime = dto.getRegTime();
				SimpleDateFormat format = new SimpleDateFormat("MM월 dd일");
				
				String date = format.format(regTime);
				
		%>
				
		<tr>
			<td><%=dto.getNickName() %></td>
			<td><%=dto.getContents() %></td>
			<td><%=date %></td>
			<td><a href="memoDelete.jsp?memoId=<%=dto.getMemoId()%>">삭제</a></td>
		</tr>
		
		
		<%
			}
		%>
		
	</table>
	
	<h3><a href="memoForm.html">메모작성</a></h3>
	
</body>
</html>

 

memoDelete.jsp

<%@page import="com.jsp.memo.MemoDAO"%>
<%@ 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>
</head>
<body>
	
	<%
		String strId = request.getParameter("memoId");
	
		int memoId = Integer.parseInt(strId);
		
		MemoDAO dao = MemoDAO.getInstance();
		
		int result = dao.deleteMemo(memoId);
		
		if(result == 1){
	%>
			<script>
				alert('메모가 삭제되었습니다.');
				window.location = 'memoList.jsp';
			</script>
	<%
		}else{
	%>
			<script>
				alert('메모가 삭제되지 않았습니다.');
				window.location = 'memoList.jsp';
			</script>
	<%
		}
	%>
	
	
</body>
</html>