'MVC' 카테고리의 글 목록

08일차_MVC패턴, 직원관리

MVC 2015. 6. 30. 14:40

----------------------------
직원관리 Servlet(MVC 패턴) 방식
- 서블릿 주소를 확장자 형태 사용.
- web.xml 파일에는 확장자 형태의 주소 한 개만 등록
- 주소 분석 및 액션 요청을 위한 전용 서블릿 클래스 작성.
- 액션 담당 전용 클래스 작성.

** 환경 설정 추가 -> WEB-INF/source 폴더 하위에 JSP 페이지 등록

10. 소스코드 -> 로그인, 관리자, 직원 전용 페이지

//Controller.java
package com.test;

import java.io.IOException;

import javax.servlet.*;
import javax.servlet.http.*;
import com.sun.corba.se.spi.orbutil.fsm.*;

public class Controller extends HttpServlet {

private static final long serialVersionUID = 1L;

@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}

@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}

private void doGetPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {

  // 요청 주소 분석 및 액션 메소드 호출
  String uri = request.getRequestURI();
  // -> "http://localhost:8090/프로젝트이름/Sample.it"

Action action = new Action();
String result = "Error.jsp";

  //요청 주소별로 작성할 것.
  if (uri.indexOf("RegionList.it") != -1) {
   // 액션 메소드 호출
   // 메소드 결과는 URI 주소 형태를 가지고 있다.
   result = action.regionList(request, response);
  }

  if (uri.indexOf("RegionInsert.it") != -1) {
   result = action.regionInsert(request, response);
  }

  if (uri.indexOf("RegionDelete.it") != -1) {
   result = action.regionDelete(request, response);
  }

  if (uri.indexOf("RegionUpdate.it") != -1) {
   result = action.regionUpdate(request, response);
  }

  if (uri.indexOf("PositionList.it") != -1) {
   // 액션 메소드 호출
   // 메소드 결과는 URI 주소 형태를 가지고 있다.
   result = action.positionList(request, response);
  }

  if (uri.indexOf("PositionInsert.it") != -1) {
   result = action.positionInsert(request, response);
  }

  if (uri.indexOf("PositionDelete.it") != -1) {
   result = action.positionDelete(request, response);
  }

  if (uri.indexOf("PositionUpdate.it") != -1) {
   result = action.positionUpdate(request, response);
  }

  if (uri.indexOf("DepartmentList.it") != -1) {
   // 액션 메소드 호출
   // 메소드 결과는 URI 주소 형태를 가지고 있다.
   result = action.departmentList(request, response);
  }

  if (uri.indexOf("DepartmentInsert.it") != -1) {
   result = action.departmentInsert(request, response);
  }

  if (uri.indexOf("DepartmentDelete.it") != -1) {
   result = action.departmentDelete(request, response);
  }

  if (uri.indexOf("DepartmentUpdate.it") != -1) {
   result = action.departmentUpdate(request, response);
  }


  //문제) 주소 등록
  if (uri.indexOf("EmployeeList.it") != -1) {
   result = action.employeeList(request, response);
  }
  if (uri.indexOf("EmployeeSearch.it") != -1) {
   result = action.employeeSearch(request, response);
  }
  if (uri.indexOf("EmployeeInsertForm.it") != -1) {
   result = action.employeeInsertForm(request, response);
  }
  if (uri.indexOf("EmployeeInsert.it") != -1) {
   result = action.employeeInsert(request, response);
  }
  if (uri.indexOf("PositionReceive.it") != -1) {
   result = action.positionReceive(request, response);
  }
  if (uri.indexOf("EmployeeDelete.it") != -1) {
   result = action.employeeDelete(request, response);
  }
  if (uri.indexOf("EmployeeUpdateForm.it") != -1) {
   result = action.employeeUpdateForm(request, response);
  }
  if (uri.indexOf("EmployeeUpdate.it") != -1) {
   result = action.employeeUpdate(request, response);
  }


  if (uri.indexOf("LoginForm.it") != -1) {
   result = action.loginForm(request, response);
  }
  if (uri.indexOf("Login.it") != -1) {
   result = action.login(request, response);
  }
  if (uri.indexOf("Logout.it") != -1) {
   result = action.logout(request, response);
  }
  if (uri.indexOf("LogoutForm.it") != -1) {
   result = action.logoutForm(request, response);
  }
  if (uri.indexOf("LoginFail.it") != -1) {
   result = action.loginFail(request, response);
  }

  if (uri.indexOf("EmpSearch.it") != -1) {
   result = action.empSearch(request, response);
  }

  // 페이지 전환 -> forward(), sendRedirect() 메소드
  if (result.indexOf("redirect:") != -1) {
   // sendRedirect 요청
   // -> 또 다른 서블릿 주소 요청
   response.sendRedirect(result.substring(9));
  } else {
   // forward 요청
   // -> 연관된 JSP 페이지 요청
   RequestDispatcher dispatcher = request.getRequestDispatcher(result);
   dispatcher.forward(request, response);
  }
}

}

//Action.java
package com.test;

import java.io.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;

