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

써니·2022년 11월 10일

MyBatis Spring

spring

목록 보기

19/23

💾직원정보저장

🔻oBootMybatis01

[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";
	}
	
	// 직원 정보 저장
	@PostMapping(value = "writeEmp")
	public String writeEmp(Emp emp, Model model) {
		log.info("writeEmp Start...");
		
		// Service, Dao , Mapper명[insertEmp] 까지 -> insert
		int result = es.insertEmp(emp);
		
		if(result > 0) {
			return "redirect:listEmp";
		} 
		else {
			model.addAttribute("msg", "입력 실패 확인해 보세요");
			return "forward:writeFormEmp";
		}
	}
	
	// 중복체크
	@GetMapping(value = "confirm")
	public String confirm(int empno, Model model) {
		Emp emp = es.detailEmp(empno);
		model.addAttribute("empno", empno);
		if(emp != null) {
			model.addAttribute("msg", "중복된 사번입니다");
			return "forward:writeForm";
		} else {
			model.addAttribute("msg", "사용 가능한 사번입니다");
			return "forward:writeForm";
		}
	}
}

[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;
	}

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

[com.oracle.oBootMybatis01.dao]

DeptDaoImpl.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.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

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>
	
	<!-- 직원 정보 저장 -->
	<insert id="insertEmp" parameterType="Emp">
		insert into emp values(#{empno}, #{ename}, #{job}, #{mgr}, #{hiredate}, #{sal}, #{comm}, #{deptno})
	</insert>
</mapper>

📂 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>
<script type="text/javascript">
	function chk() {
		if(!frm.empno.value){
			alert("사번을 입력한 후에 확인하세요");
			fmr.empno.focus();
			return false;
		} else location.href="confirm?empno="+frm.empno.value;
	}
</script>
<body>
	<h2>직원정보 입력</h2>
	<c:if test="${msg!=null}">${msg}</c:if>
	<form action="writeEmp" 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="hiredate" 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";
	}
	
	// 직원 정보 저장
	@PostMapping(value = "writeEmp")
	public String writeEmp(Emp emp, Model model) {
		log.info("writeEmp Start...");
		
		// Service, Dao , Mapper명[insertEmp] 까지 -> insert
		int result = es.insertEmp(emp);
		
		if(result > 0) {
			return "redirect:listEmp";
		} 
		else {
			model.addAttribute("msg", "입력 실패 확인해 보세요");
			return "forward:writeFormEmp";
		}
	}
	
	// 중복체크
	@GetMapping(value = "confirm")
	public String confirm(int empno, Model model) {
		log.info("confirm Start...");
		Emp emp = es.detailEmp(empno);
		model.addAttribute("empno", empno);
		if(emp != null) {
			log.info("confirm 중복된 사번");
			model.addAttribute("msg", "중복된 사번입니다");
			return "forward:writeFormEmp";
		} else {
			log.info("confirm 사용 가능한 사번");
			model.addAttribute("msg", "사용 가능한 사번입니다");
			return "forward:writeFormEmp";
		}
	}
}

직원삭제

🔻oBootMybatis01

[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";
	}
	
	// 직원 정보 저장
	@PostMapping(value = "writeEmp")
	public String writeEmp(Emp emp, Model model) {
		log.info("writeEmp Start...");
		
		// Service, Dao , Mapper명[insertEmp] 까지 -> insert
		int result = es.insertEmp(emp);
		
		if(result > 0) {
			return "redirect:listEmp";
		} 
		else {
			model.addAttribute("msg", "입력 실패 확인해 보세요");
			return "forward:writeFormEmp";
		}
	}
	
	// 중복체크
	@GetMapping(value = "confirm")
	public String confirm(int empno, Model model) {
		log.info("confirm Start...");
		Emp emp = es.detailEmp(empno);
		model.addAttribute("empno", empno);
		if(emp != null) {
			log.info("confirm 중복된 사번");
			model.addAttribute("msg", "중복된 사번입니다");
			return "forward:writeFormEmp";
		} else {
			log.info("confirm 사용 가능한 사번");
			model.addAttribute("msg", "사용 가능한 사번입니다");
			return "forward:writeFormEmp";
		}
	}
	
	// 직원삭제
	@GetMapping(value = "deleteEmp")
	public String deleteEmp(int empno, Model model) {
		log.info("deleteEmp Start...");
		int result = 0;
		result = es.deleteEmp(empno);
		
		if(result > 0) {
			return "redirect:listEmp";
		} 
		else {
			model.addAttribute("msg", "삭제 실패 확인해 보세요");
			return "forward:datailEmp";
		}
	}
}

