본문 바로가기
Spring

2024_08_13_화

by 알케니브 2024. 8. 13.

오늘의 코딩순서

(폴더: oBootMybatis01)

2. 직원부서조회(Join/Mail 전송)

 

1. 기본 틀

  • index.html + EmpDept.class

2. 페이징 작업 - 메인 페이지 + List

  • listEmpDept.jsp 
    + EmpController.class + EmpService.interface + EmpServiceImpl.class + EmpDao.interface+ EmpDaoImpl.class + EmpDept.xml(신규작성) + configuration.xml(추가)

3. 메일 보내기

  •  application.yml + mailResult.jsp + EmpController.class

3. PL/SQL 연동 - 부서입력

==> return 값 없이, call by reference로 값 전해주기

  • writeDept.jsp + DeptVO.class
    + EmpController.class + EmpService.interface + EmpServiceImpl.class
    + Dept Dao.interface + DeptDaoImpl.class + Dept.xml + configuration.xml
    + Dept_Insert3.sql

4. PL/SQL 연동 - 부서조회

==> parameter를 DTO 방식이 아닌, Map 방식으로 넘겨주기

  • writeDeptCursor.jsp
    + EmpController.class + EmpService.interface + EmpServiceImpl.class
    + Dept Dao.interface + DeptDaoImpl.class + Dept.xml 
    + Dept_Insert3.sql

오늘의 코딩 포인트

2. 직원부서조회(Join/Mail 전송)

1. 기본 틀

  • index.html
<!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>
  • EmpDept.class => Emp TBL과 Dept TBL을 Join하는 목적의 DTO
package com.oracle.oBootMybatis01.model;

import lombok.Data;

// Emp TBL과 Dept TBL을 Join하는 목적의 DTO
@Data
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;

}

2. 페이징 작업 - 메인 페이지 + List

  • listEmpDept.jsp
    Tip)
    • SMTP( Simple Mail Transfer Protocol)
      • 인터넷을 통해 이메일 메시지를 보내고 받는 데 사용되는 통신 표준 프로토콜
      • 이메일을 송수신하는 서버를 SMTP 서버라고 함
      • 중계전달자 역할을 함으로써 메일 서버 간의 송수신 뿐만 아니라 메일 클라이언트에서 서버로 보낼 때 사용되는 프로토콜
      • 인터넷에서 이메일을 교환할 때 그 과정을 정렬해줌
<%@ 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>
		<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>
			</c:forEach>
		</table>
</body>
</html>
  • EmpController.class 
package com.oracle.oBootMybatis01.controller;

import java.util.List;

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.Emp;
import com.oracle.oBootMybatis01.model.EmpDept;
import com.oracle.oBootMybatis01.service.EmpService;
import com.oracle.oBootMybatis01.service.Paging;

import jakarta.validation.Valid;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Controller
@RequiredArgsConstructor
@Slf4j
public class EmpController {
	
	private final EmpService es;

// << 2.직원부서조회=> Emp TBL과 Dept TBL을 Join >>
	
	// (0813) 현장 HW 1-1
	// Service ,DAO -> listEmpDept
	//  Mapper만 ->EmpDept.xml(tkListEmpDept)
	
	@GetMapping(value = "listEmpDept")
	public String listEmpDept(Model model) {
		System.out.println("EmpController listEmpDept Start...");
		
		
		List<EmpDept> listEmpDept = es.listEmpDept();
		model.addAttribute("listEmpDept", listEmpDept);
		
		return "listEmpDept";
	}
	
}
  • EmpService.interface
package com.oracle.oBootMybatis01.service;

import java.util.List;

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

public interface EmpService {
	
	// (0813) 현장 HW 1-2
	List<EmpDept> listEmpDept();
}
  • EmpServiceImpl.class
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;

@Service
@RequiredArgsConstructor
public class EmpServiceImpl implements EmpService {
	
	private final EmpDao ed;
	private final DeptDao dd;
		// 서로 다른 Interface(DAO)를 받은 것이기 때문에 두 개 이상 가능 

// << 2.직원부서조회=> Emp TBL과 Dept TBL을 Join >>
	