//액션 전용 클래스 -> 메소드 단위로 액션 처리
public class Action {

/*
public String 메소드이름(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
HttpSession session = request.getSession();

  return "WEB-INF/source/요청주소";
  //return "redirect:요청주소";
}
*/

public String regionList(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  HttpSession session = request.getSession();

  //액션 및 결과 출력
  RegionDAO dao = new RegionDAO();
  request.setAttribute("list", dao.regionList());

  return "WEB-INF/source/RegionList.jsp";
}

public String regionInsert(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  HttpSession session = request.getSession();

  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String regionName = request.getParameter("regionName");

  //데이터베이스 액션 -> add() 메소드 호출로 대체
  RegionDAO dao = new RegionDAO();
  Region r = new Region();
  r.setRegionName(regionName);
  dao.add(r);
  return "redirect:RegionList.it";
}

public String regionDelete(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  HttpSession session = request.getSession();

  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String regionId = request.getParameter("regionId");

  //데이터베이스 액션 -> add() 메소드 호출로 대체
  RegionDAO dao = new RegionDAO();
  dao.remove(regionId);

  return "redirect:RegionList.it";
}

public String regionUpdate(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  HttpSession session = request.getSession();

  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String regionName = request.getParameter("regionName");
  String regionId = request.getParameter("regionId");

  //데이터베이스 액션 -> add() 메소드 호출로 대체
  RegionDAO dao = new RegionDAO();
  Region r = new Region();
  r.setRegionName(regionName);
  r.setRegionId(regionId);
  dao.modify(r);

  return "redirect:RegionList.it";
}

public String positionList(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
HttpSession session = request.getSession();

  //액션 및 결과 출력
  PositionDAO dao = new PositionDAO();
  request.setAttribute("list", dao.positionList());

  return "WEB-INF/source/PositionList.jsp";
}

public String positionInsert(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  HttpSession session = request.getSession();

  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String positionName = request.getParameter("positionName");
  String minBasicPay = request.getParameter("minBasicPay");
  //데이터베이스 액션 -> add() 메소드 호출로 대체
  PositionDAO dao = new PositionDAO();
  Position p = new Position();
  p.setPositionName(positionName);
  p.setMinBasicPay(Integer.parseInt(minBasicPay));
  dao.add(p);
  return "redirect:PositionList.it";
}

public String positionDelete(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  HttpSession session = request.getSession();

  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String positionId = request.getParameter("positionId");

  //데이터베이스 액션 -> add() 메소드 호출로 대체
  PositionDAO dao = new PositionDAO();
  dao.remove(positionId);

  return "redirect:PositionList.it";
}

public String positionUpdate(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  HttpSession session = request.getSession();

  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String positionName = request.getParameter("positionName");
  String positionId = request.getParameter("positionId");
  String minBasicPay = request.getParameter("minBasicPay");
  //데이터베이스 액션 -> add() 메소드 호출로 대체
  PositionDAO dao = new PositionDAO();
  Position p = new Position();
  p.setPositionName(positionName);
  p.setPositionId(positionId);
  p.setMinBasicPay(Integer.parseInt(minBasicPay));
  dao.modify(p);

  return "redirect:PositionList.it";
}

public String departmentList(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
HttpSession session = request.getSession();

  //액션 및 결과 출력
  DepartmentDAO dao = new DepartmentDAO();
  request.setAttribute("list", dao.departmentList());

  return "WEB-INF/source/DepartmentList.jsp";
}

public String departmentInsert(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  HttpSession session = request.getSession();

  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String departmentName = request.getParameter("departmentName");

  //데이터베이스 액션 -> add() 메소드 호출로 대체
  DepartmentDAO dao = new DepartmentDAO();
  Department d = new Department();
  d.setDepartmentName(departmentName);
  dao.add(d);
  return "redirect:DepartmentList.it";
}

public String departmentDelete(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  HttpSession session = request.getSession();

  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String departmentId = request.getParameter("departmentId");

  //데이터베이스 액션 -> add() 메소드 호출로 대체
  DepartmentDAO dao = new DepartmentDAO();
  dao.remove(departmentId);

  return "redirect:DepartmentList.it";
}

public String departmentUpdate(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  HttpSession session = request.getSession();

  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String departmentName = request.getParameter("departmentName");
  String departmentId = request.getParameter("departmentId");

  //데이터베이스 액션 -> add() 메소드 호출로 대체
  DepartmentDAO dao = new DepartmentDAO();
  Department d = new Department();
  d.setDepartmentName(departmentName);
  d.setDepartmentId(departmentId);
  dao.modify(d);

  return "redirect:DepartmentList.it";
}

public String employeeList(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  HttpSession session = request.getSession();

  //문제) 데이터베이스 액션 및 결과 데이터 전달
  EmployeeDAO dao = new EmployeeDAO();
  request.setAttribute("count", dao.count());
  request.setAttribute("list", dao.list());

  return "WEB-INF/source/EmployeeList.jsp";
}

public String employeeSearch(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  HttpSession session = request.getSession();

  //문제) 데이터 수신 및 데이터베이스 액션, 결과 데이터 전달
  request.setCharacterEncoding("UTF-8");
  String searchKey = request.getParameter("searchKey");
  String searchValue = request.getParameter("searchValue");
  if (searchKey != null) {
   EmployeeDAO dao = new EmployeeDAO();
   request.setAttribute("count", dao.count(searchKey, searchValue));
   request.setAttribute("list", dao.list(searchKey, searchValue));
  }

  request.setAttribute("searchKey", searchKey);
  request.setAttribute("searchValue", searchValue);

  return "WEB-INF/source/EmployeeSearch.jsp";
}

public String employeeInsertForm(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  HttpSession session = request.getSession();

  EmployeeDAO dao = new EmployeeDAO();

  request.setAttribute("positionList", dao.positionList());
  request.setAttribute("regionList", dao.regionList());
  request.setAttribute("departmentList", dao.departmentList());

  return "WEB-INF/source/EmployeeInsertForm.jsp";
}

public String employeeInsert(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  HttpSession session = request.getSession();

  //데이터 수신
  request.setCharacterEncoding("UTF-8");
  String name = request.getParameter("name");
  String ssn = request.getParameter("ssn");
  String birthday = request.getParameter("birthday");
  String lunar = request.getParameter("lunar");
  String telephone = request.getParameter("telephone");
  String regionId = request.getParameter("regionId");
  String departmentId = request.getParameter("departmentId");
  String positionId = request.getParameter("positionId");
  String basicPay = request.getParameter("basicPay");
  String extraPay = request.getParameter("extraPay");

  //데이터베이스 액션 -> add() 메소드 호출
  EmployeeDAO dao = new EmployeeDAO();
  Employee e = new Employee();
  e.setName(name);
  e.setSsn(ssn);
  e.setBirthday(birthday);
  e.setLunar(Integer.parseInt(lunar));
  e.setTelephone(telephone);
  e.setRegionId(regionId);
  e.setDepartmentId(departmentId);
  e.setPositionId(positionId);
  e.setBasicPay(Integer.parseInt(basicPay));
  e.setExtraPay(Integer.parseInt(extraPay));
  dao.add(e);

  return "redirect:EmployeeList.it";
}

public String positionReceive(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  HttpSession session = request.getSession();

//페이지 차단 액션 추가 (관리자 전용 페이지)

  //데이터 수신 (positionId)
  String positionId = request.getParameter("positionId");

  EmployeeDAO dao = new EmployeeDAO();
  request.setAttribute("result", String.valueOf(dao.getMinBasicPay(positionId)));

  return "WEB-INF/source/PositionReceive.jsp";
}

public String employeeDelete(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  HttpSession session = request.getSession();

  //페이지 차단 액션 추가 (관리자 전용 페이지)

  request.setCharacterEncoding("UTF-8");
  String employeeId = request.getParameter("employeeId");

  EmployeeDAO dao = new EmployeeDAO();
  dao.remove(employeeId);


  return "redirect:EmployeeList.it";
}

public String employeeUpdateForm(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  HttpSession session = request.getSession();

//페이지 차단 액션 추가 (관리자 전용 페이지)

//데이터 수신 -> emploeeId
//데이터베이스 액션 -> list("employeeId",value) 메소드

  EmployeeDAO dao = new EmployeeDAO();

  String employeeId = request.getParameter("employeeId");

  request.setAttribute("emp", (dao.list("0", employeeId)).get(0));

  request.setAttribute("positionList", dao.positionList());
  request.setAttribute("departmentList",dao.departmentList());
  request.setAttribute("regionList",dao.regionList());

  return "WEB-INF/source/EmployeeUpdateForm.jsp";
}
public String employeeUpdate(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  HttpSession session = request.getSession();

  //페이지 차단 액션 추가 (관리자 전용 페이지)

  request.setCharacterEncoding("UTF-8");

  //데이터 수신
  String employeeId = request.getParameter("employeeId");
  String name = request.getParameter("name");
  String ssn = request.getParameter("ssn");
  String birthday = request.getParameter("birthday");
  String lunar = request.getParameter("lunar");
  String telephone = request.getParameter("telephone");
  String regionId = request.getParameter("regionId");
  String departmentId = request.getParameter("departmentId");
  String positionId = request.getParameter("positionId");
  String basicPay = request.getParameter("minBasicPay");
  String extraPay = request.getParameter("extraPay");
  String grade = request.getParameter("grade");

  //DB 액션 -> modify() 메소드 호출
  Employee e = new Employee();
  e.setEmployeeId(employeeId);
  e.setName(name);
  e.setSsn(ssn);
  e.setBirthday(birthday);
  e.setLunar(Integer.parseInt(lunar));
  e.setTelephone(telephone);
  e.setRegionId(regionId);
  e.setDepartmentId(departmentId);
  e.setPositionId(positionId);
  e.setBasicPay(Integer.parseInt(basicPay));
  e.setExtraPay(Integer.parseInt(extraPay));
  e.setGrade(Integer.parseInt(grade));

  EmployeeDAO dao = new EmployeeDAO();

  dao.modify(e);

  return "redirect:EmployeeList.it";
}

public String loginForm(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  return "WEB-INF/source/LoginForm.jsp";
}

public String login(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
HttpSession session = request.getSession();

  //문제)
  //데이터 수신 -> id, pw, grade

  //데이터베이스 액션->로그인 검증

  //로그인 실패-> LoginFail.it
  //로그인 성공->관리자->EmployeeList.it
  //로그인 성공->일반직원->EmpSearch.it

return "LoginFail.it";
}

public String logout(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  HttpSession session = request.getSession();

  //문제)
  //세션 객체 소멸

  return "redirect:LogoutForm.it";
}

public String logoutForm(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  return "WEB-INF/source/LogoutForm.jsp";
}

public String loginFail(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  return "WEB-INF/source/LoginFail.jsp";
}

public String empSearch(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  HttpSession session = request.getSession();

  //페이지 차단 액션 추가 (일반 직원용 페이지)
  if (session.getAttribute("login") == null) {
   response.sendRedirect("LoginForm.it");
  }


  //문제)
  //데이터 수신
  //데이터베이스 액션

return "WEB-INF/source/EmpSearch.jsp";
}

}

//EmployeeMenu.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
  <p>
   [<a href="EmployeeList.it">직원관리</a>]
   [<a href="RegionList.it">지역관리</a>]
   [<a href="DepartmentList.it">부서관리</a>]
   [<a href="PositionList.it">직위관리</a>]
   
   [<a href="Logout.it">로그아웃(${sessionScope.login})</a>]
  </p>

//EmpMenu.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
  <p>
   
   [<a href="Logout.it">로그아웃(${sessionScope.login})</a>]
  </p>

---------------------------------------

저작자표시

'MVC' 카테고리의 다른 글

07일차_MVC패턴, 직원관리 (0)	2015.06.30
06일차_MVC패턴, 직원관리 (0)	2015.06.30
05일차_MVC패턴, 직원관리 (0)	2015.06.30
04일차_MVC패턴, 회원관리, 성적관리 (0)	2015.06.30
03일차(2)_모델2방식, 성적관리 (0)	2015.06.30

알 수 없는 사용자

,

07일차_MVC패턴, 직원관리

MVC 2015. 6. 30. 14:40

----------------------------
직원관리 Servlet(MVC 패턴) 방식
- 서블릿 주소를 확장자 형태 사용.
- web.xml 파일에는 확장자 형태의 주소 한 개만 등록
- 주소 분석 및 액션 요청을 위한 전용 서블릿 클래스 작성.
- 액션 담당 전용 클래스 작성.

