국비학원_76일차(myBatis-페이징, 회원정보 조회 및 수정)

써니·2022년 11월 9일
MyBatisSpring
0

spring

목록 보기
18/23

페이징 작업

🔻oBootMybatis01

[com.oracle.oBootMybatis01.service]

  • Paging.java
  1. 한 페이지 안에 1~10개가 보임
  2. 전체 게시글 갯수에 한 페이지의 수를 나눠서 올림 해줌
  3. 공갈 페이지 예방으로 마지막 로직을 걸어줌
  • total = 전체 게시글 갯수
  • currentPage = 현재페이지
  • rowPage = 한페이지의 수
  • pageBlock = 10개씩 보여줌
  • totalPage = 실제 총 페이지의 수
  • startPage = 실제로 시작하는 페이지 수
  • endPage = 실제로 끝나느 페이지 수
package com.oracle.oBootMybatis01.service;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class Paging {
	private int currentPage = 1;	private int rowPage = 10;
	private int pageBlock	= 10;
	private int start;				private int end;
	private int startPage;			private int endPage;
	private int total;				private int totalPage;

	//				25
	public Paging(int total, String currentPage1) {
		this.total = total;	// 140
		if (currentPage1 != null) {
			this.currentPage = Integer.parseInt(currentPage1); // 2
		}
		//			1				10
		start = (currentPage - 1) * rowPage + 1; // 시작시1	11
		end	  = start + rowPage - 1;			 // 시작시10	20
		//									25		/	10
		totalPage = (int) Math.ceil((double)total / rowPage);	// 시작시 3 5 14
					//		2			2
		startPage = currentPage - (currentPage - 1) % pageBlock; // 시작시 1
		endPage = startPage + pageBlock - 1; // 10
		//	10		14
		if(endPage > totalPage) {
			endPage = totalPage;
		}
	}
}

[com.oracle.oBootMybatis01.controller]

  • EmpController.java
package com.oracle.oBootMybatis01.controller;

import java.util.List;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import com.oracle.oBootMybatis01.model.Emp;
import com.oracle.oBootMybatis01.service.EmpService;
import com.oracle.oBootMybatis01.service.Paging;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Controller
@Slf4j
@RequiredArgsConstructor
public class EmpController {
	
	private final EmpService  es;
	
	@RequestMapping(value = "listEmp")
	public String empList(Emp emp, String currentPage, Model model) {
		log.info("empList Start...");
		// Emp 전체 count 25
		int totalEmp = es.totalEmp();
		log.info("empList total=>{}", totalEmp);
		
		// Paging 작업
		Paging page = new Paging(totalEmp, currentPage);
		// Parameter emp --> page만 추가 Setting(페이징)
		emp.setStart(page.getStart()); // 시작시 1
		emp.setEnd(page.getEnd());	   // 시작시 10
		
		List<Emp> listEmp = es.listEmp(emp);
		log.info("listEmp.size()->{}",listEmp.size());
		
		model.addAttribute("totalEmp", totalEmp);
		model.addAttribute("listEmp", listEmp);
		model.addAttribute("page", page);
		return "list";
	}
}

📂 views

  • list.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>회원관리</h1>
	<a href="writeForm">입력</a>
	<h5>사원 수 : ${totalEmp}</h5>
	
	<!-- Maxium -->
	<c:set var="num" value="${page.total-page.start+1}"></c:set> 
	<table>
		<tr>
			<th>번호</th>
			<th>사번</th>
			<th>이름</th>
			<th>업무</th>
			<th>급여</th>
		</tr>
		<c:forEach var="emp" items="${listEmp}">
			<tr>
				<td>${num}</td>
				<td>${emp.empno}</td>
				<td><a href="detail?empno=${emp.empno}">${emp.ename}</a></td>
				<td>${emp.job}</td>
				<td>${emp.sal}</td>
			</tr>
			<c:set var="num" value="${num - 1}"></c:set>
		</c:forEach>
	</table>
  
	<!-- 페이징 번호 설정 -->
	<c:if test="${page.startPage > page.pageBlock }">
		<a href="listEmp?currentPage=${page.startPage-page.pageBlock}">이전</a>
	</c:if>
	<c:forEach var="i" begin="${page.startPage}" end="${page.endPage}">
		<a href="listEmp?currentPage=${i}">[${i}]</a>
	</c:forEach>
	<c:if test="${page.endPage < page.totalPage}">
		<a href="listEmp?currentPage=${page.startPage+page.pageBlock}">[다음]</a>
	</c:if>
</body>
</html>


회원상세정보

[com.oracle.oBootMybatis01.controller]

  • EmpController.java
package com.oracle.oBootMybatis01.controller;

import java.util.List;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import com.oracle.oBootMybatis01.model.Emp;
import com.oracle.oBootMybatis01.service.EmpService;
import com.oracle.oBootMybatis01.service.Paging;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Controller
@Slf4j
@RequiredArgsConstructor
public class EmpController {
	
	private final EmpService  es;
	
	@RequestMapping(value = "listEmp")
	public String empList(Emp emp, String currentPage, Model model) {
		log.info("empList Start...");
		// Emp 전체 count 25
		int totalEmp = es.totalEmp();
		log.info("empList total=>{}", totalEmp);
		
		// Paging 작업
		Paging page = new Paging(totalEmp, currentPage);
		// Parameter emp --> page만 추가 Setting(페이징)
		emp.setStart(page.getStart()); // 시작시 1
		emp.setEnd(page.getEnd());	   // 시작시 10
		
		List<Emp> listEmp = es.listEmp(emp);
		log.info("listEmp.size()->{}",listEmp.size());
		
		model.addAttribute("totalEmp", totalEmp);
		model.addAttribute("listEmp", listEmp);
		model.addAttribute("page", page);
		return "list";
	}
	