[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;
	}

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

	@Override
	public int deleteEmp(int empno) {
		log.info("deleteEmp Start...");
		int result = 0;
		result = ed.deleteEmp(empno);
		return result;
	}
}

[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;
	}

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

	@Override
	public int deleteEmp(int empno) {
		log.info("deleteEmp Start...");
		int result = 0;
		
		try {
			result = session.delete("deleteEmp", empno);
		} catch (Exception e) {
			log.info("deleteEmp Exception->{}",e.getMessage());
		}
		return result;
	}

}

📂 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>
	
	<!-- 직원 정보 저장 -->
	<insert id="insertEmp" parameterType="Emp">
		insert into emp values(#{empno}, #{ename}, #{job}, #{mgr}, #{hiredate}, #{sal}, #{comm}, #{deptno})
	</insert>
	
	<!-- 직원 삭제 -->
	<delete id="deleteEmp" parameterType="int">
		delete from emp
		where empno = #{empno}
	</delete>
</mapper>

직원부서조회(JOIN)

🔻oBootMybatis01

📂 static

index.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>회원관리</h1>
	<a href="/writeFormEmp">회원 가입</a><p>
	<a href="/listEmp">회원 목록</a><p>
	<a href="/listEmpDept">직원부서조회</a><p>
</body>
</html>

📂 views

listEmpDept.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>
	<a href="mailTransport">Mail Test</a>
	
	<table>
		<tr><th>사번</th><th>이름</th><th>업무</th><th>부서</th><th>근무지</th></tr>
		<c:forEach var="empDept" items="${listEmpDept}">
			<tr><td>${empDept.empno}</td><td>${empDept.ename}</td>
				<td>${empDept.job}</td><td>${empDept.deptno}</td>
				<td>${empDept.loc}</td></tr>
		</c:forEach>
	</table>
</body>
</html>

📂 src/main/resources

configuration.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
	<typeAliases>
		<typeAlias alias="Emp" type="com.oracle.oBootMybatis01.model.Emp"/>
		<typeAlias alias="Dept" type="com.oracle.oBootMybatis01.model.Dept" />
		<typeAlias alias="EmpDept" type="com.oracle.oBootMybatis01.model.EmpDept" />
	</typeAliases>
</configuration>

[com.oracle.oBootMybatis01.model]

EmpDept.java

package com.oracle.oBootMybatis01.model;

import lombok.Getter;
import lombok.Setter;

// Join 목적
@Getter
@Setter
public class EmpDept {
	// Emp
	private int empno;			private String ename;
	private String job;			private int mgr;
	private String hiredate; 	private int sal;
	private int comm;			private int deptno;
	
	// Dept
	private String dname;
	private String loc;
}

[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.model.EmpDept;
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";
	}
	
	// 직원 정보 저장
	@PostMapping(value = "writeEmp")
	public String writeEmp(Emp emp, Model model) {
		log.info("writeEmp Start...");
		
		// Service, Dao , Mapper명[insertEmp] 까지 -> insert
		int result = es.insertEmp(emp);
		
		if(result > 0) {
			return "redirect:listEmp";
		} 
		else {
			model.addAttribute("msg", "입력 실패 확인해 보세요");
			return "forward:writeFormEmp";
		}
	}
	
	// 중복체크
	@GetMapping(value = "confirm")
	public String confirm(int empno, Model model) {
		log.info("confirm Start...");
		Emp emp = es.detailEmp(empno);
		model.addAttribute("empno", empno);
		if(emp != null) {
			log.info("confirm 중복된 사번");
			model.addAttribute("msg", "중복된 사번입니다");
			return "forward:writeFormEmp";
		} else {
			log.info("confirm 사용 가능한 사번");
			model.addAttribute("msg", "사용 가능한 사번입니다");
			return "forward:writeFormEmp";
		}
	}
	
	// 직원삭제
	@GetMapping(value = "deleteEmp")
	public String deleteEmp(int empno, Model model) {
		log.info("deleteEmp Start...");
		int result = 0;
		result = es.deleteEmp(empno);
		
		if(result > 0) {
			return "redirect:listEmp";
		} 
		else {
			model.addAttribute("msg", "삭제 실패 확인해 보세요");
			return "forward:datailEmp";
		}
	}
	
	// 직원 부서 조회
	@GetMapping(value = "listEmpDept")
	public String listEmpDept(Model model) {
		log.info("listEmpDept Start...");
		List<EmpDept> listEmpDept = es.listEmpDept();
		model.addAttribute("listEmpDept", listEmpDept);
		return "listEmpDept";
	}
}