** 환경 설정 추가 -> WEB-INF/source 폴더 하위에 JSP 페이지 등록

10. 소스코드 -> 직원 삭제, 직원 수정 액션

//EmployeeList.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>직원관리(Servlet MVC 버전)</title>

$(".btnUpdate").click(function () {

  var employeeId = $(this).parent().parent().find("td:first").text();
  $(location).attr("href","EmployeeUpdateForm.it?employeeId="+employeeId);
});
});
</script>

</head>
<body>
<div>
<%--메뉴 영역--%>
<div class = "title">
  <h1>직원관리</h1>
  <c:import url="EmployeeMenu.jsp"></c:import>
</div>
<%--콘텐츠 영역--%>
<div class = "main">
  <h2>[직원명단]</h2>
  <form>
   <input type = "button" value = "직원추가" id = "btnAdd" name = "btnAdd">
   <input type = "button" value = "직원검색" id = "btnSearch" name = "btnSearch" onclick="location.href='EmployeeSearch.jsp'">
  </form>
  
  <p>직원수 : ${count} 명</p>
  <table id ="t01" style = "font-size:10pt;">
   <tr>
   <th>직원번호</th><th>이름</th><th>생년월일</th><th>양음력</th><th>전화번호</th>
   <th>지역명</th><th>부서명</th><th>직위명</th><th>기본급</th><th>수당</th><th>급여</th>
   <th>등급</th>
   <th>삭제</th><th>수정</th>
   </tr>
   <%--
   <tr>
   <td>1</td><td>홍길동</td><td>1991-03-01</td><td>양력</td><td>010-1234-1234</td>
   <td>서울</td><td>개발부</td><td>사원</td>
   <td style = "text-align: right;">1,000,000</td>
   <td style = "text-align: right;">500,000</td>
   <td style = "text-align: right;">1,500,000</td>
   <td>관리자</td>
   id ="" 식별자 사용 불가 ->반복문을 사용하기 떄문에
   중복이 가능한 식별자 class =""를 사용한다.
   <td><input type = "button" value = "삭제" class = "btnDelete"></td>
   <td><input type = "button" value = "수정" class = "btnUpdate"></td>
   </tr>
   --%>
   <c:forEach var="e" items="${list}">
   <tr>
   <td>${e.employeeId}</td>
   <td>${e.name}</td>
   <td>${e.birthday}</td>
   <td>${e.lunarName}</td>
   <td>${e.telephone}</td>
   <td>${e.regionName}</td>
   <td>${e.departmentName}</td>
   <td>${e.positionName}</td>
   <td style="text-align: right;"><fmt:formatNumber value="${e.basicPay}" groupingUsed="true"></fmt:formatNumber></td>
   <td style="text-align: right;"><fmt:formatNumber value="${e.extraPay}" groupingUsed="true"></fmt:formatNumber></td>
   <td style="text-align: right;"><fmt:formatNumber value="${e.pay}" groupingUsed="true"></fmt:formatNumber></td>
   <td>${e.grade==0?"관리자":"일반"}</td>
   <td><input type="button" value="삭제" class="btnDelete" ${e.grade==0?"disabled=\"disabled\"":""} ></td>
   <td><input type="button" value="수정" class="btnUpdate"></td>
   </tr>
   </c:forEach>
  </table>
</div>
</div>

</body>
</html>

//EmployeeDAO.java
package com.test;

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

public class EmployeeDAO {

// 직원 명단 출력 메소드
public ArrayList<Employee> list() {
ArrayList<Employee> result = new ArrayList<Employee>();

Connection conn = null;
PreparedStatement pstmt = null;

  try {
   conn = DBConn.getConnection();
   String sql = String
     .format("SELECT employeeId, name, birthday, lunar, lunarName, telephone, departmentId, departmentName, positionId, positionName, regionId, regionName, basicPay, extraPay, pay, grade FROM employeeView ORDER BY employeeId");
   pstmt = conn.prepareStatement(sql);
   ResultSet rs = pstmt.executeQuery();
   while (rs.next()) {
    Employee e = new Employee();
    e.setEmployeeId(rs.getString("employeeId"));
    e.setName(rs.getString("name"));
    e.setBirthday(rs.getString("birthday"));
    e.setLunar(rs.getInt("lunar"));
    e.setLunarName(rs.getString("lunarName"));
    e.setTelephone(rs.getString("telephone"));
    e.setDepartmentId(rs.getString("departmentId"));
    e.setDepartmentName(rs.getString("departmentName"));
    e.setPositionId(rs.getString("positionId"));
    e.setPositionName(rs.getString("positionName"));
    e.setRegionId(rs.getString("regionId"));
    e.setRegionName(rs.getString("regionName"));
    e.setBasicPay(rs.getInt("basicPay"));
    e.setExtraPay(rs.getInt("extraPay"));
    e.setPay(rs.getInt("pay"));
    e.setGrade(rs.getInt("grade"));
    result.add(e);
   }
   rs.close();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if (pstmt != null) {
     pstmt.close();
    }
   } catch (Exception e) {
   }
   DBConn.close();
  }
  return result;
}

// 직원 검색 출력 메소드(검색기준 : 사번,주민번호,이름,지역,부서,직위)
public ArrayList<Employee> list(String searchKey, String searchValue) {
ArrayList<Employee> result = new ArrayList<Employee>();

  Connection conn = null;
  PreparedStatement pstmt = null;
  try {
   conn = DBConn.getConnection();
   String sql = String
     .format("SELECT employeeId, name, birthday, lunar, lunarName, telephone, departmentId, departmentName, positionId, positionName, regionId, regionName, basicPay, extraPay, pay, grade FROM employeeView e");
   switch (searchKey) {
   case "0":
    sql += " WHERE employeeId=?";
    break;
   case "1":
    sql += " WHERE ssn=encrypt(?, e.name)";
    break;
   case "2":
    sql += " WHERE name=?";
    break;
   case "3":
    sql += " WHERE regionName=?";
    break;
   case "4":
    sql += " WHERE departmentName=?";
    break;
   case "5":
    sql += " WHERE positionName=?";
    break;
   case "9":
    sql += "";
    break; // 전체 검색
   }
   sql += " ORDER BY employeeId";
   pstmt = conn.prepareStatement(sql);
   if (!searchKey.equals("9")) {
    pstmt.setString(1, searchValue);
   }
   ResultSet rs = pstmt.executeQuery();
   while (rs.next()) {
    Employee e = new Employee();
    e.setEmployeeId(rs.getString("employeeId"));
    e.setName(rs.getString("name"));
    e.setBirthday(rs.getString("birthday"));
    e.setLunar(rs.getInt("lunar"));
    e.setLunarName(rs.getString("lunarName"));
    e.setTelephone(rs.getString("telephone"));
    e.setDepartmentId(rs.getString("departmentId"));
    e.setDepartmentName(rs.getString("departmentName"));
    e.setPositionId(rs.getString("positionId"));
    e.setPositionName(rs.getString("positionName"));
    e.setRegionId(rs.getString("regionId"));
    e.setRegionName(rs.getString("regionName"));
    e.setBasicPay(rs.getInt("basicPay"));
    e.setExtraPay(rs.getInt("extraPay"));
    e.setPay(rs.getInt("pay"));
    e.setGrade(rs.getInt("grade"));
    result.add(e);
   }
   rs.close();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if (pstmt != null) {
     pstmt.close();
    }
   } catch (Exception e) {

   }
   DBConn.close();
  }
  return result;
}

// 직원 전체 출력
public int count() {
  int result = 0;
  Connection conn = null;
  PreparedStatement pstmt = null;

  try {
   conn = DBConn.getConnection();
   String sql = String
     .format("select count(*) as \"count\" from employeeView");
   pstmt = conn.prepareStatement(sql);
   ResultSet rs = pstmt.executeQuery();
   while (rs.next()) {
    result = rs.getInt("count");
   }
   rs.close();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if (pstmt != null) {
     pstmt.close();
    }
   } catch (Exception e) {

   }
   DBConn.close();
  }
  return result;
}

// 직원 검색 결과 인원수 출력 메소드
public int count(String searchKey, String searchValue) {
  int result = 0;
  Connection conn = null;
  PreparedStatement pstmt = null;

  try {
   conn = DBConn.getConnection();
   String sql = String
     .format("select count(*) as \"count\" from employeeView e");
   switch (searchKey) {
   case "0":
    sql += " WHERE employeeId=?";
    break;
   case "1":
    sql += " WHERE ssn=encrypt(?, e.name)";
    break;
   case "2":
    sql += " WHERE name=?";
    break;
   case "3":
    sql += " WHERE regionName=?";
    break;
   case "4":
    sql += " WHERE departmentName=?";
    break;
   case "5":
    sql += " WHERE positionName=?";
    break;
   case "9":
    sql += "";
    break; // 전체 검색
   }
   pstmt = conn.prepareStatement(sql);
   if (!searchKey.equals("9")) {
    pstmt.setString(1, searchValue);
   }
   ResultSet rs = pstmt.executeQuery();
   while (rs.next()) {
    result = rs.getInt("count");
   }
   rs.close();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if (pstmt != null) {
     pstmt.close();
    }
   } catch (Exception e) {

   }
   DBConn.close();
  }
  return result;
}

