국비 TIL(Today I Learned) 20220805 오라클에서 divs 데이터 가져오기 실습

TIL/academy

국비 TIL(Today I Learned) 20220805 오라클에서 divs 데이터 가져오기 실습

토희 2022. 8. 5. 16:34

728x90

어제 manager 수정, 삭제기능 추가할때

강사님은

managerDetail.jsp부분에

버튼을 하나로 묶기 위해 div로 감싸서 btnWrap id주고, loc란 속성 준다음

위에 버튼 나눈거를 하나로 묶음

managerDetail.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript" 
		src="resources/script/jquery/jquery-1.12.4.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
	/*
	$("#listBtn").on("click", function() {
		$("#actionForm").attr("action","managerList"); 
		$("#actionForm").submit();
	});
	
	$("#deleteBtn").on("click", function () {
		if(confirm("삭제하시곘습니까?")){
			$("#actionForm").attr("action","managerRes"); 
			$("#actionForm").submit();
		}
	})
	
	$("#updateBtn").on("click", function () {
		$("#actionForm").attr("action","managerUpdate");
		$("#actionForm").submit();
	});
	*/
	
	$("#btnWrap").on("click", "input", function(){
		if($(this).attr("id") != "deleteBtn" ||
			($(this).attr("id") == "deleteBtn" && confirm("삭제하시곘습니까?"))){
			$("#actionForm").attr("action", $(this).attr("loc"));
			$("#actionForm").submit();
		}
	})
});

</script>
</head>
<body>
<form action="#" id="actionForm" method="post">
	<input type="hidden" name="gbn" value="d"> <!-- selRes는 gbn을 받게 되어있어서 값을 추가해줘야함 -->
	<input type="hidden" name="no" value="${data.EMP_NO}">
</form>	
사번: ${data.EMP_NO}<br/>
이름: ${data.NAME}<br/>
부서: ${data.DEPT}<br/>
<div id="btnWrap">
	<input type="button" value="수정" id="updateBtn" loc="managerUpdate" /> <!-- loc 없는 속성 내가 필요해서 달아준거 -->
	<input type="button" value="삭제" id="deleteBtn" loc="managerRes"/>
	<input type="button" value="목록" id="listBtn" loc="managerList" />
</div>
</body>
</html>

위에꺼 강사님이 불어주고

매니저 부분은 위에 Detail만 바꿨으니 전체 코드를 안가져올께, 어제꺼 참고

그리고 실습으로 또 divs 테이블 가져오기

주의점, 어제 했던건 번호를 오라클에서 시퀀스로 했는데, 오늘은 데이터가

이렇게 되어있어서, 쿼리문 작성할때 한번 생각해줘야함

divsList.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style type="text/css">
table{
	border-collapse: collapse;
}

th, td {
	border: 1px solid #000;
	padding: 5px;
}

</style>
<script type="text/javascript" src="resources/script/jquery/jquery-1.12.4.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
	$("tbody").on("click", "tr", function () {
		console.log($(this).attr("no").substring(0, 1));
		// $("#e").val($(this).attr("no").substring(0, 1));
		$("#no").val($(this).attr("no"));
		
		$("#actionForm").submit();
	})
	
	$("#insertBtn").on("click", function () {
		location.href = "divsInsert";
	});
	
	
});

</script>
</head>
<body>
<form action="divsDetail" id="actionForm" method="post">
	<input type="hidden" id="no" name="no" /> 
</form>
<input type="button" value="추가" id="insertBtn" />
<br />
<table>
	<thead>
		<tr>
			<th>분류코드</th>
			<th>분류명</th>
		</tr>
	</thead>
	<tbody>
		<c:forEach var="data" items="${list}">
			<tr no="${data.DIV_CODE}">
				<td>${data.DIV_CODE}</td>
				<td>${data.DIV_NAME}</td>
			</tr>
		</c:forEach>
	</tbody>
</table>
</body>
</html>

divsDetail.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript" 
		src="resources/script/jquery/jquery-1.12.4.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
	
	$("#btnWrap").on("click", "input", function(){
		if($(this).attr("id") != "deleteBtn" ||
			($(this).attr("id") == "deleteBtn" && confirm("삭제하시곘습니까?"))){
			$("#actionForm").attr("action", $(this).attr("loc"));
			$("#actionForm").submit();
		}
	})
});