	// (0813) 현장 HW 1-3
	@Override
	public List<EmpDept> listEmpDept() {
		List<EmpDept> empDeptList = null;
		System.out.println("EmpServiceImpl listEmpDept Start...");
		
		empDeptList = ed.listEmpDept();
		System.out.println("EmpServiceImpl listEmpDept empDeptList->"+empDeptList.size());
		
		return empDeptList;
	}

}
  • EmpDao.interface
package com.oracle.oBootMybatis01.dao;

import java.util.List;

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

public interface EmpDao {

	// (0813) 현장 HW 1-4
	List<EmpDept> 	listEmpDept();	
	
	
}
  • EmpDaoImpl.class
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
@RequiredArgsConstructor
public class EmpDaoImpl implements EmpDao {
	// Mybatis DB 연동 
	private final SqlSession session;

// << 2.직원부서조회=> Emp TBL과 Dept TBL을 Join >>
	// (0813) 현장 HW 1-5
		
	@Override
	public List<EmpDept> listEmpDept() {
		System.out.println("EmpDaoImpl listEmpDept Start...");
		
		List<EmpDept> empDept = null;
		try {
			empDept = session.selectList("tkListEmpDept");
			System.out.println("EmpDaoImpl listEmpDept empDept.size()->"+empDept.size());
		
		} catch (Exception e) {
			System.out.println("EmpDaoImpl delete Exception->"+e.getMessage());
		}
		return empDept;
	}

}
  • 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, d.deptno, d.loc
			FROM	emp e,	dept d
			WHERE	e.deptno=d.deptno
			ORDER BY empno
	</select>