	@GetMapping(value = "detailEmp")
	public String detailEmp(int empno, Model model) {
		log.info("detailEmp Start...");
		Emp emp = es.detailEmp(empno);
		log.info("emp->{}", emp);
		model.addAttribute("emp", emp);
		return "detailEmp";
	}
}

[com.oracle.oBootMybatis01.service]

  • EmpServiceImpl.java
package com.oracle.oBootMybatis01.service;

import java.util.List;

import org.springframework.stereotype.Service;

import com.oracle.oBootMybatis01.dao.EmpDao;
import com.oracle.oBootMybatis01.model.Emp;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Service
@Slf4j
@RequiredArgsConstructor
public class EmpServiceImpl implements EmpService {
	
	private final EmpDao ed;
	
	@Override
	public int totalEmp() {
		log.info("totalEmp Start...");
		int totEmpCnt = ed.totalEmp();
		log.info("totalEmp totEmpCnt=>{}",totEmpCnt);
		return totEmpCnt;
	}

	@Override
	public List<Emp> listEmp(Emp emp) {
		List<Emp> empList = null;
		log.info("listEmp Start...");
		empList = ed.listEmp(emp);
		log.info("listEmp empList.size()->{}",empList.size());
		return empList;
	}

	@Override
	public Emp detailEmp(int empno) {
		log.info("detailEmp Start...");
		Emp emp = null;
		emp = ed.detailEmp(empno);
		log.info("emp->{}", emp);
		return emp;
	}
}

[com.oracle.oBootMybatis01.dao]

  • EmpDaoImpl.java
package com.oracle.oBootMybatis01.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.springframework.stereotype.Repository;

import com.oracle.oBootMybatis01.model.Emp;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Repository
@Slf4j
@RequiredArgsConstructor
public class EmpDaoImpl implements EmpDao {
	
	// Mybatis DB 연결 -> JPA EntityManager
	private final SqlSession session;

	@Override
	public int totalEmp() {
		int totEmpCount = 0;
		log.info("totalEmp Start...");
		
		try {
			totEmpCount = session.selectOne("empTotal");
			log.info("totalEmp totEmpCount->{}", totEmpCount);
		} catch (Exception e) {
			log.info("totalEmp Exception->{}",e.getMessage());
		}
		return totEmpCount;
	}

	@Override
	public List<Emp> listEmp(Emp emp) {
		List<Emp> empList = null;
		log.info("listEmp Start...");
		//								Map ID		parameter
		try {
			empList = session.selectList("tkEmpListAll", emp);
			log.info("empList.size()->{}",empList.size());
		} catch (Exception e) {
			log.info("empList Exception->{}",e.getMessage());
		}
		return empList;
	}

	@Override
	public Emp detailEmp(int empno) {
		log.info("detailEmp Start...");
		Emp emp = new Emp();
		
		try {
			emp = session.selectOne("tkEmpSelOne", empno);
			log.info("emp.getEname()->{}",emp.getEname());
		} catch (Exception e) {
			log.info("detailEmp Exception->{}",e.getMessage());
		}
		return emp;
	}

}

📂 mapper

  • Emp.xml
<?xml version="1.0" encoding="UTF-8" ?>

<!-- ======= mapper 기본설정 ======= -->
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!-- ==== 루트 엘리먼트 & 네임스페이스 설정(프로젝트 전체 내에서 유일해야 한다.) ==== -->
<mapper namespace="com.oracle.oBootMybatis01.EmpMapper">
	<select id="empTotal" resultType="int">
		SELECT Count(*) FROM emp
	</select>
	
	<select id="tkEmpListAll" parameterType="Emp" resultType="Emp">
		select *
		from 
		(
		        select rownum rn, a.*
		        from
		        (select * from emp order by empno) a
		)
		where rn BETWEEN #{start} and #{end}
	</select>
	
	<!-- 회원상세보기 -->
	<select id="tkEmpSelOne" parameterType="int" resultType="Emp">
		select * from emp where empno = #{empno}
	</select>
</mapper>

📂 views

  • detailEmp.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h2>직원정보</h2>
	<table>
		<tr><th>사번</th><td>${emp.empno}</td></tr>
		<tr><th>이름</th><td>${emp.ename}</td></tr>
		<tr><th>업무</th><td>${emp.job}</td></tr>
		<tr><th>급여</th><td>${emp.sal}</td></tr>
		<tr><th>입사일</th><td>${emp.hiredate}</td></tr>
		<tr><th>보너스</th><td>${emp.comm}</td></tr>
		<tr><th>관리자사번</th><td>${emp.mgr}</td></tr>
		<tr><th>부서코드</th><td>${emp.deptno}</td></tr>
		<tr><td colspan="2">
			<input type="button" value="목록"
				onclick="location.href='listEmp'">
			<input type="button" value="수정"
				onclick="location.href='updateFormEmp?empno=${emp.empno}'">
			<input type="button" value="삭제"
				onclick="location.href='delete?empno=${emp.empno}'"></td>
		</tr>
	</table>
</body>
</html>



☁날짜(기본일자확인)

문제
1. DTO String hiredate
2. view : 단순조회 OK,JSP에서 input type="date" 시간 출력 문제 발생
3. 해결책 : 년월일을 짤라 넣어 주어야함



[com.oracle.oBootMybatis01.controller]

  • EmpController.java
package com.oracle.oBootMybatis01.controller;

import java.util.List;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import com.oracle.oBootMybatis01.model.Emp;
import com.oracle.oBootMybatis01.service.EmpService;
import com.oracle.oBootMybatis01.service.Paging;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Controller
@Slf4j
@RequiredArgsConstructor
public class EmpController {
	