</script>
</head>
<body>
<form action="#" id="actionForm" method="post">
	<input type="hidden" name="gbn" value="d">
	<input type="hidden" name="no" value="${data.DIV_CODE}">
</form>	
분류코드: ${data.DIV_CODE}<br/>
분류명: ${data.DIV_NAME}<br/>
<div id="btnWrap">
	<input type="button" value="수정" id="updateBtn" loc="divsUpdate" /> 
	<input type="button" value="삭제" id="deleteBtn" loc="divsRes"/>
	<input type="button" value="목록" id="listBtn" loc="divsList" />
</div>
</body>
</html>

divsInsert.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript" src="resources/script/jquery/jquery-1.12.4.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
	$("#listBtn").on("click", function () {
		history.back();
	});
	
	$("#insertBtn").on("click", function () {
		if($("#divCode").val() == ""){
			alert("분류코드를 입력하세요");
			$("#divCode").focus();
		} else if($("#divName").val() == ""){
			alert("분류명을 입력하세요");
			$("#divName").focus();
		} else {
			$("#actionForm").submit();
		}
	});
});

</script>
</head>
<body>
<form action="divsRes" id="actionForm" method="post">
<!-- gbn : 구분, 혹은 flag -->
<!-- 구분: i - insert, u - update, d - delete -->
<input type="hidden" name="gbn" value="i"/>
분류코드 <input type="text" name="divCode" id="divCode" maxlength="1" />
<br />
분류명 <input type="text" name="divName" id="divName" />
<br />
</form>
<input type="button" value="등록" id="insertBtn">
<input type="button" value="목록" id="listBtn">
</body>
</html>

divsRes.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript" src="resources/script/jquery/jquery-1.12.4.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
	
	switch("${res}"){
	case "success" : 
		if("${param.gbn}" == "u"){ // 수정이 성공했을 떄
			$("#goForm").submit();
		} else { // 등록, 삭제가 성공했을 떄
			location.href = "divsList" 
		}
		break;
	case "failed" : 
		alert("작업에 실패하였습니다.");
		history.back();
		break;
	case "error" : 
		alert("작업중 문제가 발생 하였습니다.");
		history.back();
		break;
	}
});
</script>
</head>
<body>
<form action="divsDetail" id="goForm" method="post">
	<input type="hidden" name="no" value="${param.no}" /> <!-- 전 화면에서 넘어오니까 param.no -->
</form>
</body>
</html>

divsUpdate.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript" src="resources/script/jquery/jquery-1.12.4.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
	$("#listBtn").on("click", function () {
		history.back();
	});
	
	$("#updateBtn").on("click", function () {
		if($("#divCode").val() == ""){
			alert("분류코드를 입력하세요");
			$("#divCode").focus();
		} else if($("#divName").val() == ""){
			alert("분류명을 입력하세요");
			$("#divName").focus();
		} else {
			$("#actionForm").submit();
		}
	});
});

</script>
</head>
<body>
<form action="divsRes" id="actionForm" method="post">
<input type="hidden" name="gbn" value="u"/>
<input type="hidden" name="no" value="${data.DIV_CODE}"/> 
분류번호 :<input type="text" name="divCode" id="divCode" value="${data.DIV_CODE}" /> <br />
분류명 : <input type="text" name="divName" id="divName" value="${data.DIV_NAME}" />
</form>
<input type="button" value="수정" id="updateBtn">
<input type="button" value="뒤로가기" id="listBtn">
</body>
</html>

TestController.java

package com.spring.sample.web.test.controller;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;

import com.spring.sample.web.test.service.ITestService;

@Controller
public class TestController {
	// 3.
	@Autowired
	public ITestService iTestService;

	/*
	 * Database나 파일 같이 외부에 접근하는 경우 외적 요인으로 문제가 발샐할 수 있음으로 예외처리가 반드시 필요하다.
	 */

