Mybatis) emp 패키지 시작 - 사원 정보 전체 조회

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

※ 아래 포스팅 참고

https://chanychu.tistory.com/362

Oracle) synonym 동의어 객체 생성

 

emp 패키지 시작

 

새로운 패키지가 추가되었고, DispatcherServlet에서는 Student만을 받기 때문에 수정 작업 필요

 

DispatcherServlet

public DispatcherServlet() throws FileNotFoundException, IOException, ClassNotFoundException, NoSuchMethodException, SecurityException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
    // 1. url-command.properties -> Properties 객체
    String filename = DispatcherServlet.class.getResource("/url-command.properties").getPath(); // / -> target/class 의미
    Properties prop = new Properties();
    prop.load(new FileReader(filename));

    // 2. Properties 객체 -> urlCommandMap에 요소추가 (String=AbstractController객체)    	
    Set<String> urls = prop.stringPropertyNames();	// 모든 키 셋을 리턴

    StudentService studentService = new StudentServiceImpl(new StudentDaoImpl());
    EmpService empService = new EmpServiceImpl(new EmpDaoImpl());

    for(String url : urls) {
        String className = prop.getProperty(url);
        Class<?> clz = Class.forName(className);
        Class<?>[] params = new Class<?>[1];
        Object[] args = new Object[1];

        if(url.startsWith("/student")) {
            params[0] = StudentService.class;
            args[0] = studentService;	
        } else if(url.startsWith("/emp")) {
            params[0] = EmpService.class;
            args[0] = empService;
        }

        Constructor<?> constructor = clz.getDeclaredConstructor(params);
        AbstractController controller = (AbstractController) constructor.newInstance(args);
        urlCommandMap.put(url, controller);
    }
    System.out.println("urlCommandMap = " + urlCommandMap);
}

 

emp-mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="emp"> 
	
</mapper>

 

mybatis-config.xml

mapper 추가

  <mappers>
    <mapper resource="/mapper/student/student-mapper.xml"/>
    <mapper resource="/mapper/emp/emp-mapper.xml"/>
  </mappers>

 

url-command.properties

/emp/search1.do = com.ce.app.emp.controller.EmpSearchController1

 

EmpSearchController1

@Log4j
@RequiredArgsConstructor
public class EmpSearchController1 extends AbstractController {
	private final EmpService empService;
	
	@Override
	public String doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
        return "emp/search1";
	}
}

---

사원 정보 전체 조회

 

Controller

EmpSearchController1

@Log4j
@RequiredArgsConstructor
public class EmpSearchController1 extends AbstractController {
	private final EmpService empService;
	
	@Override
	public String doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		List<Map<String, Object>> list = empService.selectEmpList();
		log.debug("list = " + list);
		request.setAttribute("list", list);
		
		return "emp/search1";
	}
}

 

Service

EmpServiceImpl

(interface 생략)

@RequiredArgsConstructor
public class EmpServiceImpl implements EmpService {
	private final EmpDao empDao;
	
	@Override
	public List<Map<String, Object>> selectEmpList() {
		try(SqlSession sqlSession = getSqlSession()) {
			return empDao.selectEmpList(sqlSession);			
		}
	}
}

 

Dao

EmpDaoImpl

(interface 생략)

public class EmpDaoImpl implements EmpDao {
	@Override
	public List<Map<String, Object>> selectEmpList(SqlSession sqlSession) {
		return sqlSession.selectList("emp.selectEmpList");
	}
}

 

emp-mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="emp"> 
	<select id="selectEmpList" resultMap="empMap">
		select * from emp
	</select>
	
	<resultMap type="map" id="empMap">
		<id column="emp_id" property="empId"/>
		<result column="emp_name" property="empName"/>
		<result column="emp_no" property="empNo"/>
		<result column="email" property="email"/>
		<result column="phone" property="phone"/>
		<result column="dept_code" property="deptCode"/>
		<result column="job_code" property="jobCode"/>
		<result column="sal_level" property="salLevel"/>
		<result column="salary" property="salary"/>
		<result column="bonus" property="bonus"/>
		<result column="manager_id" property="managerId"/>
		<result column="hire_date" property="hireDate"/>
		<result column="quit_date" property="quitDate"/>
	</resultMap>
</mapper>

 

search1.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Mybatis실습</title>
<style>
div#emp-container{text-align:center;}
table.tbl-emp{
	margin:0 auto;
	border:1px solid; 
	border-collapse:collapse;
}
table.tbl-emp th, table.tbl-emp td{
	border:1px solid;
	padding:5px;
}
div#search-container{
	padding:15px 0;
}
</style>
</head>
<body>
<div id="emp-container">
	<h2>사원정보 </h2>
	
	<table class="tbl-emp">
		<tr>
			<th></th><!-- 1부터 넘버링 처리 -->
			<th>사번</th>
			<th>사원명</th>
			<th>주민번호</th><!--뒷6자리는 ******처리-->
			<th>이메일</th>
			<th>전화번호</th>
			<th>부서코드</th>
			<th>직급코드</th>
			<th>급여레벨</th>
			<th>급여</th><!--원화기호, 세자리마다 콤마표시-->
			<th>보너스율</th><!--percent로 표시-->
			<th>매니져 사번</th>
			<th>입사일</th><!--날짜형식 yyyy/MM/dd-->
			<th>퇴사여부</th>
		</tr>
		<!-- 조회된 데이터가 있는 경우와 없는 경우를 분기처리 하세요 -->
		<c:if test="${empty list}">
			<tr>
				<td colspan="14">조회된 정보가 없습니다.</td>
			</tr>
		</c:if>
		<c:if test="${!empty list}">
			<c:forEach items="${list}" var="emp" varStatus="vs">
				<tr>
					<td>${vs.count}</td>
					<td>${emp.empId}</td>
					<td>${emp.empName}</td>
					<td>
						${fn:substring(emp.empNo, 0, 8)}******
					</td>
					<td>${emp.email}</td>
					<td>${emp.phone}</td>
					<td>${emp.deptCode}</td>
					<td>${emp.jobCode}</td>
					<td>${emp.salLevel}</td>
					<td>
						<fmt:formatNumber value="${emp.salary}" type="currency"/>
					</td>
					<td>
						<fmt:formatNumber value="${emp.bonus}" type="percent"/>
					</td>
					<td>${emp.managerId}</td>
					<td>
						<fmt:formatDate value="${emp.hireDate}" pattern="yyyy/MM/dd"/>
					</td>
					<td>${emp.quitDate}</td>
				</tr>
			</c:forEach>
		</c:if>
	</table>
</div>

</body>
</html>

 

※ JSTL <fmt:formatNumber> Tag 관련

https://www.tutorialspoint.com/jsp/jstl_format_formatnumber_tag.htm

JSTL - Core <fmt:formatNumber> Tag