	private final EmpService  es;
	
	@RequestMapping(value = "listEmp")
	public String empList(Emp emp, String currentPage, Model model) {
		log.info("empList Start...");
		// Emp 전체 count 25
		int totalEmp = es.totalEmp();
		log.info("empList total=>{}", totalEmp);
		
		// Paging 작업
		Paging page = new Paging(totalEmp, currentPage);
		// Parameter emp --> page만 추가 Setting(페이징)
		emp.setStart(page.getStart()); // 시작시 1
		emp.setEnd(page.getEnd());	   // 시작시 10
		
		List<Emp> listEmp = es.listEmp(emp);
		log.info("listEmp.size()->{}",listEmp.size());
		
		model.addAttribute("totalEmp", totalEmp);
		model.addAttribute("listEmp", listEmp);
		model.addAttribute("page", page);
		return "list";
	}
	
	// 회원 상세 정보
	@GetMapping(value = "detailEmp")
	public String detailEmp(int empno, Model model) {
		log.info("detailEmp Start...");
		Emp emp = es.detailEmp(empno);
		log.info("emp->{}", emp);
		model.addAttribute("emp", emp);
		return "detailEmp";
	}
	
	// 회원 정보 수정
	@GetMapping(value = "updateFormEmp")
	public String updateFormEmp(int empno, Model model) {
		log.info("updateFormEmp Start...");
		
		Emp emp = es.detailEmp(empno);
		
		// 문제
		// 1. DTO String hiredate
		// 2. view : 단순조회 OK,JSP에서 input type="date" 문제 발생
		// 3. 해결책 : 년월일을 짤라 넣어 주어야함
		String hiredate = emp.getHiredate().substring(0, 10);
		emp.setHiredate(hiredate);
		log.info("hiredate->{}",hiredate);
		
		model.addAttribute("emp", emp);
		
		return "updateFormEmp";
	}
}

📂 views

  • updateFormEmp.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript" src="js/jquery.js"></script>
<script type="text/javascript">
  	/* 기본일자설정 */
	function inData() {
		alert("inDate Start...");
		today =  new Date();
		// 현재년도로부터 3개월 전
		var yyyy = today.getFullYear() - 3;
		currentFirst = yyyy+'-01-01';
		console.log("currentFirst->"+ currentFirst);
		hiredate = document.getElementById("hiredate");
		hiredate.value = currentFirst;
	}
</script>
</head>
<body>
	<h2>직원정보</h2>
	<form action="updateEmp" method="post">
		<input type="hidden" name="empno" value="${emp.empno}">
		<table>
			<tr><th>사번</th><td>${emp.empno}<td></tr>
			<tr><th>이름</th><td>
				<input type="text" name="ename" required="required" value="${emp.ename}"><td></tr>
			<tr><th>업무</th><td>
				<input type="text" name="job" required="required" value="${emp.job}"><td></tr>
			<tr><th>급여</th><td>
				<input type="number" name="sal" required="required" value="${emp.sal}"><td></tr>
			<tr><th>입사일</th><td>
				<input type="date" name="hiredate" id="hiredate" value="${emp.hiredate}">
				<input type="button" value="기본일자확인" onclick="inData()"><td></tr>
			<tr><th>보너스</th><td>
				<input type="number" name="comm" required="required" value="${emp.comm}"><td></tr>
			<tr><th>관리자사번</th><td>
				<input type="number" name="mgr" value="${emp.mgr}"><td></tr>
			<tr><th>부서코드</th><td>
				<input type="number" name="deptno" required="required" value="${emp.deptno}"><td></tr>
			<tr><td colspan="2">
				<input type="submit" value="확인">
				<td>
			</tr>
		</table>
	</form>
</body>
</html>



✍회원정보수정

[com.oracle.oBootMybatis01.controller]

  • EmpController.java
package com.oracle.oBootMybatis01.controller;

import java.util.List;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import com.oracle.oBootMybatis01.model.Emp;
import com.oracle.oBootMybatis01.service.EmpService;
import com.oracle.oBootMybatis01.service.Paging;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Controller
@Slf4j
@RequiredArgsConstructor
public class EmpController {
	
	private final EmpService  es;
	
	@RequestMapping(value = "listEmp")
	public String empList(Emp emp, String currentPage, Model model) {
		log.info("empList Start...");
		// Emp 전체 count 25
		int totalEmp = es.totalEmp();
		log.info("empList total=>{}", totalEmp);
		
		// Paging 작업
		Paging page = new Paging(totalEmp, currentPage);
		// Parameter emp --> page만 추가 Setting(페이징)
		emp.setStart(page.getStart()); // 시작시 1
		emp.setEnd(page.getEnd());	   // 시작시 10
		
		List<Emp> listEmp = es.listEmp(emp);
		log.info("listEmp.size()->{}",listEmp.size());
		
		model.addAttribute("totalEmp", totalEmp);
		model.addAttribute("listEmp", listEmp);
		model.addAttribute("page", page);
		return "list";
	}
	
	// 회원 상세 정보
	@GetMapping(value = "detailEmp")
	public String detailEmp(int empno, Model model) {
		log.info("detailEmp Start...");
		Emp emp = es.detailEmp(empno);
		log.info("emp->{}", emp);
		model.addAttribute("emp", emp);
		return "detailEmp";
	}
	
