국비학원_80일차(myBatis, Ajax, json)

써니·2022년 11월 15일
MyBatisSpringajaxjson
0

spring

목록 보기
22/23

myBatis

🔎페이지 검색

📂 views

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

[com.oracle.oBootMybatis01.controller]

  • EmpController.java
	/* 조회 및 검색 */
	@RequestMapping(value = "listSearch3")
	public String listSearch3(Emp emp, String currentPage, Model model) {
		log.info("listSearch3 Start...");
		// Emp 전체 count 25
		int totalEmp = es.totalEmp();
		log.info("listSearch3 totalEmp=>{}", totalEmp);
		
		// Paging 작업
		Paging page = new Paging(totalEmp, currentPage);
		// Parameter emp --> page만 추가 Setting(페이징)
		emp.setStart(page.getStart()); // 시작시 1
		emp.setEnd(page.getEnd());	   // 시작시 10
		
		List<Emp> listSearchEmp = es.listSearchEmp(emp);
		log.info("listSearchEmp.size()->{}",listSearchEmp.size());
		
		model.addAttribute("totalEmp", totalEmp);
		model.addAttribute("listEmp", listSearchEmp);
		model.addAttribute("page", page);
		return "list";
	}

[com.oracle.oBootMybatis01.service]

  • EmpServiceImpl.java
	/* 검색기능 */
	@Override
	public List<Emp> listSearchEmp(Emp emp) {
		List<Emp> empSearchList = null;
		log.info("listSearchEmp Start...");
		empSearchList = ed.empSearchList3(emp);
		log.info("listSearchEmp empSearchList.size()->{}",empSearchList.size());
		return empSearchList;
	}

[com.oracle.oBootMybatis01.dao]

  • EmpDaoImpl.java
	@Override
	public List<Emp> empSearchList3(Emp emp) {
		log.info("empSearchList3 Start...");
		List<Emp> empSearchList3 = null;
		try {
			// keyword 검색
			// Naming Rule
			empSearchList3 = session.selectList("tkEmpSearchList3", emp);
		} catch (Exception e) {
			log.info("empSearchList3 Exception->{}",e.getMessage());
		}
		return empSearchList3;
	}

📂 mappers

  • Emp.xml
	<!-- 직원리스트  검색 -->
	<select id="tkEmpSearchList3" parameterType="Emp" resultType="Emp">
		select *
		from 
		(
		        select rownum rn, a.*
		        from
		        (select * from emp order by empno) a
		)
		where rn BETWEEN #{start} and #{end}
		<choose>
			<when test="search == 's_job'">
				AND job like '%' || #{keyword} || '%'
			</when>
			<when test="search == 's_ename'">
				AND ename like '%' || #{keyword} || '%'
			</when>
			<!-- 기본 선택 -->
			<otherwise>
				AND job like '%'
			</otherwise>
		</choose>
	</select>



🚫myBatis 인식 오류

myBatis는 >=, <= 인식에 오류가 있기 때문 를 걸어서 코드로 인식하게 한다
또한 페이징 수 설정을 하지 않아 공갈 페이지가 생성 되었기 때문에 검색한 갯수의 수도 따로 코딩해야한다.


📂 mappers

  • Emp.xml
	<select id="tkEmpListAll2" 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="tkEmpListAll" parameterType="Emp" resultType="Emp">
		<![CDATA[
		select *
		from 
		(
		        select rownum rn, a.*
		        from
		        (select * from emp order by empno) a
		)
		where rn >= #{start} 
		and   rn <= #{end}
		]]>
	</select>


Ajax

Json

[com.oracle.oBootMybatis01.controller]

  • EmpRestController.java
	@RequestMapping("/sample/sendVO2")
	public SampleVO sendVO2(int deptno) {
		log.info("deptno->{}",deptno);
		SampleVO vo = new SampleVO();
		vo.setFirstName("길동");
		vo.setLastName("홍");
		vo.setMno(deptno);
		// 객체 -> Json Converter
		return vo;
	}

📂 views

  • listEmpAjaxForm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>
<!DOCTYPE html>
<%
	String context = request.getContextPath();
