table.sql
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;
kr.employee.vo => 패키지
EmployeeVO.java
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;
}
}
kr.employee.dao => 패키지
EmployeeDAO
package kr.employee.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import kr.employee.vo.EmployeeVO;
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문 작성
sql = "INSERT INTO semployee (num,id,name,passwd,salary,job,reg_date) VALUES (semployee_seq.nextval,?,?,?,?,?,SYSDATE)";
//PreparedStatement 객체 생성
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());
//SQL문 실행
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;
EmployeeVO vo = null;
String sql = null;
try {
//커넥션풀로부터 커넥션을 할당
conn = DBUtil.getConnection();
//SQL문 작성
sql = "SELECT * FROM semployee WHERE num=?";
//PreparedStatement 객체 생성
pstmt=conn.prepareStatement(sql);
//?에 데이터 바인딩
pstmt.setInt(1, num);
//SQL문 실행
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;
EmployeeVO vo = null;
String sql = null;
try {
//커넥션풀로부터 커넥션을 할당
conn = DBUtil.getConnection();
//SQL문 작성
sql = "SELECT * FROM semployee WHERE id=?";
//PreparedStatement 객체 생성
pstmt=conn.prepareStatement(sql);
//?에 데이터 바인딩
pstmt.setString(1, id);
//SQL문 실행
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문 작성
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문 작성
sql = "DELETE FROM semployee WHERE num=?";
//PreparedStatement 객체 생성
pstmt = conn.prepareStatement(sql);
//?에 데이터 바인딩
pstmt.setInt(1, num);
//SQL문 실행
pstmt.executeUpdate();
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(null, pstmt, conn);
}
}
}
main.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>사원관리 메인</title>
<!-- href="/ch03_JSP/css/style.css" ==> 컨텍스트 경로 -->
<!-- <link rel="stylesheet" href="/ch03_JSP/css/style.css" type="text/css"> -->
<!-- request.getContextPath() 를 명시해주면 주소의 이름이 바뀌더라도 문제가 없음 -->
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css" type="text/css">
</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="registerUserForm.jsp">회원가입</a>
<a href="loginForm.jsp">로그인</a>
<%
}else{//로그인이 된 경우
%>
<a href="myPage.jsp">MyPage</a>
[<b><%= user_id %></b>님 로그인 중]
<a href="logout.jsp">로그아웃</a>
<%
}
%>
</div>
</div>
</body>
</html>
registerUserForm.jsp
<%@ 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" type="text/css">
<script type="text/javascript" src="<%= request.getContextPath() %>/js/jquery-3.7.1.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
//아이디 중복 체크 결과 -> 0:아이디 중복 체크 미실시(아이디 중복),1:아이디 미중복
let count = 0;
$('#confirm_id').click(function(){
if($('#id').val().trim()==''){
alert('아이디를 입력하세요');
$('#id').val('').focus();
return;
}
//서버와 통신
$.ajax({
url:'confirmId.jsp',
type:'post',
data:{id:$('#id').val()},
dataType:'json',
success:function(param){
if(param.result == 'idDuplicated'){
count = 0;
$('#id_signed').text('아이디 중복').css('color','red');
$('#id').val('').focus();
}else if(param.result =='idNotFound'){
count = 1;
$('#id_signed').text('사용 가능 아이디').css('color','black');
}else{
count = 0;
alert('id중복체크 오류');
}
},
error:function(){
count = 0;
alert('네트워크 오류 발생');
}
});
});//end of click
//아이디 입력창에 데이터를 입력하면 중복체크 관련 정보 초기화(ID중복확인을 누른뒤 그 상태로 다른 값 입력시 내용이 사라짐)
$('#register_form #id').keydown(function(){
count = 0;
$('#id_signed').text('');
});//end of keydown
$('#register_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('아이디 중복체크 필수');
return false;
}
}
});
});
</script>
</head>
<body>
<div class="page-main">
<h1>사원 등록</h1>
<form id="register_form" action="registerUser.jsp" method="post" >
<ul>
<li>
<label for ="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" size="10" maxlength="10" class="input-check">
</li>
<li>
<label for="passwd">비밀번호</label>
<input type="password" name="passwd" id="passwd" size="10" maxlength="12" class="input-check">
</li>
<li>
<label for="salary">급여</label>
<input type="number" name="salary" id="salary" size="10" maxlength="12" class="input-check">
</li>
<li>
<label for="job">직업</label>
<input type="text" name="job" id="job" size="10" maxlength="12" class="input-check">
</li>
</ul>
<div class="align-center">
<input type="submit" value="등록">
<input type="button" value="홈으로" onclick="location.href='main.jsp'">
</div>
</form>
</div>
</body>
</html>
registerUser.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="vo" class="kr.employee.vo.EmployeeVO"/>
<%-- request로부터 전송된 데이터를 읽어들여 자바빈에 저장 --%>
<jsp:setProperty property="*" name="vo"/>
<%
EmployeeDAO dao = EmployeeDAO.getInstance();
dao.insertEmployee(vo);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>사원등록</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css" type="text/css">
</head>
<body>
<div class="page-main">
<h1>사원등록 완료</h1>
<div class="result-display">
<div class="align-center">
사원등록 성공!<p>
<button onclick="location.href='main.jsp'">홈으로</button>
</div>
</div>
</div>
</body>
</html>
comfirmId.jsp
<%@ page language="java" contentType="text/plain; charset=UTF-8"
pageEncoding="UTF-8" trimDirectiveWhitespaces="true"%>
<%@ 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 vo = dao.checkEmployee(id);
if(vo !=null){//아이디 중복
%>
{"result":"idDuplicated"}
<%
}else{//아이디 미중복
%>
{"result":"idNotFound"}
<%
}
%>
<%@ 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" type="text/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').val('').focus();
return false;
}
if($('#passwd').val().trim()==''){
alert('비밀번호를 입력하세요');
$('#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">아이디</label>
<input type="text" name="id" id="id" maxlength="12">
</li>
<li>
<label for="passwd">비밀번호</label>
<input type="password" name="passwd" id="passwd" maxlength="12">
</li>
</ul>
<div class="align-center">
<input type ="submit" value="로그인">
<input type ="button" value="홈으로" onclick = "location.href='main.jsp'">
</div>
</form>
</div>
</body>
</html>
login.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" %>
<%
//전송된 데이터 인코딩 타입 지정
request.setCharacterEncoding("utf-8");
//전송된 데이터 반환
String id = request.getParameter("id");
String passwd = request.getParameter("passwd");
EmployeeDAO dao = EmployeeDAO.getInstance();
//id 존재 여부 확인
EmployeeVO vo = dao.checkEmployee(id);
boolean check = false;
if(vo!=null){//id 존재
//사용자가 입력한 비밀번호와 table에 저장된 비밀번호 일치 여부 확인
check = vo.isCheckedPassword(passwd);
}
if(check){//인증성공 (check가 true일 떄)
//로그인 처리
session.setAttribute("user_num", vo.getNum());
session.setAttribute("user_id", id);
//main.jsp로 리다이렉트
response.sendRedirect("main.jsp");
}else{//인증실패
%>
<script type = "text/javascript">
alert('아이디 또는 비밀번호가 불일치합니다.');
history.go(-1);
</script>
<%
}
%>
logout.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
session.invalidate();
response.sendRedirect("main.jsp");
%>
myPage.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");
}else{//로그인 된 경우
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원상세정보</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css" type="text/css">
</head>
<body>
<%
EmployeeDAO dao = EmployeeDAO.getInstance();
EmployeeVO vo = dao.getEmployee(user_num);
%>
<div class="page-main">
<h1>사원정보</h1>
<ul>
<li>아이디 : <%= vo.getId() %></li>
<li>이름 : <%= vo.getName() %></li>
<li>급여 : <%= vo.getSalary() %></li>
<li>비밀번호 : <%= vo.getPasswd() %></li>
<li>직업 : <%= vo.getJob() %></li>
<li>가입일 : <%= vo.getReg_date() %></li>
</ul>
<hr size="1" width="100%" noshade="noshade">
<div class="align-right">
<input type="button" value="사원정보수정" onclick="location.href='modifyUserForm.jsp'">
<input type="button" value="사원탈퇴" onclick="location.href='deleteUserForm.jsp'">
<input type="button" value="홈으로" onclick="location.href='main.jsp'">
</div>
</div>
</body>
</html>
<%
}
%>
<%@ 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");
}else{//로그인이 된 경우
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>사원 등록</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css" type="text/css">
<script type="text/javascript" src="<%= request.getContextPath() %>/js/jquery-3.7.1.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$('#modify_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;
}
}
});
});
</script>
</head>
<body>
<%
EmployeeDAO dao = EmployeeDAO.getInstance();
EmployeeVO vo = dao.getEmployee(user_num);
%>
<div class="page-main">
<h1>사원 등록</h1>
<form id="modify_form" action="modifyUser.jsp" method="post" >
<ul>
<li>
<label for="name">이름</label>
<input type="text" name="name" id="name"
value="<%= vo.getName() %>"
size="10" maxlength="10" class="input-check">
</li>
<li>
<label for="passwd">비밀번호</label>
<input type="password" name="passwd" id="passwd" size="10" maxlength="12" class="input-check">
</li>
<li>
<label for="salary">급여</label>
<input type="number" name="salary" id="salary"
value="<%= vo.getSalary() %>"
size="10" maxlength="12" class="input-check">
</li>
<li>
<label for="job">직업</label>
<input type="text" name="job" id="job"
value="<%= vo.getJob() %>"
size="10" maxlength="12" class="input-check">
</li>
</ul>
<div class="align-center">
<input type="submit" value="등록">
<input type="button" value="홈으로" onclick="location.href='main.jsp'">
</div>
</form>
</div>
</body>
</html>
<%
}
%>
modifyUser.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="kr.employee.dao.EmployeeDAO" %>
<%
Integer user_num = (Integer)session.getAttribute("user_num");
if(user_num==null){//로그인이 되지 않은 경우
response.sendRedirect("loginForm.jsp");
}else{//로그인이 된 경우
//전송된 데이터 인코딩 타입 지정
request.setCharacterEncoding("utf-8");
%>
<jsp:useBean id="vo" class="kr.employee.vo.EmployeeVO"/>
<jsp:setProperty property="*" name="vo"/>
<%
vo.setNum(user_num);
EmployeeDAO dao = EmployeeDAO.getInstance();
dao.updateEmployee(vo);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>사원정보수정 완료</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css" type="text/css">
</head>
<body>
<div class="page-main">
<h1>사원정보 수정완료</h1>
<div class="result-display">
<div class="align-center">
사원정보수정 완료!<p>
<button onclick="location.href='myPage.jsp'">MyPage</button>
</div>
</div>
</div>
</body>
</html>
<%
}
%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
Integer user_num =(Integer)session.getAttribute("user_num");
if(user_num==null){//로그인이 되지 않는 경우
response.sendRedirect("loginForm.jsp");
}else{//로그인이 된 경우
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>사원탈퇴</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css" type="text/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').val('').focus();
return false;
}
if($('#passwd').val().trim()==''){
alert('비밀번호를 입력하세요');
$('#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 id="delete_form" action="deleteUser.jsp" method="post">
<ul>
<li>
<label for="id">아이디</label>
<input type="text" name="id" id="id" maxlength="12">
</li>
<li>
<label for="passwd">비밀번호</label>
<input type="password" name="passwd" id="passwd" maxlength="12">
</li>
<li>
<label for="passwd">비밀번호 확인</label>
<input type="password" id="cpasswd" maxlength="12">
</li>
</ul>
<div class="align-center">
<input type="submit" value="회원탈퇴">
<input type="button" value="홈으로" onclick="location.href='main.jsp'">
</div>
</form>
</div>
</body>
</html>
<%
}
%>
deleteUser.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" %>
<%
String user_id = (String)session.getAttribute("user_id");
if(user_id==null){//로그인이 되지 않은 경우
response.sendRedirect("loginForm.jsp");
}else{//로그인이 된 경우
//전송된 데이터 인코딩 타입 지정
request.setCharacterEncoding("utf-8");
//전송된 데이터를 반환
String id = request.getParameter("id");
String passwd = request.getParameter("passwd");
//id, 비밀번호 일치 여부 체크
EmployeeDAO dao = EmployeeDAO.getInstance();
EmployeeVO vo = dao.checkEmployee(id);
boolean check = false;
//아이디가 등록되어 있고 로그인한 아이디와 일치할 경우
if(vo!=null && user_id.equals(id)){ //user_id=> 세션에 저장되어있는 id. equals(id)=> 내가 입력한 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" type="text/css">
</head>
<body>
<div class="page-main">
<h1>회원 탈퇴 완료</h1>
<div class="result-display">
회원탈퇴가 완료되었습니다.<p>
<button onclick="location.href='main.jsp'">홈으로</button>
</div>
</div>
</body>
</html>
<%
}else{
%>
<script type="text/javascript">
alert('아이디 또는 비밀번호가 불일치합니다.');
history.go(-1);
</script>
<%
}
}
%>
