[๊ตฌ๋์์นด๋ฐ๋ฏธ IT๊ตญ๋น์ง์] ์ง๋ ์๊ฐ์ ์ด์ด์ ์ค๋ผํด์ ์ด์ฉํ์ฌ ์ฌ์ ๋ชฉ๋ก ํ์ด์ง๋ฅผ ๋ง๋ค๊ณ , ํ์ด์ง ์์
๋ ํด๋ณด์๋ค.
์ค๋ผํด์ hr ๋ฐ์ดํฐ๋ฒ ์ด์ค์ employees ํ
์ด๋ธ์ ์ด์ฉํ์ฌ ์ฌ์ ๋ชฉ๋ก ํ์ด์ง๋ฅผ ๋ง๋๋ ์์
์ ํด๋ณด์๋ค. ํด๋น ํ์ด์ง๋ ๋นํ์(์ฌ์์ด ์๋ ์ฌ๋)๋ ๋ชฉ๋ก์ ๋ณผ ์๋ ์์ง๋ง ์ฌ์ ์ถ๊ฐ/์์ /์ญ์ ๋ ํ์(์ฌ์์ธ ์ฌ๋)๋ง ๊ฐ๋ฅํ๋๋ก ํ๋ค. ๋ํ, ์์ /์ญ์ ๋ ๋ณธ์ธ์ ๋ฐ์ดํฐ๋ง ๊ฐ๋ฅํ๋๋ก ํ๋ค.
package vo;
public class Employee {
private int employeeId;
private String firstName;
private String lastName;
private String email;
private String phoneNumber;
private String hireDate;
private String jobId;
private double salary;
private double commissionPct;
private int managerId;
private int departmentId;
public int getEmployeeId() {
return employeeId;
}
public void setEmployeeId(int employeeId) {
this.employeeId = employeeId;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhoneNumber() {
return phoneNumber;
}
public void setPhoneNumber(String phoneNumber) {
this.phoneNumber = phoneNumber;
}
public String getHireDate() {
return hireDate;
}
public void setHireDate(String hireDate) {
this.hireDate = hireDate;
}
public String getJobId() {
return jobId;
}
public void setJobId(String jobId) {
this.jobId = jobId;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public double getCommissionPct() {
return commissionPct;
}
public void setCommissionPct(double commissionPct) {
this.commissionPct = commissionPct;
}
public int getManagerId() {
return managerId;
}
public void setManagerId(int managerId) {
this.managerId = managerId;
}
public int getDepartmentId() {
return departmentId;
}
public void setDepartmentId(int departmentId) {
this.departmentId = departmentId;
}
}
Employee ํด๋์ค ์์ฑ
๐ ์์ฑ
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import = "vo.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>home.jsp</title>
<!-- ๋ถํธ์คํธ๋ฉ5 ์ฌ์ฉ -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
<div class="container mt-5">
<!------------------------------------------ ๋ก๊ทธ์ธ ํผ ์์ ------------------------------------------>
<%
// ์ธ์
๊ฐ์ด null์ผ๋๋ง ๋ก๊ทธ์ธ ํผ ์ถ๋ ฅ
if(session.getAttribute("loginEmployee") == null) {
%>
<h1>๋ก๊ทธ์ธ</h1>
<div class="text-danger">
<% // msg ๋ฐ์์ ์ถ๋ ฅ
if(request.getParameter("msg") != null) {
%>
<%=request.getParameter("msg")%>
<%
}
%>
</div>
<!-- employeeId, firstName, lastName ์ ์ด์ฉํ์ฌ ๋ก๊ทธ์ธ -->
<form action="<%=request.getContextPath()%>/employee/loginAction.jsp" method="post">
<table>
<tr>
<th>employeeId</th>
<td>
<input type="number" name="employeeId">
</td>
</tr>
<tr>
<th>firstName</th>
<td>
<input type="text" name="firstName">
</td>
</tr>
<tr>
<th>lastName</th>
<td>
<input type="text" name="lastName">
</td>
</tr>
</table>
<button type="submit" class="btn btn-secondary">๋ก๊ทธ์ธ</button>
</form>
<%
}
%>
<!------------------------------------------ ๋ก๊ทธ์ธ ํผ ๋ ------------------------------------------>
<!------------------------------------------ ์์ํ์ด์ง ์ถ๋ ฅ ------------------------------------------>
<%
// ์ธ์
๊ฐ์ด null์ด ์๋๋ฉด ์ถ๋ ฅ
if(session.getAttribute("loginEmployee") != null) {
// ์ธ์
๊ฐ ๋ฐ์์ค๊ธฐ
Object o = session.getAttribute("loginEmployee");
Employee sessionEmployee = null;
if(o instanceof Employee) { // instanceof์ฐ์ฐ์ : ๊ฐ์ฒด๋ณ์ instanceof ํ์
sessionEmployee = (Employee)o;
}
%>
<h1>welcome!</h1>
<p class="card-text">ํ์ํฉ๋๋ค <%=sessionEmployee.getFirstName()%>๋ :)</p>
<div class="card" style="width:400px">
<img class="card-img-top" src="<%=request.getContextPath()%>/img/cardImg.png" alt="Card image" style="width:100%">
<div class="card-body">
<h4 class="card-title"><%=sessionEmployee.getFirstName()%> <%=sessionEmployee.getLastName()%></h4>
<p class="card-text"> employeeId : <%=sessionEmployee.getEmployeeId()%></p>
<a href="<%=request.getContextPath()%>/employee/logoutAction.jsp" class="btn btn-outline-secondary">
๋ก๊ทธ์์
</a>
</div>
</div>
<%
}
%>
<!------------------------------------------ ์์ํ์ด์ง ๋ ------------------------------------------>
<br>
<div>
<a href="<%=request.getContextPath()%>/employee/employeeList.jsp" class="btn btn-outline-secondary">
์ฌ์ ๋ชฉ๋ก ๋ณด๊ธฐ
</a>
</div>
</div>
</body>
</html>
๐ ๊ฒฐ๊ณผ
๋ก๊ทธ์ธ ์ , ๋ก๊ทธ์ธ์ ํ์ง ์์๋ ์ฌ์ ๋ชฉ๋ก ๋ณด๊ธฐ ๋ฒํผ์ ๋๋ฅด๋ฉด employeeList.jsp๋ก ์ด๋ํ ์ ์๋ค.
๋ก๊ทธ์ธ ํ ํ๋ฉด
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import = "java.sql.*" %>
<%@ page import = "java.net.*" %>
<%@ page import = "vo.*" %>
<%
// ํ๊ธ ๊นจ์ง์ง ์๊ฒ ์ธ์ฝ๋ฉ
request.setCharacterEncoding("utf-8");
// 1. ์ ํจ์ฑ ๊ฒ์ฌ
// 1-1) ์ธ์
๊ฐ
// ๋ก๊ทธ์ธ ๋์ด ์๋ ์ํ๋ฉด ์ด ํ์ด์ง์ ์ฌ ์ ์๋ค
if(session.getAttribute("loginEmployee") != null) {
response.sendRedirect(request.getContextPath() + "/home.jsp");
return;
}
// 1-2) ์์ฒญ๊ฐ
// employeeId, firstName, lastName
String msg = null;
if(request.getParameter("employeeId") == null
|| request.getParameter("employeeId").equals("")) {
msg = URLEncoder.encode("employeeId๋ฅผ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("firstName") == null
|| request.getParameter("firstName").equals("")) {
msg = URLEncoder.encode("firstName์ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("lastName") == null
|| request.getParameter("lastName").equals("")) {
msg = URLEncoder.encode("lastName์ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
}
// null์ด๊ฑฐ๋ ๊ณต๋ฐฑ์ด๋ฉด home์ผ๋ก
if(msg != null) {
response.sendRedirect(request.getContextPath() + "/home.jsp?msg=" + msg);
return;
}
// null์ด๊ฑฐ๋ ๊ณต๋ฐฑ์ด ์๋๋ฉด ๊ฐ ๋ฐ์์ค๊ธฐ
int employeeId = Integer.parseInt(request.getParameter("employeeId"));
String firstName = request.getParameter("firstName");
String lastName = request.getParameter("lastName");
System.out.println(employeeId + " <- loginAction employeeId");
System.out.println(firstName + " <- loginAction firstName");
System.out.println(lastName + " <- loginAction lastName");
// 2. ๋ชจ๋ธ๊ฐ ๊ตฌํ๊ธฐ
// 2-1) ๋๋ผ์ด๋ฒ ๋ก๋ฉ ๋ฐ db ์ ์
String driver = "oracle.jdbc.driver.OracleDriver";
Class.forName(driver);
String dburl = "jdbc:oracle:thin:@localhost:1521:xe";
String dbuser = "****";
String dbpw = "****";
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
// 2-2) ์ฟผ๋ฆฌ ์์ฑ
// ์
๋ ฅํ ์ ๋ณด๊ฐ db์ ์๋์ง ์กฐํ
String sql = "SELECT count(*) FROM employees WHERE employee_id = ? AND first_name = ? AND last_name = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, employeeId);
stmt.setString(2, firstName);
stmt.setString(3, lastName);
ResultSet rs = stmt.executeQuery();
// ๋ชจ๋ธ๊ฐ์ ๋ณ์์ ์ ์ฅ
int empCnt = 0;
if(rs.next()) {
empCnt = rs.getInt(1);
}
// ์
๋ ฅํ ์ ๋ณด๊ฐ ์กด์ฌํ๋ฉด(count๊ฐ 1์ด๋ฉด) ์ธ์
์ ์ ์ฅ
if(empCnt == 1) {
Employee loginEmployee = new Employee();
loginEmployee.setEmployeeId(employeeId);
loginEmployee.setFirstName(firstName);
loginEmployee.setLastName(lastName);
session.setAttribute("loginEmployee", loginEmployee);
System.out.println("๋ก๊ทธ์ธ ์ฑ๊ณต");
} else {
msg = URLEncoder.encode("๋ก๊ทธ์ธ์ ์คํจํ์์ต๋๋ค", "utf-8");
System.out.println("๋ก๊ทธ์ธ ์คํจ");
}
// ํ์ผ๋ก ๋ฆฌ๋ค์ด๋ ์
response.sendRedirect(request.getContextPath() + "/home.jsp?msg=" + msg);
%>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%
session.invalidate(); // ๊ธฐ์กด ์ธ์
์ ์ง์ฐ๊ณ ๊ฐฑ์
response.sendRedirect(request.getContextPath() + "/home.jsp");
%>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<div class="container mt-5">
<ul class="nav nav-tabs" role="tablist">
<%
// ์ธ์
๊ฐ์ด null์ด ์๋๋ฉด ์ถ๊ฐ/๋ก๊ทธ์์ ์ถ๋ ฅ
if(session.getAttribute("loginEmployee") != null) {
%>
<li class="nav-item">
<a href="<%=request.getContextPath()%>/employee/addEmployee.jsp" class="nav-link active">
์ฌ์ ์ถ๊ฐ
</a>
</li>
<li class="nav-item">
<a href="<%=request.getContextPath()%>/employee/logoutAction.jsp" class="nav-link active">
๋ก๊ทธ์์
</a>
</li>
<%
// ์ธ์
๊ฐ์ด null์ด๋ฉด ๋ก๊ทธ์ธ ์ถ๋ ฅ
} else {
%>
<li class="nav-item">
<a href="<%=request.getContextPath()%>/home.jsp" class="nav-link active">
๋ก๊ทธ์ธ
</a>
</li>
<%
}
%>
</ul>
</div>
employeeList.jsp ํ์ด์ง ์๋จ์ incluedํ ๋ฉ์ธ ๋ฉ๋ด๋ฅผ ๋ง๋ค์๋ค. ์ฌ์ ์ถ๊ฐ์ ๋ก๊ทธ์์ ๋ฉ๋ด๋ ๋ก๊ทธ์ธ ์ํ์์ ์ถ๋ ฅ, ๋น๋ก๊ทธ์ธ์์๋ ๋ก๊ทธ์ธ ๋ฉ๋ด๋ง ์ถ๋ ฅ๋๋๋ก ํ๋ค.
๐ ์์ฑ
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import = "java.util.*" %> <!-- ArrayList ์ฌ์ฉ -->
<%@ page import = "vo.*" %>
<%
// 1. ์ ํจ์ฑ ๊ฒ์ฌ
// ํ์ด์ง์ ์ํ ์์ฒญ๊ฐ ์ฒ๋ฆฌ
int currentPage = 1;
if(request.getParameter("currentPage") != null) {
currentPage = Integer.parseInt(request.getParameter("currentPage"));
}
int rowPerPage = 10;
if(request.getParameter("rowPerPage") != null) {
rowPerPage = Integer.parseInt(request.getParameter("rowPerPage"));
}
// ๋ง๋ฆฌ์db์ ๋ค๋ฅด๊ฒ ์ค๋ผํด์ limit์ ์ธ ์ ์๊ธฐ ๋๋ฌธ์ beginRow์ endRow ๋๋ค ๊ตฌํด์ผ ํ๋ค
/*
rowPerPage๊ฐ 10์ผ ๋
currentPage beginRow endRow
1 1 10
2 11 20
3 21 30
4 31 40
*/
int beginRow = (currentPage - 1) * rowPerPage + 1;
int endRow = beginRow + (rowPerPage - 1);
// 2. ๋ชจ๋ธ๊ฐ ๊ตฌํ๊ธฐ
// ๋๋ผ์ด๋ฒ ๋ก๋ฉ ๋ฐ db ์ ์
String driver = "oracle.jdbc.driver.OracleDriver";
Class.forName(driver);
String dburl = "jdbc:oracle:thin:@localhost:1521:xe";
String dbuser = "****";
String dbpw = "****";
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
// 2-1) totalCnt ๋ชจ๋ธ์
// ๋ง์ง๋ง ํ์ด์ง์์ endRow์ ๊ฐ์ด ๋ฌ๋ผ์ง๊ธฐ ๋๋ฌธ์ totalCnt๋ฅผ ๊ตฌํด์ผ ํ๋ค
int totalCnt = 0;
// ์ฟผ๋ฆฌ ์์ฑ
String totalCntSql = "SELECT count(*) FROM EMPLOYEES";
PreparedStatement totalCntStmt = conn.prepareStatement(totalCntSql);
ResultSet totalCntRs = totalCntStmt.executeQuery();
// ๋ชจ๋ธ์
๊ฐ์ ๋ณ์์ ์ ์ฅ
if(totalCntRs.next()) {
totalCnt = totalCntRs.getInt(1);
// ๋ฐํํ ์ปฌ๋ผ์ด ํ๋์ผ ๋์๋ index(์ซ์) ์ฌ์ฉ์ ๊ถ์ฅ
}
// endRow if๋ฌธ ์ถ๊ฐ
if(endRow > totalCnt) {
endRow = totalCnt;
}
// ๋๋ฒ๊น
System.out.println("ํ์ฌ " + currentPage + "ํ์ด์ง <- employeeList currentPage");
System.out.println(rowPerPage + "๊ฐ์ฉ <- employeeList rowPerPage");
System.out.println(beginRow + "๋ฒ๋ถํฐ <- employeeList beginRow");
System.out.println(endRow + "๋ฒ๊น์ง <- employeeList endRow");
System.out.println("์ ์ฒด ํ ๊ฐฏ์: " + totalCnt + "๊ฐ <- employeeList totalCnt");
// 2-2) employeeList ๋ชจ๋ธ์
// employees ํ
์ด๋ธ์ ๋ชจ๋ ์ ๋ณด๋ฅผ ์กฐํ // ํ์ด์ง์ ์ํด rownum ์ฌ์ฉ // hireDate ๋ด๋ฆผ์ฐจ์์ผ๋ก ์ ๋ ฌ
/*
SELECT e3.*
FROM
(SELECT
rownum rnum,
e2.*
FROM
(SELECT
rownum,
e1.*
FROM employees e1
ORDER BY e1.hire_date DESC) e2) e3
WHERE e3.rnum BETWEEN ? AND ?
*/
String employeeListSql = "SELECT e3.* FROM (SELECT rownum rnum, e2.* FROM (SELECT rownum, e1.* FROM employees e1 ORDER BY e1.hire_date DESC) e2) e3 WHERE e3.rnum BETWEEN ? AND ?";
PreparedStatement employeeListStmt = conn.prepareStatement(employeeListSql);
employeeListStmt.setInt(1, beginRow);
employeeListStmt.setInt(2, endRow);
ResultSet employeeListRs = employeeListStmt.executeQuery();
// ArrayList๋ก ๋ฐ๊พธ๊ธฐ
ArrayList<Employee> employeeList = new ArrayList<Employee>();
while(employeeListRs.next()) {
Employee e = new Employee();
e.setEmployeeId(employeeListRs.getInt("employee_id"));
e.setFirstName(employeeListRs.getString("first_name"));
e.setLastName(employeeListRs.getString("last_name"));
e.setEmail(employeeListRs.getString("email"));
e.setPhoneNumber(employeeListRs.getString("phone_number"));
e.setHireDate(employeeListRs.getString("hire_date"));
e.setJobId(employeeListRs.getString("job_id"));
e.setSalary(employeeListRs.getDouble("salary"));
e.setCommissionPct(employeeListRs.getDouble("commission_pct"));
e.setManagerId(employeeListRs.getInt("manager_id"));
e.setDepartmentId(employeeListRs.getInt("department_id"));
employeeList.add(e);
}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>employeeList.jsp</title>
<!-- ๋ถํธ์คํธ๋ฉ5 ์ฌ์ฉ -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
<!-- include ํ์ด์ง : ๋ฉ์ธ๋ฉ๋ด(๊ฐ๋ก) -->
<div>
<!-- ์ก์
ํ๊ทธ -->
<jsp:include page="/inc/mainmenu.jsp"></jsp:include>
</div>
<!-------- include ํ์ด์ง ๋ ------->
<div class="container mt-5">
<!---------------------- employeeList ๋ชจ๋ธ์
์์ ---------------------->
<table class="table table-bordered">
<thead class="table-secondary">
<tr>
<th>employeeId</th>
<th>firstName</th>
<th>lastName</th>
<th>email</th>
<th>phoneNumber</th>
<th>hireDate</th>
<th>jobId</th>
<th>salary</th>
<th>commissionPct</th>
<th>managerId</th>
<th>departmentId</th>
<th>์์ / ์ญ์ </th>
</tr>
</thead>
<tbody>
<%
for(Employee e : employeeList) {
%>
<tr>
<td><%=e.getEmployeeId()%></td>
<td><%=e.getFirstName()%></td>
<td><%=e.getLastName()%></td>
<td><%=e.getEmail()%></td>
<td><%=e.getPhoneNumber()%></td>
<td><%=e.getHireDate().substring(0, 10)%></td>
<td><%=e.getJobId()%></td>
<td><%=e.getSalary()%></td>
<td><%=e.getCommissionPct()%></td>
<td><%=e.getManagerId()%></td>
<td><%=e.getDepartmentId()%></td>
<td>
<!-- ์ธ์
์ ๋ณด๊ฐ ์ผ์นํ ๋๋ง ์์ / ์ญ์ ๋ฒํผ ์ถ๋ ฅ -->
<%
if(session.getAttribute("loginEmployee") != null) {
// ์ธ์
๊ฐ ๋ถ๋ฌ์ค๊ธฐ
Object o = session.getAttribute("loginEmployee");
Employee sessionEmployee = null;
if(o instanceof Employee) { // instanceof์ฐ์ฐ์ : ๊ฐ์ฒด๋ณ์ instanceof ํ์
sessionEmployee = (Employee)o;
}
// ์ธ์
์ ๋ณด ๋ณ์์ ์ ์ฅ
int sessionEmployeeId = sessionEmployee.getEmployeeId();
// ์ธ์
์ ๋ณด๊ฐ ์ผ์นํ๋์ง ํ์ธ
if(sessionEmployeeId == e.getEmployeeId()) {
%>
<a href="<%=request.getContextPath()%>/employee/modifyEmployee.jsp?employeeId=<%=e.getEmployeeId()%>" class="btn btn-sm">
✏
</a>
<a href="<%=request.getContextPath()%>/employee/removeEmployeeAction.jsp?employeeId=<%=e.getEmployeeId()%>" class="btn btn-sm">
🗑
</a>
<%
}
}
%>
</td>
</tr>
<%
}
%>
</tbody>
</table>
<!---------------------- employeeList ๋ชจ๋ธ์
๋ ---------------------->
<div class="text-center">
<!---------------------------- ํ์ด์ง ์์ ---------------------------->
<%
if(currentPage > 1) { // 1ํ์ด์ง๋ฉด "์ด์ "์ ์ถ๋ ฅํ์ง ์๋๋ค
%>
<a href="<%=request.getContextPath()%>/employee/employeeList.jsp?currentPage=<%=currentPage - 1%>&rowPerPage=<%=rowPerPage%>" class="btn btn-sm btn-secondary">์ด์ </a>
<%
}
%>
<%=currentPage%>ํ์ด์ง
<%
if(endRow != totalCnt) { // ๋ง์ง๋ง ํ์ด์ง๋ฉด "๋ค์"์ ์ถ๋ ฅํ์ง ์๋๋ค
%>
<a href="<%=request.getContextPath()%>/employee/employeeList.jsp?currentPage=<%=currentPage + 1%>&rowPerPage=<%=rowPerPage%>" class="btn btn-sm btn-secondary">๋ค์</a>
<%
}
%>
<!---------------------------- ํ์ด์ง ๋ ---------------------------->
</div>
</div>
</body>
</html>
// ์ธ์
์ ์ถ๊ฐํ ๋
Employee loginEmployee = new Employee();
session.setAttribute("loginEmployee", loginEmployee);
// ์ธ์
์์ ๊ฐ์ ธ์ฌ๋
Object o = session.getAttribute("loginEmployee");
Employee sessionEmployee = null;
if(o instanceof Employee) { // instanceof์ฐ์ฐ์ : ๊ฐ์ฒด๋ณ์ instanceof ํ์
sessionEmployee = (Employee)o;
}
๐ ๊ฒฐ๊ณผ
๋ก๊ทธ์ธX
๋ก๊ทธ์ธO, ์์ /์ญ์ ๋ฒํผ์ ํ์ฌ ๋ก๊ทธ์ธํ ์์ด๋์ผ ๋๋ง ์ถ๋ ฅ๋๋ค.
๐ ์์ฑ
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%
// ์ ํจ์ฑ ๊ฒ์ฌ
// ๋ก๊ทธ์์ ์ํ๋ฉด ์ด ํ์ด์ง์ ์ฌ ์ ์๋ค
if(session.getAttribute("loginEmployee") == null) {
response.sendRedirect(request.getContextPath() + "/employeeList.jsp");
return;
}
// ๋๋ผ์ด๋ฒ ๋ก๋ฉ ๋ฐ db ์ ์
String driver = "oracle.jdbc.driver.OracleDriver";
Class.forName(driver);
String dburl = "jdbc:oracle:thin:@localhost:1521:xe";
String dbuser = "****";
String dbpw = "****";
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
// ์ธ๋ํค ์นผ๋ผ ์กฐํ๋ฅผ ์ํ ์ฟผ๋ฆฌ ์์ฑ
// JOBS
String jobSql = "SELECT job_id jobId FROM JOBS";
PreparedStatement jodStmt = conn.prepareStatement(jobSql);
ResultSet jodRs = jodStmt.executeQuery();
// DEPARTMENTS
String departmentSql = "SELECT department_id deptId FROM DEPARTMENTS";
PreparedStatement departmentStmt = conn.prepareStatement(departmentSql);
ResultSet departmentRs = departmentStmt.executeQuery();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>addEmployee.jsp</title>
<!-- ๋ถํธ์คํธ๋ฉ5 ์ฌ์ฉ -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
<!-- include ํ์ด์ง : ๋ฉ์ธ๋ฉ๋ด(๊ฐ๋ก) -->
<div>
<!-- ์ก์
ํ๊ทธ -->
<jsp:include page="/inc/mainmenu.jsp"></jsp:include>
</div>
<!-------- include ํ์ด์ง ๋ ------->
<div class="container mt-5">
<!---------------------- add form ์์ ---------------------->
<h3>์ฌ์ ์ถ๊ฐ</h3>
<!-- ์คํจ์ ์๋ฌ๋ฉ์ธ์ง ์ถ๋ ฅ -->
<div class="text-danger">
<%
if(request.getParameter("msg") != null) {
%>
<%=request.getParameter("msg")%>
<%
}
%>
</div>
<form action="<%=request.getContextPath()%>/employee/addEmployeeAction.jsp" method="post">
<table class="table table-bordered">
<tr>
<th class="table-secondary">employeeId</th>
<td>
<input type="number" name="employeeId">
</td>
</tr>
<tr>
<th class="table-secondary">firstName</th>
<td>
<input type="text" name="firstName">
</td>
</tr>
<tr>
<th class="table-secondary">lastName</th>
<td>
<input type="text" name="lastName">
</td>
</tr>
<tr>
<th class="table-secondary">email</th>
<td>
<input type="text" name="email">
</td>
</tr>
<tr>
<th class="table-secondary">phoneNumber</th>
<td>
<input type="text" name="phoneNumber">
</td>
</tr>
<tr>
<th class="table-secondary">hireDate</th>
<td>
<input type="date" name="hireDate">
</td>
</tr>
<tr>
<th class="table-secondary">jobId</th>
<td>
<select name="jobId">
<%
while(jodRs.next()) {
%>
<option value="<%=jodRs.getString("jobId")%>"><%=jodRs.getString("jobId")%></option>
<%
}
%>
</select>
</td>
</tr>
<tr>
<th class="table-secondary">salary</th>
<td>
<input type="number" name="salary">
</td>
</tr>
<tr>
<th class="table-secondary">commissionPct</th>
<td>
<input type="number" name="commissionPct" step="0.01" max="0.99" placeholder="๋ฒ์: 1๋ฏธ๋ง">
<!-- ์์ซ์ ์
๋ ฅ ํ์ฉ, ์
๋ ฅ ๋ฒ์ 1๋ฏธ๋ง์ผ๋ก ์ ํํ๊ธฐ -->
</td>
</tr>
<tr>
<th class="table-secondary">managerId</th>
<td>
<input type="number" name="managerId">
</td>
</tr>
<tr>
<th class="table-secondary">departmentId</th>
<td>
<select name="departmentId">
<%
while(departmentRs.next()) {
%>
<option value="<%=departmentRs.getInt("deptId")%>"><%=departmentRs.getInt("deptId")%></option>
<%
}
%>
</select>
</td>
</tr>
</table>
<a href="<%=request.getContextPath()%>/employee/employeeList.jsp" class="btn btn-sm btn-outline-secondary">
๋ค๋ก๊ฐ๊ธฐ
</a>
<button type="submit" class="btn btn-sm btn-secondary">์ถ๊ฐ</button>
</form>
<!---------------------- add form ๋ ---------------------->
</div>
</body>
</html>
๐ ๊ฒฐ๊ณผ
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import = "vo.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.net.*" %>
<%
// ํ๊ธ ๊นจ์ง์ง ์๊ฒ ์ธ์ฝ๋ฉ
request.setCharacterEncoding("utf-8");
// 1. ์ ํจ์ฑ ๊ฒ์ฌ
// 1-1) ์ธ์
๊ฐ
// ๋ก๊ทธ์์ ์ํ๋ฉด ์ด ํ์ด์ง์ ์ฌ ์ ์๋ค
if(session.getAttribute("loginEmployee") == null) {
response.sendRedirect(request.getContextPath() + "/employee/employeeList.jsp");
return;
}
// 1-2) ์์ฒญ๊ฐ
// employeeId, firstName, lastName, email, phoneNumber, hireDate, jobId, salary, commissionPct, managerId, departmentId
String msg = null;
if(request.getParameter("employeeId") == null
|| request.getParameter("employeeId").equals("")) {
msg = URLEncoder.encode("employeeId๋ฅผ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("firstName") == null
|| request.getParameter("firstName").equals("")) {
msg = URLEncoder.encode("firstName์ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("lastName") == null
|| request.getParameter("lastName").equals("")) {
msg = URLEncoder.encode("lastName์ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("email") == null
|| request.getParameter("email").equals("")) {
msg = URLEncoder.encode("email์ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("phoneNumber") == null
|| request.getParameter("phoneNumber").equals("")) {
msg = URLEncoder.encode("phoneNumber๋ฅผ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("hireDate") == null
|| request.getParameter("hireDate").equals("")) {
msg = URLEncoder.encode("hireDate๋ฅผ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("jobId") == null
|| request.getParameter("jobId").equals("")) {
msg = URLEncoder.encode("jobId๋ฅผ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("salary") == null
|| request.getParameter("salary").equals("")) {
msg = URLEncoder.encode("salary๋ฅผ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("commissionPct") == null
|| request.getParameter("commissionPct").equals("")) {
msg = URLEncoder.encode("commissionPct๋ฅผ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("managerId") == null
|| request.getParameter("managerId").equals("")) {
msg = URLEncoder.encode("managerId๋ฅผ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("departmentId") == null
|| request.getParameter("departmentId").equals("")) {
msg = URLEncoder.encode("departmentId๋ฅผ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
}
// null์ด๊ฑฐ๋ ๊ณต๋ฐฑ์ด๋ฉด msg์ ํจ๊ป ๋ฆฌ๋ค์ด๋ ์
if(msg != null) {
response.sendRedirect(request.getContextPath() + "/employee/addEmployee.jsp?msg=" + msg);
return;
}
// 2. ๋ชจ๋ธ๊ฐ ๊ตฌํ๊ธฐ
// 2-1) ๋๋ผ์ด๋ฒ ๋ก๋ฉ ๋ฐ db ์ ์
String driver = "oracle.jdbc.driver.OracleDriver";
Class.forName(driver);
String dburl = "jdbc:oracle:thin:@localhost:1521:xe";
String dbuser = "****";
String dbpw = "****";
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
// 2-2) employeeId ์ค๋ณต๊ฒ์ฌ
// employee_id๊ฐ ๊ธฐ๋ณธํค์ด๋ฏ๋ก ๋จผ์ ์ค๋ณต๊ฒ์ฌ๋ฅผ ํด๋ณธ๋ค
int employeeId = Integer.parseInt(request.getParameter("employeeId"));
String employeeIdCntSql = "SELECT count(*) FROM employees WHERE employee_id = ?";
PreparedStatement employeeIdCntStmt = conn.prepareStatement(employeeIdCntSql);
employeeIdCntStmt.setInt(1, employeeId);
ResultSet employeeIdCntRs = employeeIdCntStmt.executeQuery();
// ๊ฐฏ์ ํ์ธ
int employeeIdCnt = 0;
if(employeeIdCntRs.next()) {
employeeIdCnt = employeeIdCntRs.getInt("count(*)");
// ํด๋น employeeId์ ๊ฐฏ์๋ฅผ cnt ๋ณ์์ ์ ์ฅ
// 0์ผ ๊ฒฝ์ฐ ์ค๋ณต ์์
}
// 0๋ณด๋ค ํด ๊ฒฝ์ฐ ์ค๋ณต ์์ // msg์ ํจ๊ป ๋ฆฌ๋ค์ด๋ ์
if(employeeIdCnt > 0) {
System.out.println(employeeIdCnt + " <- addEmployeeAction ์ค๋ณต๋ ์์ด๋ ๊ฐฏ์");
msg = URLEncoder.encode("์ค๋ณต๋ employeeId์
๋๋ค ", "utf-8");
response.sendRedirect(request.getContextPath() + "/employee/addEmployee.jsp?msg=" + msg);
return;
} else {
System.out.println("addEmployeeAction ์ค๋ณต๋ ์์ด๋ ์์");
}
// 2-3) email ์ค๋ณต๊ฒ์ฌ
// email์ด ์ ๋ํฌํค์ด๋ฏ๋ก ์ค๋ณต๊ฒ์ฌ๋ฅผ ํด๋ณธ๋ค
String email = request.getParameter("email");
String emailCntSql = "SELECT count(*) FROM employees WHERE email = ?";
PreparedStatement emailCntStmt = conn.prepareStatement(emailCntSql);
emailCntStmt.setString(1, email);
ResultSet emailCntRs = emailCntStmt.executeQuery();
// ๊ฐฏ์ ํ์ธ
int emailCnt = 0;
if(emailCntRs.next()) {
emailCnt = emailCntRs.getInt("count(*)");
// ํด๋น email์ ๊ฐฏ์๋ฅผ cnt ๋ณ์์ ์ ์ฅ
// 0์ผ ๊ฒฝ์ฐ ์ค๋ณต ์์
}
// 0๋ณด๋ค ํด ๊ฒฝ์ฐ ์ค๋ณต ์์ // msg์ ํจ๊ป ๋ฆฌ๋ค์ด๋ ์
if(emailCnt > 0) {
System.out.println(emailCnt + " <- addEmployeeAction ์ค๋ณต๋ ์ด๋ฉ์ผ ๊ฐฏ์");
msg = URLEncoder.encode("์ค๋ณต๋ email์
๋๋ค ", "utf-8");
response.sendRedirect(request.getContextPath() + "/employee/addEmployee.jsp?msg=" + msg);
return;
} else {
System.out.println("addEmployeeAction ์ค๋ณต๋ ์ด๋ฉ์ผ ์์");
}
// ์ค๋ณต ์์ผ๋ฉด ๋๋จธ์ง ๊ฐ๋ ์ ์ฅ
String firstName = request.getParameter("firstName");
String lastName = request.getParameter("lastName");
String phoneNumber = request.getParameter("phoneNumber");
String hireDate = request.getParameter("hireDate");
String jobId = request.getParameter("jobId");
double salary = Double.parseDouble(request.getParameter("salary")); // ๋๋ธ ํ์
์ผ๋ก ํ๋ณํ
double commissionPct = Double.parseDouble(request.getParameter("commissionPct")); // ๋๋ธ ํ์
์ผ๋ก ํ๋ณํ
int managerId = Integer.parseInt(request.getParameter("managerId"));
int departmentId = Integer.parseInt(request.getParameter("departmentId"));
// 2-3) ์ฟผ๋ฆฌ ์์ฑ
String sql = "INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, employeeId);
stmt.setString(2, firstName);
stmt.setString(3, lastName);
stmt.setString(4, email);
stmt.setString(5, phoneNumber);
stmt.setString(6, hireDate);
stmt.setString(7, jobId);
stmt.setDouble(8, salary);
stmt.setDouble(9, commissionPct);
stmt.setInt(10, managerId);
stmt.setInt(11, departmentId);
// 2-3) ์ฟผ๋ฆฌ๊ฐ ์ ์งํ๋์๋์ง ํ์ธ
int row = stmt.executeUpdate();
if(row == 1) { // ์ฑ๊ณต์
System.out.println(row + " <- addEmployeeAction ์ฑ๊ณต");
msg = URLEncoder.encode("์ ์์ ์ผ๋ก ์ถ๊ฐ ๋์์ต๋๋ค", "utf-8");
response.sendRedirect(request.getContextPath() + "/employee/employeeList.jsp?msg=" + msg);
return;
} else { // ์คํจ์
System.out.println(row + " <- addEmployeeAction ์คํจ");
msg = URLEncoder.encode("์ถ๊ฐ๋์ง ์์์ต๋๋ค ๋ค์ ์๋ํด์ฃผ์ธ์", "utf-8");
response.sendRedirect(request.getContextPath() + "/employee/addEmployee.jsp?msg=" + msg);
return;
}
%>
๐ ์์ฑ
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="vo.*" %>
<%@ page import="java.sql.*" %>
<%
// 1. ์ ํจ์ฑ ๊ฒ์ฌ
// ์ธ์
์ ๋ณด๊ฐ ์๊ฑฐ๋ employeeId ๊ฐ์ด ์์ผ๋ฉด ์ด ํ์ด์ง์ ์ฌ ์ ์๋ค
if(session.getAttribute("loginEmployee") == null
|| request.getParameter("employeeId") == null
|| request.getParameter("employeeId").equals("")) {
response.sendRedirect(request.getContextPath() + "/employee/employeeList.jsp");
return;
}
int paramEmployeeId = Integer.parseInt(request.getParameter("employeeId"));
// ์ธ์
์ ๋ณด ๋ถ๋ฌ์ค๊ธฐ
Object o = session.getAttribute("loginEmployee");
Employee sessionEmployee = null;
if(o instanceof Employee) { // instanceof์ฐ์ฐ์ : ๊ฐ์ฒด๋ณ์ instanceof ํ์
sessionEmployee = (Employee)o;
}
int sessionEmployeeId = sessionEmployee.getEmployeeId();
// employeeId๊ฐ๊ณผ ์ธ์
์ ๋ณด๊ฐ ์ผ์นํ๋์ง ํ์ธ
if(paramEmployeeId != sessionEmployeeId) {
response.sendRedirect(request.getContextPath() + "/employee/employeeList.jsp");
return;
}
// 2. ๋ชจ๋ธ๊ฐ ๊ตฌํ๊ธฐ
// ๋๋ผ์ด๋ฒ ๋ก๋ฉ ๋ฐ db ์ ์
String driver = "oracle.jdbc.driver.OracleDriver";
Class.forName(driver);
String dburl = "jdbc:oracle:thin:@localhost:1521:xe";
String dbuser = "****";
String dbpw = "****";
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
// 2-1) ์์ ์ ์
๋ ฅ๊ฐ ์กฐํ๋ฅผ ์ํ ์ฟผ๋ฆฌ ์์ฑ
// SELECT * FROM employees WHERE employee_Id = ?
String sql = "SELECT employee_id employeeId, first_name firstName, last_name lastName, email email, phone_number phoneNumber, hire_date hireDate, job_id jobId, Salary salary, commission_pct commissionPct, manager_id managerId, department_id departmentId FROM employees WHERE employee_id = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, paramEmployeeId);
ResultSet rs = stmt.executeQuery();
// Voํ์
์ผ๋ก ๋ฐ๊พธ๊ธฐ
Employee employee = null;
if(rs.next()) {
employee = new Employee();
employee.setEmployeeId(rs.getInt("employeeId"));
employee.setFirstName(rs.getString("firstName"));
employee.setLastName(rs.getString("lastName"));
employee.setEmail(rs.getString("email"));
employee.setPhoneNumber(rs.getString("phoneNumber"));
employee.setHireDate(rs.getString("hireDate"));
employee.setJobId(rs.getString("jobId"));
employee.setSalary(rs.getDouble("salary"));
employee.setCommissionPct(rs.getDouble("commissionPct"));
employee.setManagerId(rs.getInt("managerId"));
employee.setDepartmentId(rs.getInt("departmentId"));
}
// 2-2) ์ธ๋ํค ์นผ๋ผ ์กฐํ๋ฅผ ์ํ ์ฟผ๋ฆฌ ์์ฑ
// JOBS
String jobSql = "SELECT job_id jobId FROM JOBS";
PreparedStatement jodStmt = conn.prepareStatement(jobSql);
ResultSet jodRs = jodStmt.executeQuery();
// DEPARTMENTS
String departmentSql = "SELECT department_id deptId FROM DEPARTMENTS";
PreparedStatement departmentStmt = conn.prepareStatement(departmentSql);
ResultSet departmentRs = departmentStmt.executeQuery();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>modifyEmployee.jsp</title>
<!-- ๋ถํธ์คํธ๋ฉ5 ์ฌ์ฉ -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
<!-- include ํ์ด์ง : ๋ฉ์ธ๋ฉ๋ด(๊ฐ๋ก) -->
<div>
<!-- ์ก์
ํ๊ทธ -->
<jsp:include page="/inc/mainmenu.jsp"></jsp:include>
</div>
<!-------- include ํ์ด์ง ๋ ------->
<div class="container mt-5">
<!---------------------- modify form ์์ ---------------------->
<h3>์ ๋ณด ์์ </h3>
<!-- ์คํจ์ ์๋ฌ๋ฉ์ธ์ง ์ถ๋ ฅ -->
<div class="text-danger">
<%
if(request.getParameter("msg") != null) {
%>
<%=request.getParameter("msg")%>
<%
}
%>
</div>
<form action="<%=request.getContextPath()%>/employee/modifyEmployeeAction.jsp" method="post">
<table class="table table-bordered">
<tr>
<th class="table-secondary">employeeId</th>
<td>
<input type="number" name="employeeId" value="<%=employee.getEmployeeId()%>" readonly>
<!-- ๊ธฐ๋ณธํค์ด๋ฏ๋ก ์์ ๋ถ๊ฐ(readonly) -->
</td>
</tr>
<tr>
<th class="table-secondary">firstName</th>
<td>
<input type="text" name="firstName" value="<%=employee.getFirstName()%>">
</td>
</tr>
<tr>
<th class="table-secondary">lastName</th>
<td>
<input type="text" name="lastName" value="<%=employee.getLastName()%>">
</td>
</tr>
<tr>
<th class="table-secondary">email</th>
<td>
<input type="text" name="email" value="<%=employee.getEmail()%>">
</td>
</tr>
<tr>
<th class="table-secondary">phoneNumber</th>
<td>
<input type="text" name="phoneNumber" value="<%=employee.getPhoneNumber()%>">
</td>
</tr>
<tr>
<th class="table-secondary">hireDate</th>
<td>
<input type="date" name="hireDate" value="<%=employee.getHireDate().substring(0,10)%>">
</td>
</tr>
<tr>
<th class="table-secondary">jobId</th>
<td>
<select name="jobId">
<%
while(jodRs.next()) {
%>
<option value="<%=jodRs.getString("jobId")%>" <%if(jodRs.getString("jobId").equals(employee.getJobId())) {%> selected <%}%>>
<%=jodRs.getString("jobId")%>
</option>
<%
}
%>
</select>
</td>
</tr>
<tr>
<th class="table-secondary">salary</th>
<td>
<input type="number" name="salary" value="<%=employee.getSalary()%>">
</td>
</tr>
<tr>
<th class="table-secondary">commissionPct</th>
<td>
<input type="number" name="commissionPct" value="<%=employee.getCommissionPct()%>" step="0.01" max="0.99" placeholder="๋ฒ์: 1๋ฏธ๋ง">
<!-- ์์ซ์ ์
๋ ฅ ํ์ฉ, ์
๋ ฅ ๋ฒ์ 1๋ฏธ๋ง์ผ๋ก ์ ํํ๊ธฐ -->
</td>
</tr>
<tr>
<th class="table-secondary">managerId</th>
<td>
<input type="number" name="managerId" value="<%=employee.getManagerId()%>">
</td>
</tr>
<tr>
<th class="table-secondary">departmentId</th>
<td>
<select name="departmentId">
<%
while(departmentRs.next()) {
%>
<option value="<%=departmentRs.getInt("deptId")%>" <%if(departmentRs.getInt("deptId") == employee.getDepartmentId()) {%> selected <%}%>>
<%=departmentRs.getInt("deptId")%>
</option>
<%
}
%>
</select>
</td>
</tr>
</table>
<a href="<%=request.getContextPath()%>/employee/employeeList.jsp" class="btn btn-sm btn-outline-secondary">
๋ค๋ก๊ฐ๊ธฐ
</a>
<button type="submit" class="btn btn-sm btn-secondary">์์ </button>
</form>
<!---------------------- modify form ๋ ---------------------->
</div>
</body>
</html>
๐ ๊ฒฐ๊ณผ
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import = "vo.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.net.*" %>
<%
// ํ๊ธ ๊นจ์ง์ง ์๊ฒ ์ธ์ฝ๋ฉ
request.setCharacterEncoding("utf-8");
//1. ์ ํจ์ฑ ๊ฒ์ฌ
// ์ธ์
์ ๋ณด๊ฐ ์๊ฑฐ๋ employeeId ๊ฐ์ด ์์ผ๋ฉด ์ด ํ์ด์ง์ ์ฌ ์ ์๋ค
if(session.getAttribute("loginEmployee") == null
|| request.getParameter("employeeId") == null
|| request.getParameter("employeeId").equals("")) {
response.sendRedirect(request.getContextPath() + "/employee/employeeList.jsp");
return;
}
int paramEmployeeId = Integer.parseInt(request.getParameter("employeeId"));
// ์ธ์
์ ๋ณด ๋ถ๋ฌ์ค๊ธฐ
Object o = session.getAttribute("loginEmployee");
Employee sessionEmployee = null;
if(o instanceof Employee) { // instanceof์ฐ์ฐ์ : ๊ฐ์ฒด๋ณ์ instanceof ํ์
sessionEmployee = (Employee)o;
}
int sessionEmployeeId = sessionEmployee.getEmployeeId();
// employeeId๊ฐ๊ณผ ์ธ์
์ ๋ณด๊ฐ ์ผ์นํ๋์ง ํ์ธ
if(paramEmployeeId != sessionEmployeeId) {
response.sendRedirect(request.getContextPath() + "/employee/employeeList.jsp");
return;
}
// ์์ฒญ๊ฐ
// firstName, lastName, email, phoneNumber, hireDate, jobId, salary, commissionPct, managerId, departmentId
String msg = null;
if(request.getParameter("firstName") == null
|| request.getParameter("firstName").equals("")) {
msg = URLEncoder.encode("firstName์ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("lastName") == null
|| request.getParameter("lastName").equals("")) {
msg = URLEncoder.encode("lastName์ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("email") == null
|| request.getParameter("email").equals("")) {
msg = URLEncoder.encode("email์ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("phoneNumber") == null
|| request.getParameter("phoneNumber").equals("")) {
msg = URLEncoder.encode("phoneNumber๋ฅผ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("hireDate") == null
|| request.getParameter("hireDate").equals("")) {
msg = URLEncoder.encode("hireDate๋ฅผ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("jobId") == null
|| request.getParameter("jobId").equals("")) {
msg = URLEncoder.encode("jobId๋ฅผ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("salary") == null
|| request.getParameter("salary").equals("")) {
msg = URLEncoder.encode("salary๋ฅผ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("commissionPct") == null
|| request.getParameter("commissionPct").equals("")) {
msg = URLEncoder.encode("commissionPct๋ฅผ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("managerId") == null
|| request.getParameter("managerId").equals("")) {
msg = URLEncoder.encode("managerId๋ฅผ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
} else if(request.getParameter("departmentId") == null
|| request.getParameter("departmentId").equals("")) {
msg = URLEncoder.encode("departmentId๋ฅผ ์
๋ ฅํด์ฃผ์ธ์", "utf-8");
}
// null์ด๊ฑฐ๋ ๊ณต๋ฐฑ์ด๋ฉด msg์ ํจ๊ป ๋ฆฌ๋ค์ด๋ ์
if(msg != null) {
response.sendRedirect(request.getContextPath() + "/employee/modifyEmployee.jsp?employeeId=" + paramEmployeeId + "&msg=" + msg);
return;
}
// 2. ๋ชจ๋ธ๊ฐ ๊ตฌํ๊ธฐ
// 2-1) ๋๋ผ์ด๋ฒ ๋ก๋ฉ ๋ฐ db ์ ์
String driver = "oracle.jdbc.driver.OracleDriver";
Class.forName(driver);
String dburl = "jdbc:oracle:thin:@localhost:1521:xe";
String dbuser = "****";
String dbpw = "****";
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
// 2-2) email ์ค๋ณต๊ฒ์ฌ
// email์ด ์ ๋ํฌํค์ด๋ฏ๋ก ์ค๋ณต๊ฒ์ฌ๋ฅผ ํด๋ณธ๋ค
String email = request.getParameter("email");
// ๊ธฐ์กด ์
๋ ฅ ๊ฐ์ ์ ์ธํ๊ณ ์กฐํํด์ผํ๊ธฐ ๋๋ฌธ์ WHERE์ ์ employee_id๊ฐ ์ผ์นํ์ง ์๋๋ค๋ ์กฐ๊ฑด์ ์ถ๊ฐ
String emailCntSql = "SELECT count(*) FROM employees WHERE email = ? AND employee_id != ?";
PreparedStatement emailCntStmt = conn.prepareStatement(emailCntSql);
emailCntStmt.setString(1, email);
emailCntStmt.setInt(2, paramEmployeeId);
ResultSet emailCntRs = emailCntStmt.executeQuery();
// ๊ฐฏ์ ํ์ธ
int emailCnt = 0;
if(emailCntRs.next()) {
emailCnt = emailCntRs.getInt("count(*)");
// ํด๋น email์ ๊ฐฏ์๋ฅผ cnt ๋ณ์์ ์ ์ฅ
// 0์ผ ๊ฒฝ์ฐ ์ค๋ณต ์์
}
// 0๋ณด๋ค ํด ๊ฒฝ์ฐ ์ค๋ณต ์์ // msg์ ํจ๊ป ๋ฆฌ๋ค์ด๋ ์
if(emailCnt > 0) {
System.out.println(emailCnt + " <- modifyEmployeeAction ์ค๋ณต๋ ์ด๋ฉ์ผ ๊ฐฏ์");
msg = URLEncoder.encode("์ค๋ณต๋ email์
๋๋ค ", "utf-8");
response.sendRedirect(request.getContextPath() + "/employee/modifyEmployee.jsp?employeeId=" + paramEmployeeId + "&msg=" + msg);
return;
} else {
System.out.println("modifyEmployeeAction ์ค๋ณต๋ ์ด๋ฉ์ผ ์์");
}
// ์ค๋ณต ์์ผ๋ฉด ๋๋จธ์ง ๊ฐ๋ ์ ์ฅ
String firstName = request.getParameter("firstName");
String lastName = request.getParameter("lastName");
String phoneNumber = request.getParameter("phoneNumber");
String hireDate = request.getParameter("hireDate");
String jobId = request.getParameter("jobId");
double salary = Double.parseDouble(request.getParameter("salary")); // ๋๋ธ ํ์
์ผ๋ก ํ๋ณํ
double commissionPct = Double.parseDouble(request.getParameter("commissionPct")); // ๋๋ธ ํ์
์ผ๋ก ํ๋ณํ
int managerId = Integer.parseInt(request.getParameter("managerId"));
int departmentId = Integer.parseInt(request.getParameter("departmentId"));
// 2-3) ์ฟผ๋ฆฌ ์์ฑ
String sql = "UPDATE employees SET first_name = ?, last_name = ?, email = ?, phone_number = ?, hire_date = ?, job_id = ?, salary = ?, commission_pct = ?, manager_id = ?, department_id = ? WHERE employee_id = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, firstName);
stmt.setString(2, lastName);
stmt.setString(3, email);
stmt.setString(4, phoneNumber);
stmt.setString(5, hireDate);
stmt.setString(6, jobId);
stmt.setDouble(7, salary);
stmt.setDouble(8, commissionPct);
stmt.setInt(9, managerId);
stmt.setInt(10, departmentId);
stmt.setInt(11, paramEmployeeId);
// 2-3) ์ฟผ๋ฆฌ๊ฐ ์ ์งํ๋์๋์ง ํ์ธ
int row = stmt.executeUpdate();
if(row == 1) { // ์ฑ๊ณต์
System.out.println(row + " <- modifyEmployeeAction ์ฑ๊ณต");
msg = URLEncoder.encode("์ ์์ ์ผ๋ก ์์ ๋์์ต๋๋ค", "utf-8");
response.sendRedirect(request.getContextPath() + "/employee/employeeList.jsp?msg=" + msg);
return;
} else { // ์คํจ์
System.out.println(row + " <- modifyEmployeeAction ์คํจ");
msg = URLEncoder.encode("์์ ๋์ง ์์์ต๋๋ค ๋ค์ ์๋ํด์ฃผ์ธ์", "utf-8");
response.sendRedirect(request.getContextPath() + "/employee/modifyEmployee.jsp?employeeId=" + paramEmployeeId + "&msg=" + msg);
return;
}
%>
employee_id != ?
์ ์ถ๊ฐํ๋ค!<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import = "vo.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.net.*" %>
<%
//1. ์ ํจ์ฑ ๊ฒ์ฌ
// ์ธ์
์ ๋ณด๊ฐ ์๊ฑฐ๋ employeeId ๊ฐ์ด ์์ผ๋ฉด ์ด ํ์ด์ง์ ์ฌ ์ ์๋ค
if(session.getAttribute("loginEmployee") == null
|| request.getParameter("employeeId") == null
|| request.getParameter("employeeId").equals("")) {
response.sendRedirect(request.getContextPath() + "/employee/employeeList.jsp");
return;
}
int paramEmployeeId = Integer.parseInt(request.getParameter("employeeId"));
// ์ธ์
์ ๋ณด ๋ถ๋ฌ์ค๊ธฐ
Object o = session.getAttribute("loginEmployee");
Employee sessionEmployee = null;
if(o instanceof Employee) { // instanceof์ฐ์ฐ์ : ๊ฐ์ฒด๋ณ์ instanceof ํ์
sessionEmployee = (Employee)o;
}
int sessionEmployeeId = sessionEmployee.getEmployeeId();
// employeeId๊ฐ๊ณผ ์ธ์
์ ๋ณด๊ฐ ์ผ์นํ๋์ง ํ์ธ
if(paramEmployeeId != sessionEmployeeId) {
response.sendRedirect(request.getContextPath() + "/employee/employeeList.jsp");
return;
}
// 2. ๋ชจ๋ธ๊ฐ ๊ตฌํ๊ธฐ
// 2-1) ๋๋ผ์ด๋ฒ ๋ก๋ฉ ๋ฐ db ์ ์
String driver = "oracle.jdbc.driver.OracleDriver";
Class.forName(driver);
String dburl = "jdbc:oracle:thin:@localhost:1521:xe";
String dbuser = "****";
String dbpw = "****";
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
// 2-2) ์ฟผ๋ฆฌ ์์ฑ
String sql = "DELETE FROM employees WHERE employee_id = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, paramEmployeeId);
// 2-3) ์ฟผ๋ฆฌ๊ฐ ์ ์งํ๋์๋์ง ํ์ธ
int row = stmt.executeUpdate();
String msg = null;
if(row == 1) { // ์ฑ๊ณต์ ๋ก๊ทธ์์ ๋๋ฏ๋ก home์ผ๋ก
System.out.println(row + " <- removeEmployeeAction ์ฑ๊ณต");
session.invalidate(); // ์ฌ์์ ๋ณด๊ฐ ์ญ์ ๋์์ผ๋ฏ๋ก ์ธ์
์ด๊ธฐํ
msg = URLEncoder.encode("์ ์์ ์ผ๋ก ์ญ์ ๋์์ต๋๋ค", "utf-8");
response.sendRedirect(request.getContextPath() + "/home.jsp?msg=" + msg);
return;
} else { // ์คํจ์ msg์ ํจ๊ป list๋ก
System.out.println(row + " <- removeEmployeeAction ์คํจ");
msg = URLEncoder.encode("์ญ์ ๋์ง ์์์ต๋๋ค ๋ค์ ์๋ํด์ฃผ์ธ์", "utf-8");
response.sendRedirect(request.getContextPath() + "/employee/employeeList.jsp?msg=" + msg);
return;
}
%>
๐ง ํ๋ฃจ๋ฅผ ๋ง์น๋ฉฐ
delete from job_history
commit;
alter TRIGGER HR.UPDATE_JOB_HISTORY disable;
์ํ๋ฐ์ดํฐ์ job_history, TRIGGER ๋ฑ ์ฌ๋ฌ ์กฐ๊ฑด๋ค์ด ๊ฑธ๋ ค ์์ด์ ์์ ํ๋ฉด์ ์ ๋ฅผ ๋จน์์ง๋ง, ๊ทธ๋๋ ํด๊ฒฐ๋ฐฉ๋ฒ์ ์ฐพ์๋ด๋ฉด์ ํ์ด์ง๋ฅผ ์์ฑํ์ ๋์๋ ๋ฟ๋ฏํ๋ค!