%>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript" src="js/jquery.js"></script>
<script type="text/javascript">

	function getDeptName(vDeptno) {
		console.log(vDeptno);
		alert("vDeptno->"+vDeptno);
		// 행동강령: Ajax로 부서번호 보내고 부서명 받음
		$.ajax(
				{
					url:"<%=context%>/getDeptName",
					data:{deptno : vDeptno},
					dataType:'text',
					success:function(data){
						alert("success ajax Data -> "+data);
						$('#deptName').val(data);	/* input Tag */
						$('#msg').html(data);		/* span id Tag */
					}
				}
		);
	}
	
	// RestController
	// Deptno -> deptName 조건은 객체로 가져옴
	function getDept(vDeptno) {
		alert("vDeptno->"+vDeptno);
		$.ajax(
				{
					url:"sample/sendVO2",
					data:{deptno : vDeptno},
					dataType:'json',	// 데이터를 그대로 갖고 오기 때문에 json
					success:function(data){
						resultStr = data.firstName + " " + data.lastName + " " + data.mno;
						alert("ajax getDept resultStr->"+resultStr);
						$('#RestDept').val(resultStr); 	// input태그 -> val
					}
				}		
		
		);
	}

</script>
</head>
<body>
	<h2>회원 정보</h2>
	<table>
		<tr><th>사번</th><th>이름</th><th>업무</th><th>부서</th><th>근무지</th></tr>
		<c:forEach var="emp" items="${listEmp}">
			<tr><td>${emp.empno }</td><td>${emp.ename }</td>
				<td>${emp.job }</td>
				<td>${emp.deptno} 
				    <input type="button" id="btn_idCheck" value="부서명" onmouseover="getDeptName(${emp.deptno })">
				</td>
				<td>${empDept.loc }</td>
			</tr>
		</c:forEach>
	</table>
	deptName:  <input type="text" id="deptName"  readonly="readonly"><p>
    Message :  <span id="msg"></span><p>
    
    RESTController sendVO2 : <input type="text" id="RestDept" readonly="readonly">
    RESTController sendVO2 : sendVO2<input type="button" id="btn_Dept" value="부서명" onclick="getDept(10)"><p>
</body>
</html>


객체리스트

[com.oracle.oBootMybatis01.controller]

  • EmpController.java
	@RequestMapping(value = "listEmpAjaxForm2")
	public String listEmpAjaxForm2(Model model) {
		log.info("listEmpAjaxForm2 Start...");
		Emp emp = new Emp();
		// Parameter emp --> page만 추가 Setting(페이징)
		emp.setStart(1); // 시작시 1
		emp.setEnd(15);	   // 시작시 10
		List<Emp> listEmp = es.listEmp(emp);
		model.addAttribute("listEmp", listEmp);
		
		return "listEmpAjaxForm2";
	}

📂 views

  • listEmpAjaxForm2.jsp

멀티 Row -> varStatus="status"

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script  src="http://code.jquery.com/jquery-latest.min.js"></script>
</head>
<body>
	<h2>회원 정보</h2>
	<table>
	<tr><th>번호</th><th>사번</th><th>이름</th><th>업무</th><th>부서</th><th>근무지</th></tr>
	<c:forEach var="emp" items="${listEmp}" varStatus="status">
		<tr id="emp${status.index}"><td>emp${status.index}</td>
		    <td><input type="text" id="empno${status.index}" value="${emp.empno }">${emp.empno }</td>
		    <td><input type="text" id="ename${status.index}" value="${emp.ename }">${empt.ename }</td>
			<td>${emp.job }</td><td>${emp.deptno } 
			    <input type="button" id="btn_idCheck2" value="부서Row Delete" onclick="getDeptDelete(${status.index})">
			</td>
			<%-- <td>${empDept.loc }</td> --%>
		</tr>
	</c:forEach>
</table>
	Dept_list:	<div id="Dept_list"></div>

	Dept_list3:
	<div id="Dept_list3">

	</div>
</body>
</html>

💻여기까지 결과화면



select로 만들기

JSON

