내용 |
EmpRepository.java
package com.coderby.myapp.hr.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import com.coderby.myapp.hr.model.EmpVO;
@Repository
public class EmpRepository implements IEmpRepository {
@Autowired
JdbcTemplate jdbcTemplate;
private class EmpMapper implements RowMapper<EmpVO> {
@Override
public EmpVO mapRow(ResultSet rs, int count) throws SQLException {
EmpVO emp = new EmpVO();
emp.setEmployeeId(rs.getInt("employee_id"));
emp.setFirstName(rs.getString("first_name"));
emp.setLastName(rs.getString("last_name"));
emp.setEmail(rs.getString("email"));
emp.setPhoneNumber(rs.getString("phone_number"));
emp.setHireDate(rs.getDate("hire_date"));
emp.setJobId(rs.getString("job_id"));
emp.setSalary(rs.getDouble("salary"));
emp.setCommissionPct(rs.getDouble("commission_pct"));
emp.setManagerId(rs.getInt("manager_id"));
emp.setDepartmentId(rs.getInt("department_id"));
return emp;
}
}
@Override
public int getEmpCount() {
String sql = "select count(*) from employees";
return jdbcTemplate.queryForObject(sql, Integer.class);
}
@Override
public int getEmpCount(int deptid) {
String sql = "select count(*) from employees where department_id=?";
return jdbcTemplate.queryForObject(sql, Integer.class, deptid);
}
@Override
public List<EmpVO> getEmpList() {
String sql = "select * from employees";
return jdbcTemplate.query(sql, new RowMapper<EmpVO>() {
@Override
public EmpVO mapRow(ResultSet rs, int count) throws SQLException {
EmpVO emp = new EmpVO();
emp.setEmployeeId(rs.getInt("employee_id"));
emp.setFirstName(rs.getString("first_name"));
emp.setLastName(rs.getString("last_name"));
emp.setEmail(rs.getString("email"));
emp.setPhoneNumber(rs.getString("phone_number"));
emp.setHireDate(rs.getDate("hire_date"));
emp.setJobId(rs.getString("job_id"));
emp.setSalary(rs.getDouble("salary"));
emp.setCommissionPct(rs.getDouble("commission_pct"));
emp.setManagerId(rs.getInt("manager_id"));
emp.setDepartmentId(rs.getInt("department_id"));
return emp;
}
});
}
@Override
public EmpVO getEmpInfo(int empid) {
String sql = "select employee_id, first_name, last_name, "
+ "email, phone_number, hire_date, job_id, salary, "
+ "commission_pct, manager_id, department_id "
+ "from employees where employee_id=?";
return jdbcTemplate.queryForObject(sql, new EmpMapper(), empid);
}
@Override
public void updateEmp(EmpVO emp) {
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=?";
jdbcTemplate.update(sql, emp.getFirstName(),
emp.getLastName(),
emp.getEmail(),
emp.getPhoneNumber(),
emp.getHireDate(),
emp.getJobId(),
emp.getSalary(),
emp.getCommissionPct(),
emp.getManagerId(),
emp.getDepartmentId(),
emp.getEmployeeId() );
}
@Override
public void insertEmp(EmpVO emp) {
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 (?,?,?,?,?,sysdate,?,?,?,?,?)";
jdbcTemplate.update(sql,
emp.getEmployeeId(),
emp.getFirstName(),
emp.getLastName(),
emp.getEmail(),
emp.getPhoneNumber(),
emp.getJobId(),
emp.getSalary(),
emp.getCommissionPct(),
emp.getManagerId(),
emp.getDepartmentId()
);
}
@Override
public void deleteJobHistory(int empid) {
String sql = "delete from job_history where employee_id=?";
jdbcTemplate.update(sql, empid);
}
@Override
public void deleteEmp(int empid, String email) {
String sql = "delete from employees where employee_id=? and email=?";
jdbcTemplate.update(sql, empid, email);
}
@Override
public List<Map<Integer, String>> getAllDeptId() {
String sql = "select department_id, department_name from departments";
return jdbcTemplate.query(sql, new RowMapper<Map<Integer, String>>() {
@Override
public Map<Integer, String> mapRow(ResultSet rs, int count) throws SQLException {
Map<Integer, String> deptMap = new HashMap<Integer, String>();
deptMap.put(rs.getInt("department_id"), rs.getString("department_name"));
return deptMap;
};
});
}
@Override
public List<Map<String, Object>> getAllJobId() {
String sql = "select job_id as jobId, job_title as title from jobs";
return jdbcTemplate.queryForList(sql);
}
@Override
public List<Map<String, Object>> getAllManagerId() {
String sql = "select "
+ " d.manager_id as managerId, e.first_name as firstName "
+ "from departments d join employees e "
+ " on d.manager_id = e.employee_id "
+ "order by d.manager_id";
return jdbcTemplate.queryForList(sql);
}
}//end class
updateform.jsp
<%@ page contentType="text/html; charset=UTF-8" trimDirectiveWhitespaces="true"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>CoderBy</title>
</head>
<body>
<h1>사원정보 수정</h1>
<form action="./update" method="post">
<table border="1">
<tr>
<th>EMPLOYEE_ID</th>
<td><input type="number" name="employeeId" value="${emp.employeeId}" readonly></td>
</tr>
<tr>
<th>FIRST_NAME</th>
<td><input type="text" name="firstName" value="${emp.firstName}"></td>
</tr>
<tr>
<th>LAST_NAME</th>
<td><input type="text" name="lastName" value="${emp.lastName}" required></td>
</tr>
<tr>
<th>EMAIL</th>
<td><input type="text" name="email" value="${emp.email}" required></td>
</tr>
<tr>
<th>PHONE_NUMBER</th>
<td><input type="text" name="phoneNumber" value="${emp.phoneNumber}"></td>
</tr>
<tr>
<th>HIRE_DATE</th>
<td><input type="date" name="hireDate" value="${emp.hireDate}" required></td>
</tr>
<tr>
<th>JOB_ID</th>
<td>
<select name="jobId">
<c:forEach var="job" items="${jobs}">
<c:if test="${emp.jobId == job.jobId}">
<option value="${job.jobId}" selected>${job.title}</option>
</c:if>
<c:if test="${emp.jobId != job.jobId}">
<option value="${job.jobId}">${job.title}</option>
</c:if>
</c:forEach>
</select>
</td>
</tr>
<tr>
<th>SALARY</th>
<td><input type="number" name="salary" value="${emp.salary}"></td>
</tr>
<tr>
<th>COMMISSION_PCT</th>
<td><input type="number" name="commissionPct" value="${emp.commissionPct}" step="0.1" min="0" max="0.99"></td>
</tr>
<tr>
<th>MANAGER_ID</th>
<td>
<select name="managerId">
<c:forEach var="manager" items="${managers}">
<c:if test="${emp.managerId == manager.managerId}">
<option value="${manager.managerId}" selected>${manager.firstName}</option>
</c:if>
<c:if test="${emp.managerId != manager.key}">
<option value="${manager.managerId}">${manager.firstName}</option>
</c:if>
</c:forEach>
</select>
</td>
</tr>
<tr>
<th>DEPARTMENT_ID</th>
<td>
<select name="departmentId">
<c:forEach var="map" items="${depts}">
<c:forEach var="dept" items="${map}">
<c:if test="${emp.departmentId == dept.key}">
<option value="${dept.key}" selected>${dept.value}</option>
</c:if>
<c:if test="${emp.departmentId != job.key}">
<option value="${dept.key}">${dept.value}</option>
</c:if>
</c:forEach>
</c:forEach>
</select>
</td></tr>
<tr>
<th> </th>
<td>
<input type="submit" value="수정">
<input type="reset" value="취소">
</td>
</tr>
</table>
</form>
</body>
</html>
EmpMain.java
package com.coderby.myapp.hr;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.coderby.myapp.hr.model.EmpVO;
import com.coderby.myapp.hr.service.IEmpService;
public class EmpMain {
public static void main(String[] args) {
AbstractApplicationContext context =
new ClassPathXmlApplicationContext("spring/application-config.xml");
IEmpService empService = context.getBean("empService", IEmpService.class);
System.out.println("-- 사원의 수 조회");
System.out.println(empService.getEmpCount());
System.out.println(empService.getEmpCount(50));
System.out.println("-- 103번 사원의 정보를 조회합니다.");
System.out.println(empService.getEmpInfo(103));
System.out.println("-- 사원 전체 정보를 조회합니다.");
System.out.println(empService.getEmpList());
System.out.println("-- 새로운 사원 정보를 입력합니다.");
EmpVO emp = new EmpVO();
emp.setEmployeeId(210);
emp.setFirstName("JinKyoung");
emp.setLastName("Heo");
emp.setEmail("HEOJK");
emp.setPhoneNumber("222-222");
emp.setJobId("IT_PROG");
emp.setSalary(8000);
emp.setCommissionPct(0.2);
emp.setManagerId(100);
emp.setDepartmentId(10);
try {
empService.insertEmp(emp);
System.out.println("insert ok");
}catch(Exception e) {
System.out.println(e.getMessage());
}
System.out.println("-- 신규 사원의 정보를 조회/출력합니다.");
EmpVO emp210 = empService.getEmpInfo(210);
System.out.println(emp210);
System.out.println("-- 210번 사원의 급여를 10% 인상시킵니다.");
emp210.setSalary(emp210.getSalary() * 1.1);
System.out.println("-- 수정된 사원의 정보를조회/출력합니다.");
emp210 = empService.getEmpInfo(210);
System.out.println(emp210);
System.out.println("-- 210번 사원의 정보를 삭제합니다.");
empService.deleteEmp(210, "HEOJK");
System.out.println("-- 모든 부서번호와 부서이름 정보를 출력합니다.");
System.out.println(empService.getAllDeptId());
System.out.println("-- 모든 직무아이디와 직무타이틀을 출력합니다.");
System.out.println(empService.getAllJobId());
System.out.println("-- 모든 매니저번호와 매니저이름을 출력합니다.");
System.out.println(empService.getAllManagerId());
}
}
|