[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 com.oracle.oBootMybatis01.model.EmpDept;

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;
	}

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

	@Override
	public int deleteEmp(int empno) {
		log.info("deleteEmp Start...");
		int result = 0;
		result = ed.deleteEmp(empno);
		return result;
	}

	@Override
	public List<EmpDept> listEmpDept() {
		log.info("listEmpDept Start...");
		List<EmpDept> listEmpDept = null;
		listEmpDept = ed.listEmpDept();
		log.info("listEmpDept.size()->{}",listEmpDept.size());
		return listEmpDept;
	}
}

[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 com.oracle.oBootMybatis01.model.EmpDept;

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;
	}

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

	@Override
	public int deleteEmp(int empno) {
		log.info("deleteEmp Start...");
		int result = 0;
		
		try {
			result = session.delete("deleteEmp", empno);
		} catch (Exception e) {
			log.info("deleteEmp Exception->{}",e.getMessage());
		}
		return result;
	}

	@Override
	public List<EmpDept> listEmpDept() {
		log.info("listEmpDept Start...");
		List<EmpDept> listEmpDept = null;
		
		try {
			listEmpDept = session.selectList("tkListEmpDept");
		} catch (Exception e) {
			log.info("listEmpDept Exception->{}",e.getMessage());
		}
		return listEmpDept;
	}
}

📂 mapper

EmpDept.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.EmpDeptMapper">
	<select id="tkListEmpDept" parameterType="EmpDept" resultType="EmpDept">
		select e.empno, e.ename, e.job, e.deptno, d.loc
		from emp e, dept d
		where e.deptno = d.deptno
		order by e.empno
	</select>
</mapper>

📩메일(SMTP)

simple mail transfer protocol

🔻oBootMybatis01

[com.oracle.oBootMybatis01.controller]

private final JavaMailSender mailSender; -> 선언
SMPT google

EmpController.java

package com.oracle.oBootMybatis01.controller;

import java.util.List;

import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeUtility;
import javax.servlet.http.HttpServletRequest;