stringify(): 객체를 매개변수로서 수용하고, JSON 문자열 형태로 변환합니다.


📂 views

  • listEmpAjaxForm2.jsp

멀티 Row -> varStatus="status"

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script  src="http://code.jquery.com/jquery-latest.min.js"></script>
<script type="text/javascript">
	function getListDept() {
		var str = "";
		var str2 = "";
		console.log("getListDept Run");
		alert("getListDept Run...");
		$.ajax(
				{
					url:"/sendVO3",
					dataType:'json',
					success:function(data){
						var jsonStr = JSON.stringify(data);
						alert("jsonStr->"+jsonStr);
						$('#dept_list_str').append(jsonStr);
						str += "<select name = 'dept'>"
						$(data).each(
							function(){
								str2 = "<option value='"+this.deptno+"'> "+this.dname+"</option>";
								str += str2;
							}		
						)
						str += "</select></p>"
						$('#dept_list_combobox').append(str);
					}
				}		
		
		);	
		
	}

</script>
</head>
<body>
	<h2>회원 정보</h2>
	<table>
	<tr><th>번호</th><th>사번</th><th>이름</th><th>업무</th><th>부서</th><th>근무지</th></tr>
	<c:forEach var="emp" items="${listEmp}" varStatus="status">
		<tr id="emp${status.index}"><td>emp${status.index}</td>
		    <td><input type="text" id="empno${status.index}" value="${emp.empno }">${emp.empno }</td>
		    <td><input type="text" id="ename${status.index}" value="${emp.ename }">${empt.ename }</td>
			<td>${emp.job }</td><td>${emp.deptno } 
			    <input type="button" id="btn_idCheck2" value="부서Row Delete" onclick="getDeptDelete(${status.index})">
			</td>
			<%-- <td>${empDept.loc }</td> --%>
		</tr>
	</c:forEach>
</table>

	RestController LISTVO3: <input type="button" id="btn_Dept3" value="부서명 LIST"  onclick="getListDept()"><p>
	Dept_list:	<div id="dept_list_str"></div>

	Dept_list3:
	<div id="dept_list_combobox">

	</div>
</body>
</html>

💻여기까지 결과화면



JSON 삭제

[com.oracle.oBootMybatis01.controller]

  • EmpRestController.java
	@RequestMapping("/empnoDelete")
	public int empnoDelete(int empno) {
		log.info("empnoDelete Start...");
		int result = 0;
		result = es.deleteEmp(empno);
		return result;
	}

📂 views

  • listEmpAjaxForm2.jsp

    location.replace("listEmpAjaxForm2"); 삭제 후 새로고침

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script  src="http://code.jquery.com/jquery-latest.min.js"></script>
<script type="text/javascript">
	function getListDept() {
		var str = "";
		var str2 = "";
		console.log("getListDept Run");
		alert("getListDept Run...");
		$.ajax(
				{
					url:"/sendVO3",
					dataType:'json',
					success:function(data){
						var jsonStr = JSON.stringify(data);
						alert("jsonStr->"+jsonStr);
						$('#dept_list_str').append(jsonStr);
						str += "<select name = 'dept'>"
						$(data).each(
							function(){
								str2 = "<option value='"+this.deptno+"'> "+this.dname+"</option>";
								str += str2;
							}		
						)
						str += "</select></p>"
						$('#dept_list_combobox').append(str);
					}
				}		
		
		);	
	}
	
	function getDeptDelete(vIndex) {
		var selEmpno = $("#empno"+vIndex).val();
		var selEname = $("#ename"+vIndex).val()
		alert("getDeptDelete selEmpno->"+selEmpno);
		alert("getDeptDelete selEname->"+selEname);
		// Server --> /empnoDelete(EmpRestController)
		//            service :  deleteEmp
		// Parameter --> empno : selEmpno , ename : selEname
		// 성공             --> 해당 라인 삭제
		$.ajax(
				{
					url:"/empnoDelete",
					data:{empno : selEmpno},
					dataType:'json',
					success:function(data){
						alert("data->"+data);
						if(data == 1){
							alert("삭제 성공");
							location.replace("listEmpAjaxForm2");
						}
						else{
							alert("삭제실패");
						}
					}
				}
		
		);
		
	}

