20221110-75 myBatis 게시판(직원정보입력저장,아이디중복체크,회원목록삭제,직원부서조회,구글이용해메일보내기,부서입력저장

복습
한줄가져올떄는 selectOne
여러줄 가져올때는 selectList

xml 에서
하나일때는 int ,String
여러개일때는 Dto타입

session은 sql 임

단순하게 저장만하면
Date hiredate
날짜를 조작하고 싶으면 String으로 잡기
String hiredate

lmpl 구현체에서는 @Repository 꼭 걸어주기

namespace 환경에다가 mapper라고 지정
아이디위에 패키지 경로

---

🔽직원정보입력 저장

// Service, Dao , Mapper명[insertEmp] 까지 -> insert

EmpController

	//직원정보저장
	@PostMapping(value = "writeEmp")  
	public String writeEmp(Emp emp, Model model) {
	log.info("EmpController  writeEmp start. . . ");
    /// Service, Dao , Mapper명[insertEmp] 까지 -> insert
	//실패시 --> writeFormEmp
    int result = es.insertEmp(emp);
    if(result >0 ) return "redirect:listEmp";
    else {  model.addAttribute("msg", "입력실패 확인해 보세요");
		
		
	return "redirect:writeFormEmp";
		
	}

EmpService

int insertEmp(Emp emp);

EmpServiceImpl

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

EmpDao

  int insertEmp(Emp emp);
		

EmpDaoImpl

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

Emp.xml

 <insert id="insertEmp" parameterType="Emp" >
 		INSERT INTO emp 
 		VALUES ( #{empno}, #{ename}, #{job},#{mgr}, #{hiredate},  #{sal}, #{comm},#{deptno})
 
 </insert>

✅회원정보입력완

🔽아이디 중복체크

writeFormEmp.jsp

<script type="text/javascript">
	function chk(){
		if( !frm.empno.value) {
			alert("사번을 입력한 후에 확인하세요");
			frm.empno.focus();
			return false;
			
		}else location.href ="confirm?empno="+frm.empno.value;
		
		
	}
	


</script>

EmpController

//아이디 중복체크
	@GetMapping(value = "confirm")
	public String confirm (int empno, Model model) {
		Emp emp = es.detailEmp(empno);
		model.addAttribute("empno",empno);
		if(emp != null) {
			log.info( "EmpController confirm 중복된 사번. . .  ");
			model.addAttribute("msg" , "중복된 사번입니다");
			return "forward:writeFormEmp";
			
		}else {
			model.addAttribute("msg" , "사용가능한 사번입니다");
			log.info( "EmpController confirm 중복된 사번. . .  ");
			return "forward:writeFormEmp";
		}
	}

✅아이디 중복체크 완

🔽 회원목록삭제

// Controller -->  deleteEmp    1.parameter : empno
//     name -> Service, dao , mapper
// return -> listEmp

EmpController

@GetMapping(value = "deleteEmp")
	public String deleteEmp(int empno, Model model) {
		log.info("EmpController  deleteEmp start. . . ");
		int result = es.deleteEmp(empno);
		model.addAttribute("deleteEmp" ,result);
		
		return "redirect:listEmp";
	}

EmpService

int deleteEmp(int empno);

EmpServiceImpl

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

EmpDao

int deleteEmp(int empno);

EmpDaoImpl

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

Emp.xml

<delete id="deleteEmp" parameterType="int" >
     DELETE FROM emp
     WHERE empno = #{empno}
   
   </delete>

✅회원목록 삭제완

🔽 직원부서조회

EmpDept

package com.oracle.oBootMybatis01.model;

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;

}

listEmpDept.jsp

<h2>회원 부서 정보</h2>
	<a href="mailTransport">Mail Test</a>
	<table>
	 	<tr><th>사번</th><th>이름</th><th>업무</th><th>부서</th><th>근무지</th>
		<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>

EmpController

//직원부서조회
	@GetMapping(value = "listEmpDept")
	public String listEmpDept(Model model) {
		log.info("EmpController  dellistEmpDept start. . . ");
		// Service ,DAO -> listEmpDept
		// Mapper만 ->tkListEmpDept

		List<EmpDept>listEmpDept = es.listEmpDept();
		
		
		model.addAttribute("listEmpDept", listEmpDept);
		
		
		
	return "listEmpDept";	
	}
	

EmpService

List<EmpDept> listEmpDept();

EmpServiceImpl

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


		return empDept;
	}

EmpDao

	List<EmpDept> listEmpDept();

EmpDaoImpl

@Override
	public List<EmpDept> listEmpDept() {
		List<EmpDept> empDept =  null;
		
		empDept=session.selectList("tkListEmpDept");
		
		return empDept;
	}

EmpDept.xml

<select id="tkListEmpDept" parameterType="EmpDept" 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>

✅직원부서조회 완

🔽구글이용한 메일보내기

Mime ❓ 전자우편 Internet 표준 Format

application.yml(구글설정)

  # gmail Transfer 
  mail:
    host: smtp.gmail.com
    port: 587
    username: khj97041444@gmail.com
    password: swznfwdmcefkluje 
    properties:
      mail:
        smtp:
          auth: true
          starttls.enable: true

EmpController

	//메일전송 
	@RequestMapping(value = "mailTransport")
	public String mailTransport(HttpServletRequest request, Model model) {
	System.out.println("mailsending. . . .");
	String tomail = "gpdy0102@gmail.com";		//받는사람이메일
		System.out.println(tomail);
		String setfrom = "khj97041444@gmail.com";		//보내는사람메일(형식상)야물에 저장되어있음 
		String title = "mailTransport입니다 ";  			//제목
		
		//smtp는 mine을 사용함 ->다목적  
		// Mime 전자우편 Internet 표준 Format
		
		try {
			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);
			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) {
				System.out.println( " mailTransport e.getMessage()" +e.getMessage() );
				model.addAttribute("check", 2);  //메일전달실패
		}

		return "mailResult";
		
	}
	