	// 회원 정보 수정
	@GetMapping(value = "updateFormEmp")
	public String updateFormEmp(int empno, Model model) {
		log.info("updateFormEmp Start...");
		
		Emp emp = es.detailEmp(empno);
		
		// 문제
		// 1. DTO String hiredate
		// 2. view : 단순조회 OK,JSP에서 input type="date" 문제 발생
		// 3. 해결책 : 년월일을 짤라 넣어 주어야함
		String hiredate = emp.getHiredate().substring(0, 10);
		emp.setHiredate(hiredate);
		log.info("hiredate->{}",hiredate);
		
		model.addAttribute("emp", emp);
		
		return "updateFormEmp";
	}
	
	// 회원 정보 수정하기
	@PostMapping(value = "updateEmp")
	public String updateForm(Emp emp, Model model) {
		log.info("updateEmp Start...");
		
//      1. EmpService안에 updateEmp method 선언
//      1) parameter : Emp
//      2) Return      updateCount (int)
//
//   	2. EmpDao updateEmp method 선언
//                             	 		mapper ID   ,    Parameter
//   	updateCount = session.update("TKempUpdate",emp);

		int updateCount = es.updateEmp(emp);
		log.info("updateCount->{}",updateCount);
		model.addAttribute("updateCount", updateCount);
		model.addAttribute("kk3", "Message Test");
		
		return "redirect:listEmp";
	}
}

[com.oracle.oBootMybatis01.service]

  • EmpServiceImpl.java
package com.oracle.oBootMybatis01.service;

import java.util.List;

import org.springframework.stereotype.Service;

import com.oracle.oBootMybatis01.dao.EmpDao;
import com.oracle.oBootMybatis01.model.Emp;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Service
@Slf4j
@RequiredArgsConstructor
public class EmpServiceImpl implements EmpService {
	
	private final EmpDao ed;
	
	@Override
	public int totalEmp() {
		log.info("totalEmp Start...");
		int totEmpCnt = ed.totalEmp();
		log.info("totalEmp totEmpCnt=>{}",totEmpCnt);
		return totEmpCnt;
	}

	@Override
	public List<Emp> listEmp(Emp emp) {
		List<Emp> empList = null;
		log.info("listEmp Start...");
		empList = ed.listEmp(emp);
		log.info("listEmp empList.size()->{}",empList.size());
		return empList;
	}

	@Override
	public Emp detailEmp(int empno) {
		log.info("detailEmp Start...");
		Emp emp = null;
		emp = ed.detailEmp(empno);
		log.info("emp->{}", emp);
		return emp;
	}

	@Override
	public int updateEmp(Emp emp) {
		log.info("updateEmp Start...");
		int updateCount = 0;
		updateCount = ed.updateEmp(emp);
		return updateCount;
		
	}
}

[com.oracle.oBootMybatis01.dao]

  • EmpDaoImpl.java
package com.oracle.oBootMybatis01.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.springframework.stereotype.Repository;

import com.oracle.oBootMybatis01.model.Emp;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Repository
@Slf4j
@RequiredArgsConstructor
public class EmpDaoImpl implements EmpDao {
	
	// Mybatis DB 연결 -> JPA EntityManager
	private final SqlSession session;

	@Override
	public int totalEmp() {
		int totEmpCount = 0;
		log.info("totalEmp Start...");
		
		try {
			totEmpCount = session.selectOne("empTotal");
			log.info("totalEmp totEmpCount->{}", totEmpCount);
		} catch (Exception e) {
			log.info("totalEmp Exception->{}",e.getMessage());
		}
		return totEmpCount;
	}

	@Override
	public List<Emp> listEmp(Emp emp) {
		List<Emp> empList = null;
		log.info("listEmp Start...");
		//								Map ID		parameter
		try {
			empList = session.selectList("tkEmpListAll", emp);
			log.info("empList.size()->{}",empList.size());
		} catch (Exception e) {
			log.info("empList Exception->{}",e.getMessage());
		}
		return empList;
	}

	@Override
	public Emp detailEmp(int empno) {
		log.info("detailEmp Start...");
		Emp emp = new Emp();
		
		try {
			emp = session.selectOne("tkEmpSelOne", empno);
			log.info("emp.getEname()->{}",emp.getEname());
		} catch (Exception e) {
			log.info("detailEmp Exception->{}",e.getMessage());
		}
		return emp;
	}

	@Override
	public int updateEmp(Emp emp) {
		log.info("updateEmp Start...");
		int updateCount = 0;
		
		try {
			updateCount = session.update("tkEmpUpdate", emp);
		} catch (Exception e) {
			log.info("updateEmp Exception->{}",e.getMessage());
		}
		return updateCount;
	}

}

📂 mapper

  • Emp.xml
<?xml version="1.0" encoding="UTF-8" ?>

<!-- ======= mapper 기본설정 ======= -->
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!-- ==== 루트 엘리먼트 & 네임스페이스 설정(프로젝트 전체 내에서 유일해야 한다.) ==== -->
<mapper namespace="com.oracle.oBootMybatis01.EmpMapper">
	<select id="empTotal" resultType="int">
		SELECT Count(*) FROM emp
	</select>
	
	<select id="tkEmpListAll" parameterType="Emp" resultType="Emp">
		select *
		from 
		(
		        select rownum rn, a.*
		        from
		        (select * from emp order by empno) a
		)
		where rn BETWEEN #{start} and #{end}
	</select>
	
	<!-- 회원상세보기 -->
	<select id="tkEmpSelOne" parameterType="int" resultType="Emp">
		select * from emp where empno = #{empno}
	</select>
	
	<!-- 회원정보수정 -->
	<update id="tkEmpUpdate" parameterType="Emp">
		update emp set
					ename = #{ename},
					job   = #{job},
					sal   = #{sal},
					hiredate = #{hiredate},
					<if test="comm != null">comm = #{comm},</if>
					mgr = #{mgr},
					deptno = #{deptno}
		where empno = #{empno}
	</update>
