1. 아래를 프로그래밍 하시오.
emp/salgrade 로 치고 들어 오면
salgrade 테이블을 조인 emp/salgrade.jsp 에서
해서 이름, 월급, 등급이 나오도록하시오.
(단 resultmap 을 할용 하시오)
SalGradeVO
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class SalGradeVO {
private int grade;
private int losal;
private int hisal;
private List<EmpVO> empList;
}EmpController
@GetMapping("/salgrade")
public String salgrade(Model model) {
System.out.println("salgrade()..");
model.addAttribute("salEmpList", empService.getSalEmpList());
return "emp/salgrade";
}
EmpService
public List<SalGradeVO> getSalEmpList() {
return empMapper.getSalEmpList();
}EmpMapper
List<SalGradeVO> getSalEmpList();EmpMapper.xml
<resultMap id="salMap" type="SalGradeVO" >
<result property="grade" column="grade"/>
<result property="lowsal" column="lowsal"/>
<result property="hisal" column="hisal"/>
<collection property="empList" resultMap="empMap"></collection>
</resultMap>
<select id="getSalEmpList" resultMap="salMap">
<![CDATA[
select ename, sal, grade from emp, salgrade where sal between losal and hisal
]]>
</select>salgrade.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>sal 리스트</h1>
<table width="50%" border="1">
<tr>
<td>이름</td>
<td>월급</td>
<td>등급</td>
</tr>
<c:forEach var="sal" items="${salEmpList}">
<c:forEach var="emp" items="${sal.empList}">
<tr>
<td>${emp.ename}</td>
<td>${emp.sal}</td>
<td>${sal.grade}</td>
</tr>
</c:forEach>
</c:forEach>
</table>
</body>
</html>2. 아래를 프로그래밍 하시오.
emp/info 로 치고 들어 오면
salgrade, dept, emp 테이블을 조인 emp/info.jsp 에서
해서 이름, 월급, 부서번호,부서위치,등급이 나오도록하시오.
(단 resultmap 을 할용 하시오)
SalgradeDeptEmpVO
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class SalgradeDeptEmpVO {
private int grade;
private int losal;
private int hisal;
private List<DeptEmpVO> deptList;
}DeptEmpVO
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class DeptEmpVO {
private int deptno;
private String dname;
private String loc;
private List<EmpVO> empList;
}EmpVO
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class EmpVO {
private int empno;
private String ename;
private String job;
private int mgr;
private Date hiredate;
private int sal;
private int comm;
private int deptno;
}EmpController
@GetMapping("/salgrade")
public String salgrade(Model model) {
System.out.println("salgrade()..");
model.addAttribute("salEmpList", empService.getSalEmpList());
return "emp/salgrade";
}
@GetMapping("/info")
public String info(Model model) {
System.out.println("salDeptEmpList()..");
model.addAttribute("salDeptEmpList", empService.getSalDeptEmpList());
return "emp/info";
}EmpService
public List<SalgradeDeptEmpVO> getSalDeptEmpList() {
return empMapper.getSalDeptEmpList();
}EmpMapper
public List<SalgradeDeptEmpVO> getSalDeptEmpList();EmpMapper.xml
<select id="getSalDeptEmpList" resultMap="salgradeMapQ">
<![CDATA[
select * from emp e , dept d, salgrade s where e.deptno=d.deptno and sal between losal and hisal
]]>
</select>
<resultMap id="salgradeMapQ" type="salgradeDeptEmpVO">
<result property="grade" column="grade"/>
<result property="losal" column="losal"/>
<result property="hisal" column="hisal"/>
<collection property="deptList" resultMap="deptMapQ"></collection>
</resultMap>
<resultMap id="deptMapQ" type="DeptEmpVO">
<result property="deptno" column="deptno"/>
<result property="dname" column="dname"/>
<result property="loc" column="loc"/>
<collection property="empList" resultMap="empMapQ"></collection>
</resultMap>
<resultMap id="empMapQ" type="EmpVO">
<result property="empno" column="empno"/>
<result property="ename" column="ename"/>
<result property="job" column="job"/>
<result property="mgr" column="mgr"/>
<result property="hiredate" column="hiredate"/>
<result property="sal" column="sal"/>
<result property="comm" column="comm"/>
<result property="deptno" column="deptno"/>
</resultMap>info.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>info 리스트</h1>
<table width="50%" border="1">
<tr>
<td>이름</td>
<td>월급</td>
<td>부서번호</td>
<td>부서위치</td>
<td>부서등급</td>
</tr>
<c:forEach var="sal" items="${salDeptEmpList}">
<c:forEach var="dept" items="${sal.deptList}">
<c:forEach var="emp" items="${dept.empList}">
<tr>
<td>${emp.ename}</td>
<td>${emp.sal}</td>
<td>${dept.deptno}</td>
<td>${dept.loc}</td>
<td>${sal.grade}</td>
</tr>
</c:forEach>
</c:forEach>
</c:forEach>
</table>
</body>
</html>