TIL 0502

[실습] 게시판

Board Table 생성

CREATE TABLE mboard(
num number not null,
title varchar2(150) not null,
name varchar2(30) not null,
passwd varchar2(12) not null,
content clob not null,
ip varchar2(30) not null,
reg_date date default SYSDATE not null,
constraint mboard_pk primary key(num)
);
CREATE SEQUENCE mboard_seq;

Board VO

package kr.board.vo;

import java.sql.Date;

public class BoardVO {
	private int num;
	private String title;
	private String name;
	private String passwd;
	private String content;
	private String ip;
	private Date reg_date;
	
	// 비밀번호 체크
	public boolean isCheckedPassword(String userPasswd) {
		if(passwd.equals(userPasswd)) { // 비밀번호 일치
			return true;
		}
		// 비밀번호 불일치
		return false;
	}
	 
	public int getNum() {
		return num;
	}
	public void setNum(int num) {
		this.num = num;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPasswd() {
		return passwd;
	}
	public void setPasswd(String passwd) {
		this.passwd = passwd;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public String getIp() {
		return ip;
	}
	public void setIp(String ip) {
		this.ip = ip;
	}
	public Date getReg_date() {
		return reg_date;
	}
	public void setReg_date(Date reg_date) {
		this.reg_date = reg_date;
	}

}

Board DAO

package kr.board.dao;

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


import kr.board.vo.BoardVO;
import kr.util.DBUtil;

public class BoardDAO {
	private static BoardDAO instance = new BoardDAO();
	public static BoardDAO getInstance() {
		return instance;
	}
	
	private BoardDAO() {
		
	}
	
	// 글 저장하기
	public void insert(BoardVO boardVO) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		
		try {
			conn = DBUtil.getConnection();
			sql = "INSERT INTO mboard(num,title,name,passwd,content,ip) VALUES(mboard_seq.nextval,?,?,?,?,?)";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, boardVO.getTitle());
			pstmt.setString(2, boardVO.getName());
			pstmt.setString(3, boardVO.getPasswd());
			pstmt.setString(4, boardVO.getContent());
			pstmt.setString(5, boardVO.getIp());
			
			pstmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e);
		}finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}
	
	//글의 총 개수
	public int getCount() throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql =null;
		int count = 0;
		try {
			conn = DBUtil.getConnection();
			sql="SELECT COUNT(*) FROM mboard";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				// 컬럼 인덱스를 사용하면 된다.하나만 있으면 어차피 1이기 때문에
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			throw new Exception(e);
		}finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}
		
		return count;
	}
	
	// 글 목록 작업
	public List<BoardVO> getList(int startRow, int endRow) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<BoardVO> list= null;
		String sql =null;
		try {
			conn = DBUtil.getConnection();
			sql = "SELECT * FROM (SELECT ROWNUM rnum,a.* FROM (SELECT * FROM mboard order by num asc )a) WHERE rnum>=? and rnum<=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, startRow);
			pstmt.setInt(2, endRow);
			rs = pstmt.executeQuery();
			list = new ArrayList<BoardVO>();
			while (rs.next()) {
				BoardVO boardVO = new BoardVO();
				boardVO.setNum(rs.getInt("num"));
				boardVO.setTitle(rs.getString("title"));
				boardVO.setName(rs.getString("name"));
				boardVO.setReg_date(rs.getDate("reg_date"));
				
				// 자바빈을 arraylist에 저장하는 작업이 필요함
				list.add(boardVO);
			}
		} catch (Exception e) {
			throw new Exception(e);
		}finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}
		return list;
	}
	
	// 글 상세 보기
	public BoardVO getBoard(int num) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		BoardVO board = null;
		try {
			conn = DBUtil.getConnection();
			sql ="SELECT * FROM mboard WHERE num=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				board = new BoardVO();
				board.setNum(rs.getInt("num"));
				board.setTitle(rs.getString("title"));
				board.setName(rs.getString("name"));
				board.setPasswd(rs.getString("passwd"));
				board.setContent(rs.getString("content"));
				board.setReg_date(rs.getDate("reg_date"));
			}
		} catch (Exception e) {
			throw new Exception(e);
		}finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}
		return board;
	}
	
	// 글 수정하기
	public void update(BoardVO boardVO) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try {
			conn = DBUtil.getConnection();
			sql = "UPDATE mboard SET title=?,name=?,content=?,ip=? WHERE num=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, boardVO.getTitle());
			pstmt.setString(2, boardVO.getName());
			pstmt.setString(3, boardVO.getContent());
			pstmt.setString(4, boardVO.getIp());
			pstmt.setInt(5, boardVO.getNum());
			pstmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e);
		}finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}
	
	// 글 삭제하기
	public void delete(int num) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try {
			conn=DBUtil.getConnection();
			sql="DELETE FROM mboard WHERE num=?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			pstmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e);
		}finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}
}