	// 글 목록
	@RequestMapping(value = "/sellList")
	public ModelAndView sellList(ModelAndView mav) throws Throwable {
		// 데이터 취득
		List<HashMap<String, String>> list = iTestService.getSellList();

		mav.addObject("list", list);
		mav.setViewName("test/sellList");

		return mav;
	}

	// 글 상세보기
	@RequestMapping(value = "/sellDetail")
	// 페이지 이동할때 값이 하나가 넘어가는 경우가 거의 없음,
	// params를 다른이름 줘도됨, a이든 뭐든 상관없음, 여기서만, 나는 HashMap만 받으면 된다
	public ModelAndView sellDetail(@RequestParam HashMap<String, String> params, ModelAndView mav) throws Throwable {

		// no가 값이 안 넘어오거나 no자체가 안넘어온 경우
		if (params.get("no") == null || params.get("no") == "") {
			// setViewName에서 redirect : 해당 주소로 이동한다.
			// 단, GET방식밖에 안됨
			// 사용시 고민해야부분 중 하나는 단순이동할때는 redirect, 나머지는 문제가 있을수있다. 값이 주소창에 나옴!
			// mav.addObject("test", "abc"); // get방식으로 쓰이는 예
			mav.setViewName("redirect:sellList");

		} else {
			HashMap<String, String> data = iTestService.getSell(params);

			mav.addObject("data", data);
			mav.setViewName("test/sellDetail");
		}

		return mav;
	}

	// 글 추가
	@RequestMapping(value = "/sellInsert")
	public ModelAndView sellInsert(ModelAndView mav) {
		// DB에 붙을게 없으니, 굳이 예외처리 할 필요 없음
		mav.setViewName("test/sellInsert");
		return mav;
	}

	// 글 등록
	@RequestMapping(value = "/sellRes")
	public ModelAndView sellInsertRes(@RequestParam HashMap<String, String> params, ModelAndView mav) throws Throwable {

		try {
			int cnt = 0; // insert, update, delete 다 쓸거기 때문에 처음에 0줌

			switch (params.get("gbn")) {
			case "i":
				cnt = iTestService.insertSell(params);
				break;
			case "u":
				cnt = iTestService.updateSell(params);
				break;
			case "d":
				cnt = iTestService.deleteSell(params);
				break;
			}

			if (cnt > 0) { // 1건 이상 등록된 경우
				mav.addObject("res", "success");
			} else { // 등록 안된 경우
				mav.addObject("res", "failded");
			}
		} catch (Exception e) { // 예외 발생시
			e.printStackTrace();
			mav.addObject("res", "error");
		}
		mav.setViewName("test/sellRes");
		return mav;
	}

	// 글 수정페이지
	@RequestMapping(value = "/sellUpdate")
	public ModelAndView sellUpdate(@RequestParam HashMap<String, String> params, ModelAndView mav) throws Throwable {
		// 기존데이터 조회해와야함
		HashMap<String, String> data = iTestService.getSell(params);
		mav.addObject("data", data);

		mav.setViewName("test/sellUpdate");

		return mav;

	}

	// 실습
	@RequestMapping(value = "/managerList")
	public ModelAndView managerList(ModelAndView mav) throws Throwable {
		// 데이터 취득
		List<HashMap<String, String>> list = iTestService.getManagerList();

		mav.addObject("list", list);
		mav.setViewName("test/managerList");

		return mav;
	}

	@RequestMapping(value = "/managerDetail")
	public ModelAndView managerDetail(@RequestParam HashMap<String, String> params, ModelAndView mav) throws Throwable {

		if (params.get("no") == null || params.get("no") == "") {
			mav.setViewName("redirect:managerList");

		} else {
			HashMap<String, String> data = iTestService.getManager(params);

			mav.addObject("data", data);
			mav.setViewName("test/managerDetail");
		}

		return mav;
	}

	@RequestMapping(value = "/managerInsert")
	public ModelAndView managerInsert(ModelAndView mav) {
		mav.setViewName("test/managerInsert");
		return mav;

	}