</mapper>


❌오류보고서

hiredate가 입력이 안된 회원을 수정을 했을 때 오류 페이지 출력
-> 그래서 if문을 걸어줘서 해결

  • EmpController.java
package com.oracle.oBootMybatis01.controller;

import java.util.List;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import com.oracle.oBootMybatis01.model.Emp;
import com.oracle.oBootMybatis01.service.EmpService;
import com.oracle.oBootMybatis01.service.Paging;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Controller
@Slf4j
@RequiredArgsConstructor
public class EmpController {
	
	private final EmpService  es;
	
	@RequestMapping(value = "listEmp")
	public String empList(Emp emp, String currentPage, Model model) {
		log.info("empList Start...");
		// Emp 전체 count 25
		int totalEmp = es.totalEmp();
		log.info("empList total=>{}", totalEmp);
		
		// Paging 작업
		Paging page = new Paging(totalEmp, currentPage);
		// Parameter emp --> page만 추가 Setting(페이징)
		emp.setStart(page.getStart()); // 시작시 1
		emp.setEnd(page.getEnd());	   // 시작시 10
		
		List<Emp> listEmp = es.listEmp(emp);
		log.info("listEmp.size()->{}",listEmp.size());
		
		model.addAttribute("totalEmp", totalEmp);
		model.addAttribute("listEmp", listEmp);
		model.addAttribute("page", page);
		return "list";
	}
	
	// 회원 상세 정보
	@GetMapping(value = "detailEmp")
	public String detailEmp(int empno, Model model) {
		log.info("detailEmp Start...");
		Emp emp = es.detailEmp(empno);
		log.info("emp->{}", emp);
		model.addAttribute("emp", emp);
		return "detailEmp";
	}
	
	// 회원 정보 수정
	@GetMapping(value = "updateFormEmp")
	public String updateFormEmp(int empno, Model model) {
		log.info("updateFormEmp Start...");
		
		Emp emp = es.detailEmp(empno);
		
		// 문제
		// 1. DTO String hiredate
		// 2. view : 단순조회 OK,JSP에서 input type="date" 문제 발생
		// 3. 해결책 : 년월일을 짤라 넣어 주어야함
		String hiredate = "";
		if(emp.getHiredate() != null) {
			hiredate = emp.getHiredate().substring(0, 10);
			emp.setHiredate(hiredate);
		}
		log.info("hiredate->{}",hiredate);
		
		model.addAttribute("emp", emp);
		
		return "updateFormEmp";
	}
	
	// 회원 정보 수정하기
	@PostMapping(value = "updateEmp")
	public String updateForm(Emp emp, Model model) {
		log.info("updateEmp Start...");
		
//      1. EmpService안에 updateEmp method 선언
//      1) parameter : Emp
//      2) Return      updateCount (int)
//
//   	2. EmpDao updateEmp method 선언
//                             	 		mapper ID   ,    Parameter
//   	updateCount = session.update("TKempUpdate",emp);

		int updateCount = es.updateEmp(emp);
		log.info("updateCount->{}",updateCount);
		model.addAttribute("updateCnt", updateCount);
		model.addAttribute("kk3", "Message Test");
		
		return "redirect:listEmp";
	}
}

🧐redirect vs forword

forward 방식 -> 요청 정보 그대로 유지(변화 X, 재사용 O)
현재 실행중인 페이지와 forward에 의해 호출될 페이지는 request, response 객체를 공유
-> 시스템에 변화가 생기지 않는 단순조회(리스트보기, 검색) 바람직

Redirect 방식 -> 새로운 요청 수행(변화 O, 재사용 X)
새로운 페이지에서 request, response 객체를 새롭게 생성
-> 시스템(session, DB)에 변화가 생기는 요청(로그인, 회원가입, 글쓰기)의 경우 바람직


  • EmpController.java
package com.oracle.oBootMybatis01.controller;

import java.util.List;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import com.oracle.oBootMybatis01.model.Emp;
import com.oracle.oBootMybatis01.service.EmpService;
import com.oracle.oBootMybatis01.service.Paging;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Controller
@Slf4j
@RequiredArgsConstructor
public class EmpController {
	
	private final EmpService  es;
	
	@RequestMapping(value = "listEmp")
	public String empList(Emp emp, String currentPage, Model model) {
		log.info("empList Start...");
		// Emp 전체 count 25
		int totalEmp = es.totalEmp();
		log.info("empList total=>{}", totalEmp);
		
		// Paging 작업
		Paging page = new Paging(totalEmp, currentPage);
		// Parameter emp --> page만 추가 Setting(페이징)
		emp.setStart(page.getStart()); // 시작시 1
		emp.setEnd(page.getEnd());	   // 시작시 10
		
		List<Emp> listEmp = es.listEmp(emp);
		log.info("listEmp.size()->{}",listEmp.size());
		
		model.addAttribute("totalEmp", totalEmp);
		model.addAttribute("listEmp", listEmp);
		model.addAttribute("page", page);
		return "list";
	}
	
	// 회원 상세 정보
	@GetMapping(value = "detailEmp")
	public String detailEmp(int empno, Model model) {
		log.info("detailEmp Start...");
		Emp emp = es.detailEmp(empno);
		log.info("emp->{}", emp);
		model.addAttribute("emp", emp);
		return "detailEmp";
	}
	