글 작성 Form

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글 작성</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
<script type="text/javascript" src="<%= request.getContextPath() %>/js/jquery-3.7.1.min.js"></script>
<script type="text/javascript">
window.onload = function () {
	const form = document.getElementById('write_form');
	form.onsubmit = function () {
		const items = document.querySelectorAll('input[type="text"],input[type="password"],textarea')
		for(let i = 0; i < items.length; i++){
			if(items[i].value.trim() == ''){
				const label = document.querySelector('label[for="'+items[i].id+'"]');
				alert(label.textContent + ' 항목은 필수 입력하셔야 합니다.');
				
				items[i].value='';
				items[i].focus();
				return false;
			}	// end of if
		} // end of for
	};
};
</script>
</head>
<body>
<div class="page-main"> 
<h1>글 작성</h1>
<form action="write.jsp" id="write_form" method="post">
<ul>
	<li>
		<label for="title">제목</label>
		<input type="text" id="title" name="title" size="30" maxlength="50">
	</li>
	<li>
		<label for="name">작성자</label>
		<input type="text" id="name" name="name" size="10" maxlength="10">
	</li>
	<li>
		<label for="passwd">비밀번호</label>
		<input type="password" id="passwd" name="passwd" size="10" maxlength="12">
	</li>
	<li>
		<label for="content">내용</label>
		<textarea rows="5" cols="40" id="content" name="content"></textarea>
	</li>
</ul>
	<div class="align-center">
		<input type="submit" value="등록">
		<input type="button" value="목록" onclick="location.href='list.jsp'">
	</div>
</form>
</div>
</body>
</html>

글 작성 JSP

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="kr.board.dao.BoardDAO"%>
<%
	request.setCharacterEncoding("UTF-8");
%>
<jsp:useBean id="board" class="kr.board.vo.BoardVO"/>
<jsp:setProperty property="*" name="board"/>
<%	
	// 클라이언트의 ip 주소 저장
	board.setIp(request.getRemoteAddr());
	// dao를 변수명으로 지정하고, getinstance를 호출시켜 연결시킴
	BoardDAO dao = BoardDAO.getInstance();
	//dao에서 insert메서드를 호출하는데 board를 인자로 받아와라
	dao.insert(board);
%> 
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글 등록</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
</head>
<body>
	<div class="page-main">
		<h1>글 등록 완료</h1>
		<div class="result-display">
			<div class="align-center">
				글 등록 성공!
				<p>
				<button onclick="location.href='list.jsp'">글 목록</button>
			</div>
		</div>
	</div>
</body>
</html>

글 목록 JSP

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="kr.board.dao.BoardDAO" %>
<%@ page import="kr.board.vo.BoardVO" %>
<%@ page import="java.util.List" %>
<%@ page import="kr.util.PagingUtil"%>

<%
	// 선택한 페이지 번호
	String pageNum = request.getParameter("pageNum");
// 최초에 list.jsp를 호출하면 pageNum을 전달할 수 없기 때문에 null이 되고 연산할 때 연산이 되지 않는 문제가 있어서 최초 호출시 무조건 1페이지로 설정
	if(pageNum == null){
		pageNum="1";
	}
	// 한 화면에 몇 개의 글(행,레코드)를 보여줄지 지정
	int rowCount = 10;
	// 한 화면에 몇 개의 페이지 수를 보여줄 지를 지정
	int pageCount = 10;
	
	// 현재 선택한 페이지 String -> int
	int currentPage = Integer.parseInt(pageNum);
	
	BoardDAO dao = BoardDAO.getInstance();
	int count = dao.getCount();
	
	PagingUtil util = new PagingUtil(currentPage,count,rowCount,pageCount,"list.jsp");
	
	List<BoardVO> list = null;
	if(count > 0){
		list = dao.getList(util.getStartRow(), util.getEndRow());
	}

%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>게시판 목록</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
</head>
<body>
<div class="page-main">
	<h1>게시판 목록</h1>
	<div class="align-right">
		<input type="button" value="글 작성" onclick="location.href='writeForm.jsp'">
	</div>
	