	@RequestMapping(value = "/managerRes")
	public ModelAndView managerInsertRes(@RequestParam HashMap<String, String> params, ModelAndView mav)
			throws Throwable {

		System.out.println(params.toString());
		try {
			int cnt = 0; // insert, update, delete 다 쓸거기 때문에 처음에 0줌

			switch (params.get("gbn")) {
			case "i":
				cnt = iTestService.insertManager(params);
				break;
			case "u":
				cnt = iTestService.updateManager(params);
				break;
			case "d":
				cnt = iTestService.deleteManager(params);
				break;
			}

			if (cnt > 0) { // 1건 이상 등록된 경우
				mav.addObject("res", "success");
			} else { // 등록 안된 경우
				mav.addObject("res", "failded");
			}
		} catch (Exception e) { // 예외 발생시
			e.printStackTrace();
			mav.addObject("res", "error");
		}
		mav.setViewName("test/managerRes");
		return mav;
	}

	// 글 수정페이지
	@RequestMapping(value = "/managerUpdate")
	public ModelAndView managerUpdate(@RequestParam HashMap<String, String> params, ModelAndView mav) throws Throwable {
		// 기존데이터 조회해와야함
		HashMap<String, String> data = iTestService.getManager(params);
		mav.addObject("data", data);

		mav.setViewName("test/managerUpdate");

		return mav;

	}

	// divs 글 목록페이지
	@RequestMapping(value = "/divsList")
	public ModelAndView divsList(ModelAndView mav) throws Throwable {
		// 데이터 취득
		List<HashMap<String, String>> list = iTestService.getDivsList();

		mav.addObject("list", list);
		mav.setViewName("test/divsList");

		return mav;
	}

	// divs 글 상세페이지
	@RequestMapping(value = "/divsDetail")

	public ModelAndView divsDetail(@RequestParam HashMap<String, String> params, ModelAndView mav) throws Throwable {

		System.out.println(params.toString());
		if (params.get("no") == null || params.get("no") == "") {
			mav.setViewName("redirect:divsList");

		} else {
			HashMap<String, String> data = iTestService.getDivs(params);

			mav.addObject("data", data);
			mav.setViewName("test/divsDetail");
		}

		return mav;
	}

	// divs 글 추가
	@RequestMapping(value = "/divsInsert")
	public ModelAndView divsInsert(ModelAndView mav) {
		mav.setViewName("test/divsInsert");
		return mav;

	}

	@RequestMapping(value = "/divsRes")
	public ModelAndView divsInsertRes(@RequestParam HashMap<String, String> params, ModelAndView mav) throws Throwable {

		System.out.println(params.toString());
		try {
			int cnt = 0; // insert, update, delete 다 쓸거기 때문에 처음에 0줌

			switch (params.get("gbn")) {
			case "i":
				cnt = iTestService.insertDivs(params);
				break;
			case "u":
				cnt = iTestService.updateDivs(params);
				break;
			case "d":
				cnt = iTestService.deleteDivs(params);
				break;
			}

			if (cnt > 0) { // 1건 이상 등록된 경우
				mav.addObject("res", "success");
			} else { // 등록 안된 경우
				mav.addObject("res", "failded");
			}
		} catch (Exception e) { // 예외 발생시
			e.printStackTrace();
			mav.addObject("res", "error");
		}
		mav.setViewName("test/divsRes");
		return mav;
	}

	@RequestMapping(value = "/divsUpdate")
	public ModelAndView divsUpdate(@RequestParam HashMap<String, String> params, ModelAndView mav) throws Throwable {
		// 기존데이터 조회해와야함
		HashMap<String, String> data = iTestService.getDivs(params);
		mav.addObject("data", data);

		mav.setViewName("test/divsUpdate");

		return mav;

	}

}

ITestService.java

package com.spring.sample.web.test.service;

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

public interface ITestService {

	public List<HashMap<String, String>> getSellList() throws Throwable;

	public HashMap<String, String> getSell(HashMap<String, String> params) throws Throwable;

	public int insertSell(HashMap<String, String> params) throws Throwable;

	public int deleteSell(HashMap<String, String> params) throws Throwable;

	public int updateSell(HashMap<String, String> params) throws Throwable;

	public List<HashMap<String, String>> getManagerList() throws Throwable;

	public HashMap<String, String> getManager(HashMap<String, String> params) throws Throwable;

	public int insertManager(HashMap<String, String> params) throws Throwable;

	public int updateManager(HashMap<String, String> params) throws Throwable;

