본문 바로가기
Spring

Day59 2024.08.13.화 #코딩일기

by SSONG1521 2024. 8. 13.


(면접 내용)

**masterTBL, transaction TBL 의 차이점

master TBL : 기준이 되는 TBL (거래처, 제품코드, 사원) -> 한 번 입력하면 수정이 없는 (회사의 기준이 되는 TBL)

transaction TBL : 게시판, 판매 (매출일자, 거래처 등등) -> 매일 transaction이 발생한다는 의미로!!

ERP 전사적자원관리(Enterprise Resource Planning) -> 기업의 모든 업무(인사, 회계, 구매자재, 재고관리, 물류)를 관리

 

InnerJoin. outer join 차이

equi join

 

hash 암호화 !! -> 복구화를 잘 시키지 않는다.

 

 

 

 

*DTO로 parameter 넘겨주는 방식과 Map으로 넘겨주는 방식 ★★

항목 Map DTO
정의 Map 각자 구현 DTO
Parameter Random Table 기준
참조 Column
사용 - 규정을 정하기 힘들 때
- 소통 원활하지 않을 때		 규정(기준)이 명확
문제점 유지보수가 어려움
(소스코드를 추적하면서 찾아야 함)
하지만 급할 때는 쓰기 편하다
(시간절약)		 유지보수 편리 (명확하다)
개발공수가 더 필요 ↑

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Spring Starter Project oBootMybatis01
folder resource/static
html index 
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>회원 관리</h1>
	<a href="/listEmpStart">회원 목록(기본 CRUD)</a><p>
	<a href="/listEmpDept">직원부서조회(Join/Mail전송)</a><p>
	<a href="/writeDeptIn">PL/SQL(부서입력) </a><p>
	<a href="/writeDeptCursor">PL/SQL(부서조회 Cursor) </a><p>
	<a href="/memberJpa/new">JPA Member(CRUD) </a><p>
	<a href="/interCeptorForm">interCeptor(가로채기) </a><p>
	<a href="/upLoadFormStart">UpLoad(이미지 올리기) </a><p>
	<a href="/ajaxForm">Ajax Form Test </a><p>
	<a href="/transactionInsertUpdate">Transaction Test</a><p>
</body>
</html>
Console

 

 

 

 

 

 

Spring Starter Project oBootMybatis01
folder src/main/webapp/WEB-INF/views
JSP 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(SMTP)</a>
	<p>Simple Mail Transfer Protocol -> OSI 전송계층</p>
	<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>
Console

 

 - SMTP 메일 전송하기 위해 쓰는 표준 서버

 

 

 

 

 

Spring Starter Project oBootMybatis01
package com.oracle.oBootMybatis01.Model
class EmpDept 
package com.oracle.oBootMybatis01.model;

import lombok.Data;

@Data
public class EmpDept {
	//두 TBL이 많다는 전제하에  Join 목적으로 만든 DTO
	
	//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;
}
Console
 

 

 - 두 TBL을 join 하는 방법 

 

 

 

 

 

 

 

Spring Starter Project oBootMybatis01
folder src/main/webapp/WEB-INF/views
JSP 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>

 

Console

 

 

 

 

 

Spring Starter Project oBootMybatis01
folder src/main/webapp/WEB-INF/views
JSP writeDept3 
<%@ 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>
Console

 

 

 

 

 

Spring Starter Project oBootMybatis01
package com.oracle.oBootMybatis01.model
class DeptVO 
package com.oracle.oBootMybatis01.model;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

@Getter
@Setter
@ToString
public class DeptVO {
	//입력
	private int 	deptno;
	private String 	dname;
	private String 	loc;
	
	//출력
	private int 	odeptno;
	private String 	odname;
	private String 	oloc;
}
Console
 

 

 - 입력과 출력으로 나눠서!! (DeptVO DTO 만들어준 것!!)

 

 

 

 

 

Spring Starter Project oBootMybatis01
folder src/main/webapp/WEB-INF/views
JSP writeDeptCursor.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>부서정보 Procedure Cursor List</h2>
	<table>
		<tr>
			<th>부서명</th>
			<th>부서이름</th>
			<th>근무지</th>
		</tr>
		<c:forEach var="listDept" items="${deptList }">
			<tr>
				<td>${listDept.deptno }</td>
				<td>${listDept.dname }</td>
				<td>${listDept.loc }</td>
			</tr>
		</c:forEach>
	</table>
</body>
</html>
Console

 

 

 

 

 

 

Spring Starter Project oBootMybatis01
folder resources
yml application.yml 
server:
  port: 8387