<%
	if(count == 0){
%>
	<div class="result-display">저장된 글이 없습니다.</div>
<%	} 
	else{
%>
	<!-- 목록 출력 시작 -->
	<table>
		<tr>
			<th>글 번호</th>
			<th>제목</th>
			<th>작성자</th>
			<th>작성일</th>
		</tr>
<%
	for(BoardVO boardVO : list){
%>
		<tr>
			<td><%=boardVO.getNum() %></td>
			<td><a href="detail.jsp?num=<%=boardVO.getNum()%>"><%=boardVO.getTitle() %></a></td>
			<td><%=boardVO.getName() %></td>
			<td><%=boardVO.getReg_date() %></td>
		</tr>
<%
	}
%>
	
	</table>
<%} %>
	<!-- 목록 출력 끝 -->
	<!-- 페이지 표시 시작 -->
	<div class="align-center">
	<%= util.getPage() %>
	</div>
	<!-- 페이지 표시 끝 -->
</div>
</body>
</html>

글 정보 JSP

 <%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="kr.board.dao.BoardDAO"%>
<%@ page import="kr.board.vo.BoardVO"%>

<%
	int num = Integer.parseInt(request.getParameter("num"));
	BoardDAO dao = BoardDAO.getInstance();
	BoardVO board = dao.getBoard(num);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글 상세 정보</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
</head>
<body>
	<div class="page-main">
		<h1>게시판 글 상세</h1>
		<ul>
			<li>글 번호 : <%=board.getNum() %></li>
			<li>제목 : <%=board.getTitle() %></li>
			<li>작성자 : <%=board.getName() %></li>
		</ul>
		<hr width="100%" size="1" noshade="noshade">
			<p> 
				<%=board.getContent() %>
			</p>
		<hr width="100%" size="1" noshade="noshade">
		
		<div class="align-right">
			작성일 : <%=board.getReg_date() %>
			<input type="button" value="수정" onclick="location.href='updateForm.jsp?num=<%=board.getNum()%>'">
			<input type="button" value="삭제" onclick="location.href='deleteForm.jsp?num=<%=board.getNum()%>'">
			<input type="button" value="목록" onclick="location.href='list.jsp'">
		</div>
	</div>
</body>
</html>
 

글 수정 Form

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="kr.board.dao.BoardDAO"%>
<%@ page import="kr.board.vo.BoardVO"%>
<% 
	int num = Integer.parseInt(request.getParameter("num"));
	BoardDAO dao = BoardDAO.getInstance();
	BoardVO board = dao.getBoard(num);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글 수정</title>
<link rel="stylesheet"
	href="<%=request.getContextPath()%>/css/style.css">
<script type="text/javascript"
	src="<%=request.getContextPath()%>/js/jquery-3.7.1.min.js"></script>
<script type="text/javascript">
	window.onload = function() {
		const form = document.getElementById('update_form');
		form.onsubmit = function() {
			const items = document
					.querySelectorAll('input[type="text"],input[type="password"],textarea')
			for (let i = 0; i < items.length; i++) {
				if (items[i].value.trim() == '') {
					const label = document.querySelector('label[for="'
							+ items[i].id + '"]');
					alert(label.textContent + ' 항목은 필수 입력하셔야 합니다.');

					items[i].value = '';
					items[i].focus();
					return false;
				} // end of if
			} // end of for
		};
	};
</script>
</head>
<body>
	<div class="page-main">
		<h1>글 작성</h1>
		<form action="update.jsp" id="update_form" method="post">
			<input type="hidden" value="<%=num%>" name="num"> 
			<ul>
				<li>
					<label for="title">제목</label> 
					<input type="text" id="title" name="title" size="30" maxlength="50" value="<%=board.getTitle()%>">
				</li>
				<li>
					<label for="name">작성자</label> 
					<input type="text" id="name" name="name" size="10" maxlength="10" value="<%=board.getName()%>">
				</li>
				<li>
					<label for="passwd">비밀번호</label> 
					<input type="password"  id="passwd" name="passwd" size="10" maxlength="12">
				</li>
				<li>
					<label for="content">내용</label> 
					<textarea rows="5" cols="40" id="content" name="content"><%=board.getContent()%></textarea>
				</li>
			</ul>
			<div class="align-center">
				<input type="submit" value="수정"> 
				<input type="button" value="목록" onclick="location.href='list.jsp'">
			</div>
		</form>
	</div>
</body>
</html>

글 수정 JSP

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="kr.board.dao.BoardDAO"%>
<%@ page import="kr.board.vo.BoardVO" %>
<%
	// 인코딩 타입 지정
	request.setCharacterEncoding("UTF-8");
