내용

글번호 702
작성자 heojk
작성일 2017-07-01 11:07:15
제목 emp 다루기 숙제
내용 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());
	}
}
첨부파일 사원관리.xlsx (11,227byte)