// 직원 입력 메소드
public int add(Employee e) {
  int result = 0;
  /*
   * INSERT INTO employee (employeeId ,name ,ssn ,birthday ,lunar
   * ,telephone ,departmentId ,positionId ,regionId ,basicPay ,extraPay)
   * VALUES (employeeSeq.nextval , ? , encrypt(?, ?) , ? --'1980-12-12' ,
   * ? --양력 0, 음력 1 , ? --'010-123-1234' , ? --departmentId FK , ?
   * --positionId FK , ? --regionId FK , ? , ?)
   */

  // 문제) 입력 관련 액션 추가
  Connection conn = null;
  PreparedStatement pstmt = null;

  try {
   conn = DBConn.getConnection();
   String sql = String
     .format("INSERT INTO employee (employeeId,name,ssn,birthday ,lunar,telephone,departmentId ,positionId,regionId ,basicPay,extraPay) VALUES (employeeSeq.nextval, ?, encrypt(?, ?) ,? , ? ,? ,? , ?,? , ?, ?)");
   pstmt = conn.prepareStatement(sql);

   pstmt.setString(1, e.getName());
   pstmt.setString(2, e.getSsn());
   pstmt.setString(3, e.getName());
   pstmt.setString(4, e.getBirthday());
   pstmt.setInt(5, e.getLunar());
   pstmt.setString(6, e.getTelephone());
   pstmt.setString(7, e.getDepartmentId());
   pstmt.setString(8, e.getPositionId());
   pstmt.setString(9, e.getRegionId());
   pstmt.setInt(10, e.getBasicPay());
   pstmt.setInt(11, e.getExtraPay());

result = pstmt.executeUpdate();

  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   try {
    if (pstmt != null) {
     pstmt.close();
    }
   } catch (Exception ex) {

   }
   DBConn.close();
  }

return result;
}

// 직위 종류 출력 메소드
public ArrayList<Position> positionList() {
ArrayList<Position> result = new ArrayList<Position>();

  // SELECT positionId, positionName, minBasicPay
  // FROM position
  // ORDER BY positionId
  Connection conn = null;
  PreparedStatement pstmt = null;

try {
conn = DBConn.getConnection();

   String sql = "SELECT positionId, positionName, minBasicPay FROM position ORDER BY positionId";
   pstmt = conn.prepareStatement(sql);
   ResultSet rs = pstmt.executeQuery();

   while (rs.next()) {
    Position p = new Position();
    p.setPositionId(rs.getString("positionId"));
    p.setPositionName(rs.getString("positionName"));
    p.setMinBasicPay(rs.getInt("minBasicPay"));
    result.add(p);

}

rs.close();

  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   try {
    if (pstmt != null) {
     pstmt.close();
    }
   } catch (Exception ex) {

   }
   DBConn.close();
  }

return result;
}

// 최소 기본급 검색 메소드
public int getMinBasicPay(String positionId) {
int result = 0;

  // SELECT minBasicPay FROM position WHERE positionId=?
  Connection conn = null;
  PreparedStatement pstmt = null;

try {
conn = DBConn.getConnection();

   String sql = "SELECT minBasicPay FROM position WHERE positionId=?";
   pstmt = conn.prepareStatement(sql);
   pstmt.setString(1, positionId);

ResultSet rs = pstmt.executeQuery();

   while (rs.next()) {
    result = rs.getInt("minBasicPay");
   }

rs.close();

  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   try {
    if (pstmt != null) {
     pstmt.close();
    }
   } catch (Exception ex) {

   }
   DBConn.close();
  }

return result;
}

// 지역 종류 출력 메소드
public ArrayList<Region> regionList() {

ArrayList<Region> result = new ArrayList<Region>();

  Connection conn = null;
  PreparedStatement pstmt = null;
  try {
   conn = DBConn.getConnection();

   // SELECT regionId, regionName, delCheck
   // FROM regionView
   // ORDER BY regionId;
   String sql = "SELECT regionId, regionName, delCheck FROM regionView ORDER BY regionId";
   pstmt = conn.prepareStatement(sql);
   ResultSet rs = pstmt.executeQuery();
   while (rs.next()) {
    Region r = new Region();
    r.setRegionId(rs.getString("regionId"));
    r.setRegionName(rs.getString("regionName"));
    r.setDelCheck(rs.getInt("delCheck"));
    result.add(r);
   }
   rs.close();

  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if (pstmt != null) {
     pstmt.close();
    }
   } catch (Exception e) {
   }

DBConn.close();
}

return result;
}

// 부서 종류 출력 메소드
public ArrayList<Department> departmentList() {

ArrayList<Department> result = new ArrayList<Department>();

Connection conn = null;
PreparedStatement pstat = null;

  try {
   conn = DBConn.getConnection();
   String sql = "SELECT departmentId, departmentName, delCheck FROM departmentView ORDER BY departmentId";
   pstat = conn.prepareStatement(sql);
   ResultSet rs = pstat.executeQuery();
   while (rs.next()) {
    Department d = new Department();
    d.setDepartmentId(rs.getString("departmentId"));
    d.setDepartmentName(rs.getString("departmentName"));
    d.setDelCheck(rs.getInt("delCheck"));
    result.add(d);
   }
   rs.close();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if (pstat != null) {
     pstat.close();
    }
   } catch (Exception e) {
   }
   DBConn.close();
  }
  return result;
}

// 로그인 메소드
// grade 변수에는 0 또는 1 전달될 예정
// 0->관리자 로그인 시도
// 1->일반직원 로그인 시도
public int login(String id, String pw, String grade) {
int result = 0;

Connection conn = null;
PreparedStatement pstmt = null;

  try {
   conn = DBConn.getConnection();
   String sql = String
     .format("SELECT COUNT(*) AS count FROM employeeView WHERE name = ? AND ssn = encrypt(?,?)");
   if (grade.equals("0")) {
    sql += " AND grade=0";
   }

   pstmt = conn.prepareStatement(sql);
   pstmt.setString(1, id);
   pstmt.setString(2, pw);
   pstmt.setString(3, id);

   ResultSet rs = pstmt.executeQuery();
   while (rs.next()) {
    result = rs.getInt("count");
   }
   rs.close();

  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if (pstmt != null) {
     pstmt.close();
    }
   } catch (Exception e) {
   }
   DBConn.close();
  }

return result;

}

// 직원 명단 삭제 메소드
public int remove(String employeeId) {
int result = 0;

  Connection conn = null;
  PreparedStatement pstmt = null;
  try {
   conn = DBConn.getConnection();
   String sql = "DELETE FROM employee where employeeId =?";
   pstmt = conn.prepareStatement(sql);

pstmt.setString(1, employeeId);

result = pstmt.executeUpdate();

  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if (pstmt != null) {
     pstmt.close();
    }
   } catch (Exception e) {
    e.printStackTrace();
   }
  }
  DBConn.close();
  return result;
}

// 직원 수정 메소드
public int modify(Employee e) {
int result = 0;

Connection conn = null;
PreparedStatement pstmt = null;

  try {
   conn = DBConn.getConnection();
   /*
    * --직원 정보 수정 쿼리 UPDATE employee SET birthday=? ,lunar=?
    * ,telephone=? ,departmentId=? ,positionId=? ,regionId=?
    * ,basicPay=? ,extraPay=? ,grade=0 WHERE employeeId=1 and
    * ssn=encrypt('8012121122345', '홍길동');
    */
   String sql = "UPDATE employee SET birthday=?,lunar=?,telephone=?,departmentId=?,positionId=?,regionId=?,basicPay=?,extraPay=?,grade=? WHERE employeeId=? and ssn=encrypt(?, ?)";
   pstmt = conn.prepareStatement(sql);

   pstmt.setString(1, e.getBirthday());
   pstmt.setInt(2, e.getLunar());
   pstmt.setString(3, e.getTelephone());
   pstmt.setString(4, e.getDepartmentId());
   pstmt.setString(5, e.getPositionId());
   pstmt.setString(6, e.getRegionId());
   pstmt.setInt(7, e.getBasicPay());
   pstmt.setInt(8, e.getExtraPay());
   pstmt.setInt(9, e.getGrade());
   pstmt.setString(10, e.getEmployeeId());
   pstmt.setString(11, e.getSsn());
   pstmt.setString(12, e.getName());

result = pstmt.executeUpdate();

  } catch (Exception e1) {
   e1.printStackTrace();
  } finally {
   try {
    if (pstmt != null) {
     pstmt.close();
    }
   } catch (Exception e2) {
    e2.printStackTrace();
   }
   DBConn.close();
  }

return result;
}

}