	public int deleteManager(HashMap<String, String> params) throws Throwable;

	public List<HashMap<String, String>> getDivsList() throws Throwable;

	public HashMap<String, String> getDivs(HashMap<String, String> params) throws Throwable;

	public int insertDivs(HashMap<String, String> params) throws Throwable;

	public int updateDivs(HashMap<String, String> params) throws Throwable;

	public int deleteDivs(HashMap<String, String> params) throws Throwable;

}

TestService.java

package com.spring.sample.web.test.service;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.spring.sample.web.test.dao.ITestDao;

@Service
public class TestService implements ITestService {
	// 2. DAO를 가져다 쓰는걸 만들어야함
	@Autowired
	public ITestDao iTestDao;

	@Override
	public List<HashMap<String, String>> getSellList() throws Throwable {
		return iTestDao.getSellList();
	}

	@Override
	// getSell매소드의 인자로 HashMap<String, String> param 받는거
	public HashMap<String, String> getSell(HashMap<String, String> params) throws Throwable {
		return iTestDao.getSell(params);
	}

	@Override
	public int insertSell(HashMap<String, String> params) throws Throwable {
		return iTestDao.insertSell(params);
	}

	@Override
	public int deleteSell(HashMap<String, String> params) throws Throwable {
		return iTestDao.deleteSell(params);
	}

	@Override
	public int updateSell(HashMap<String, String> params) throws Throwable {
		return iTestDao.updateSell(params);
	}

	@Override
	public List<HashMap<String, String>> getManagerList() throws Throwable {
		return iTestDao.getManagerList();
	}

	@Override
	public HashMap<String, String> getManager(HashMap<String, String> params) throws Throwable {
		return iTestDao.getManager(params);
	}

	@Override
	public int insertManager(HashMap<String, String> params) throws Throwable {
		return iTestDao.insertManager(params);
	}

	@Override
	public int updateManager(HashMap<String, String> params) throws Throwable {
		return iTestDao.updateManager(params);
	}

	@Override
	public int deleteManager(HashMap<String, String> params) throws Throwable {
		return iTestDao.deleteManager(params);
	}

	@Override
	public List<HashMap<String, String>> getDivsList() throws Throwable {
		return iTestDao.getDivsList();
	}

	@Override
	public HashMap<String, String> getDivs(HashMap<String, String> params) throws Throwable {
		return iTestDao.getDivs(params);
	}

	@Override
	public int insertDivs(HashMap<String, String> params) throws Throwable {
		return iTestDao.insertDivs(params);
	}

	@Override
	public int updateDivs(HashMap<String, String> params) throws Throwable {
		return iTestDao.updateDivs(params);
	}

	@Override
	public int deleteDivs(HashMap<String, String> params) throws Throwable {
		return iTestDao.deleteDivs(params);
	}

}

ITestDao.java

package com.spring.sample.web.test.dao;

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

public interface ITestDao {

	public List<HashMap<String, String>> getSellList() throws Throwable;

	public HashMap<String, String> getSell(HashMap<String, String> params) throws Throwable;

	public int insertSell(HashMap<String, String> params) throws Throwable;

	public int deleteSell(HashMap<String, String> params) throws Throwable;

	public int updateSell(HashMap<String, String> params) throws Throwable;

	public List<HashMap<String, String>> getManagerList() throws Throwable;

	public HashMap<String, String> getManager(HashMap<String, String> params) throws Throwable;

	public int insertManager(HashMap<String, String> params) throws Throwable;

	public int deleteManager(HashMap<String, String> params) throws Throwable;

	public int updateManager(HashMap<String, String> params) throws Throwable;

	public List<HashMap<String, String>> getDivsList() throws Throwable;

	public HashMap<String, String> getDivs(HashMap<String, String> params) throws Throwable;

	public int insertDivs(HashMap<String, String> params) throws Throwable;

	public int updateDivs(HashMap<String, String> params) throws Throwable;

	public int deleteDivs(HashMap<String, String> params) throws Throwable;

}

TestDao.java

package com.spring.sample.web.test.dao;

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