%>
<jsp:useBean id="boardVO" class="kr.board.vo.BoardVO"/>
<jsp:setProperty property="*" name="boardVO"/>
<%
	// useBean의 id하고 setProperty의 name을 일치시켜야함
	BoardDAO dao = BoardDAO.getInstance();

	// 비밀번호 인증을 위해서 기본키를 전달하고 한 건의 레코드를 전달 받음
	BoardVO db_board = dao.getBoard(boardVO.getNum());
	
	boolean check = false;
	if(db_board!=null){
		check = db_board.isCheckedPassword(boardVO.getPasswd());
	}
	if(check){
		boardVO.setIp(request.getRemoteAddr());
		dao.update(boardVO);
%>
<script type="text/javascript">
	alert("글 수정이 완료되었습니다.");
	location.href='detail.jsp?num=<%=boardVO.getNum()%>'
</script>
<%
	} else{
%>
<script type="text/javascript">
	alert('비밀번호 불일치');
	history.go(-1);
</script>
<%
	}
%>

글 삭제 Form

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	int num = Integer.parseInt(request.getParameter("num"));
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글 삭제</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
<script type="text/javascript">
window.onload=function(){
	const myForm = document.getElementById('delete_form');
	// 이벤트 연결
	myForm.onsubmit=function(){
		const passwd = document.getElementById('passwd');
		if(passwd.value.trim()==''){
			alert('비밀번호를 입력하세요');
			passwd.value='';
			passwd.focus();
			return false;
		}
	}
}
</script>
</head>
<body>
<div class="page-main">
	<form action="delete.jsp" method="post" id="delete_form">
			<input type="hidden" name="num" value="<%=num%>">
			<ul>
				<li>
				<label for="passwd">비밀번호</label>
				<input type="password" name="passwd" id="passwd" size="12" maxlength="12">
				</li>
			</ul>
			<div class="align-center">
				<input type="submit" value="삭제">
				<input type="button" value="목록" onclick="location.href='list.jsp'">
			</div>
		</form>
</div>	
</body>
</html>

글 삭제 JSP

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="kr.board.dao.BoardDAO"%>
<%@ page import="kr.board.vo.BoardVO" %>
<%
	// 인코딩 타입 지정
	request.setCharacterEncoding("UTF-8");
	int num = Integer.parseInt(request.getParameter("num"));
	String passwd = request.getParameter("passwd");
	
	BoardDAO dao = BoardDAO.getInstance();
	// 비밀번호 인증을 위해 기본키를 전달해서 한 건의 레코드를 전달 받음
	BoardVO db_board = dao.getBoard(num);
	
	boolean check = false;
	if(db_board!=null){
		// 비밀번호 체크
		check = db_board.isCheckedPassword(passwd);
	}
	if(check){
		dao.delete(num);
%>
<script type="text/javascript">
	alert("글 삭제가 완료되었습니다.");
	location.href='list.jsp';
</script>
<%
	} else{
%>
<script type="text/javascript">
	alert('비밀번호 불일치');
	history.go(-1);
</script>
<%
	}
%>

---

[실습] 사원 등록

Employee Table 생성

CREATE TABLE semployee(
num number primary key,
id varchar2(12) unique not null,
name varchar2(30) not null,
passwd varchar2(12) not null,
salary number(8) not null,
job varchar2(30) not null,
reg_date date default sysdate not null
);
CREATE sequence semployee_seq;

Employee VO

package kr.employee.vo;

import java.sql.Date;

public class EmployeeVO {
	private int num;
	private String id;
	private String name;
	private String passwd;
	private int salary;
	private String job;
	private Date reg_date;
	
	// 비밀번호 체크
	public boolean isCheckedPassword(String userPasswd) {
		if(passwd.equals(userPasswd)) {// 비밀번호 일치
			return true;
		}
		return false;	// 비밀번호 불일치
	}
	
	public int getNum() {
		return num;
	}
	public void setNum(int num) {
		this.num = num;
	}
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPasswd() {
		return passwd;
	}
	public void setPasswd(String passwd) {
		this.passwd = passwd;
	}
	public int getSalary() {
		return salary;
	}
	public void setSalary(int salary) {
		this.salary = salary;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public Date getReg_date() {
		return reg_date;
	}
	public void setReg_date(Date reg_date) {
		this.reg_date = reg_date;
	}
	
	
}

Employee DAO

package kr.employee.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import kr.employee.vo.EmployeeVO;
import kr.member.vo.MemberVO;
import kr.util.DBUtil;

public class EmployeeDAO {

	private static EmployeeDAO instance = new EmployeeDAO();

	public static EmployeeDAO getInstance() {
		return instance;
	}

	private EmployeeDAO() {
	}