//Action.java
package com.test;

import java.io.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;

//액션 전용 클래스 -> 메소드 단위로 액션 처리
public class Action {

/*
public String 메소드이름(HttpServletRequest request, HttpServletResponse response)
  throws ServletException, IOException {

  return "WEB-INF/source/요청주소";
  //return "redirect:요청주소";
}
*/

public String regionList(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  //액션 및 결과 출력
  RegionDAO dao = new RegionDAO();
  request.setAttribute("list", dao.regionList());

  return "WEB-INF/source/RegionList.jsp";
}

public String regionInsert(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String regionName = request.getParameter("regionName");

  //데이터베이스 액션 -> add() 메소드 호출로 대체
  RegionDAO dao = new RegionDAO();
  Region r = new Region();
  r.setRegionName(regionName);
  dao.add(r);
  return "redirect:RegionList.it";
}

public String regionDelete(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String regionId = request.getParameter("regionId");

  //데이터베이스 액션 -> add() 메소드 호출로 대체
  RegionDAO dao = new RegionDAO();
  dao.remove(regionId);

  return "redirect:RegionList.it";
}

public String regionUpdate(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String regionName = request.getParameter("regionName");
  String regionId = request.getParameter("regionId");

  //데이터베이스 액션 -> add() 메소드 호출로 대체
  RegionDAO dao = new RegionDAO();
  Region r = new Region();
  r.setRegionName(regionName);
  r.setRegionId(regionId);
  dao.modify(r);

  return "redirect:RegionList.it";
}

public String positionList(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  //액션 및 결과 출력
  PositionDAO dao = new PositionDAO();
  request.setAttribute("list", dao.positionList());

  return "WEB-INF/source/PositionList.jsp";
}

public String positionInsert(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String positionName = request.getParameter("positionName");
  String minBasicPay = request.getParameter("minBasicPay");
  //데이터베이스 액션 -> add() 메소드 호출로 대체
  PositionDAO dao = new PositionDAO();
  Position p = new Position();
  p.setPositionName(positionName);
  p.setMinBasicPay(Integer.parseInt(minBasicPay));
  dao.add(p);
  return "redirect:PositionList.it";
}

public String positionDelete(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String positionId = request.getParameter("positionId");

  //데이터베이스 액션 -> add() 메소드 호출로 대체
  PositionDAO dao = new PositionDAO();
  dao.remove(positionId);

  return "redirect:PositionList.it";
}

public String positionUpdate(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String positionName = request.getParameter("positionName");
  String positionId = request.getParameter("positionId");
  String minBasicPay = request.getParameter("minBasicPay");
  //데이터베이스 액션 -> add() 메소드 호출로 대체
  PositionDAO dao = new PositionDAO();
  Position p = new Position();
  p.setPositionName(positionName);
  p.setPositionId(positionId);
  p.setMinBasicPay(Integer.parseInt(minBasicPay));
  dao.modify(p);

  return "redirect:PositionList.it";
}

public String departmentList(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  //액션 및 결과 출력
  DepartmentDAO dao = new DepartmentDAO();
  request.setAttribute("list", dao.departmentList());

  return "WEB-INF/source/DepartmentList.jsp";
}

public String departmentInsert(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String departmentName = request.getParameter("departmentName");

  //데이터베이스 액션 -> add() 메소드 호출로 대체
  DepartmentDAO dao = new DepartmentDAO();
  Department d = new Department();
  d.setDepartmentName(departmentName);
  dao.add(d);
  return "redirect:DepartmentList.it";
}

public String departmentDelete(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String departmentId = request.getParameter("departmentId");

  //데이터베이스 액션 -> add() 메소드 호출로 대체
  DepartmentDAO dao = new DepartmentDAO();
  dao.remove(departmentId);

  return "redirect:DepartmentList.it";
}

public String departmentUpdate(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  //액션 전용
  //데이터 수신(한글 인코딩 추가)
  request.setCharacterEncoding("UTF-8");
  String departmentName = request.getParameter("departmentName");
  String departmentId = request.getParameter("departmentId");

  //데이터베이스 액션 -> add() 메소드 호출로 대체
  DepartmentDAO dao = new DepartmentDAO();
  Department d = new Department();
  d.setDepartmentName(departmentName);
  d.setDepartmentId(departmentId);
  dao.modify(d);

  return "redirect:DepartmentList.it";
}

public String employeeList(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  //문제) 데이터베이스 액션 및 결과 데이터 전달
  EmployeeDAO dao = new EmployeeDAO();
  request.setAttribute("count", dao.count());
  request.setAttribute("list", dao.list());

  return "WEB-INF/source/EmployeeList.jsp";
}

public String employeeSearch(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  //문제) 데이터 수신 및 데이터베이스 액션, 결과 데이터 전달
  request.setCharacterEncoding("UTF-8");
  String searchKey = request.getParameter("searchKey");
  String searchValue = request.getParameter("searchValue");
  if (searchKey != null) {
   EmployeeDAO dao = new EmployeeDAO();
   request.setAttribute("count", dao.count(searchKey, searchValue));
   request.setAttribute("list", dao.list(searchKey, searchValue));
  }

  request.setAttribute("searchKey", searchKey);
  request.setAttribute("searchValue", searchValue);

  return "WEB-INF/source/EmployeeSearch.jsp";
}

public String employeeInsertForm(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  EmployeeDAO dao = new EmployeeDAO();

  request.setAttribute("positionList", dao.positionList());
  request.setAttribute("regionList", dao.regionList());
  request.setAttribute("departmentList", dao.departmentList());

  return "WEB-INF/source/EmployeeInsertForm.jsp";
}

public String employeeInsert(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  //데이터 수신
  request.setCharacterEncoding("UTF-8");
  String name = request.getParameter("name");
  String ssn = request.getParameter("ssn");
  String birthday = request.getParameter("birthday");
  String lunar = request.getParameter("lunar");
  String telephone = request.getParameter("telephone");
  String regionId = request.getParameter("regionId");
  String departmentId = request.getParameter("departmentId");
  String positionId = request.getParameter("positionId");
  String basicPay = request.getParameter("basicPay");
  String extraPay = request.getParameter("extraPay");

  //데이터베이스 액션 -> add() 메소드 호출
  EmployeeDAO dao = new EmployeeDAO();
  Employee e = new Employee();
  e.setName(name);
  e.setSsn(ssn);
  e.setBirthday(birthday);
  e.setLunar(Integer.parseInt(lunar));
  e.setTelephone(telephone);
  e.setRegionId(regionId);
  e.setDepartmentId(departmentId);
  e.setPositionId(positionId);
  e.setBasicPay(Integer.parseInt(basicPay));
  e.setExtraPay(Integer.parseInt(extraPay));
  dao.add(e);

  return "redirect:EmployeeList.it";
}

public String positionReceive(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  //데이터 수신 (positionId)
  String positionId = request.getParameter("positionId");

  EmployeeDAO dao = new EmployeeDAO();
  request.setAttribute("result", String.valueOf(dao.getMinBasicPay(positionId)));

  return "WEB-INF/source/PositionReceive.jsp";
}

public String employeeDelete(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

   request.setCharacterEncoding("UTF-8");
   String employeeId = request.getParameter("employeeId");

   EmployeeDAO dao = new EmployeeDAO();
   dao.remove(employeeId);


   return "redirect:EmployeeList.it";
}

public String employeeUpdateForm(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  //데이터 수신 -> emploeeId
  //데이터베이스 액션 -> list("employeeId",value) 메소드

  EmployeeDAO dao = new EmployeeDAO();

  String employeeId = request.getParameter("employeeId");

  request.setAttribute("emp", (dao.list("0", employeeId)).get(0));

  request.setAttribute("positionList", dao.positionList());
  request.setAttribute("departmentList",dao.departmentList());
  request.setAttribute("regionList",dao.regionList());

  return "WEB-INF/source/EmployeeUpdateForm.jsp";
}
public String employeeUpdate(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

  request.setCharacterEncoding("UTF-8");

  //데이터 수신
  String employeeId = request.getParameter("employeeId");
  String name = request.getParameter("name");
  String ssn = request.getParameter("ssn");
  String birthday = request.getParameter("birthday");
  String lunar = request.getParameter("lunar");
  String telephone = request.getParameter("telephone");
  String regionId = request.getParameter("regionId");
  String departmentId = request.getParameter("departmentId");
  String positionId = request.getParameter("positionId");
  String basicPay = request.getParameter("minBasicPay");
  String extraPay = request.getParameter("extraPay");
  String grade = request.getParameter("grade");

  //DB 액션 -> modify() 메소드 호출
  Employee e = new Employee();
  e.setEmployeeId(employeeId);
  e.setName(name);
  e.setSsn(ssn);
  e.setBirthday(birthday);
  e.setLunar(Integer.parseInt(lunar));
  e.setTelephone(telephone);
  e.setRegionId(regionId);
  e.setDepartmentId(departmentId);
  e.setPositionId(positionId);
  e.setBasicPay(Integer.parseInt(basicPay));
  e.setExtraPay(Integer.parseInt(extraPay));
  e.setGrade(Integer.parseInt(grade));

  EmployeeDAO dao = new EmployeeDAO();

  dao.modify(e);

  return "redirect:EmployeeList.it";
}

}