import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.mail.javamail.MimeMessageHelper;
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.model.EmpDept;
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;
	private final JavaMailSender mailSender;
	
	@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";
	}
	
	// 직원 정보 저장
	@PostMapping(value = "writeEmp")
	public String writeEmp(Emp emp, Model model) {
		log.info("writeEmp Start...");
		
		// Service, Dao , Mapper명[insertEmp] 까지 -> insert
		int result = es.insertEmp(emp);
		
		if(result > 0) {
			return "redirect:listEmp";
		} 
		else {
			model.addAttribute("msg", "입력 실패 확인해 보세요");
			return "forward:writeFormEmp";
		}
	}
	
	// 중복체크
	@GetMapping(value = "confirm")
	public String confirm(int empno, Model model) {
		log.info("confirm Start...");
		Emp emp = es.detailEmp(empno);
		model.addAttribute("empno", empno);
		if(emp != null) {
			log.info("confirm 중복된 사번");
			model.addAttribute("msg", "중복된 사번입니다");
			return "forward:writeFormEmp";
		} else {
			log.info("confirm 사용 가능한 사번");
			model.addAttribute("msg", "사용 가능한 사번입니다");
			return "forward:writeFormEmp";
		}
	}
	
	// 직원삭제
	@GetMapping(value = "deleteEmp")
	public String deleteEmp(int empno, Model model) {
		log.info("deleteEmp Start...");
		int result = 0;
		result = es.deleteEmp(empno);
		
		if(result > 0) {
			return "redirect:listEmp";
		} 
		else {
			model.addAttribute("msg", "삭제 실패 확인해 보세요");
			return "forward:datailEmp";
		}
	}
	
	// 직원 부서 조회
	@GetMapping(value = "listEmpDept")
	public String listEmpDept(Model model) {
		log.info("listEmpDept Start...");
		List<EmpDept> listEmpDept = es.listEmpDept();
		model.addAttribute("listEmpDept", listEmpDept);
		return "listEmpDept";
	}
	
	// 메일 시작
	@RequestMapping(value = "mailTransport")
	public String mailTransport(HttpServletRequest request, Model model) {
		log.info("mailSending");
		String tomail = "khj97041444@gmail.com"; 	// 받는 사람 이메일
		log.info("tomail->{}",tomail);
		String setfrom = "gpdy0102@gmail.com";		// yml 등록된 사람으로 감
		String title = "mailTransport 입니다";		// 제목
		
		try {
			// Mime 전자우편 Internet 표준 Format
			MimeMessage message = mailSender.createMimeMessage();
			MimeMessageHelper messageHelper = new MimeMessageHelper(message, true, "UTF-8");
			messageHelper.setFrom(setfrom);		// 보내는사람 생략하거나 하면 정상작동을 안함
			messageHelper.setTo(tomail);		// 받는 사람 이메일
			messageHelper.setSubject(title);	// 메일제목은 생략이 가능하다
			String tempPassword = (int) (Math.random() * 99999) + 1 + "";
			messageHelper.setText("임시 비밀번호입니다 : " + tempPassword);
			log.info("임시 비밀번호 입니다  : {}",tempPassword);
			DataSource dataSource = new FileDataSource("c:\\log\\hwa.png");
			messageHelper.addAttachment(MimeUtility.encodeText("hwa3.png", "UTF-8", "B"), dataSource); // 첨부문서
			mailSender.send(message);
			model.addAttribute("check", 1);
			// DB tempPassword Logic 구성
		} catch (Exception e) {
			log.info("mailTransport error->{}",e.getMessage());
			model.addAttribute("check", 2); // 메일 전달이 실패 시 model에 2를 담아서 감
		}
		return "mailResult";
	}
}

📂 views

mailResult.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>Mail 전송 결과</h1>
	<c:if test="${check==1}">성공적으로 전송되었습니다</c:if>
	<c:if test="${check!=1}">메일전송이 실패되었습니다</c:if>
	<c:if test="${check==null}">메일전송이 실패되었습니다 Null</c:if>
</body>
</html>

📂 src/main/resources

application.yml

server:
  port : 8391
  
# Oracle Connect
spring:
  datasource:
    url: jdbc:oracle:thin:@localhost:1521/xe
    username: scott
    password: tiger
    driver-class-name: oracle.jdbc.driver.OracleDriver
    
  # Jpa Setting
  jpa:
    hibernate: 
      ddl-auto: create # none create update
    properties: 
      hibernate:
        default_batch_fetch_size: 500
      show_sql: true      # System.out에 하이버네이트 실행 SQL
      format_sql: true
  
  # view Resolver
  mvc: 
    view: 
      prefix: /WEB-INF/views/
      suffix: .jsp
  
  # gmail Transfer
  mail: 
    host: smtp.gmail.com
    port: 587
    username: gpdy0102@gmail.com
    password: 구글 앱 비밀번호
    properties:
      mail: 
        smtp:
          auth: true
          starttls.enable: true