	// 사원 등록
	public void insertEmployee(EmployeeVO vo) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try {
			conn = DBUtil.getConnection();
			sql="INSERT INTO semployee(num,id,name,passwd,salary,job)VALUES(semployee_seq.nextval,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, vo.getId());
			pstmt.setString(2, vo.getName());
			pstmt.setString(3, vo.getPasswd());
			pstmt.setInt(4, vo.getSalary());
			pstmt.setString(5, vo.getJob());
			pstmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e);
		} finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}

	// 사원 상세 정보
	public EmployeeVO getEmployee(int num) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		EmployeeVO vo = null;
		try {
			conn = DBUtil.getConnection();
			sql = "SELECT * FROM semployee WHERE num=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				vo = new EmployeeVO();
				vo.setNum(rs.getInt("num"));
				vo.setId(rs.getString("id"));
				vo.setName(rs.getString("name"));
				vo.setPasswd(rs.getString("passwd"));
				vo.setSalary(rs.getInt("salary"));
				vo.setJob(rs.getString("job"));
				vo.setReg_date(rs.getDate("reg_date"));
			}
		} catch (Exception e) {
			throw new Exception(e);
		} finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}

		return vo;
	}
	
	// 아이디 중복 체크, 로그인 체크
	public EmployeeVO checkEmployee(String id) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		EmployeeVO vo = null;
		try {
			conn = DBUtil.getConnection();
			sql="SELECT * FROM semployee WHERE id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				vo = new EmployeeVO();
				vo.setId(rs.getString("id"));
				vo.setNum(rs.getInt("num"));
				vo.setPasswd(rs.getString("passwd"));
			}
		} catch (Exception e) {
			throw new Exception(e);
		} finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}

		return vo;	
	}

	// 사원 정보 수정
	public void updateEmployee(EmployeeVO vo) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try {
			conn = DBUtil.getConnection();
			sql="UPDATE semployee SET name=?,passwd=?,salary=?,job=? WHERE num=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, vo.getName());
			pstmt.setString(2, vo.getPasswd());
			pstmt.setInt(3, vo.getSalary());
			pstmt.setString(4, vo.getJob());
			pstmt.setInt(5, vo.getNum());
			pstmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e);
		} finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}

	// 사원 탈퇴
	public void deleteEmployee(int num) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try {
			conn = DBUtil.getConnection();
			sql="DELETE FROM semployee WHERE num=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			pstmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e);
		} finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}
}

Main Page

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
						<!-- 컨텍스트 경로로 지정해도 문제 없고, 컨텍스트 명이 바뀌더라도 문제 발생이 일어나지 않는다 -->
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
<title>사원 관리 Main</title>
</head>
<body>
	<%
		String user_id = (String)session.getAttribute("user_id");
	%>
	<div class="page-main">
	 <h1>사원관리 메인</h1>
	 <div class="align-right">
	 	<%
	 		if(user_id == null){	// 로그인 실패
	 	%>
	 		<a href="insertEmployeeForm.jsp">회원 가입</a>
	 		<a href="loginForm.jsp">로그인</a>
	 	<%
	 		} else{	// 로그인 성공
	 	%>
	 		<a href="myPage.jsp">마이페이지</a>
	 		<b><%=user_id %></b> 로그인 중
	 		<a href="logout.jsp">로그아웃</a>
	 	<%		
	 		}
	 	%>
	 </div>
	</div>
</body>
</html>