	// 회원 정보 수정
	@GetMapping(value = "updateFormEmp")
	public String updateFormEmp(int empno, Model model) {
		log.info("updateFormEmp Start...");
		
		Emp emp = es.detailEmp(empno);
		
		// 문제
		// 1. DTO String hiredate
		// 2. view : 단순조회 OK,JSP에서 input type="date" 문제 발생
		// 3. 해결책 : 년월일을 짤라 넣어 주어야함
		String hiredate = "";
		if(emp.getHiredate() != null) {
			hiredate = emp.getHiredate().substring(0, 10);
			emp.setHiredate(hiredate);
		}
		log.info("hiredate->{}",hiredate);
		
		model.addAttribute("emp", emp);
		
		return "updateFormEmp";
	}
	
	// 회원 정보 수정하기
	@PostMapping(value = "updateEmp")
	public String updateForm(Emp emp, Model model) {
		log.info("updateEmp Start...");
		
//      1. EmpService안에 updateEmp method 선언
//      1) parameter : Emp
//      2) Return      updateCount (int)
//
//   	2. EmpDao updateEmp method 선언
//                             	 		mapper ID   ,    Parameter
//   	updateCount = session.update("TKempUpdate",emp);

		int updateCount = es.updateEmp(emp);
		log.info("updateCount->{}",updateCount);
		model.addAttribute("uptCnt", updateCount);
		model.addAttribute("kk3", "Message Test");
		
		return "forward:listEmp";
		//return "redirect:listEmp";
	}
}

📂 views

  • list.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>회원관리</h1>
	<a href="writeFormEmp">입력</a>
	<h5>사원 수 : ${totalEmp}</h5>
	<p>kk3 수정시 전달 Message : ${kk3}</p>
	<p>uptCnt 수정시 전달 Message : ${uptCnt}</p>
	
	<!-- Maxium -->
	<c:set var="num" value="${page.total-page.start+1}"></c:set> 
	<table>
		<tr>
			<th>번호</th>
			<th>사번</th>
			<th>이름</th>
			<th>업무</th>
			<th>급여</th>
		</tr>
		<c:forEach var="emp" items="${listEmp}">
			<tr>
				<td>${num}</td>
				<td>${emp.empno}</td>
				<td><a href="detailEmp?empno=${emp.empno}">${emp.ename}</a></td>
				<td>${emp.job}</td>
				<td>${emp.sal}</td>
			</tr>
			<c:set var="num" value="${num - 1}"></c:set>
		</c:forEach>
	</table>
	
	<!-- 페이징 번호 설정 -->
	<c:if test="${page.startPage > page.pageBlock }">
		<a href="listEmp?currentPage=${page.startPage-page.pageBlock}">이전</a>
	</c:if>
	<c:forEach var="i" begin="${page.startPage}" end="${page.endPage}">
		<a href="listEmp?currentPage=${i}">[${i}]</a>
	</c:forEach>
	<c:if test="${page.endPage < page.totalPage}">
		<a href="listEmp?currentPage=${page.startPage+page.pageBlock}">[다음]</a>
	</c:if>
</body>
</html>



직원정보입력창

🤔과제라 정답은 아님

목표
1. 관리자인 사람 리스트 가져오기
2. 부서리스트 가져오기


📂 views

  • writeFormEmp.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h2>직원정보 입력</h2>
	<c:if test="${msg!=null}">${msg}</c:if>
	<form action="write" method="post" name="frm">
		<table>
			<tr><th>사번</th><td>
				<input type="number" name="empno" required="required" maxlength="4" value="${empno }">
				<input type="button" value="중복확인" onclick="chk()"></td></tr>
			<tr><th>이름</th><td>	<input type="text" name="ename" required="required"></td></tr>
			<tr><th>업무</th><td><input type="text" name="job" required="required"></td></tr>
			<tr><th>급여</th><td>	<input type="number" name="sal" required="required"></td></tr>
			<tr><th>입사일</th><td><input type="date" name="hiredater" required="required"></td></tr>
			<tr><th>보너스</th><td><input type="number" name="comm" required="required"></td></tr>
			<tr><th>관리자사번</th><td>
				<select name="mgr">
					<c:forEach var="emp" items="${empMngList}">
						<option value="${emp.empno}">${emp.ename}</option>
					</c:forEach>
				</select></td>
			</tr>
			<tr><th>부서코드</th><td>
				<select name="deptno">
					<c:forEach var="dept" items="${deptList}">
						<option value="${dept.deptno}">${dept.dname}</option>
					</c:forEach>
				</select></td>
			</tr>
			<tr><td colspan="2"><input type="submit" value="확인"></td></tr>
		</table>
	</form>
</body>
</html>

[com.oracle.oBootMybatis01.controller]

  • EmpController.java
package com.oracle.oBootMybatis01.controller;

import java.util.List;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import com.oracle.oBootMybatis01.model.Dept;
import com.oracle.oBootMybatis01.model.Emp;
import com.oracle.oBootMybatis01.service.EmpService;
import com.oracle.oBootMybatis01.service.Paging;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Controller
@Slf4j
@RequiredArgsConstructor
public class EmpController {
	
	private final EmpService  es;
	
	@RequestMapping(value = "listEmp")
	public String empList(Emp emp, String currentPage, Model model) {
		log.info("empList Start...");
		// Emp 전체 count 25
		int totalEmp = es.totalEmp();
		log.info("empList total=>{}", totalEmp);
		
		// Paging 작업
		Paging page = new Paging(totalEmp, currentPage);
		// Parameter emp --> page만 추가 Setting(페이징)
		emp.setStart(page.getStart()); // 시작시 1
		emp.setEnd(page.getEnd());	   // 시작시 10
		
		List<Emp> listEmp = es.listEmp(emp);
		log.info("listEmp.size()->{}",listEmp.size());
		
		model.addAttribute("totalEmp", totalEmp);
		model.addAttribute("listEmp", listEmp);
		model.addAttribute("page", page);
		return "list";
	}
	