//Controller.java
package com.test;

import java.io.IOException;

import javax.servlet.*;
import javax.servlet.http.*;
import com.sun.corba.se.spi.orbutil.fsm.*;

public class Controller extends HttpServlet {

private static final long serialVersionUID = 1L;

@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}

@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}

private void doGetPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {

  // 요청 주소 분석 및 액션 메소드 호출
  String uri = request.getRequestURI();
  // -> "http://localhost:8090/프로젝트이름/Sample.it"

Action action = new Action();
String result = "Error.jsp";

  //요청 주소별로 작성할 것.
  if (uri.indexOf("RegionList.it") != -1) {
   // 액션 메소드 호출
   // 메소드 결과는 URI 주소 형태를 가지고 있다.
   result = action.regionList(request, response);
  }

  if (uri.indexOf("RegionInsert.it") != -1) {
   result = action.regionInsert(request, response);
  }

  if (uri.indexOf("RegionDelete.it") != -1) {
   result = action.regionDelete(request, response);
  }

  if (uri.indexOf("RegionUpdate.it") != -1) {
   result = action.regionUpdate(request, response);
  }

  if (uri.indexOf("PositionList.it") != -1) {
   // 액션 메소드 호출
   // 메소드 결과는 URI 주소 형태를 가지고 있다.
   result = action.positionList(request, response);
  }

  if (uri.indexOf("PositionInsert.it") != -1) {
   result = action.positionInsert(request, response);
  }

  if (uri.indexOf("PositionDelete.it") != -1) {
   result = action.positionDelete(request, response);
  }

  if (uri.indexOf("PositionUpdate.it") != -1) {
   result = action.positionUpdate(request, response);
  }

  if (uri.indexOf("DepartmentList.it") != -1) {
   // 액션 메소드 호출
   // 메소드 결과는 URI 주소 형태를 가지고 있다.
   result = action.departmentList(request, response);
  }

  if (uri.indexOf("DepartmentInsert.it") != -1) {
   result = action.departmentInsert(request, response);
  }

  if (uri.indexOf("DepartmentDelete.it") != -1) {
   result = action.departmentDelete(request, response);
  }

  if (uri.indexOf("DepartmentUpdate.it") != -1) {
   result = action.departmentUpdate(request, response);
  }


  //문제) 주소 등록
  if (uri.indexOf("EmployeeList.it") != -1) {
   result = action.employeeList(request, response);
  }
  if (uri.indexOf("EmployeeSearch.it") != -1) {
   result = action.employeeSearch(request, response);
  }
  if (uri.indexOf("EmployeeInsertForm.it") != -1) {
   result = action.employeeInsertForm(request, response);
  }
  if (uri.indexOf("EmployeeInsert.it") != -1) {
   result = action.employeeInsert(request, response);
  }
  if (uri.indexOf("PositionReceive.it") != -1) {
   result = action.positionReceive(request, response);
  }
  if (uri.indexOf("EmployeeDelete.it") != -1) {
   result = action.employeeDelete(request, response);
  }
  if (uri.indexOf("EmployeeUpdateForm.it") != -1) {
   result = action.employeeUpdateForm(request, response);
  }
  if (uri.indexOf("EmployeeUpdate.it") != -1) {
   result = action.employeeUpdate(request, response);
  }

  // 페이지 전환 -> forward(), sendRedirect() 메소드
  if (result.indexOf("redirect:") != -1) {
   // sendRedirect 요청
   // -> 또 다른 서블릿 주소 요청
   response.sendRedirect(result.substring(9));
  } else {
   // forward 요청
   // -> 연관된 JSP 페이지 요청
   RequestDispatcher dispatcher = request.getRequestDispatcher(result);
   dispatcher.forward(request, response);
  }
}

}

//EmployeeUpdateForm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>직원관리(Servlet MVC 버전)</title>

$("#birthday").datepicker({
  changeYear:true
  , changeMonth: true
  , dateFormat:"yy-mm-dd"
});

$("#btnSearch").click(function() {
$(location).attr("href", "EmployeeSearch.it");
});
$("#btnList").click(function() {
$(location).attr("href", "EmployeeList.it");
});

//기본급
minBasicPay($("#positionId").val());

//선택 목록에 체인지 이벤트 등록
$("#positionId").change(function(){
  minBasicPay($("#positionId").val());
});

//양/음력 자동 선택
for (var i=0; i<$(".lunar").length; ++i) {
  var lunar = $(".lunar").eq(i);
  if ($(lunar).val() == "${emp.lunar}") {
   $(lunar).attr("checked", "checked");
  }
}

//지역 자동 선택
var regionOptions = $("#regionId").find("option");
for (var i=0; i<$(regionOptions).length; ++i) {
  var regionOption = $(regionOptions).eq(i);
  if ($(regionOption).val() == "${emp.regionId}") {
   $(regionOption).attr("selected", "selected");
  }
}

//문제) 자동 선택 과정 추가
//부서, 직위 항목
//등급

});

function minBasicPay(positionId) {

$.get("PositionReceive.it?positionId=" + $("#positionId").val(),
   function(result) {
    $("#minBasicPay").html(result);
   });
}
</script>

</head>
<body>
<div>
<%--메뉴 영역--%>
<div class = "title">
<%--
  동일한 메뉴 디자인을 여러 페이지에 적용한 경우
  수정 사항이 발생하면 모든 페이지를 동일하게 수정해야 한다.
  -> 동일한 메뉴 디자인을 별도의 페이지로 작성
  -> 필요하는 페이지에 동적 삽입
--%>
  <h1>직원관리</h1>
  <c:import url="EmployeeMenu.jsp"></c:import>
</div>
<%--콘텐츠 영역--%>
<div class = "main">
  <h2>[직원명단]</h2>

  <form>
   <%-- 직원명단 버튼은 식별자로 class 사용 --%>
   <input type="button" value="직원명단" id="btnList">
   <input type="button" value="직원검색" id="btnSearch">
  </form>

         <form action="EmployeeUpdate.it" method="post" id="myForm">
            <table id="t02">
               <tr>
                  <th>직원번호</th>
                  <td><input type="text" id="employeeId" name="employeeId" readonly="readonly" value="${emp.employeeId}"> readonly</td>
               </tr>
               <tr>
                  <th>이름</th>
                  <td><input type="text" id="name" name="name" readonly="readonly" value="${emp.name}"> readonly</td>
               </tr>
               <tr>
                  <th>주민번호</th>
                  <td><input type="text" id="ssn" name="ssn" required="required"> 재입력 </td>
               </tr>
               <tr>
                  <th>생년월일</th>
                  <td>
                  
                  <input type="text" id="birthday" name="birthday" value="${emp.birthday}" required="required">달력

                  </td>
               </tr>
               <tr>
                  <th>양/음력</th>
                  <td><input type="radio" class="lunar" name="lunar" value="0" >양력
                     <input type="radio" class="lunar" name="lunar" value="1">음력</td>
               </tr>
               <tr>
                  <th>전화번호</th>
                  <td><input type="text" id="telephone" name="telephone" value="${emp.telephone }" required="required"></td>
               </tr>
               <tr>
                  <th>지역명</th>
                  <td><select id="regionId" name="regionId">
                        
                        <c:forEach var="r" items="${regionList}">
                        <option value="${r.regionId}">${r.regionName}</option>
                        </c:forEach>
                  </select></td>
               </tr>
               <tr>
                  <th>부서명</th>
                  <td><select id="departmentId" name="departmentId">
                        
                        <c:forEach var="d" items="${departmentList}">
                        <option value="${d.departmentId}">${d.departmentName}</option>
                        </c:forEach>
                  </select></td>
               </tr>
               <tr>
                  <th>직위명</th>
                  <td><select id="positionId" name="positionId">
                        
                                <c:forEach var="p" items="${positionList}">
                        <option value="${p.positionId}">${p.positionName}</option>
                        </c:forEach>

                  </select></td>
               </tr>
               <tr>
                  <th>기본급</th>
                  <td><input type="text" name="minBasicPay" value="${emp.basicPay }" required="required">(최소 <span id="minBasicPay"></span>원
                     이상)</td>
               </tr>
               <tr>
                  <th>수당</th>
                  <td><input type="text" name="extraPay" value="${emp.extraPay }"   required="required"></td>
               </tr>
               <tr>
                  <th>등급</th>
                  <td><input type="radio" class="grade" name="grade" value="0" >관리자
                     <input type="radio" class="grade" name="grade" value="1">일반직원</td>
               <tr>
                  <th></th>
                  <td><input type="submit" value="직원수정"></td>
               </tr>
            </table>
         </form>