# Mybatis
mybatis:
  config-location: classpath:configuration.xml
  mapper-locations: classpath:mappers/*.xml
  
 
logging.level:
  org.hibernate.SQL: debug   # logger를 통해 하이버네이트 실행 SQL

PL/SQL

🔻oBootMybatis01

[com.oracle.oBootMybatis01.controller]

EmpController.java

// Procedure Test 입력화면
@RequestMapping(value = "writeDeptIn")
public String writeDeptIn(Model model) {
	log.info("writeDeptIn Start...");
	return "writeDept3";
}

📂 views

writeDept3.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%><%@ include file="header.jsp" %>
<!DOCTYPE html><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
	function chk() {
/* 		if (!frm.empno.value) {
			alert("사번을 입력한 후에 확인하세요");
			frm.empno.focus();
			return false;
		} else location.href="confirm.do?empno="+frm.empno.value; */
	}
</script>
</head>
<body>

 <h2>부서정보 입력</h2>
 <c:if test="${msg!=null}">${msg}</c:if>
	<form action="writeDept" method="post" name="frm">
		<table>
			<tr><th>부서번호</th><td><input type="number" name="deptno" 
				required="required" maxlength="2" >
				<input type="button" value="중복확인:미구현" 
				onclick="chk()"> </td></tr>
			<tr><th>부서이름</th><td><input type="text" name="dname" 
				required="required"> </td></tr>
			<tr><th>부서위치</th><td><input type="text" name="loc" 
				required="required"></td></tr>
		
			<tr><td colspan="2">
			<input type="submit" value="확인"></td></tr>
			
		</table>
		입력된 부서번호 :<c:if test="${dept.odeptno!=null}">${dept.odeptno}</c:if><p>
	         입력된 부서명   :<c:if test="${dept.odname!=null}">${dept.odname}</c:if><p> 
	         입력된 부서위치 :<c:if test="${dept.oloc!=null}">${dept.oloc}</c:if><p> 
	        <%-- ${deptVO.Oloc} --%>
	        
	   <h2>부서 정보 List</h2>
	   <table>
		<tr><th>부서명</th><th>부서이름</th><th>근무지</th></tr>
		<c:forEach var="listDept" items="${listDept}">
			<tr><td>${listDept.odeptno }</td>
			    <td>${listDept.odname }</td>
				<td>${listDept.oloc }</td>
			</tr>
		</c:forEach>
	   </table>     
	        
	</form>   
	
</body>
</html>

💻초기화면

🔻oBootMybatis01

[com.oracle.oBootMybatis01.model]

readonly를 위함

DeptVo.java

package com.oracle.oBootMybatis01.model;

import lombok.Data;

// Procedure를 위한 VO - readonly
@Data
public class DeptVo {
	// 입력
	private int deptno;
	private String dname;
	private String loc;
	// 출력
	private int odeptno;
	private String odname;
	private String oloc;
}

[com.oracle.oBootMybatis01.controller]

EmpController.java

package com.oracle.oBootMybatis01.controller;

import java.util.List;

import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeUtility;
import javax.servlet.http.HttpServletRequest;