import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class TestDao implements ITestDao {
	// 1.
	@Autowired
	public SqlSession sqlSession;

	@Override
	public List<HashMap<String, String>> getSellList() throws Throwable {
		// selectList("namespace.id"): 해당 namespace에 있는 id를 찾아서 조회 실행 목록을 받음
		// 목록으로 받을때 selectList
		return sqlSession.selectList("test.getSellList");
	}

	@Override
	public HashMap<String, String> getSell(HashMap<String, String> params) throws Throwable {
		// selectOne(쿼리, 데이터) : 해당 쿼리에 데이터를 전달하고 단건 결과를 돌려받음
		return sqlSession.selectOne("test.getSell", params);
	}

	@Override
	public int insertSell(HashMap<String, String> params) throws Throwable {
		return sqlSession.insert("test.insertSell", params);
	}

	@Override
	public int deleteSell(HashMap<String, String> params) throws Throwable {
		return sqlSession.delete("test.deleteSell", params);
	}

	@Override
	public int updateSell(HashMap<String, String> params) throws Throwable {
		return sqlSession.update("test.updateSell", params);
	}

	@Override
	public List<HashMap<String, String>> getManagerList() throws Throwable {
		// selectList("namespace.id"): 해당 namespace에 있는 id를 찾아서 조회 실행 목록을 받음
		// 목록으로 받을때 selectList
		return sqlSession.selectList("test.getManagerList");
	}

	@Override
	public HashMap<String, String> getManager(HashMap<String, String> params) throws Throwable {
		// selectOne(쿼리, 데이터) : 해당 쿼리에 데이터를 전달하고 단건 결과를 돌려받음
		// selectOne이기 때문에 여러줄 들어오면 터짐
		return sqlSession.selectOne("test.getManager", params);
	}

	@Override
	public int insertManager(HashMap<String, String> params) throws Throwable {
		return sqlSession.insert("test.insertManager", params);
	}

	@Override
	public int deleteManager(HashMap<String, String> params) throws Throwable {
		return sqlSession.delete("test.deleteManager", params);
	}

	@Override
	public int updateManager(HashMap<String, String> params) throws Throwable {
		return sqlSession.update("test.updateManager", params);
	}

	@Override
	public List<HashMap<String, String>> getDivsList() throws Throwable {
		return sqlSession.selectList("test.getDivsList");
	}

	@Override
	public HashMap<String, String> getDivs(HashMap<String, String> params) throws Throwable {
		return sqlSession.selectOne("test.getDivs", params);
	}

	@Override
	public int insertDivs(HashMap<String, String> params) throws Throwable {
		return sqlSession.insert("test.insertDivs", params);
	}

	@Override
	public int updateDivs(HashMap<String, String> params) throws Throwable {
		return sqlSession.update("test.updateDivs", params);
	}

	@Override
	public int deleteDivs(HashMap<String, String> params) throws Throwable {
		return sqlSession.delete("test.deleteDivs", params);
	}

}