	// 회원 상세 정보
	@GetMapping(value = "detailEmp")
	public String detailEmp(int empno, Model model) {
		log.info("detailEmp Start...");
		Emp emp = es.detailEmp(empno);
		log.info("emp->{}", emp);
		model.addAttribute("emp", emp);
		return "detailEmp";
	}
	
	// 회원 정보 수정
	@GetMapping(value = "updateFormEmp")
	public String updateFormEmp(int empno, Model model) {
		log.info("updateFormEmp Start...");
		
		Emp emp = es.detailEmp(empno);
		
		// 문제
		// 1. DTO String hiredate
		// 2. view : 단순조회 OK,JSP에서 input type="date" 문제 발생
		// 3. 해결책 : 년월일을 짤라 넣어 주어야함
		String hiredate = "";
		if(emp.getHiredate() != null) {
			hiredate = emp.getHiredate().substring(0, 10);
			emp.setHiredate(hiredate);
		}
		log.info("hiredate->{}",hiredate);
		
		model.addAttribute("emp", emp);
		
		return "updateFormEmp";
	}
	
	// 회원 정보 수정하기
	@PostMapping(value = "updateEmp")
	public String updateForm(Emp emp, Model model) {
		log.info("updateEmp Start...");
		
//      1. EmpService안에 updateEmp method 선언
//      1) parameter : Emp
//      2) Return      updateCount (int)
//
//   	2. EmpDao updateEmp method 선언
//                             	 		mapper ID   ,    Parameter
//   	updateCount = session.update("TKempUpdate",emp);

		int updateCount = es.updateEmp(emp);
		log.info("updateCount->{}",updateCount);
		model.addAttribute("uptCnt", updateCount);
		model.addAttribute("kk3", "Message Test");
		
		return "forward:listEmp";
		//return "redirect:listEmp";
	}
	
	// 회원 가입
	@RequestMapping(value = "writeFormEmp")
	public String writeForm(Model model) {
		log.info("writeForm Start...");
		// 관지자 사번 만 Get
		List<Emp> empList = es.listManager();
		
		// 부서(코드, 부서명)
		List<Dept> deptList = es.deptSelect();
		
		model.addAttribute("empMngList", empList);
		model.addAttribute("deptList", deptList);
		return "writeFormEmp";
	}
}

[com.oracle.oBootMybatis01.service]

  • EmpServiceImpl.java
package com.oracle.oBootMybatis01.service;

import java.util.List;

import org.springframework.stereotype.Service;

import com.oracle.oBootMybatis01.dao.DeptDao;
import com.oracle.oBootMybatis01.dao.EmpDao;
import com.oracle.oBootMybatis01.model.Dept;
import com.oracle.oBootMybatis01.model.Emp;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Service
@Slf4j
@RequiredArgsConstructor
public class EmpServiceImpl implements EmpService {
	
	private final EmpDao ed;
	private final DeptDao dd;
	
	@Override
	public int totalEmp() {
		log.info("totalEmp Start...");
		int totEmpCnt = ed.totalEmp();
		log.info("totalEmp totEmpCnt=>{}",totEmpCnt);
		return totEmpCnt;
	}

	@Override
	public List<Emp> listEmp(Emp emp) {
		List<Emp> empList = null;
		log.info("listEmp Start...");
		empList = ed.listEmp(emp);
		log.info("listEmp empList.size()->{}",empList.size());
		return empList;
	}

	@Override
	public Emp detailEmp(int empno) {
		log.info("detailEmp Start...");
		Emp emp = null;
		emp = ed.detailEmp(empno);
		log.info("emp->{}", emp);
		return emp;
	}

	@Override
	public int updateEmp(Emp emp) {
		log.info("updateEmp Start...");
		int updateCount = 0;
		updateCount = ed.updateEmp(emp);
		return updateCount;
		
	}
	
	// emp->관리자
	@Override
	public List<Emp> listManager() {
		log.info("listManager Start...");
		
		List<Emp> listManager = null;
		listManager = ed.listManager(); 
		
		log.info("listManager.size()->{}",listManager.size());
		// ed.listManager();
		return listManager;
	}
	
	// dept->다 가져옴
	@Override
	public List<Dept> deptSelect() {
		log.info("deptSelect Start...");
		
		List<Dept> deptList = null;
		deptList = dd.deptSelect();
		
		log.info("deptList.size()->{}",deptList.size());
		// deptList =  dd.deptSelect();
		return deptList;
	}
}

[com.oracle.oBootMybatis01.dao]

  • EmpDaoImpl.java
package com.oracle.oBootMybatis01.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.springframework.stereotype.Repository;

import com.oracle.oBootMybatis01.model.Emp;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Repository
@Slf4j
@RequiredArgsConstructor
public class EmpDaoImpl implements EmpDao {
	
	// Mybatis DB 연결 -> JPA EntityManager
	private final SqlSession session;

	@Override
	public int totalEmp() {
		int totEmpCount = 0;
		log.info("totalEmp Start...");
		
		try {
			totEmpCount = session.selectOne("empTotal");
			log.info("totalEmp totEmpCount->{}", totEmpCount);
		} catch (Exception e) {
			log.info("totalEmp Exception->{}",e.getMessage());
		}
		return totEmpCount;
	}

	@Override
	public List<Emp> listEmp(Emp emp) {
		List<Emp> empList = null;
		log.info("listEmp Start...");
		//								Map ID		parameter
		try {
			empList = session.selectList("tkEmpListAll", emp);
			log.info("empList.size()->{}",empList.size());
		} catch (Exception e) {
			log.info("empList Exception->{}",e.getMessage());
		}
		return empList;
	}