import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.mail.javamail.MimeMessageHelper;
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.DeptVO;
import com.oracle.oBootMybatis01.model.Emp;
import com.oracle.oBootMybatis01.model.EmpDept;
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;
	private final JavaMailSender mailSender;
	
	@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";
	}
	
	// 직원 정보 저장
	@PostMapping(value = "writeEmp")
	public String writeEmp(Emp emp, Model model) {
		log.info("writeEmp Start...");
		
		// Service, Dao , Mapper명[insertEmp] 까지 -> insert
		int result = es.insertEmp(emp);
		
		if(result > 0) {
			return "redirect:listEmp";
		} 
		else {
			model.addAttribute("msg", "입력 실패 확인해 보세요");
			return "forward:writeFormEmp";
		}
	}
	
	// 중복체크
	@GetMapping(value = "confirm")
	public String confirm(int empno, Model model) {
		log.info("confirm Start...");
		Emp emp = es.detailEmp(empno);
		model.addAttribute("empno", empno);
		if(emp != null) {
			log.info("confirm 중복된 사번");
			model.addAttribute("msg", "중복된 사번입니다");
			return "forward:writeFormEmp";
		} else {
			log.info("confirm 사용 가능한 사번");
			model.addAttribute("msg", "사용 가능한 사번입니다");
			return "forward:writeFormEmp";
		}
	}
	
	// 직원삭제
	@GetMapping(value = "deleteEmp")
	public String deleteEmp(int empno, Model model) {
		log.info("deleteEmp Start...");
		int result = 0;
		result = es.deleteEmp(empno);
		
		if(result > 0) {
			return "redirect:listEmp";
		} 
		else {
			model.addAttribute("msg", "삭제 실패 확인해 보세요");
			return "forward:datailEmp";
		}
	}
	
	// 직원 부서 조회
	@GetMapping(value = "listEmpDept")
	public String listEmpDept(Model model) {
		log.info("listEmpDept Start...");
		List<EmpDept> listEmpDept = es.listEmpDept();
		model.addAttribute("listEmpDept", listEmpDept);
		return "listEmpDept";
	}
	
	// 메일 시작
	@RequestMapping(value = "mailTransport")
	public String mailTransport(HttpServletRequest request, Model model) {
		log.info("mailSending");
		String tomail = "khj97041444@gmail.com"; 	// 받는 사람 이메일
		log.info("tomail->{}",tomail);
		String setfrom = "gpdy0102@gmail.com";		// yml 등록된 사람으로 감
		String title = "mailTransport 입니다";		// 제목
		
		try {
			// Mime 전자우편 Internet 표준 Format
			MimeMessage message = mailSender.createMimeMessage();
			MimeMessageHelper messageHelper = new MimeMessageHelper(message, true, "UTF-8");
			messageHelper.setFrom(setfrom);		// 보내는사람 생략하거나 하면 정상작동을 안함
			messageHelper.setTo(tomail);		// 받는 사람 이메일
			messageHelper.setSubject(title);	// 메일제목은 생략이 가능하다
			String tempPassword = (int) (Math.random() * 99999) + 1 + "";
			messageHelper.setText("임시 비밀번호입니다 : " + tempPassword);
			log.info("임시 비밀번호 입니다  : {}",tempPassword);
			DataSource dataSource = new FileDataSource("c:\\log\\hwa.png");
			messageHelper.addAttachment(MimeUtility.encodeText("hwa3.png", "UTF-8", "B"), dataSource); // 첨부문서
			mailSender.send(message);
			model.addAttribute("check", 1);
			// DB tempPassword Logic 구성
		} catch (Exception e) {
			log.info("mailTransport error->{}",e.getMessage());
			model.addAttribute("check", 2); // 메일 전달이 실패 시 model에 2를 담아서 감
		}
		return "mailResult";
	}
	
	// Procedure Test 입력화면
	@RequestMapping(value = "writeDeptIn")
	public String writeDeptIn(Model model) {
		log.info("writeDeptIn Start...");
		return "writeDept3";
	}
	
	// Procedure 통한 Dept 입력후 VO 전달
	@PostMapping(value = "wirteDept")
	public String writeDept(DeptVO deptVO, Model model) {
		es.insertDept(deptVO);
		
		if(deptVO == null) {
			log.info("deptVo Null");
		} else {
			log.info("deptVo.getOdeptno()->{}", deptVO.getOdeptno());
			log.info("deptVo.getOdname()->{}", deptVO.getOdname());
			log.info("deptVo.getOloc()->{}", deptVO.getOloc());
			model.addAttribute("msg", "정상 입력 되었습니다 ^^");
			model.addAttribute("dept", deptVO);
		}
		
		return "writeDept3";
	}
}

