1. View 파트
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<jsp:useBean id="dao" class="src.mvc.dao.EDao"></jsp:useBean>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>사원 정보 입력</title>
</head>
<body>
<h1>사원 정보 입력</h1>
<hr>
<form action="input.do" method="post">
사원번호<input type="text" name="empno"><br>
사원이름<input type="text" name="ename"><br>
직급<input type="text" name="job"><br>
상사<select>
<c:forEach var="mgr" items="${dao.managerSelect()}">
<option value="${mgr.empno}">(${mgr.empno})(${mgr.ename})</option>
</c:forEach>
</select><br>
입사일<input type="date" name="rDate"><br>
급여<input type="text" name="sal"><br>
커미션<input type="text" name="comm"><br>
부서 <select>
<c:forEach var="dept" items="${dao.deptSelect()}">
<option value="${dept.empno}">(${dept.deptno})(${dept.dname})</option>
</c:forEach>
</select><br>
부서위치 <select>
<c:forEach var="loc" items="${dao.locSelect()}">
<option value="${loc.loc}">(${loc.loc})</option>
</c:forEach>
</select><br>
<input type="submit" value="입력">
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page import="java.sql.Timestamp" %>
<%@ page import="src.mvc.*" %>
<% request.setCharacterEncoding("UTF-8"); %>
<jsp:useBean id="dao" class="src.mvc.dao.EDao"></jsp:useBean>
<c:choose>
<c:when test="${dao.list() == null} ">
<script type="text/javascript">
alert("입력 데이터가 올바르지 않습니다!");
history.back();
</script>
</c:when>
</c:choose>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>정보입력이 정상적으로 처리 되었습니다.</h1>
<a href="list.do">리스트</a>
</body>
</html>
1-3. list.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 http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>직원 목록</title>
<style type="text/css">
tr:last-child {
background-color: yellow;
}
</style>
</head>
<body>
<h1>EMP 테이블 직원 목록</h1>
<table cellpadding="0" cellspacing="0" border="1">
<tr>
<td>사원번호
<td>사원이름
<td>직급(업무)
<td>상사(이름)
<td>입사일
<td>급여
<td>커미션
<td>부서번호
<td>부서이름
<td>부서위치
<td>관리
</tr>
<c:forEach var="dto" items="${list}">
<tr>
<td>${dto.empno}</td>
<td>${dto.ename}</td>
<td>${dto.job}</td>
<td>${dto.mgr}</td>
<td>${dto.hiredate}</td>
<td>${dto.sal}</td>
<td>${dto.comm}</td>
<td>${dto.deptno}</td>
<td>${dto.dname}</td>
<td>${dto.loc}</td>
<td><a href="#">수정</a></td>
</tr>
</c:forEach>
</table>
</body>
</html>
</html>
2. Model 파트
2-1. EFrontController.java
package src.mvc.controller;
import java.io.IOException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import src.mvc.command.ECommand;
import src.mvc.command.EDeptSelectCommand;
import src.mvc.command.EDeptWriteCommand;
import src.mvc.command.EEmpWriteCommand;
import src.mvc.command.EListCommand;
import src.mvc.command.ELocSelectCommand;
import src.mvc.command.EManagerSelectCommand;
/**
* Servlet implementation class BFrontController
*/
@WebServlet("*.do") // 모델 내 여러 커멘드를 하나의 컨트롤러로 로직 실행을 지시한다.
public class EFrontController extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public EFrontController() {
super();
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
System.out.println("doGet");
actionDo(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
System.out.println("doPost");
actionDo(request, response);
}
// 액션 메소드
private void actionDo(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
System.out.println("actionDo");
request.setCharacterEncoding("UTF-8");
String viewPage = null;
ECommand command = null;
// uri 요청
String uri = request.getRequestURI();
// 경로 요청
String conPath = request.getContextPath();
// 요청한 경로를 본 길이만큼 자른다.(서블릿내 매핑한 것으로)
String com = uri.substring(conPath.length());
// 요청했던 데이터들을 콘솔에 출력
System.out.println(uri);
System.out.println(conPath);
System.out.println(com);
// 해당 경로가 본 서블릿 매핑 경로와 일치할경우
if (com.equals("/list.do")) {
command = new EListCommand();// 커맨드 리스트 객체에 담는다.
command.execute(request, response);// 요청 및 응답 객체를 담아 커맨드 리스트를 실행한다.
viewPage = "list.jsp"; // Controller -> View로 응답한다.
} else if (com.equals("/input.do")) {
command = new EManagerSelectCommand();
command.execute(request, response);
viewPage = "input.jsp";
} else if (com.equals("/input.do")) {
command = new EDeptSelectCommand();
command.execute(request, response);
viewPage = "input.jsp";
} else if (com.equals("/input.do")) {
command = new ELocSelectCommand();
command.execute(request, response);
viewPage = "input.jsp";
} else if (com.equals("/input.do")) {
command = new EDeptWriteCommand();
command.execute(request, response);
viewPage = "input.jsp";
} else if (com.equals("/input.do")) {
command = new EEmpWriteCommand();
command.execute(request, response);
viewPage = "inpit.jsp";
}
// 디스패쳐 객체로 viewPage를 담아 요청한다.
// 클라이언트가 요청하면서 전송한 데이터를 그대로 유지할 수 있다.
RequestDispatcher dispatcher = request.getRequestDispatcher(viewPage);
// 인증이 완료되면 포워딩을 한다.
dispatcher.forward(request, response);
}
}
3. Conroller 파트
3-1. ECommand.java
package src.mvc.command;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public interface ECommand { // 커맨드 인터페이스
abstract void execute(HttpServletRequest request, HttpServletResponse response);
}
3-2. EDeptSelectCommand.java
package src.mvc.command;
import java.util.ArrayList;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import src.mvc.dao.EDao;
import src.mvc.dto.EDto;
// 커맨드 부서 셀렉트 객체
public class EDeptSelectCommand implements ECommand {
@Override
public void execute(HttpServletRequest request, HttpServletResponse response) {
EDao dao = new EDao();
ArrayList<EDto> dtos = dao.list(); // 리스트로 담은 DB 내용을
request.setAttribute("deptSel", dtos); // request 객체에 deptSel란 이름으로 담는다.
}
}
3-3. EDeptWriteCommand.java
package src.mvc.command;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import src.mvc.dao.EDao;
// 커맨드 부서 입력 객체
public class EDeptWriteCommand implements ECommand {
@Override
public void execute(HttpServletRequest request, HttpServletResponse response) {
String deptno = request.getParameter("deptno");
String dname = request.getParameter("dname");
String loc = request.getParameter("loc");
// int형으로 변환
int deptnoInt = Integer.parseInt(deptno);
EDao dao = new EDao();
dao.deptWrite(deptnoInt, dname, loc);
}
}
3-4. EEmpWriteCommand.java
package src.mvc.command;
import java.sql.Timestamp;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import src.mvc.dao.EDao;
// 커맨드 직원 입력 객체
public class EEmpWriteCommand implements ECommand {
@Override
public void execute(HttpServletRequest request, HttpServletResponse response) {
String empno = request.getParameter("empno");
String ename = request.getParameter("ename");
String job = request.getParameter("job");
String mgr = request.getParameter("mgr");
String hiredate = request.getParameter("hiredate");
String sal = request.getParameter("sal");
String comm = request.getParameter("comm");
// int형으로 변환
int empnoInt = Integer.parseInt(empno);
int mgrInt = Integer.parseInt(mgr);
int salInt = Integer.parseInt(sal);
int commInt = Integer.parseInt(comm);
// Timestamp형으로 변환
Timestamp hiredateTimestamp = Timestamp.valueOf(hiredate);
EDao dao = new EDao();
dao.empWrite(empnoInt, ename, job,
mgrInt, hiredateTimestamp, salInt, commInt);
}
}
3-5. EListCommand.java
package src.mvc.command;
import java.util.ArrayList;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import src.mvc.dao.EDao;
import src.mvc.dto.EDto;
// 커맨드 리스트 객체
public class EListCommand implements ECommand {
@Override
public void execute(HttpServletRequest request, HttpServletResponse response) {
EDao dao = new EDao();
ArrayList<EDto> dtos = dao.list(); // 리스트로 담은 DB 내용을
request.setAttribute("list", dtos); // request 객체에 list란 이름으로 담는다.
}
}
3-6. ELocSelectCommand.java
package src.mvc.command;
import java.util.ArrayList;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import src.mvc.dao.EDao;
import src.mvc.dto.EDto;
// 커맨드 위치 셀렉트 객체
public class ELocSelectCommand implements ECommand {
@Override
public void execute(HttpServletRequest request, HttpServletResponse response) {
EDao dao = new EDao();
ArrayList<EDto> dtos = dao.list(); // 리스트로 담은 DB 내용을
request.setAttribute("locSel", dtos); // request 객체에 locSel이란 이름으로 담는다.
}
}
3-7. EManageSelectCommand.java
package src.mvc.command;
import java.util.ArrayList;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import src.mvc.dao.EDao;
import src.mvc.dto.EDto;
// 커맨드 매니저 셀렉트 객체
public class EManagerSelectCommand implements ECommand {
@Override
public void execute(HttpServletRequest request, HttpServletResponse response) {
EDao dao = new EDao();
ArrayList<EDto> dtos = dao.list(); // 리스트로 담은 DB 내용을
request.setAttribute("manSel", dtos); // request 객체에 manSel란 이름으로 담는다.
}
}
3-8. EDto.java
package src.mvc.dto;
import java.sql.Timestamp;
public class EDto { // 게시글 정보를 참조하는 객체
private int empno; // 사원번호(EMP)
private String ename; // 사원이름(EMP)
private String job; // 사원직급(EMP)
private int mgr; // 상사(EMP)
private Timestamp hiredate; // 입사일(EMP)
private int sal; // 급여(EMP)
private int comm; // 커미션(EMP)
private int deptno; // 부서번호(EMP, DEPT)
private String dname; // 부서이름(DEPT)
private String loc; // 부서위치(DEPT)
public EDto() { }
// 부서 위치 정보 가져오기
public EDto(String loc) {
this.loc = loc;
}
// 상사 정보, 부서 정보 가져오기
public EDto(int no, String name) {
//상사 정보
this.empno = no;
this.ename = name;
//부서 정보
this.deptno = no;
this.dname = name;
}
// 직원 정보 가져오기
public EDto(int empno, String ename, String job,
int mgr, Timestamp hiredate, int sal,
int comm, int deptno, String dname, String loc) {
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public Timestamp getHiredate() {
return hiredate;
}
public void setHiredate(Timestamp hiredate) {
this.hiredate = hiredate;
}
public int getSal() {
return sal;
}
public void setSal(int sal) {
this.sal = sal;
}
public int getComm() {
return comm;
}
public void setComm(int comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
}
3-9. EDao.java
package src.mvc.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import src.mvc.dto.EDto;
//DB 데이터 처리 객체
public class EDao {
DataSource dataSource; // 커넥션 풀
public EDao() { // 컨텍스트 내용을 생성자 함수에 구현
try {
// 톰캣에 있는 context.xml내 커넥션풀 구현문을 참조해서 가져온다
Context context = new InitialContext();
// context.xml내 커넥션 풀에 있는 name("java:comp/env/jdbc/oracle")을 가져온다.
dataSource = (DataSource) context.lookup("java:comp/env/jdbc/oracle");
} catch (Exception e) {
e.printStackTrace();
}
}
// 상사 메뉴 조회하기
public ArrayList<EDto> managerSelect() {
ArrayList<EDto> dtos = new ArrayList<EDto>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 커넥션풀에 있는 커넥션 메소드 호출
connection = dataSource.getConnection();
String query = "SELECT m.empno||')'||m.ename\r\n"
+ "FROM emp e, emp m \r\n"
+ "WHERE e.mgr = m.empno\r\n"
+ "GROUP BY m.ename, m.empno;"; // 매니저 번호에 대응되는 직원명 조회
preparedStatement = connection.prepareStatement(query);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int empno = resultSet.getInt("empno");
String ename = resultSet.getString("ename");
EDto dto = new EDto(empno, ename);
dtos.add(dto);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(resultSet != null) resultSet.close();
if(preparedStatement != null) preparedStatement.close();
if(connection != null) connection.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
return dtos;
}
// 부서 조회하기
public ArrayList<EDto> deptSelect() {
ArrayList<EDto> dtos = new ArrayList<EDto>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 커넥션풀에 있는 커넥션 메소드 호출
connection = dataSource.getConnection();
String query = "SELECT DISTINCT deptno||')'||dname\r\n"
+ "FROM dept;"; // 부서 번호에 대응되는 부서명 조회
preparedStatement = connection.prepareStatement(query);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int deptno = resultSet.getInt("deptno");
String dname = resultSet.getString("dname");
EDto dto = new EDto(deptno, dname);
dtos.add(dto);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(resultSet != null) resultSet.close();
if(preparedStatement != null) preparedStatement.close();
if(connection != null) connection.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
return dtos;
}
// 부서 위치 조회하기
public ArrayList<EDto> locSelect() {
ArrayList<EDto> dtos = new ArrayList<EDto>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 커넥션풀에 있는 커넥션 메소드 호출
connection = dataSource.getConnection();
String query = "SELECT DISTINCT loc\r\n"
+ "FROM dept;"; // 부서 위치 조회
preparedStatement = connection.prepareStatement(query);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
String loc = resultSet.getString("loc");
EDto dto = new EDto(loc);
dtos.add(dto);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(resultSet != null) resultSet.close();
if(preparedStatement != null) preparedStatement.close();
if(connection != null) connection.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
return dtos;
}
// 부서 정보 입력하기
public void deptWrite(int deptno, String dname, String loc) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = dataSource.getConnection();
String query = "INSERT INTO dept (deptno, dname, loc) "
+ "values (?, ?, ?)";
preparedStatement = connection.prepareStatement(query);
preparedStatement.setInt(1, deptno);
preparedStatement.setString(2, dname);
preparedStatement.setString(3, loc);
int rn = preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(preparedStatement != null) preparedStatement.close();
if(connection != null) connection.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
// 직원 정보 입력하기
public void empWrite(int empno, String ename, String job,
int mgr, Timestamp hiredate, int sal, int comm) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = dataSource.getConnection();
String query = "INSERT INTO dept (empno, ename, job,"
+ "mgr, hiredate, sal, comm) "
+ "values (?, ?, ?,"
+ "?, ?, ?, ?)";
preparedStatement = connection.prepareStatement(query);
preparedStatement.setInt(1, empno);
preparedStatement.setString(2, ename);
preparedStatement.setString(3, job);
preparedStatement.setInt(4, mgr);
preparedStatement.setTimestamp(5, hiredate);
preparedStatement.setInt(6, sal);
preparedStatement.setInt(7, comm);
int rn = preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(preparedStatement != null) preparedStatement.close();
if(connection != null) connection.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
// 모든 직원 및 부서정보 가져오기
public ArrayList<EDto> list() {
ArrayList<EDto> dtos = new ArrayList<EDto>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 커넥션풀에 있는 커넥션 메소드 호출
connection = dataSource.getConnection();
String query = "select e.empno,\r\n" // 사원번호
+ " e.ename,\r\n" // 사원이름
+ " e.job,\r\n" // 직급
+ " nvl(e.mgr, 0),\r\n" // 상사
+ " e.hiredate,\r\n" // 입사일
+ " e.sal,\r\n" // 급여
+ " nvl(e.comm, 0),\r\n" // 커미션
+ " d.deptno,\r\n" // 부서번호
+ " d.dname,\r\n" // 부서이름
+ " d.loc\r\n" // 부서위치
+ "from dept d, emp e\r\n"
+ "where d.deptno = e.deptno\r\n"
+ "order by e.ename"; // 게시글 정렬
preparedStatement = connection.prepareStatement(query);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int empno = resultSet.getInt("empno");
String ename = resultSet.getString("ename");
String job = resultSet.getString("job");
int mgr = resultSet.getInt("mgr");
Timestamp hiredate = resultSet.getTimestamp("hiredate");
int sal = resultSet.getInt("sal");
int comm = resultSet.getInt("comm");
int deptno = resultSet.getInt("deptno");
String dname = resultSet.getString("dname");
String loc = resultSet.getString("loc");
EDto dto = new EDto(empno, ename, job,
mgr, hiredate, sal,
comm, deptno, dname, loc);
dtos.add(dto);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(resultSet != null) resultSet.close();
if(preparedStatement != null) preparedStatement.close();
if(connection != null) connection.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
return dtos;
}
}
최근댓글