mailResult.jsp

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

✅ 메일전송 완

PL/SQL

부서입력화면 (Procedure Test)

PL/SQL(부서입력)

EmpController

//Procedure Test 입력화면
	@RequestMapping(value = "/writeDeptIn")
	public String writeDeptIn(Model model) {
		System.out.println("/writeDeptIn start . . ");
		return "writeDept3";
	}

writeDept3

<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

package com.oracle.oBootMybatis01.model;

import lombok.Data;

@Data
public class DeptVo {
	//입력
	private int deptno;
	private String dname;
	private String loc;
	 
	
	
	//출력
	private int odeptno;
	private String odname;
	private String oloc;
	
	
}

부서입력 저장

//Procedure 를 통한 Dept 입력후 vo 전달

EmpController

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

EmpService

```java

void insertDept(DeptVO deptVO);

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

DeptDao

```java
void insertDept(DeptVO deptVO);

##   DeptDaoImpl
```java
	@Override
	public void insertDept(DeptVO deptVO) {
		System.out.println("DeptDaoImpl insertDept start. . . ");
		session.selectOne("ProcDeptInsert", deptVO);
		
	}

Dept.xml

<select id="ProcDeptInsert" parameterType="DeptVO" statementType="CALLABLE">
  		{
  			call Dept_Insert3(
  			
  			 #{deptno,       mode = IN   , jdbcType=INTEGER}
  			 , #{dname,      mode = IN   , jdbcType=INTEGER}
  			 , #{loc,    		mode = IN   , jdbcType=INTEGER}
  			 , #{odeptno,    mode = OUT   , jdbcType=INTEGER}
  			 , #{odname,    mode = OUT   , jdbcType=INTEGER}
  			 , #{oloc,    	   	mode = OUT   , jdbcType=INTEGER}
  			 
  			
  			)		
  		
  
  
  }
  </select>

✅부서입력 저장 완