</div>
</div>
</body>
</html>

------------------------------------------------

저작자표시

'MVC' 카테고리의 다른 글

08일차_MVC패턴, 직원관리 (0)	2015.06.30
06일차_MVC패턴, 직원관리 (0)	2015.06.30
05일차_MVC패턴, 직원관리 (0)	2015.06.30
04일차_MVC패턴, 회원관리, 성적관리 (0)	2015.06.30
03일차(2)_모델2방식, 성적관리 (0)	2015.06.30

알 수 없는 사용자

,

06일차_MVC패턴, 직원관리

MVC 2015. 6. 30. 14:40

-----------------------------
직원관리 Servlet(MVC 패턴) 방식
- 서블릿 주소를 확장자 형태 사용.
- web.xml 파일에는 확장자 형태의 주소 한 개만 등록
- 주소 분석 및 액션 요청을 위한 전용 서블릿 클래스 작성.
- 액션 담당 전용 클래스 작성.

** 환경 설정 추가 -> WEB-INF/source 폴더 하위에 JSP 페이지 등록

10. 소스코드

//Employee.java -> grade 항목 추가
package com.test;

public class Employee {

private String employeeId
, name
, ssn
, birthday
, lunarName
, telephone
, departmentId
, departmentName
, positionId
, positionName
, regionId
, regionName;

private int lunar; // 0(양력) 또는 1(음력)
private int basicPay, extraPay, pay;

private int grade; //등급 (0 또는 1)

public int getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}

public String getSsn() {
  return ssn;
}
public void setSsn(String ssn) {
  this.ssn = ssn;
}

public String getEmployeeId() {
  return employeeId;
}
public void setEmployeeId(String employeeId) {
  this.employeeId = employeeId;
}
public String getName() {
  return name;
}
public void setName(String name) {
  this.name = name;
}
public String getBirthday() {
  return birthday;
}
public void setBirthday(String birthday) {
  this.birthday = birthday;
}
public String getLunarName() {
  return lunarName;
}
public void setLunarName(String lunarName) {
  this.lunarName = lunarName;
}
public String getTelephone() {
  return telephone;
}
public void setTelephone(String telephone) {
  this.telephone = telephone;
}
public String getDepartmentId() {
  return departmentId;
}
public void setDepartmentId(String departmentId) {
  this.departmentId = departmentId;
}
public String getDepartmentName() {
  return departmentName;
}
public void setDepartmentName(String departmentName) {
  this.departmentName = departmentName;
}
public String getPositionId() {
  return positionId;
}
public void setPositionId(String positionId) {
  this.positionId = positionId;
}
public String getPositionName() {
  return positionName;
}
public void setPositionName(String positionName) {
  this.positionName = positionName;
}
public String getRegionId() {
  return regionId;
}
public void setRegionId(String regionId) {
  this.regionId = regionId;
}
public String getRegionName() {
  return regionName;
}
public void setRegionName(String regionName) {
  this.regionName = regionName;
}
public int getLunar() {
  return lunar;
}
public void setLunar(int lunar) {
  this.lunar = lunar;
}
public int getBasicPay() {
  return basicPay;
}
public void setBasicPay(int basicPay) {
  this.basicPay = basicPay;
}
public int getExtraPay() {
  return extraPay;
}
public void setExtraPay(int extraPay) {
  this.extraPay = extraPay;
}
public int getPay() {
  return pay;
}
public void setPay(int pay) {
  this.pay = pay;
}

}

//EmployeeDAO.java
package com.test;

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

public class EmployeeDAO {

//직원 명단 출력 메소드
public ArrayList<Employee> list(){
  ArrayList<Employee> result = new ArrayList<Employee>();

  Connection conn = null;
  PreparedStatement pstmt = null;

  try{
   conn = DBConn.getConnection();
   String sql = String.format("SELECT employeeId, name, birthday, lunar, lunarName, telephone, departmentId, departmentName, positionId, positionName, regionId, regionName, basicPay, extraPay, pay, grade FROM employeeView ORDER BY employeeId");
   pstmt = conn.prepareStatement(sql);
   ResultSet rs = pstmt.executeQuery();
   while(rs.next()){
    Employee e = new Employee();
    e.setEmployeeId(rs.getString("employeeId"));
    e.setName(rs.getString("name"));
    e.setBirthday(rs.getString("birthday"));
    e.setLunar(rs.getInt("lunar"));
    e.setLunarName(rs.getString("lunarName"));
    e.setTelephone(rs.getString("telephone"));
    e.setDepartmentId(rs.getString("departmentId"));
    e.setDepartmentName(rs.getString("departmentName"));
    e.setPositionId(rs.getString("positionId"));
    e.setPositionName(rs.getString("positionName"));
    e.setRegionId(rs.getString("regionId"));
    e.setRegionName(rs.getString("regionName"));
    e.setBasicPay(rs.getInt("basicPay"));
    e.setExtraPay(rs.getInt("extraPay"));
    e.setPay(rs.getInt("pay"));
    e.setGrade(rs.getInt("grade"));
    result.add(e);
   }
   rs.close();
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   try{
    if(pstmt != null){
     pstmt.close();
    }
   }catch(Exception e){
   }
   DBConn.close();
  }
  return result;
}

//직원 검색 출력 메소드(검색기준 : 사번,주민번호,이름,지역,부서,직위)
public ArrayList<Employee> list(String searchKey, String searchValue){
  ArrayList<Employee> result = new ArrayList<Employee>();

  Connection conn = null;
  PreparedStatement pstmt = null;
  try{
   conn = DBConn.getConnection();
   String sql = String.format("SELECT employeeId, name, birthday, lunar, lunarName, telephone, departmentId, departmentName, positionId, positionName, regionId, regionName, basicPay, extraPay, pay, grade FROM employeeView e");
   switch(searchKey){
   case "0" : sql += " WHERE employeeId=?"; break;
   case "1" : sql += " WHERE ssn=encrypt(?, e.name)"; break;
   case "2" : sql += " WHERE name=?"; break;
   case "3" : sql += " WHERE regionName=?"; break;
   case "4" : sql += " WHERE departmentName=?"; break;
   case "5" : sql += " WHERE positionName=?"; break;
   case "9" : sql += ""; break; //전체 검색
   }
   sql += " ORDER BY employeeId";
   pstmt = conn.prepareStatement(sql);
   if (!searchKey.equals("9")) {
    pstmt.setString(1, searchValue);
   }
   ResultSet rs = pstmt.executeQuery();
   while(rs.next()){
    Employee e = new Employee();
    e.setEmployeeId(rs.getString("employeeId"));
    e.setName(rs.getString("name"));
    e.setBirthday(rs.getString("birthday"));
    e.setLunarName(rs.getString("lunarName"));
    e.setTelephone(rs.getString("telephone"));
    e.setDepartmentName(rs.getString("departmentName"));
    e.setPositionName(rs.getString("positionName"));
    e.setRegionName(rs.getString("regionName"));
    e.setBasicPay(rs.getInt("basicPay"));
    e.setExtraPay(rs.getInt("extraPay"));
    e.setPay(rs.getInt("pay"));
    e.setGrade(rs.getInt("grade"));
    result.add(e);
   }
   rs.close();
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   try{
    if(pstmt != null){
     pstmt.close();
    }
   }catch(Exception e){

   }
   DBConn.close();
  }
  return result;
}
//직원 전체 출력
public int count(){
  int result = 0;
  Connection conn = null;
  PreparedStatement pstmt = null;

  try{
   conn = DBConn.getConnection();
   String sql = String.format("select count(*) as \"count\" from employeeView");
   pstmt = conn.prepareStatement(sql);
   ResultSet rs = pstmt.executeQuery();
   while(rs.next()){
    result = rs.getInt("count");
   }
   rs.close();
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   try{
    if(pstmt != null){
     pstmt.close();
    }
   }catch(Exception e){

   }
   DBConn.close();
  }
  return result;
}
//직원 검색 결과 인원수 출력 메소드
  public int count(String searchKey, String searchValue){
   int result = 0;
   Connection conn = null;
   PreparedStatement pstmt = null;

   try{
    conn = DBConn.getConnection();
    String sql = String.format("select count(*) as \"count\" from employeeView e");
    switch(searchKey){
    case "0" : sql += " WHERE employeeId=?"; break;
    case "1" : sql += " WHERE ssn=encrypt(?, e.name)"; break;
    case "2" : sql += " WHERE name=?"; break;
    case "3" : sql += " WHERE regionName=?"; break;
    case "4" : sql += " WHERE departmentName=?"; break;
    case "5" : sql += " WHERE positionName=?"; break;
    case "9" : sql += ""; break; //전체 검색
    }
    pstmt = conn.prepareStatement(sql);
    if (!searchKey.equals("9")) {
     pstmt.setString(1, searchValue);
    }
    ResultSet rs = pstmt.executeQuery();
    while(rs.next()){
     result = rs.getInt("count");
    }
    rs.close();
   }catch(Exception e){
    e.printStackTrace();
   }finally{
    try{
     if(pstmt != null){
      pstmt.close();
     }
    }catch(Exception e){

    }
    DBConn.close();
   }
   return result;
  }