# Oracle Connect
spring:
  datasource:
    driver-class-name: oracle.jdbc.driver.OracleDriver
    url: jdbc:oracle:thin:@localhost:1521/xe
    username: scott
    password: tiger
    
  #Jpa Setting
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: update
            # @Entity라고 설정되어 있는 것들 만 update, none, create 등등
            # release 할 때에는 none으로 바꿔주어야 한다!!!!
            
  #View Resolver
  mvc:
    view:
      prefix: /WEB-INF/views/
      suffix: .jsp
      
  # gmail Transfer    
  mail:
    host: stmp.gmail.com
    port: 587
    username: kateshs0521@gmail.com
    password:
    properties:
      mail:
        smtp:
          auth: true
          starttls.enable: true
      
# Mybatis
mybatis:
  config-location: classpath:configuration.xml
  mapper-locations: classpath:mappers/*.xml
  # classpath -> resource이다!!
Console
 

 

 - mail 하기 위해서 추가해준다. id, passwd 내 것 넣기

    mvc와 같은 줄에 맞춰서 넣어준다.

 - authentication : 사용자가 맞냐 안맞냐 인증

   Authorization : role이 할당 받았냐 안받았냐
 - starttls -> ssl (secure socket layer)과 tls 차이



Spring Starter Project oBootMybatis01
package com.oracle.oBootMybatis01.Controller
class EmpController 
package com.oracle.oBootMybatis01.controller;

import java.util.HashMap;
import java.util.List;

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.validation.BindingResult;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
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 com.oracle.wls.shaded.org.apache.regexp.RE;
import com.oracle.wls.shaded.org.apache.xalan.xsltc.compiler.sym;

import jakarta.mail.internet.MimeMessage;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.validation.Valid;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Controller
@RequiredArgsConstructor
//logger 쓰는 것!!!
@Slf4j
public class EmpController {
	private final EmpService es;
	private final JavaMailSender mailSender;
	
	@RequestMapping(value = "listEmpStart")
	public String listEmpStart(Emp emp, Model model) {
		System.out.println("EmpController listEmpStart Start...");
		//21명 -> 3page가 있어야 한다.
		int totalEmp = es.totalEmp();
		String currentPage = "1";
		
		//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);
		System.out.println("EmpController list listEmp.size()-> "+listEmp.size());
		
		model.addAttribute("totalEmp", totalEmp);
		model.addAttribute("listEmp",listEmp);
		model.addAttribute("page",page);
		System.out.println("EmpController listEmpStart End...");
		
		// ViewResolver에 의해 list.jsp로 이동
		return "list";
	}
	
	@RequestMapping(value = "listEmp")
	public String listEmp(Emp emp, Model model) {
		System.out.println("EmpController listEmp Start...");
		//21명 -> 3page가 있어야 한다.
		int totalEmp = es.totalEmp();
		
		//Paging 작업
		Paging page = new Paging(totalEmp, emp.getCurrentPage());
		
		//Parameter emp --> Page만 추가 Setting
		emp.setStart(page.getStart());	//시작 시 1
		emp.setEnd(page.getEnd());		//시작 시 10
		
		List<Emp> listEmp = es.listEmp(emp);
		System.out.println("EmpController list listEmp.size()-> "+listEmp.size());
		
		model.addAttribute("totalEmp", totalEmp);
		model.addAttribute("listEmp",listEmp);
		model.addAttribute("page",page);
		System.out.println("EmpController listEmp End...");
		
		// ViewResolver에 의해 list.jsp로 이동
		return "list";
	}
	
	@GetMapping(value = "detailEmp")
	public String detailEmp(Emp emp1, Model model) {
		System.out.println("EmpController Start detailEmp...");
//		1. EmpService안에 detailEmp method 선언
//		   1) parameter : empno
//		   2) Return      Emp
//
//		2. EmpDao   detailEmp method 선언 
////		                    mapper ID   ,    Parameter
//		emp = session.selectOne("tkEmpSelOne",    empno);
//		System.out.println("emp-> "+emp1);
		
		Emp emp = es.detailEmp(emp1.getEmpno());
		
		System.out.println("emp-> "+emp1);
		model.addAttribute("emp", emp);	
		System.out.println("EmpController End detailEmp...");
		
		return "detailEmp";
	}
	
	@GetMapping(value = "updateFormEmp")
	public String updateFormEmp(Emp emp1, Model model) {
		//							emp1에 empno가 들어가진다.
		System.out.println("EmpController Start updateFormEmp...");
		Emp emp = es.detailEmp(emp1.getEmpno());
		System.out.println("EmpController updateFormEmp emp-> "+emp);
		// 문제 
		// 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);
		}
		
		System.out.println("hiredate-> "+hiredate);
		
		model.addAttribute("emp", emp);	
		System.out.println("EmpController End updateFormEmp...");
		return "updateFormEmp";
	}
	
	@PostMapping(value = "updateEmp")
	public String updateEmp(Emp emp, Model model) {
		log.info("EmpController updateEmp Start....");
		int updateCount = es.updateEmp(emp);
//      1. EmpService안에 updateEmp method 선언
//      1) parameter : Emp
//      2) Return      updateCount (int)
//
//   2. EmpDao updateEmp method 선언
//	                              mapper ID   ,    Parameter
//   updateCount = session.update("tkEmpUpdate",   emp);
		System.out.println("EmpController es.updateEmp updateCount--> "+updateCount);
		model.addAttribute("uptCnt",updateCount);		//Test Controller 간 Data 전달
		model.addAttribute("kk3","Message Test");		//Test Controller 간 Data 전달
		System.out.println("EmpController addAttribute After....");
		log.info("EmpController updateEmp End....");
		
		//redirect: 같은 controller 안에 있는 listEmp로 돌아간다는 의미!!!!
		return "forward:listEmp";
//		return "redirect:listEmp";
			
	}
	
	@RequestMapping(value = "writeFormEmp")
	public String writeFormEmp(Model model) {
		System.out.println("EmpController writeFormEmp Start...");
		
		
		//관리자 사번만 Get
		List<Emp> empList = es.listManager();
		System.out.println("EmpController writeForm empList.size()-> "+ empList.size());
		model.addAttribute("empMngList", empList);	//emp Manager List
		
		// 1. service -> listManager
		// 2. Dao     -> listManager
		// 3. mapper  -> tkSelectManager
		
		//부서(코드, 부서명)
		List<Dept> deptList = es.deptSelect();
		model.addAttribute("deptList", deptList);	//dept
		System.out.println("EmpController writeForm deptList.size()-> "+deptList.size());
		
		System.out.println("EmpController writeFormEmp End...");
		
		return "writeFormEmp";
	}
	
	@PostMapping(value = "writeEmp")
	public String writeEmp(Emp emp, Model model) {
		System.out.println("EmpController start writeEmp...");
		
		// Service, Dao , Mapper명[insertEmp] 까지 -> insert
		int insertResult = es.insertEmp(emp);
		if (insertResult > 0) return "redirect:listEmp";
		else {
			model.addAttribute("msg", "입력 실패! 확인해보세요");
			return "forward:writeFormEmp";
		}

	}
	
	@GetMapping(value = "confirm")
	public String confirm(Emp emp1, Model model) {
		Emp emp = es.detailEmp(emp1.getEmpno());
		model.addAttribute("empno", emp1.getEmpno());
		if (emp != null) {
			System.out.println("EmpController confirm 중복된 사번..");
			model.addAttribute("msg", "중복된 사번입니다.");
//			return "forward:writeFormEmp";
		} else {
			System.out.println("EmpController confirm 사용 가능한 사번..");
			model.addAttribute("msg", "사용 가능한 사번입니다.");
//			return "forward:writeFormEmp";
		}
		return "forward:writeFormEmp";
	}
	
	@RequestMapping(value = "deleteEmp")
	public String deleteEmp(Emp emp, Model model) {
		System.out.println("EmpController Start deleteEmp...");
		// Controller -->  deleteEmp    1.parameter : empno
		// name -> Service, dao , mapper
		// return -> listEmp
		int result = es.deleteEmp(emp.getEmpno());
		return "redirect:listEmp";
	}
	
	@RequestMapping(value = "writeFormEmp3")
	public String writeFormEmp3(Model model) {
		System.out.println("EmpController writeFormEmp3 Start...");
		
		
		//관리자 사번만 Get
		List<Emp> empList = es.listManager();
		System.out.println("EmpController writeForm empList.size()-> "+ empList.size());
		model.addAttribute("empMngList", empList);	//emp Manager List
		
		// 1. service -> listManager
		// 2. Dao     -> listManager
		// 3. mapper  -> tkSelectManager
		
		//부서(코드, 부서명)
		List<Dept> deptList = es.deptSelect();
		model.addAttribute("deptList", deptList);	//dept
		System.out.println("EmpController writeForm deptList.size()-> "+deptList.size());
		
		System.out.println("EmpController writeFormEmp3 End...");
		
		return "writeFormEmp3";
	}
	
	// Validation시 참조
	@PostMapping(value = "writeEmp3")
	public String writeEmp3(@ModelAttribute("emp") @Valid Emp emp
							, BindingResult result
							, Model model) {
		System.out.println("EmpController start writeEmp3...");
		
		// Validation 오류시 Result
		if (result.hasErrors()) {
			System.out.println("EmpController writeEmp3 hasErrors...");
			model.addAttribute("msg", "BindingResult 입력 실패 확인해보세요");
			return "forward:writeFormEmp3";
		}
		
		// Service, Dao , Mapper명[insertEmp] 까지 -> insert
		int insertResult = es.insertEmp(emp);
		if (insertResult > 0) return "redirect:listEmp";
		else {
			model.addAttribute("msg", "입력 실패! 확인해보세요");
			return "forward:writeFormEmp3";
		}

	}
	
	@RequestMapping(value = "listSearch3")
	public String listSearch3(Emp emp, Model model) {
		System.out.println("EmpController listSearch3 Start...");
		System.out.println("EmpController listSearch3 emp-> "+emp);
		
		//Emp 전체 count
		int totalEmp = es.condTotalEmp(emp);
		System.out.println("EmpController listSearch3 totalEmp-> "+totalEmp);
		
		//Paging 작업
		Paging page = new Paging(totalEmp, emp.getCurrentPage());
		
		//Parameter emp --> Page 만 추가 Setting
		emp.setStart(page.getStart());	//시작 시 1
		emp.setEnd(page.getEnd());		//시작 시 10
		System.out.println("EmpController listSearch3 page-> "+page);
		
		List<Emp> listSearchEmp = es.listSearchEmp(emp);
		System.out.println("EmpController listSearch3 listSearchEmp.size()-> "+listSearchEmp.size());
		
		model.addAttribute("totalEmp", totalEmp);
		//왜 listEmp로 보내주냐면, list.jsp로 가보면 listEmp로 되어있기 때문에 (재활용 위해서)
		model.addAttribute("listEmp", listSearchEmp);
		model.addAttribute("page", page);
		
		
		return "list";
	}
	
	@GetMapping(value = "listEmpDept")
	public String listEmpDept(Model model) {
		System.out.println("EmpController listEmpDept start...");
		// Service ,DAO -> listEmpDept
		// Mapper만 ->EmpDept.xml(tkListEmpDept)
		List<EmpDept> listEmpDept = es.listEmpDept();
		model.addAttribute("listEmpDept", listEmpDept);
		
		System.out.println("EmpController listEmpDept End...");
		return "listEmpDept";
	}
	
	@RequestMapping(value = "mailTransport")
	public String mailTransport(HttpServletRequest request, Model model) {
		System.out.println("mailSending...");
		String tomail = "kate__@naver.com";							//받는 사람 이메일
		System.out.println(tomail);
		String setfrom = "kateshs0521@gmail.com";
		String title = "mailTransport 입니다";		//제목
		
		try {
			// MIME(영어: Multipurpose Internet Mail Extensions)는 전자 우편을 위한 인터넷 표준 포맷
			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() * 999999) +1 +"";
			messageHelper.setText("임시 비밀번호입니다 : "+tempPassword);		//메일 내용
			System.out.println("임시 비밀번호입니다 : "+tempPassword);
			
			mailSender.send(message);
			model.addAttribute("check", 1);		//정상 전달
			
			//DB Logic
			
			
			
		} catch (Exception e) {
			System.out.println("mailTransport e.getMessage()-> "+e.getMessage());
			model.addAttribute("check", 2);	//메일 전달 실패
		}
		
		return "mailResult";
	}
	
	// Procedure Test 입력화면
	@RequestMapping(value = "writeDeptIn")
	public String writeDeptIn(Model model) {	
		System.out.println("writeDeptIn Start.,..");
		
		System.out.println("writeDeptIn End....");
		return "writeDept3";
	}
	
	//Procedure 통한 Dept 입력 후 VO 전달
	@PostMapping(value = "writeDept")
	public String writeDept(DeptVO deptVO, Model model) {
		es.insertDept(deptVO);
		if (deptVO == null) {
			System.out.println("deptVO NULL");
		} else {
			System.out.println("deptVO.getOdeptno()"+deptVO.getOdeptno());
			System.out.println("deptVO.getOdname()"+deptVO.getOdname());
			System.out.println("deptVO.getOloc()"+deptVO.getOloc());
			model.addAttribute("msg", "정상 입력 되었습니다. ^^");
			model.addAttribute("dept", deptVO);
		}
		return "writeDept3";
	}
	
	//Map 적용
	@GetMapping(value = "writeDeptCursor")
	public String writeDeptCursor(Model model) {
		System.out.println("EmpController writeDeptCursor Start...");
		// 부서범위 조회
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("sDeptno", 10);
		map.put("eDeptno", 55);
		
		//call by reference map
		es.selListDept(map);
		List<Dept> deptLists = (List<Dept>)map.get("dept");
		for(Dept dept : deptLists) {
			System.out.println("writeDeptCursor dept -> "+dept);
			System.out.println("dept.getDname()-> "+dept.getDname());
			System.out.println("dept.getLoc()-> "+dept.getLoc());
		}
		System.out.println("deptList Size-> "+deptLists.size());
		model.addAttribute("deptList", deptLists);
		
		return "writeDeptCursor";
	}
	
}
Console
 

 

 - JavaMailSender를 넣어준다. (application에 넣어주었기 때문에 된다.)

 

 - MIME : Multipurpose Internet Mail Extensions

               다양한 유형의 정보를 식별하기 위한 표준

                MIME(영어: Multipurpose Internet Mail Extensions)는 전자 우편을 위한 인터넷 표준 포맷

 

 

 

 

 

 

Spring Starter Project oBootMybatis01
package com.oracle.oBootMybatis01.service
interface EmpService 
package com.oracle.oBootMybatis01.service;

import java.util.HashMap;
import java.util.List;

import com.oracle.oBootMybatis01.model.Dept;
import com.oracle.oBootMybatis01.model.DeptVO;
import com.oracle.oBootMybatis01.model.Emp;
import com.oracle.oBootMybatis01.model.EmpDept;

public interface EmpService {
	int 			totalEmp();
	List<Emp>  		listEmp(Emp emp);
	Emp 			detailEmp(int empno);
	int 			updateEmp(Emp emp);
	List<Emp> 		listManager();
	List<Dept> 		deptSelect();
	int 			insertEmp(Emp emp);
	int 			deleteEmp(int empno);
	int 			condTotalEmp(Emp emp);
	List<Emp> 		listSearchEmp(Emp emp);
	List<EmpDept> 	listEmpDept();
	void 			insertDept(DeptVO deptVO);
	void 			selListDept(HashMap<String, Object> map);
}
Console
 

 

 

 

 

 

Spring Starter Project oBootMybatis01
package com.oracle.oBootMybatis01.service
class EmpServiceImpl 
package com.oracle.oBootMybatis01.service;

import java.util.HashMap;
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;

@Service
@RequiredArgsConstructor
public class EmpServiceImpl implements EmpService {
	
	//interface가 각각 하나라면, 여러개의 dao를 만들 수 있다.
	//if EmpDao interface에 JPA dao가 하나 더 연결(implement)되어 있으면, 기존의 myBatis는 annotation 없애주어야 한다.
	private final EmpDao ed;
	private final DeptDao dd;

	@Override
	public int totalEmp() {
		System.out.println("EmpServiceImpl totalEmp start...");
		int totEmpCnt = ed.totalEmp();
		System.out.println("EmpServiceImpl totalEmp totEmpCnt->"+totEmpCnt);
		return totEmpCnt;
	}

	@Override
	public List<Emp> listEmp(Emp emp) {
		List<Emp> empList = null;
		System.out.println("EmpServiceImpl listManager Start...");
		empList = ed.listEmp(emp);
		System.out.println("EmpServiceImpl listEmp empList.size()-> "+empList.size());
		return empList;
	}

	@Override
	public Emp detailEmp(int empno) {
		System.out.println("EmpServiceImpl detailEmp start...");
		Emp emp = ed.detailEmp(empno);
		System.out.println("EmpServiceImpl detailEmp emp-> "+emp);
//		1. EmpService안에 detailEmp method 선언
//		   1) parameter : empno
//		   2) Return      Emp
		return emp;
	}

	@Override
	public int updateEmp(Emp emp) {
//      1. EmpService안에 updateEmp method 선언
//      1) parameter : Emp
//      2) Return      updateCount (int)
		System.out.println("EmpServiceImpl updateEmp Start...");
		int updateCount = 0;
		updateCount = ed.updateEmp(emp);
		System.out.println("EmpServiceImpl updateEmp updateCount-> "+updateCount);
		System.out.println("EmpServiceImpl updateEmp ed.updateEmp After...");
		return updateCount;
	}

	@Override
	public List<Emp> listManager() {
		System.out.println("EmpServiceImpl listManager Start...");
		List<Emp> empList = null;
		empList = ed.listManager();
		System.out.println("EmpServiceImpl listManager empList.size()-> "+empList.size());
		System.out.println("EmpServiceImpl listManager ed.listManager After...");
		
		return empList;
	}

	@Override
	public List<Dept> deptSelect() {
		System.out.println("EmpServiceImpl deptSelect Start...");
		List<Dept> deptList = null;
		deptList = dd.deptSelect();
		
		System.out.println("EmpServiceImpl deptSelect deptList.size()-> "+deptList.size());
		System.out.println("EmpServiceImpl deptSelect dd.deptSelect After...");
		//tkSelectDept
		return deptList;
	}

	@Override
	public int insertEmp(Emp emp) {
		System.out.println("EmpServiceImpl insertEmp Start...");
		int result = ed.insertEmp(emp);
		
		System.out.println("EmpServiceImpl insertEmp result-> "+result);
		System.out.println("EmpServiceImpl insertEmp ed.insertEmp After...");
		
		return result;
	}

	@Override
	public int deleteEmp(int empno) {
		System.out.println("EmpServiceImpl deleteEmp Start...");
		int result = ed.deleteEmp(empno);

		System.out.println("EmpServiceImpl insertEmp result-> "+result);
		System.out.println("EmpServiceImpl insertEmp ed.insertEmp After...");
		
		return result;
	}

	@Override
	public int condTotalEmp(Emp emp) {
		System.out.println("EmpServiceImpl condTotalEmp Start...");
		int totEmpCnt = ed.condTotalEmp(emp);
		System.out.println("EmpServiceImpl condTotalEmp totEmpCnt -> "+totEmpCnt);
		return totEmpCnt;
	}

	@Override
	public List<Emp> listSearchEmp(Emp emp) {
		System.out.println("EmpServiceImpl listSearchEmp Start...");
		List<Emp> empList = null;
		// 1. DAO  ed.empSearchList3(emp);
        // 2. Mapper selectList("tkEmpSearchList3", emp);
		empList = ed.empSearchList3(emp);
		System.out.println("EmpServiceImpl listSearchEmp empList -> "+empList);
		return empList;
	}

	@Override
	public List<EmpDept> listEmpDept() {
		System.out.println("EmpServiceImpl listEmpDept Start...");
		List<EmpDept> listEmpDept = null;
		
		listEmpDept = ed.listEmpDept();
		System.out.println("EmpServiceImpl listEmpDept listEmpDept.size() -> "+listEmpDept.size());
		return listEmpDept;
	}

	@Override
	public void insertDept(DeptVO deptVO) {
		System.out.println("EmpServiceImpl insertDept Start...");
		dd.insertDept(deptVO);
		
	}

	@Override
	public void selListDept(HashMap<String, Object> map) {
		System.out.println("EmpServiceImpl selListDept Start...");
		dd.selListDept(map);
		
	}

}
Console
 

 

 

 

 

 

 

 

 

Spring Starter Project oBootMybatis01
package com.oracle.oBootMybatis01.dao
interface EmpDao 
package com.oracle.oBootMybatis01.dao;

import java.util.List;

import com.oracle.oBootMybatis01.model.Emp;
import com.oracle.oBootMybatis01.model.EmpDept;

public interface EmpDao {
	int totalEmp();
	List<Emp> listEmp(Emp emp);
	Emp detailEmp(int empno);
	int updateEmp(Emp emp);
	List<Emp> listManager();
	int insertEmp(Emp emp);
	int deleteEmp(int empno);
	int condTotalEmp(Emp emp);
	List<Emp> empSearchList3(Emp emp);
	List<EmpDept> listEmpDept();	
}
Console
 

 

 

 

Spring Starter Project oBootMybatis01
package com.oracle.oBootMybatis01.dao
class EmpDaoImpl 
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;

@Repository
//final 끌고 들어와준다.
@RequiredArgsConstructor

public class EmpDaoImpl implements EmpDao {
	//Mybatis DB 연동 (DI 작업)
	private final SqlSession session;

	@Override
	public int totalEmp() {
		int totEmpCount = 0;
		System.out.println("EmpDaoImpl Start totalEmp...");
		
		try {
			//.으로 이름 넣어주는 이유 : id가 많기 때문에!!
			//현재 logic을 통해 나온 totEmpCount는 COUNT(*)이다!
			totEmpCount = session.selectOne("com.oracle.oBootMyBatis01.EmpMapper.empTotal");
			System.out.println("EmpDaoImpl totalEmp totEmpCount-> "+totEmpCount);
		} catch (Exception e) {
			System.out.println("EmpDaoImpl totalEmp e.getMessage()->"+e.getMessage());
		}

		return totEmpCount;
	}

	@Override
	public List<Emp> listEmp(Emp emp) {
		List<Emp> empList = null;
		System.out.println("EmpDaoImpl listEmp Start...");
		try {
			//ID가 unique여야한다. (PK처럼 생각해야한다.)
			//								Map ID		parameter
			empList = session.selectList("tkEmpListAll", emp);
			System.out.println("EmpDaoImpl listEmp empList.size()-> "+empList.size());
		} catch (Exception e) {
			System.out.println("EmpDaoImpl listEmp e.getMessage()-> "+e.getMessage());
		}
		return empList;
	}

	@Override
	public Emp detailEmp(int empno) {
//		2. EmpDao   detailEmp method 선언 
////    mapper ID   ,    Parameter
		//emp = session.selectOne("tkEmpSelOne",    empno);
		//System.out.println("emp-> "+emp1);
		
		Emp emp = new Emp();
		System.out.println("EmpDaoImpl detailEmp Start...");
		try {
			emp = session.selectOne("tkEmpSelOne", empno);
			System.out.println("EmpDaoImpl detailEmp emp-> "+emp);
		} catch (Exception e) {
			System.out.println("EmpDaoImpl detailEmp e.getMessage()-> "+e.getMessage());
		}
		return emp;
	}

	@Override
	public int updateEmp(Emp emp) {
//   2. EmpDao updateEmp method 선언
									//	    mapper ID   ,    Parameter
			//updateCount = session.update("tkEmpUpdate",   emp);
		int updateCount = 0;
		try {
			updateCount = session.update("tkEmpUpdate", emp);
			System.out.println("EmpDaoImpl updateEmp updateCount-> "+updateCount);
		} catch (Exception e) {
			System.out.println("EmpDaoImpl updateEmp e.getMessage()-> "+e.getMessage());
		}
		return updateCount;
	}

	@Override
	public List<Emp> listManager() {
		List<Emp> empList = null;
		System.out.println("EmpDaoImpl listManager Start...");
		try {
			// emp 관리자만 select				Naming Rule
			empList = session.selectList("tkSelectManager");
			System.out.println("EmpDaoImpl listManager empList-> "+empList);
		} catch (Exception e) {
			System.out.println("EmpDaoImpl listManager e.getMessage()-> "+e.getMessage());
		}
		
		return empList;
	}

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

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

	@Override
	public int condTotalEmp(Emp emp) {
		int totEmpCount = 0;
		System.out.println("EmpDaoImpl condTotalEmp Start...");
		try {
			totEmpCount = session.selectOne("condEmpTotal", emp);
			System.out.println("EmpDaoImpl condTotalEmp totEmpCount-> "+totEmpCount);
		} catch (Exception e) {
			System.out.println("EmpDaoImpl condTotalEmp e.getMessage()-> "+e.getMessage());
		}
		
		return totEmpCount;
	}

	@Override
	public List<Emp> empSearchList3(Emp emp) {
		List<Emp> empList = null;
		System.out.println("EmpDaoImpl empSearchList3 Start...");
		try {
			empList = session.selectList("tkEmpSearchList3", emp);
			System.out.println("EmpDaoImpl empSearchList3 empList-> "+empList);
		} catch (Exception e) {
			System.out.println("EmpDaoImpl empSearchList3 e.getMessage()-> "+e.getMessage());
		}
		return empList;
	}

	@Override
	public List<EmpDept> listEmpDept() {
		List<EmpDept> listEmpDept = null;
		System.out.println("EmpDaoImpl listEmpDept Start...");
		try {
			listEmpDept = session.selectList("tkListEmpDept");
			System.out.println("EmpDaoImpl listEmpDept listEmpDept size()-> "+listEmpDept.size());
		} catch (Exception e) {
			System.out.println("EmpDaoImpl listEmpDept e.getMessage()-> "+e.getMessage());
		}
		return listEmpDept;
	}

}
Console
 

 

 

 

 

 

 

 

 

Spring Starter Project oBootMybatis01
folder resources/mappers
xml EmpDept.xml 
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.oracle.oBootMyBatis01.EmpDeptMapper">
	<select id="tkListEmpDept" 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 empno
	</select>
	
	<select id="tkListEmpDept3" 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 empno
	</select>
	
	<select id="tkListEmpDept4" 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 empno
	</select>
	
	
</mapper>
Console
 

 

 - outerjoin 을 써야하는 이유 -> deptno가 null인 값들은 표현되지 않는다.

 

 - 따라서 outer join을 해주어야 한다. Left Outer Join ㅁ, right Outer Join ㅁ

    ㅁ은 emp에서 null값이 있어도 나오는 것 (FK 기준으로)

    ㅁ은 dept 에서 null 값이 있어도 나오는 것 (FK 기준으로)

 

    inner join / outer join (11:12 다시 듣기) -> 매우 중요

    Hash join, enl join?

SQL join, DBMS join ----> inner/outer join -----> left outer join / right outer join (★★★ )

 

left outer join : d와 연결되어있지 않은 e가 전부 나오는 것

 right outer join: e와 연결되어있지 않은 d가 전부 나오는 것

 

 

 -  foreign key를 앞에 둔 left outer join을 많이 한다.

 

 

 

 

Spring Starter Project oBootMybatis01
package com.oracle.oBootMybatis01.dao
interface DeptDao 
package com.oracle.oBootMybatis01.dao;

import java.util.HashMap;
import java.util.List;

import com.oracle.oBootMybatis01.model.Dept;
import com.oracle.oBootMybatis01.model.DeptVO;

public interface DeptDao {
	List<Dept> 	deptSelect();
	void 		insertDept(DeptVO deptVO);
	void 		selListDept(HashMap<String, Object> map);
}
Console
 

 

 

 

 

 

 

Spring Starter Project oBootMybatis01
package com.oracle.oBootMybatis01.dao
class DeptDaoImpl 
package com.oracle.oBootMybatis01.dao;

import java.util.HashMap;
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;

@Repository
@RequiredArgsConstructor
public class DeptDaoImpl implements DeptDao {
	//Mybatis DB 연동
	private final SqlSession session;
	
	@Override
	public List<Dept> deptSelect() {
		List<Dept> deptList = null;
		System.out.println("DeptDaoImpl deptSelect Start...");
		
		try {
			deptList = session.selectList("tkSelectDept");
		} catch (Exception e) {
			System.out.println("DeptDaoImpl deptSelect e.getMessage()-> "+e.getMessage());
		}
		return deptList;
	}

	@Override
	public void insertDept(DeptVO deptVO) {
		System.out.println("DeptDaoImpl insertDept Start....");
		session.selectOne("procDeptInsert", deptVO);
		
	}

	@Override
	public void selListDept(HashMap<String, Object> map) {
		System.out.println("DeptDaoImpl selListDept Start....");
//		ResultMap은 DB 컬럼명과 DTO의 변수 명이 다를 때 사용
		session.selectOne("procDeptList", map);
		
	}

}
Console
 

 

 

 

 

 



Spring Starter Project oBootMybatis01
folder resources/mappers
xml Dept.xml 
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.oracle.oBootMyBatis01.DeptMapper">

	<resultMap type="Dept" id="DeptResult">
		<result property="deptno" 	column="deptno"/>
		<result property="dname" 	column="dname"/>
		<result property="loc" 		column="loc"/>
	</resultMap>

	<!-- deptSelect -->
	<select id="tkSelectDept" resultType="Dept">
		SELECT *
		FROM dept
	</select>
	
	
	<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>
	
	<select id="procDeptList" parameterType="java.util.Map" statementType="CALLABLE">
		{
			call Dept_Cursor3(
								 #{sDeptno, mode=IN,  jdbcType=INTEGER}
								,#{eDeptno, mode=IN,  jdbcType=INTEGER}
								,#{dept,	mode=OUT, jdbcType=CURSOR
													, javaType=java.sql.ResultSet
													, resultMap=DeptResult}
								)
		}
	</select>
</mapper>
Console
 

 

 - procedure

oraCallEmpIn에서 확인해보기  (모르겠으면 확인)

   여기서 mode는 소문자로 써주어야 한다.

 

 - oracle SCOTT에 프로시져 해주기!!

CREATE OR REPLACE PROCEDURE Dept_Insert3
(vdeptno    IN dept.deptno%TYPE,
 vdname     IN dept.dname%TYPE,
 vloc       IN dept.loc%TYPE,
 p_deptno   OUT dept.deptno%TYPE,
 p_dname    OUT dept.dname%TYPE,
 p_loc      OUT dept.loc%TYPE)
IS
BEGIN
    INSERT INTO dept VALUES(vdeptno, vdname, vloc);
    COMMIT;
    
    DBMS_OUTPUT.ENABLE;
    -- %TYPE 데이터형 변수 사용
    SELECT  deptno, dname, loc
    INTO    p_deptno, p_dname, p_loc
    FROM    dept
    WHERE   deptno=vdeptno;
    
    --결과값 출력
    DBMS_OUTPUT.PUT_LINE('부서번호 : '||p_deptno);
    DBMS_OUTPUT.PUT_LINE('부서이름 : '||p_dname);
    DBMS_OUTPUT.PUT_LINE('부서위치 : '||p_loc);
END;

 

 - procedure로 받으면, Callable

   resultMap: ResultMap은 DB 컬럼명과 DTO의 변수 명이 다를 때 사용 

    

    SQL문으로 dept_cursor3 만들어주기

CREATE OR REPLACE PROCEDURE Dept_Cursor3
(sdeptno     IN dept.deptno%TYPE,
 edeptno     IN dept.deptno%TYPE,
 Dept_Cursor OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN Dept_Cursor
        For
            SELECT  deptno, dname, loc
            FROM    dept
            WHERE   deptno BETWEEN sdeptno AND edeptno;
END Dept_Cursor3;

 

 

 

Spring Starter Project oBootMybatis01
folder resources
xml configuration.xml 
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
	<typeAliases>
												<!-- model 에 있는 Emp class에서 복사해옴 -->
		<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>
Console
 

 

 - EmpDept를 configuration으로 받아주어야 한다.

 

 

 

 

 

 

'Spring' 카테고리의 다른 글

Day61 2024.08.19.월 #코딩일기  (0) 2024.08.19
Day60 2024.08.14.수 #코딩일기  (0) 2024.08.14
Day58 2024.08.12.월 #코딩일기  (0) 2024.08.12
Day57 2024.08.09.금 #코딩일기  (0) 2024.08.09
Day56 2024.08.08.목 #코딩일기  (0) 2024.08.08

관련글

티스토리툴바