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;
@WebServlet("*.do")
public class EFrontController extends HttpServlet {
private static final long serialVersionUID = 1L;
public EFrontController() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
System.out.println("doGet");
actionDo(request, 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;
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";
} 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";
}
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();
request.setAttribute("deptSel", dtos);
}
}
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 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 empnoInt = Integer.parseInt(empno);
int mgrInt = Integer.parseInt(mgr);
int salInt = Integer.parseInt(sal);
int commInt = Integer.parseInt(comm);
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();
request.setAttribute("list", dtos);
}
}
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();
request.setAttribute("locSel", dtos);
}
}
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();
request.setAttribute("manSel", dtos);
}
}
3-8. EDto.java
package src.mvc.dto;
import java.sql.Timestamp;
public class EDto {
private int empno;
private String ename;
private String job;
private int mgr;
private Timestamp hiredate;
private int sal;
private int comm;
private int deptno;
private String dname;
private String loc;
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;
public class EDao {
DataSource dataSource;
public EDao() {
try {
Context context = new InitialContext();
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;
}
}
최근댓글