</script>
</head>
<body>
	<h2>회원 정보</h2>
	<table>
	<tr><th>번호</th><th>사번</th><th>이름</th><th>업무</th><th>부서</th><th>근무지</th></tr>
	<c:forEach var="emp" items="${listEmp}" varStatus="status">
		<tr id="emp${status.index}"><td>emp${status.index}</td>
		    <td>
		    <input type="hidden" id="deptno${status.index}" value="${emp.deptno}">
		    <input type="text" id="empno${status.index}" value="${emp.empno }">${emp.empno }
		    </td>
		    <td>
		    <input type="text" id="ename${status.index}" value="${emp.ename }">${empt.ename }
		    </td>
			<td>${emp.job }</td><td>${emp.deptno } 
			    <input type="button" id="btn_idCheck2" value="사원Row Delete" onclick="getDeptDelete(${status.index})">
			</td>
			<%-- <td>${empDept.loc }</td> --%>
		</tr>
	</c:forEach>
</table>

	RestController LISTVO3: <input type="button" id="btn_Dept3" value="부서명 LIST"  onclick="getListDept()"><p>
	Dept_list:	<div id="dept_list_str"></div>

	Dept_list3:
	<div id="dept_list_combobox">

	</div>
</body>
</html>



💬채팅

WebSocket

  • build.gradle --> 추가
implementation 'com.googlecode.json-simple:json-simple:1.1.1'

[com.oracle.oBootMybatis01.configuration]

  • WebSocketConfig.java
package com.oracle.oBootMybatis01.configuration;

import org.springframework.web.socket.config.annotation.WebSocketConfigurer;
import org.springframework.web.socket.config.annotation.WebSocketHandlerRegistry;

public class WebSocketConfig implements WebSocketConfigurer {

	@Override
	public void registerWebSocketHandlers(WebSocketHandlerRegistry registry) {
		// TODO Auto-generated method stub

	}

}

[com.oracle.oBootMybatis01.handler]

  • SocketHandler.java
package com.oracle.oBootMybatis01.handler;

import java.util.HashMap;

import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
import org.json.simple.parser.ParseException;
import org.springframework.stereotype.Component;
import org.springframework.web.socket.CloseStatus;
import org.springframework.web.socket.TextMessage;
import org.springframework.web.socket.WebSocketSession;
import org.springframework.web.socket.handler.TextWebSocketHandler;

import lombok.extern.slf4j.Slf4j;

@Slf4j
@Component
public class SocketHandler extends TextWebSocketHandler {
	
	HashMap<String, WebSocketSession> sessionMap = new HashMap<>();
	
	// 메소드는 메시지를 수신하면 실행
	@Override
	protected void handleTextMessage(WebSocketSession session, TextMessage message) throws Exception {
		// TODO Auto-generated method stub
		super.handleTextMessage(session, message);
	}
	
	// 웹소켓 연결이 되면 동작
	@Override
	public void afterConnectionEstablished(WebSocketSession session) throws Exception {
		// TODO Auto-generated method stub
		super.afterConnectionEstablished(session);
	}
	
	// 웹소켓이 종료되면 동작
	@Override
	public void afterConnectionClosed(WebSocketSession session, CloseStatus status) throws Exception {
		// TODO Auto-generated method stub
		super.afterConnectionClosed(session, status);
	}
	
	// handleTextMessage 메소드 에 JSON파일이 들어오면 파싱해주는 함수를 추가
	private static JSONObject jsonToObjectParser(String jsonStr) {
		JSONParser parser = new JSONParser();
		JSONObject jsonObj = null;
		try {
			jsonObj = (JSONObject) parser.parse(jsonStr);
		} catch (ParseException e) {
			log.info("ParseException->{}",e.getMessage());
		}
		return jsonObj;
	}
}
profile
써니
이전 포스트

국비학원_79일차(이미지 업로드, AOP, JPA, Ajax)

다음 포스트

국비학원_81일차(채팅, Socket)

0개의 댓글