회원 가입 Form

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>사원 등록</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
<script type="text/javascript" src="<%= request.getContextPath() %>/js/jquery-3.7.1.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
	let count = 0;
	
	$('#confirm_id').click(function(){
		if($('#id').val().trim()==''){
			alert('ID를 입력하세요.');
			$('#id').val('').focus();
			return;
		}
		
		// 서버와 통신
		$.ajax({
			url:'confirmID.jsp',
			type:'post',
			dataType:'json',
			data:{id:$('#id').val()},
			success:function(param){
				if(param.result == 'idDuplicated'){
					count = 0;
					$('#id_signed').text('중복된 ID입니다.').css('color','red');
					$('#id').val('').focus();
				}else if(param.result == 'idNotFound'){
					count = 1;
					$('#id_signed').text('사용 가능한 ID입니다.').css('color','black');
				}else{
					count = 0;
					alert('ID 중복 체크 오류');
				}
			},
			error:function(){
				count = 0;
				alert('Network Error Occurred');
			}
		})
	});	//end of Click
	

	$('#insert_form #id').keydown(function() {
		count = 0;
		$('#id_signed').text('');
	}); // end of Keydown
	
	$('#insert_form').submit(function(){
	    const items = document.querySelectorAll('.input-check');
	    for(let i = 0; i < items.length; i++){
	        if(items[i].value.trim() == ''){
	            const label = document.querySelector('label[for="'+items[i].id+'"]');
	            alert(label.textContent + ' 필수 항목입니다.');
	            
	            items[i].value = '';
	            items[i].focus();
	            return false;
	        }
	        if(items[i].id == 'id' && count == 0){
	            alert('ID 중복 체크는 필수입니다.');
	            return false;
	        }
	    } // end of for
	});
});
</script>
</head>
<body>
<div class="page-main">
		<h1>사원 등록</h1>
		<form action="insertEmployee.jsp" method="post" id="insert_form">
			<ul>
				<li>
					<label for="id">ID</label>
					<input type="text" name="id" id="id"  size="7" maxlength="12" autocomplete="off" class="input-check">
					<input type="button" id="confirm_id" value="ID 중복 확인">
					<span id="id_signed"></span>
				</li>
				<li>
					<label for="name">이름</label>
					<input type="text" name="name" id="name" class="input-check" >
				</li>
				<li>
					<label for="passwd">PW</label>
					<input type="password" name="passwd" id="passwd" class="input-check" maxlength="12">
				</li>
				<li>
					<label for="salary">연봉</label>
					<input type="number" name="salary" id="salary" >
				</li>
				<li>
					<label for="job">직업</label>
					<input type="text" name="job" id="job" class="input-check">
				</li>
			</ul>
			<div class="align-center">
				<input type="submit" value="Register">
				<input type="button" value="Home" onclick="location.href='main.jsp'">
			</div>
		</form>
	</div>
</body>
</html>

회원 가입 JSP

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="kr.employee.dao.EmployeeDAO" %>
<%
	// 전송된 데이터 인코딩 타입 지정
	request.setCharacterEncoding("UTF-8");
%>
<jsp:useBean id="employee" class="kr.employee.vo.EmployeeVO" />
<jsp:setProperty property="*" name="employee" />
<%
	EmployeeDAO dao = EmployeeDAO.getInstance();
	dao.insertEmployee(employee);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>사원 등록</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
</head>
<body>
	<div class="page-main">
		<h1>사원 등록 완료</h1>
		<div class="result-display">
			<div class="align-center">
				등록되었습니다.
				<p>
				<button onclick="location.href='main.jsp'">Home</button>
			</div>
		</div>
	</div>
</body>
</html>

ID 중복 확인

<%@ page language="java" contentType="text/plain; charset=UTF-8"
	pageEncoding="UTF-8" trimDirectiveWhitespaces="true"%>
<%@ page import="kr.util.DBUtil"%>
<%@ page import="kr.employee.dao.EmployeeDAO"%>
<%@ page import="kr.employee.vo.EmployeeVO"%>
<%
	//전송된 데이터 인코딩 타입 지정
	request.setCharacterEncoding("UTF-8");
	// 전송된 데이터 반환
	String id = request.getParameter("id");
	
	EmployeeDAO dao = EmployeeDAO.getInstance();
	EmployeeVO member = dao.checkEmployee(id);
	if(member!=null){// 아이디 중복되었음
%>
{"result" : "idDuplicated"}
<%
	}else{	// 아이디 미중복
%>
{"result":"idNotFound"}
<%	
	}
%>

로그인 Form

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>사원 로그인</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
<script type="text/javascript" src="<%= request.getContextPath() %>/js/jquery-3.7.1.min.js"></script>
<script type="text/javascript">
 $(function(){
	 $('#login_form').submit(function(){
		if($('#id').val().trim()==''){
			alert('ID를 입력해주세요.');
			$('#id').val('').focus();
			return false;
		};
		if($('#passwd').val().trim()==''){
			alert('PW를 입력해주세요.');
			$('#passwd').val('').focus();
			return false;
		};
	 });
 });
 </script>
</head>
<body>
	<div class="page-main">
	<h1>로그인</h1>
		<form action="login.jsp" method="post" id="login_form">
		<ul>
			<li>
				<label for="id">ID</label>
				<input type="text" name="id" id="id" maxlength="12">
			</li>
			<li>
				<label for="passwd">PW</label> 
				<input type="password"name="passwd" id="passwd" maxlength="12">
			</li>
			</ul>
			<div class="align-center">
				<input type="submit" value="LogIn">
				<input type="button" value="Home" onclick="location.href='main.jsp'">
			</div>
		</form>
	</div>
</body>
</html>

로그인 JSP