[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.DeptVO;
import com.oracle.oBootMybatis01.model.Emp;
import com.oracle.oBootMybatis01.model.EmpDept;

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;
	}

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

	@Override
	public int deleteEmp(int empno) {
		log.info("deleteEmp Start...");
		int result = 0;
		result = ed.deleteEmp(empno);
		return result;
	}

	@Override
	public List<EmpDept> listEmpDept() {
		log.info("listEmpDept Start...");
		List<EmpDept> listEmpDept = null;
		listEmpDept = ed.listEmpDept();
		log.info("listEmpDept.size()->{}",listEmpDept.size());
		return listEmpDept;
	}

	@Override
	public void insertDept(DeptVO deptVO) {
		log.info("insertDept Start...");
		dd.insertDept(deptVO);
	}
}

[com.oracle.oBootMybatis01.dao]

DeptDaoImpl.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.Dept;
import com.oracle.oBootMybatis01.model.DeptVO;

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;
	}

	@Override
	public void insertDept(DeptVO deptVO) {
		log.info("insertDept Start...");
		session.selectOne("ProcDeptInsert", deptVO);
		
	}
}

📂 src/main/resources

configuration.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
	<typeAliases>
		<typeAlias alias="Emp" type="com.oracle.oBootMybatis01.model.Emp"/>
		<typeAlias alias="Dept" type="com.oracle.oBootMybatis01.model.Dept" />
		<typeAlias alias="EmpDept" type="com.oracle.oBootMybatis01.model.EmpDept" />
		<typeAlias alias="DeptVO" type="com.oracle.oBootMybatis01.model.DeptVO" />
	</typeAliases>
</configuration>

📂 mappers

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>
	
	<!-- PL/SQL - Procedure -->
	<select id="ProcDeptInsert" parameterType="DeptVO" statementType="CALLABLE">
		{
			call Dept_Insert3(
					 #{deptno	,	mode=IN		, jdbcType=INTEGER}
					,#{dname	,	mode=IN		, jdbcType=VARCHAR}
					,#{loc		,	mode=IN		, jdbcType=VARCHAR}
					,#{odeptno	,	mode=OUT	, jdbcType=INTEGER}
					,#{odname	,	mode=OUT	, jdbcType=VARCHAR}
					,#{oloc		,	mode=OUT	, jdbcType=VARCHAR}
			)
		}
	</select>
</mapper>

써니

이전 포스트

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

다음 포스트

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

spring

💾직원정보저장

🔻oBootMybatis01

[com.oracle.oBootMybatis01.controller]

[com.oracle.oBootMybatis01.service]

[com.oracle.oBootMybatis01.dao]

📂 mapper

📂 views

🔎중복검사

[com.oracle.oBootMybatis01.controller]

직원삭제

🔻oBootMybatis01

[com.oracle.oBootMybatis01.controller]

[com.oracle.oBootMybatis01.service]

[com.oracle.oBootMybatis01.dao]

📂 mapper

직원부서조회(JOIN)

🔻oBootMybatis01

📂 static

📂 views

📂 src/main/resources

[com.oracle.oBootMybatis01.model]

[com.oracle.oBootMybatis01.controller]

[com.oracle.oBootMybatis01.service]

[com.oracle.oBootMybatis01.dao]

📂 mapper

📩메일(SMTP)

simple mail transfer protocol

🔻oBootMybatis01

[com.oracle.oBootMybatis01.controller]

📂 views

📂 src/main/resources

PL/SQL

🔻oBootMybatis01

[com.oracle.oBootMybatis01.controller]

📂 views

💻초기화면

🔻oBootMybatis01

[com.oracle.oBootMybatis01.model]

[com.oracle.oBootMybatis01.controller]

[com.oracle.oBootMybatis01.service]

[com.oracle.oBootMybatis01.dao]

📂 src/main/resources

📂 mappers

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

국비학원_78일차(Validation, PL/SQL, interCeptor)

0개의 댓글