	@Override
	public Emp detailEmp(int empno) {
		log.info("detailEmp Start...");
		Emp emp = new Emp();
		
		try {
			emp = session.selectOne("tkEmpSelOne", empno);
			log.info("emp.getEname()->{}",emp.getEname());
		} catch (Exception e) {
			log.info("detailEmp Exception->{}",e.getMessage());
		}
		return emp;
	}

	@Override
	public int updateEmp(Emp emp) {
		log.info("updateEmp Start...");
		int updateCount = 0;
		
		try {
			updateCount = session.update("tkEmpUpdate", emp);
		} catch (Exception e) {
			log.info("updateEmp Exception->{}",e.getMessage());
		}
		return updateCount;
	}

	@Override
	public List<Emp> listManager() {
		log.info("listManager Start...");
		List<Emp> empList = null;
		
		try {
			empList = session.selectList("tkSelectManager");
			// emp 관리자만 Select           Naming Rule 
			// empList = session.selectList("tkSelectManager");
		} catch (Exception e) {
			log.info("listManager Exception->{}",e.getMessage());
		}
		return empList;
	}

}

📂 mapper

  • Emp.xml
<?xml version="1.0" encoding="UTF-8" ?>

<!-- ======= mapper 기본설정 ======= -->
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!-- ==== 루트 엘리먼트 & 네임스페이스 설정(프로젝트 전체 내에서 유일해야 한다.) ==== -->
<mapper namespace="com.oracle.oBootMybatis01.EmpMapper">
	<select id="empTotal" resultType="int">
		SELECT Count(*) FROM emp
	</select>
	
	<select id="tkEmpListAll" parameterType="Emp" resultType="Emp">
		select *
		from 
		(
		        select rownum rn, a.*
		        from
		        (select * from emp order by empno) a
		)
		where rn BETWEEN #{start} and #{end}
	</select>
	
	<!-- 회원상세보기 -->
	<select id="tkEmpSelOne" parameterType="int" resultType="Emp">
		select * from emp where empno = #{empno}
	</select>
	
	<!-- 회원정보수정 -->
	<update id="tkEmpUpdate" parameterType="Emp">
		update emp set
					ename = #{ename},
					job   = #{job},
					sal   = #{sal},
					hiredate = #{hiredate},
					<if test="#{comm} != null"> comm = #{comm}, </if>
					mgr = #{mgr},
					deptno = #{deptno}
		where empno = #{empno}
	</update>
	
	<!-- 관리자 리스트 불러오기 -->
	<select id="tkSelectManager" parameterType="Emp" resultType="Emp">
		select *
		from emp
		where empno in(select mgr from emp)
	</select>
</mapper>


⭐Emp SQL을 살펴보자

● 다중행 서브쿼리

  • 서브쿼리에서 반환되는 결과 행이 하나 이상일 때 사용하는 서브쿼리
  • 메인쿼리의 WHERE절에서 서브쿼리의 결과와 비교할 경우에는 다중 행 비교 연산자를 사용하여 비교
  • 다중 행 비교 연산자 : IN, ANY, SOME, ALL, EXISTS
    1) IN : 메인 쿼리의 비교 조건이 서브쿼리의 결과중에서 하나라도 일치하면 참, ‘=‘비교만 가능
    2) ANY, SOME : 메인 쿼리의 비교 조건이 서브쿼리의 결과중에서 하나라도 일치하면 참
    3) ALL : 메인 쿼리의 비교 조건이 서브쿼리의 결과중에서 모든값이 일치하면 참
    4) EXISTS : 메인 쿼리의 비교 조건이 서브쿼리의 결과중에서 만족하는 값이 하나라도 존재하면 참
select *
from emp
where empno in(select mgr from emp);


emp 테이블의 관리자번호를 출력

select mgr from emp;

따라서 먼저 emp 테이블의 관리자번호를 출력 후 전체 empno에서 관리자 번호와 일치하는 것을 출력



[com.oracle.oBootMybatis01.dao]

  • DeptDao.java( interface )
package com.oracle.oBootMybatis01.dao;

import java.util.List;

import com.oracle.oBootMybatis01.model.Dept;

public interface DeptDao {
	List<Dept> deptSelect();
}

[com.oracle.oBootMybatis01.dao]

  • DeptDaoImpl.java( + interface )
package com.oracle.oBootMybatis01.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.springframework.stereotype.Repository;

import com.oracle.oBootMybatis01.model.Dept;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Repository
@Slf4j
@RequiredArgsConstructor
public class DeptDaoImpl implements DeptDao {
	
	private final SqlSession session;

	@Override
	public List<Dept> deptSelect() {
		log.info("deptSelect Start...");
		List<Dept> deptList = null;
		
		try {
			deptList = session.selectList("tkSelectDept");
			// deptList =  session.selectList("tkSelectDept");
		} catch (Exception e) {
			log.info("deptSelect Exception->{}",e.getMessage());
		}
		
		return deptList;
	}

}

📂 mapper

  • Dept.xml
<?xml version="1.0" encoding="UTF-8"?>

<!-- ======= mapper 기본설정 ======= -->
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!-- ==== 루트 엘리먼트 & 네임스페이스 설정(프로젝트 전체 내에서 유일해야 한다.) ==== -->
<mapper namespace="com.oracle.oBootMybatis01.DeptMapper">
	<select id="tkSelectDept" parameterType="Dept" resultType="Dept">
		select * from dept
	</select>
</mapper>

profile
써니
이전 포스트

국비학원_75일차(Api, myBatis)

다음 포스트

국비학원_77일차(myBatis3, SMTP 메일)

0개의 댓글