<%@page import="kr.employee.vo.EmployeeVO"%>
<%@page import="kr.employee.dao.EmployeeDAO"%>
<%@ page import="kr.util.DBUtil"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	request.setCharacterEncoding("utf-8");

	// 전송된 데이터 반환
	String id = request.getParameter("id");
	String passwd = request.getParameter("passwd");

	EmployeeDAO dao = EmployeeDAO.getInstance();
	EmployeeVO vo = dao.checkEmployee(id);
	
	boolean check = false;
	if(vo!=null){
		check = vo.isCheckedPassword(passwd);
	}
	if(check){
		session.setAttribute("user_num", vo.getNum());
		session.setAttribute("user_id", vo.getId());
		response.sendRedirect("main.jsp");
	}else{
%>
<script type="text/javascript">
alert('아이디 또는 비밀번호가 불일치합니다.');
history.go(-1);
</script>
<%
	}
%>

로그아웃 JSP

<%@ page language="java" contentType="text/plain; charset=UTF-8"
    pageEncoding="UTF-8" trimDirectiveWhitespaces="true"%>
<%
	// 로그아웃
	session.invalidate();
	response.sendRedirect("main.jsp");
%>

사원 마이페이지 JSP

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="kr.employee.vo.EmployeeVO"%>
<%@ page import="kr.employee.dao.EmployeeDAO"%>
<%
    Integer user_num = (Integer) session.getAttribute("user_num");
    if (user_num == null) { // 로그인 되지 않은 경우
        response.sendRedirect("loginForm.jsp");
        return;
    }
	// 로그인 된 경우
	EmployeeDAO dao = EmployeeDAO.getInstance();
	EmployeeVO vo = dao.getEmployee(user_num);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>사원 상세 정보</title>
<link rel="stylesheet" href="<%=request.getContextPath()%>/css/style2.css">
</head>
<body>
	<div class="page-main">
		<h1>사원 정보</h1>
		<ul>
			<li>이름  : <%= vo.getName() %></li>
			<li>아이디  : <%= vo.getId() %></li>
			<li>연봉  : <%= String.format("%,d원", vo.getSalary()) %></li>
			<li>직업   : <%= vo.getJob() %></li>
			<li>가입일  : <%= vo.getReg_date() %></li>
		</ul>
		<hr size="2" width="100%" noshade="noshade">
		<div class="align-right">
			<input type="button" value="Update Info" onclick="location.href='updateEmployeeForm.jsp'">
			<input type="button" value="Delete Account" onclick="location.href='deleteEmployeeForm.jsp'">
			<input type="button" value="Home" onclick="location.href='main.jsp'">
		</div>
	</div>
</body>
</html>

사원 정보 수정 Form

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="kr.employee.dao.EmployeeDAO"%>
<%@ page import="kr.employee.vo.EmployeeVO"%>
<%
	Integer user_num = (Integer) session.getAttribute("user_num");
	if (user_num == null) { // 로그인 되지 않은 경우
    	response.sendRedirect("loginForm.jsp");
    	return;
	} // 로그인 된 경우
	request.setCharacterEncoding("UTF-8");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>사원 정보 수정</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
<script type="text/javascript" src="<%= request.getContextPath() %>/js/jquery-3.7.1.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
	$('#update_form').submit(function(){
	    const items = document.querySelectorAll('.input-check');
	    for(let i = 0; i < items.length; i++){
	        if(items[i].value.trim() == ''){
	            const label = document.querySelector('label[for="'+items[i].id+'"]');
	            alert(label.textContent + ' 항목은 필수 입력');
	            
	            items[i].value = '';
	            items[i].focus();
	            return false;
	        }
	    } // end of for
	});
});
</script>
</head>
<body>
<%
	EmployeeDAO dao = EmployeeDAO.getInstance();
	EmployeeVO vo = dao.getEmployee(user_num);
%>
<div class="page-main">
		<h1>사원 정보 수정</h1>
		<form action="updateEmployee.jsp" method="post" id="update_form">
			<ul>
				<li>
					<label for="name">이름</label>
					<input type="text" name="name" id="name" value="<%=vo.getName() %>" class="input-check" >
				</li>
				<li>
					<label for="passwd">PW</label>
					<input type="password" name="passwd" id="passwd" class="input-check" maxlength="12">
				</li>
				<li>
					<label for="salary">연봉</label>
					<input type="number" name="salary" value="<%=vo.getSalary() %>" id="salary" >
				</li>
				<li>
					<label for="job">직업</label>
					<input type="text" name="job" id="job" value="<%=vo.getJob() %>" class="input-check">
				</li>
			</ul>
			<div class="align-center">
				<input type="submit" value="Update">
				<input type="button" value="Home" onclick="location.href='main.jsp'">
			</div>
		</form>
	</div>
</body>
</html>