</mapper>
  • configuration.xml (Left Outer Join) (이걸로 사용하기
<?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, d.deptno, d.loc
			FROM	emp e,	dept d
			WHERE	e.deptno=d.deptno(+)
			ORDER BY empno
	</select>
	
</mapper>

 

 

+++ OUTER JOIN 활용방법 => 0627 일지 참고

  • configuration.xml (Join 없는 버전)
<?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="tkListEmpDept3" resultType="EmpDept">
			SELECT	e.empno, e.ename, e.job, d.deptno, d.loc
			FROM	emp e,	dept d
			WHERE	e.deptno=d.deptno
			ORDER BY empno
	</select>
	
</mapper>
  • configuration.xml (Right Outer Join) 
<?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="tkListEmpDept4" resultType="EmpDept">
			SELECT	e.empno, e.ename, e.job, d.deptno, d.loc
			FROM	emp e,	dept d
			WHERE	e.deptno(+)=d.deptno
			ORDER BY empno
	</select>
	
</mapper>

3. 메일 보내기

server:
  port: 8387
# Oracle Connect
spring:
  datasource:
    driver-class-name: oracle.jdbc.OracleDriver
    url: jdbc:oracle:thin:@localhost:1521/xe
    username: scott
    password: tiger
    
  #JPA Setting
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: update

  # View Resolver
  mvc:
    view:
      prefix: /WEB-INF/views/
      suffix: .jsp    
      
  # gmail Transfer
  mail:
    host: smtp.gmail.com
    port: 587
    username: rollout147@gmail.com 
    password: bbwt lmdi vhew burk
    properties:
      mail:
        smtp:
          auth: true
          starttls.enable: true    
      

  # Mybatis
mybatis:
  config-location: classpath:configuration.xml
  mapper-locations: classpath:mappers/*.xml
  • 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}">메일전송에 실패했습니다</c:if>
</body>
</html>
  • EmpController.class
    Tip)
    • Mime(Multipurpose intenet mail extension): 전자 우편을 위한 인터넷 표준 포맷
package com.oracle.oBootMybatis01.controller;

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.Emp;
import com.oracle.oBootMybatis01.model.EmpDept;
import com.oracle.oBootMybatis01.service.EmpService;
import com.oracle.oBootMybatis01.service.Paging;

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

@Controller
@RequiredArgsConstructor
@Slf4j
public class EmpController {
	
	private final EmpService es;
	private final JavaMailSender mailSender;

// 2.직원부서조회 => Mail 보내기
	@RequestMapping(value="mailTransport")
	public String mailTransport(HttpServletRequest request, Model model) {
		System.out.println("mailSending...");
		
		String tomail = "rollout147@naver.com";		// 받는 사람 이메일
		System.out.println(tomail);
		
		String setfrom = "rollout147@gmail.com";	// 보내는 사람 이메일
		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() * 999999) + 1 + "";
			messageHelper.setText("임시 비밀번호입니다 : " + tempPassword); // 메일내용
			System.out.println("임시 비밀번호입니다 : " + tempPassword);
			
			mailSender.send(message);
			model.addAttribute("check",1);	//정상 전달
			
			// DB 로직
			
		} catch (Exception e) {
			System.out.println("mailTransport e.getMessage()->"+e.getMessage());
			
			model.addAttribute("check", 2);	// 메일 전달 실패
		}
		return "mailResult";
	}
}

 

3. PL/SQL 연동 - 부서입력

  Tip) return 값 없이, call by reference로 값 전해주기

  • 스프링에서 Call by Reference를 이용하면 메서드 내부에서 객체의 상태를 변경하고, 그 변경된 상태를 반환하지 않고도 메서드 외부에서 확인할 수 있음
    ==> 이는 객체의 참조가 메서드에 전달되기 때문에 가능한 것이며, Java의 객체 다루기 방식에 기반한 자연스러운 동작
  • Java에서 객체(클래스의 인스턴스) 타입의 경우, 메서드 호출 시 객체의 **참조값(Reference)**이 복사되어 전달됨.
    즉, 메서드 내부에서 객체의 속성(필드)을 변경하면 그 변화가 메서드를 호출한 쪽에서도 반영된다는 뜻임
  • writeDept.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>
  • DeptVO.class
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;
	
}
  • EmpController.class
package com.oracle.oBootMybatis01.controller;

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 jakarta.mail.internet.MimeMessage;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.validation.Valid;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Controller
@RequiredArgsConstructor
@Slf4j
public class EmpController {
	
	private final EmpService es;
	private final JavaMailSender mailSender;
    
    // PL/SQL 연동
	// 1. Procedure Test 입력화면
	@RequestMapping(value = "writeDeptIn")
	public String writeDeptIn(Model model) {
		System.out.println("writeDeptIn Start...");
	
		return "writeDept3";
	}
	
	// 2. Procedure를 통해 Dept 입력 후 VO 전달
	@RequestMapping(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";
	}
  • EmpService.interface
package com.oracle.oBootMybatis01.service;

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 {
	
	// (0813) 현장 HW 1-2. Emp TBL과 Dept TBL을 Join
	List<EmpDept> listEmpDept();
	// PL/SQL 연동
	void 		insertDept(DeptVO deptVO);
}
  • EmpServiceImpl.class
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;

@Service
@RequiredArgsConstructor
public class EmpServiceImpl implements EmpService {
	
	private final EmpDao ed;
	private final DeptDao dd;
		// 서로 다른 Interface(DAO)를 받은 것이기 때문에 두 개 이상 가능 

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

}
  • DeptDao.interface
package com.oracle.oBootMybatis01.dao;

import java.util.List;

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

public interface DeptDao {

	// Emp TBL과 Dept TBL을 Join
	List<Dept> 		deptSelect();

	// PL/SQL 연동
	void 			insertDept(DeptVO deptVO);

}
  • DeptDaoImpl.class 
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;

@Repository
@RequiredArgsConstructor
public class DeptDaoImpl implements DeptDao {
	// Mybatis 연동
	private final SqlSession session;
	
	// 2.직원부서조회
	// PL/SQL 연동
	@Override
	public void insertDept(DeptVO deptVO) {
		System.out.println("DeptDaoImpl insertDept Start...");
		session.selectOne("procDeptInsert", deptVO);
		
	}

}
  • Dept.xml
    Tip)     mode와 jdbcType의 대소문자는 꼭 이대로 써야함
    • statementType
<?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">

	<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>
  • 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>
  • Dept_Insert3.sql ==> 프로시저
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
;

4. PL/SQL 연동 - 부서조회

==> parameter를 DTO 방식이 아닌, Map 방식으로 넘겨주기

   ∴ configuration.xml이 필요없음

 

항목 Map 방식 DTO 방식
정의    
parameter 형식 random으로 자유롭게 정의 - Table 기준으로 선언함
- 필요시 참조 Column도 입력 가능
언제 사용? 1. 규정을 정하기 힘들 때
2. 팀원과의 소통이 원활하지 않을 때
(ex. 다른 회사팀과의 협력)
3. 사용 빈도 낮음		 1. 규정이 있을 때 기준에 의거하여 사용함
2. 사용 빈도 높음
장점 1. 개발시간이 짧음
2. 필요 데이터를 여러 DTO에 나눠 담을 때는 각각의 VO에 담아서 전부 전달하기 어려우므로,
Map에 필요한 데이터만 담아서 전달함
3. 단일 Key값을 갖는 경우에는 Controller에서 Collections.singletonMap로 응답을 반환하기도 함
4. 밑의 단점이 부각되지 않는 상황이라면 오히려 좋아지는 케이스도 있음		 1. 명확하여 유지보수가 쉬움(추천↑↑)⭐  
2. 추가적으로 정적 팩토리 메소드를 구현할 수 있음
3. 빌더 패턴을 적용할 수 있어서 유용함
단점 1. 유지보수가 어려움
2. 직관성이 떨어짐
3. 안정성이 떨어짐
4. 코드 가독성이 떨어짐
5. 타입캐스팅을 필요로 하여 비용이 증가함
6. 컴파일 에러를 유발할 수 없음
7. String 텍스트를 Key로 사용함
8. 불변성을 확보할 수 없음		 개발시간이 김
  • 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>
  • EmpController.class
    Tip)
    • parameter를 DTO로 넘겨주는 방식과 Map 방식으로 넘겨주는 방식의 차이⭐⭐
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 jakarta.mail.internet.MimeMessage;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.validation.Valid;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Controller
@RequiredArgsConstructor
@Slf4j
public class EmpController {
	
	private final EmpService es;
	private final JavaMailSender mailSender;
	

	// PL/SQL 연동 후 부서 조회
	// DTO방식이 아닌, 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);
	
		es.selListDept(map);
       	 //DAO의 map이 이 map로 넘어감
        
		List<Dept> deptLists = (List<Dept>) map.get("dept");
        	// Dept.xml의 #{dept 임
		for(Dept dept : deptLists) {
			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";	
	}
}
  • EmpService.interface
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 {
	
	// (0813) 현장 HW 1-2. Emp TBL과 Dept TBL을 Join
	List<EmpDept> listEmpDept();
	// PL/SQL 연동
	void 		insertDept(DeptVO deptVO);
	void 		selListDept(HashMap<String, Object> map);
}
  • EmpServiceImpl.class
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 {
	
	private final EmpDao ed;
	private final DeptDao dd;
		// 서로 다른 Interface(DAO)를 받은 것이기 때문에 두 개 이상 가능 

	
	// PL/SQL 연동 후 부서 조회
	@Override
	public void selListDept(HashMap<String, Object> map) {
		System.out.println("EmpServiceImpl selListDept Start...");
		dd.selListDept(map);
	}
	

}
  • Dept Dao.interface
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 {

	// Emp TBL과 Dept TBL을 Join
	List<Dept> 		deptSelect();

	// PL/SQL 연동
	void 			insertDept(DeptVO deptVO);

	void 			selListDept(HashMap<String, Object> map);

}
  • DeptDaoImpl.class
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 연동
	private final SqlSession session;
	
	// PL/SQL 연동 후 부서 조회
	@Override
	public void selListDept(HashMap<String, Object> map) {
		System.out.println("DeptDaoImpl selListDept Start...");
		
        //ResultMap은 DB 컬럼명과 DTO의 변수 명이 다를 때 사용
		session.selectOne("procDeptList", map);
        	//xml의 dept값이 map에 들어감
	}

}
  • Dept.xml
    Tip) 
    • resultMap
<?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>
	
	<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>
  • Dept_Cursor3.sql 
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' 카테고리의 다른 글

2024_08_19_월~08_20_화  (0) 2024.08.19
2024_08_14_수  (0) 2024.08.14
2024_08_12_월  (0) 2024.08.12
2024_08_08_목~08_12_월  (0) 2024.08.08
2024_08_07_수~08_08_목  (0) 2024.08.07

관련글

티스토리툴바