Test_SQL.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">
<!-- namespace : 클래스와 동일, 이 파일의 대표명 -->
<mapper namespace="test">
	<!-- select : 조회 
		id: 구분자, 메소드명과 동일
		resultType : 조회 결과 중 한줄에 대한 자료 형태를 지정
		hashmap: mybatis-config.xml에서 별칭 지정해놔서 줄여쓸수있는거임
		쿼리 삽입시 주의사항: ;이 있는경우 문제가 발생한다. 이유는 해당 쿼리 실행시 자동으로 추가되기 때문
	-->
	<select id="getSellList" resultType="hashmap">
		SELECT SELL_NO, ITEM_NAME, COUNT, SELL_DT
		FROM SELL
		ORDER BY SELL_DT DESC, SELL_NO DESC
	</select>
	
	<!-- 
		parameterType : 실행시 받는 값 
		#{키} : 헤당위치에 문자열로 키에 해당하는 값을 넣어준다
		ex) no에 3이 들어있는 경우
		WHERE SELL_NO = #{no}
		=> WHERE SELL_NO = '3' // 문자열이기 때문에  ' '이 들어감
		parameterType dao받는값, resultType값이 db실행결과
	-->
	<select id="getSell" resultType="hashmap" parameterType="hashmap">
		SELECT SELL_NO, ITEM_NAME, COUNT, SELL_DT
		FROM SELL
		WHERE SELL_NO = #{no}
	</select>
	
	<!-- insert는 resultType 이 없음 -->
	<insert id="insertSell" parameterType="hashmap">
		INSERT INTO SELL(SELL_NO, ITEM_NAME, COUNT, SELL_DT)
		VALUES(SELL_SEQ.NEXTVAL, #{itemName},#{count},#{sellDt})
	</insert>
	
	<delete id="deleteSell" parameterType="hashmap">
		DELETE FROM SELL
		WHERE SELL_NO = #{no}
	</delete>
	
	<update id="updateSell" parameterType="hashmap">
		UPDATE SELL SET ITEM_NAME = #{itemName},
                COUNT = #{count},
                SELL_DT = #{sellDt}
       	WHERE SELL_NO = #{no}
	</update>
	
	<select id="getManagerList" resultType="hashmap">
		SELECT EMP_NO, NAME, DEPT
		FROM MANAGER
	</select>

	<select id="getManager" resultType="hashmap" parameterType="hashmap">
		SELECT EMP_NO, NAME, DEPT
		FROM MANAGER
		WHERE EMP_NO = #{no}
	</select>
	
	<insert id="insertManager" parameterType="hashmap">
		INSERT INTO MANAGER(EMP_NO, NAME, DEPT)
		VALUES(MANAGER_SEQ.NEXTVAL, #{name}, #{deptName})
	</insert>
	
	<delete id="deleteManager" parameterType="hashmap">
		DELETE FROM MANAGER
		WHERE EMP_NO = #{no}
	</delete>
	
	<update id="updateManager" parameterType="hashmap">
		UPDATE MANAGER SET NAME = #{name},
                DEPT = #{deptName}
                WHERE EMP_NO = #{no}
	</update>
	
	<select id="getDivsList" resultType="hashmap">
		SELECT DIV_CODE, DIV_NAME FROM DIVS ORDER BY DIV_CODE
	</select>
	
	<select id="getDivs" resultType="hashmap" parameterType="hashmap">
		SELECT DIV_CODE, DIV_NAME FROM DIVS
		WHERE DIV_CODE = #{no}
	</select>
	
	<insert id="insertDivs" parameterType="hashmap">
		INSERT INTO DIVS(DIV_CODE, DIV_NAME)
		VALUES((SELECT #{divCode} || LPAD(NVL(MAX(REPLACE(DIV_CODE, #{divCode}, '')),0) + 1,2,0 )
		FROM DIVS
		WHERE DIV_CODE LIKE #{divCode} || '%'), #{divName})
	</insert>
	
	<delete id="deleteDivs" parameterType="hashmap">
		DELETE FROM DIVS
		WHERE DIV_CODE = #{no}
	</delete>
	
	<update id="updateDivs" parameterType="hashmap">
		UPDATE DIVS SET DIV_CODE = #{divCode},
                DIV_NAME = #{divName}
                WHERE DIV_CODE = #{no}
	</update>
	
</mapper>

728x90

'TIL > academy' 카테고리의 다른 글

국비 TIL(Today I Learned) 20220809 테이블 페이징 처리 (0)	2022.08.09
국비 TIL(Today I Learned) 20220808 Spring 시험, 팀별 프로젝트 (0)	2022.08.09
국비 TIL(Today I Learned) 20220804 스프링 게시판 글추가, 수정, 삭제 (0)	2022.08.04
국비 TIL(Today I Learned) 20220803 스프링, DB랑 연결, DAO, myBatis (0)	2022.08.03
국비 TIL(Today I Learned) 20220802 스프링.... 뭘 배웠는지 몰라서 제목을 뭐라 써야할지도 모르겠다. (0)	2022.08.02

현재글국비 TIL(Today I Learned) 20220805 오라클에서 divs 데이터 가져오기 실습

Til, 스프링부트, mariaDB, 배열, spring-boot-starter-validation, 변수, Spring Boot 구동오류, java, mysql, Spring Boot, Github, 국비학원, IntelliJ, TlL, issuelabels, firebase, 쿼리문, 채팅기능, redis, redis명령어,

Today :
Yesterday :

일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

makeaplayground