사원 정보 수정 JSP

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="kr.employee.dao.EmployeeDAO"%>
<%@ page import="kr.employee.vo.EmployeeVO"%>
<%
	Integer user_num = (Integer) session.getAttribute("user_num");
	if (user_num == null) { // 로그인 되지 않은 경우
    	response.sendRedirect("loginForm.jsp");
    	return;
	} 
	else {// 로그인 된 경우
	request.setCharacterEncoding("utf-8");
%>
<jsp:useBean id="employee" class="kr.employee.vo.EmployeeVO"/>
<jsp:setProperty property="*" name="employee"/>
<%
	employee.setNum(user_num);
	EmployeeDAO dao = EmployeeDAO.getInstance();
	dao.updateEmployee(employee);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>사원 정보 업데이트</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
</head>
<body>
	<div class="page-main">
	<h1>사원 정보 업데이트</h1>
		<div class="result-display">
			<div class="align-center">
				사원 정보가 업데이트 되었습니다.<p>
				<input type="button" class="button" value="MyPage" onclick="location.href='myPage.jsp'">
			</div>
		</div>
	</div>
</body>
</html>
<%
	}
%>

사원 삭제 Form

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="kr.employee.vo.EmployeeVO"%>
<%@ page import="kr.employee.dao.EmployeeDAO"%>
<%
String user_id = (String) session.getAttribute("user_id");
if (user_id == null) { // 로그인 되지 않은 경우
	response.sendRedirect("loginForm.jsp");
	return;
} else {
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>사원 계정 탈퇴</title>
	<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
	<script type="text/javascript" src="<%= request.getContextPath() %>/js/jquery-3.7.1.min.js"></script>
	<script type="text/javascript">
	$(function() {
		$('#delete_form').submit(function(){
			if($('#id').val().trim()==''){
				alert('ID를 입력하세요.');
				$('#id').val('').focus();
				return false;
			}
			if($('#passwd').val().trim()==''){
				alert('PW를 입력하세요.');
				$('#passwd').val('').focus();
				return false;
			}
			if($('#cpasswd').val().trim()==''){
				alert('비밀번호 확인을 입력하세요.');
				$('#cpasswd').val('').focus();
				return false;
			}

			if($('#passwd').val() != $('#cpasswd').val()){
				alert('비밀번호와 비밀번호 확인이 불일치합니다.');
				$('#cpasswd').val('').focus();
				return false;
			}
		});
	});
	</script>
</head>
<body>
	<div class="page-main">
		<h1>사원 계정 탈퇴</h1>
		<form action="deleteEmployee.jsp" id="delete_form" method="post">
			<ul>
				<li>
					<label for="id">ID</label>
					<input type="text" id="id" name="id" maxlength="12">
				</li>
				<li>
					<label for="passwd">PW</label>
					<input type="password" id="passwd" name="passwd" maxlength="12">
				</li>
				<li>
					<label for="passwd">PW Check</label>
					<input type="password" id="cpasswd" maxlength="12">
				</li>
			</ul>
				<div class="align-center">
					<input type="submit" value="Delete Account">
					<input type="button" value="Home" onclick="location.href='main.jsp'">
				</div>
		</form>
	</div>
	</body>
</html>
<%
	}
%>

사원 삭제 JSP

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="kr.employee.vo.EmployeeVO"%>
<%@ page import="kr.employee.dao.EmployeeDAO"%>

<%
	String user_id = (String) session.getAttribute("user_id");
	if (user_id == null) { // 로그인 되지 않은 경우
		response.sendRedirect("loginForm.jsp");
		return;
	} else {
		request.setCharacterEncoding("utf-8");
		
		String id = request.getParameter("id");
		String passwd = request.getParameter("passwd");
		
		EmployeeDAO dao = EmployeeDAO.getInstance();
		EmployeeVO vo = dao.checkEmployee(user_id);
		
		boolean check = false;
		if(vo!=null && user_id.equals(id)){
			check = vo.isCheckedPassword(passwd);
		}
		if(check){
			dao.deleteEmployee(vo.getNum());
			session.invalidate();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>탈퇴 완료</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
</head>
<body>
	<div class="page-main">
	<h1>사원 계정 탈퇴 완료</h1>
		<div class="result-display">
			<div class="align-center">
				사원 탈퇴가 완료되었습니다.<p>
				<input type="button" class="button" value="Home" onclick="location.href='main.jsp'">
			</div>
		</div>
	</div>
</body>
</html>
<%
	}else{
%>
<script type="text/javascript">
	alert('아이디 또는 비밀번호가 불일치 합니다.');
	history.go(-1);
</script>
<%		
	}
}
%>

---