  //직원 입력 메소드
  public int add(Employee e) {
   int result = 0;
   /*
   INSERT INTO employee (employeeId
   ,name
   ,ssn
   ,birthday
   ,lunar
   ,telephone
   ,departmentId
   ,positionId
   ,regionId
   ,basicPay
   ,extraPay)
   VALUES (employeeSeq.nextval
   , ?
   , encrypt(?, ?)
   , ? --'1980-12-12'
   , ? --양력 0, 음력 1
   , ? --'010-123-1234'
   , ? --departmentId FK
   , ? --positionId FK
   , ? --regionId FK
   , ?
   , ?)
   */

   //문제) 입력 관련 액션 추가
        Connection conn = null;
        PreparedStatement pstmt = null;

        try{
           conn = DBConn.getConnection();
           String sql = String.format("INSERT INTO employee (employeeId,name,ssn,birthday ,lunar,telephone,departmentId ,positionId,regionId ,basicPay,extraPay) VALUES (employeeSeq.nextval, ?, encrypt(?, ?) ,? , ? ,? ,? , ?,? , ?, ?)");
           pstmt = conn.prepareStatement(sql);

           pstmt.setString(1, e.getName());
           pstmt.setString(2, e.getSsn());
           pstmt.setString(3,e.getName());
           pstmt.setString(4, e.getBirthday());
           pstmt.setInt(5, e.getLunar());
           pstmt.setString(6, e.getTelephone());
           pstmt.setString(7, e.getDepartmentId());
           pstmt.setString(8, e.getPositionId());
           pstmt.setString(9, e.getRegionId());
           pstmt.setInt(10, e.getBasicPay());
           pstmt.setInt(11, e.getExtraPay());

result = pstmt.executeUpdate();

        }catch(Exception ex){
           ex.printStackTrace();
        }finally{
           try{
              if(pstmt != null){
                 pstmt.close();
              }
           }catch(Exception ex){

           }
           DBConn.close();
        }


   return result;
  }

  //직위 종류 출력 메소드
  public ArrayList<Position> positionList() {
   ArrayList<Position> result = new ArrayList<Position>();

   //SELECT positionId, positionName, minBasicPay
      // FROM position
      // ORDER BY positionId
     Connection conn = null;
     PreparedStatement pstmt = null;

     try{
        conn = DBConn.getConnection();

     String sql = "SELECT positionId, positionName, minBasicPay FROM position ORDER BY positionId";
     pstmt = conn.prepareStatement(sql);
     ResultSet rs = pstmt.executeQuery();

     while(rs.next()){
      Position p = new Position();
      p.setPositionId(rs.getString("positionId"));
      p.setPositionName(rs.getString("positionName"));
      p.setMinBasicPay(rs.getInt("minBasicPay"));
      result.add(p);

     }

     rs.close();

     }catch(Exception ex){
            ex.printStackTrace();
         }finally{
            try{
               if(pstmt != null){
                  pstmt.close();
               }
            }catch(Exception ex){

            }
            DBConn.close();
     }

   return result;
  }

  //최소 기본급 검색 메소드
  public int getMinBasicPay(String positionId) {
   int result = 0;

   //SELECT minBasicPay FROM position WHERE positionId=?
     Connection conn = null;
     PreparedStatement pstmt = null;

     try{
        conn = DBConn.getConnection();

     String sql = "SELECT minBasicPay FROM position WHERE positionId=?";
     pstmt = conn.prepareStatement(sql);
     pstmt.setString(1, positionId);

     ResultSet rs = pstmt.executeQuery();

     while(rs.next()){
      result = rs.getInt("minBasicPay");
     }

     rs.close();

     }catch(Exception ex){
            ex.printStackTrace();
         }finally{
            try{
               if(pstmt != null){
                  pstmt.close();
               }
            }catch(Exception ex){

            }
            DBConn.close();
     }

   return result;
  }

  //지역 종류 출력 메소드
  public ArrayList<Region> regionList() {

   ArrayList<Region> result = new ArrayList<Region>();

   Connection conn = null;
   PreparedStatement pstmt = null;
   try {
    conn = DBConn.getConnection();

    //SELECT regionId, regionName, delCheck
    // FROM regionView
    // ORDER BY regionId;
    String sql = "SELECT regionId, regionName, delCheck FROM regionView ORDER BY regionId";
    pstmt = conn.prepareStatement(sql);
    ResultSet rs = pstmt.executeQuery();
    while(rs.next()) {
     Region r = new Region();
     r.setRegionId(rs.getString("regionId"));
     r.setRegionName(rs.getString("regionName"));
     r.setDelCheck(rs.getInt("delCheck"));
     result.add(r);
    }
    rs.close();

   }catch(Exception e){
    e.printStackTrace();
   }finally{
    try {
     if (pstmt != null) {
      pstmt.close();
     }
    }catch(Exception e){
    }

    DBConn.close();
   }

   return result;
  }

  //부서 종류 출력 메소드
  public ArrayList<Department> departmentList() {

   ArrayList<Department> result = new ArrayList<Department>();

   Connection conn = null;
   PreparedStatement pstat = null;

   try{
    conn = DBConn.getConnection();
    String sql = "SELECT departmentId, departmentName, delCheck FROM departmentView ORDER BY departmentId";
    pstat = conn.prepareStatement(sql);
    ResultSet rs = pstat.executeQuery();
    while(rs.next()){
     Department d = new Department();
     d.setDepartmentId(rs.getString("departmentId"));
     d.setDepartmentName(rs.getString("departmentName"));
     d.setDelCheck(rs.getInt("delCheck"));
     result.add(d);
    }
    rs.close();
   }catch(Exception e){
    e.printStackTrace();
   }finally{
    try{
     if(pstat!=null){
      pstat.close();
     }
    }catch(Exception e){
    }
    DBConn.close();
   }
   return result;
  }

  //로그인 메소드
  //grade 변수에는 0 또는 1 전달될 예정
  //0->관리자 로그인 시도
  //1->일반직원 로그인 시도
  public int login(String id, String pw, String grade){
   int result = 0;

   Connection conn = null;
   PreparedStatement pstmt = null;

   try{
    conn = DBConn.getConnection();
    String sql = String.format("SELECT COUNT(*) AS count FROM employeeView WHERE name = ? AND ssn = encrypt(?,?)");
    if (grade.equals("0")) {
     sql += " AND grade=0";
    }

    pstmt = conn.prepareStatement(sql);
     pstmt.setString(1, id);
     pstmt.setString(2, pw);
     pstmt.setString(3, id);

    ResultSet rs = pstmt.executeQuery();
    while(rs.next()){
     result = rs.getInt("count");
    }
    rs.close();

   }catch(Exception e){
    e.printStackTrace();
   }finally{
    try{
     if(pstmt != null){
      pstmt.close();
     }
    }catch(Exception e){
    }
    DBConn.close();
   }

   return result;

  }


  //직원 삭제 메소드
  public int remove(String employeeId) {
   int result = 0;

   //DELETE FROM employee WHERE employeeId=?

   //문제) 삭제 액션 작성

   return result;
  }

  //직원 수정 메소드
  public int modify(Employee emp) {
   int result = 0;
   /*
   UPDATE employee SET birthday=?
     ,lunar=?
     ,telephone=?
     ,departmentId=?
     ,positionId=?
     ,regionId=?
     ,basicPay=?
     ,extraPay=?
     ,grade=?
    WHERE employeeId=? and ssn=encrypt(?, ?);
    */

   //문제) 수정 액션 작성

   return result;
  }


}