TIL/academy

국비 TIL(Today I Learned) 20220902 시험

토희 2022. 9. 2. 17:51
728x90

시험문제

 

테이블

 

시퀀스

 

 

내가 만든 화면

list화면

update화면

insert화면24번 새로 넣음

delect

번호 24번 없앰

 

 

pshacc.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>
<!-- Common CSS -->
<link rel="stylesheet" type="text/css" href="resources/css/common/cmn.css" />
<!-- Popup CSS -->
<link rel="stylesheet" type="text/css" href="resources/css/common/popup.css" />
<style type="text/css">

.all_table{
	font-size: 14px;
	height: 200px;
}
.wrap{ 
	width: 800px; 
	margin: 0 auto;
}
.mtb{
	margin: 5px;
}
.login{
	vertical-align: baseline;
}
.con{
	width: calc(100% - 22px); 
	height: 20px; 
	border: 1px solid #d7d7d7; 
	resize: none; 

}

.update{
	display: none;
}
.con_td{
	font-size: 0
}
.paging_area{
	display: block; 
	position: relative; 
	left: 0;
	margin-bottom: 10px;
}
.search_area{
	text-align: center;
}
body {
	overflow: auto;
}

#accType, #dt{
	min-width: 100px;
    height: 30px;
    vertical-align: middle;
    border: 1px solid #d7d7d7;
}
#price {
	width: 150px;
    height: 28px;
    padding: 0px 2px;
    text-indent: 5px;
    vertical-align: middle;
    border: 1px solid #d7d7d7;
    outline-color: #70adf9;
}
#info {
	width: 300px;
    height: 28px;
    padding: 0px 2px;
    text-indent: 5px;
    vertical-align: middle;
    border: 1px solid #d7d7d7;
    outline-color: #70adf9;
}
</style>
<script type="text/javascript" 
		src="resources/script/jquery/jquery-1.12.4.min.js"></script>
<script type="text/javascript" 
src="resources/script/common/popup.js"></script>
<!-- Slimscroll -->
<!-- <script type="text/javascript" src="resources/script/jquery/jquery.slimscroll.js"></script> -->
<script type="text/javascript">
$(document).ready(function () {

	
	reloadList();
	

	$("#insertBtn").on("click", function () {
		if($("#price").val() == ""){
			makeAlert("알림","금액을 입력하세요." , function () {
				$("#price").focus();
			});
		}else if($("#price").val()*1 < 0){ 
			makeAlert("알림","금액을 0 이상이여야 합니다." , function () {
				$("#price").focus();
			});
		} 
		else if($("#dt").val() == ""){ 
			makeAlert("알림","날짜를 입력하세요." , function () {
				$("#dt").focus();
			});
		} 
		
		else if($.trim($("#info").val()) == ""){
				makeAlert("알림","내용을 입력하세요." , function () {
					$("#info").focus();
				});
	 		}	else {
	 				action("insert");
			}
	
		})
		
	// 페이징 클릭시
	$(".paging_area").on("click", "span", function () {
		$("#page").val($(this).attr("page"));

		reloadList();
	})	
		

	
	// 목록의 삭제버튼 클릭시
	$("tbody").on("click", ".delete_btn", function () {
		var no = $(this).parent().parent().attr("no");
		
		  makePopup({
		         title : "알림",
		         contents : "삭제하시겠습니까?",
		         // draggable : true,
		         buttons : [{
		            name : "삭제",
		            func:function() {
		            	$("#no").val(no);
		            	action("delete");
		            	closePopup(); // 제일 위의 팝업 닫기
		            }
		         }, {
		            name : "취소"
		    }]
		})
	})
	
	// 목록 수정버튼 클릭시
	$("tbody").on("click", ".update_btn", function () {
		var no = $(this).parent().parent().attr("no");
		$("#no").val(no);
		
		
		
		if ($(this).parent().parent().children().eq(2).html() == '지출'){
			var accType = "0";
		} else {
			accType = "1"
		}
		
		$("#accType").val(accType);
		var price = $(this).parent().parent().children().eq(1).html();
		$("#price").val(price);
		//eq(인덱스번호) : 자식들 중 인덱스 몇번째 인지 찾아서 취득
		var info = $(this).parent().parent().children().eq(3).html();
		// 수정 내용 넣기 전 <> 변화
		info = info.replace(/&lt;/gi, "<");
		info = info.replace(/&gt;/gi, ">");
		
		
		$("#info").val(info);
		var dt = $(this).parent().parent().children().eq(4).html();
		$("#dt").val(dt);
		
		
		// 등록버튼 감추기 + 수정, 취소버튼 나타나기
		$(".insert").hide();
		$(".update").show();
		
		// 작성영역에 포커스
		$("#info").focus();
	})
	
	// 수정 영역의 취소버튼
	$("thead #cancelBtn").on("click", function () {
		// 입력내용 초기화
		$("#no").val("");
		$("#accType").val("0");
		$("#info").val("");
		$("#price").val("")
		$("#dt").val("")
		// 등록버튼 나타나기 + 수정, 취소버튼 감추기
		$(".insert").show();
		$(".update").hide();
	})
	
	// 수정 영역의 수정버튼
	$("thead #updateBtn").on("click", function () {
		action("update");
	})
	
})


var msg ={
	"insert" : "등록",
	"update" : "수정",
	"delete" : "삭제",
}

function action(flag) {
	// con의 <를을 웹문자로 변환
	$("#info").val($("#info").val().replace(/</gi, "&lt;"));
	// con의 >를을 웹문자로 변환
	$("#info").val($("#info").val().replace(/>/gi, "&gt;"));
	
	
	// Javascript object에서의 [] : 해당 키값으로 내용을 불러오거나 넣을 수있다. 
	// Java의 Map에서 get, put역활

	console.log(msg[flag]);

	
	var params = $("#actionForm").serialize();
	
	$.ajax({
		url : "ACCAction/" + flag,
		type : "POST", 
		dataType: "json", 
		data: params, 
		success : function(res) {
			console.log(res);
		
			
			switch(res.msg){
			case "success" :
				// 내용 초기화
				$("#info").val("");
				$("#no").val("");
				$("#price").val("");
				$("#dt").val("");
				$("#accType").val("0");


				// 목록 재조회
				switch(flag){
				case "insert" :
				case "delete" :
					// 조회 데이터 초기화
					$("#page").val("1");

					break;
				case "update" :

					// 입력내용 초기화
					$("#no").val("");
					$("#info").val("");
					// 등록버튼 나타나기 + 수정, 취소버튼 감추기
					$(".insert").show();
					$(".update").hide();
					break;

				}
				reloadList();
				break;
			case "fail" :
				makeAlert("알림" ,  msg[flag] + "에 실패하였습니다.");
				break;
			case "error" :
				makeAlert("알림" , msg[flag] + " 중 문제가 발생하였습니다.");
				break;
			}
		},
		error : function(request, status, error) { 
			console.log(request.responseText); 
		}
	}); //Ajax End
} // action Function End

function reloadList() {
	var params = $("#searchForm").serialize();
	$.ajax({
		url : "ACCList",
		type : "POST", 
		dataType: "json", 
		data: params, 
		success : function(res) { 
			drawList(res.list);
			drawPaging(res.pd);
			drawTotal(res.sumlist);
			
		},
		error : function(request, status, error) { 
			console.log(request.responseText); 
		}
	}); //Ajax End
}

function drawList(list,sumlist) {
	
	var html = "";
	
	for(var data of list){
		html +="<tr no=\"" + data.NO  + "\">"; // 번호
		html +="<td>" + data.NO + "</td>"; //번호
		html +="<td>" + data.PRICE + "</td>"; //금액
		html +="<td>" + data.ACC_TYPE + "</td>"; //지출/수입
		html +="<td>" + data.INFO + "</td>"; //내역
		html +="<td>" + data.DT + "</td>"; //일자
		html +="<td>";
		//if("${sMemNo}" == data.MEM_NO){ // 작성자이면
			html +="<div class=\"cmn_btn_ml mtb update_btn\">수정</div><br/>";
			html +="<div class=\"cmn_btn_ml mtb delete_btn\">삭제</div>";
		//}
		html +="</td>";
		html +="</tr>";
	}
	
	$(".list tbody").html(html);
}

function drawTotal(sumlist) {
	
	
	var html = "";
	
	console.log(sumlist)
		html +="<tr>";
		html +="<td>" + sumlist.지출 + "</td>";
		html +="<td>" + sumlist.수입 + "</td>"; 
		html +="<td>" + sumlist.총액 + "</td>";
		html +="</tr>";
	
	
	$(".all tbody").html(html);
}



function drawPaging(pd) {
	var html = "";
	
	html += "<span class=\"page_btn page_first\" page=\"1\">처음</span>";
	// 이전
	if($("#page").val() == "1"){
		html += "<span class=\"page_btn page_prev\" page=\"1\">이전</span>";
	} else{
		// 문자열을 숫자로 바꾸기위해 *1
		html += "<span class=\"page_btn page_prev\" page=\"" + ($("#page").val() *1 - 1) + "\">이전</span>";
	}
	
	for(var i = pd.startP; i <= pd.endP; i++){
		if($("#page").val() * 1 == i){ // 현재 페이지
			html += "<span class=\"page_btn_on\" page=\"" + i + "\">" + i + "</span>";
		} else { // 다른 페이지
			html += "<span class=\"page_btn\" page=\"" + i + "\">" + i + "</span>";
		}
	}
	
	if($("#page").val() *1 == pd.endP){ // 현재페이지가 마지막 페이지라면
		html += "<span class=\"page_btn page_next\" page=\"" +pd.maxP+ "\">다음</span>";
	} else {
		html += "<span class=\"page_btn page_next\" page=\"" + ($("#page").val() *1 + 1) + "\">다음</span>";
	}
	
	html += "<span class=\"page_btn page_last\" page=\"" +pd.maxP+ "\">마지막</span>";
	
	$(".paging_area").html(html);
                                                                     
}

</script>
</head>
<body>
<div class="wrap">
	<table class="board_table all">
			<thead>
				<tr>
					<th>지출</th>
					<th>수입</th>
					<th>총액</th>
				</tr>
			</thead>
			<tbody>

			</tbody>
		</table>
		
	<div class="board_area">
		<!-- 작성 또는 로그인 -->
		
		<!-- 목록 -->
		<table class="board_table list">
			<colgroup>
				<col width="100"> <!-- 번호 -->
				<col width="100"> <!-- 금액 -->
				<col width="100"> <!-- 지출/수입 -->
				<col width="300"> <!-- 내용 -->
				<col width="100"> <!-- 날짜 -->
				<col width="100"> <!-- 버튼 -->
			</colgroup>
			<thead>
						<tr >
							<form action="#" id="actionForm">
								<input type="hidden" name="no" id="no">
							<td colspan="1">
								<select name="accType" id="accType" value="0">
									<option value="0">지출</option>
									<option value="1">수입</option>
								</select>
							</td>
							<td colspan="1">
								<input  type="number"  class="price" name="price" id="price" placeholder="금액" />
							</td>
							<td colspan="2">
								<input type="text" class="info" name="info" id="info" placeholder="내역" />
							</td>
							<td colspan="1">
								<input type="date" name="dt" id="dt"/>  
							</td>
							</form>
							<td>
								<div class="insert">
									<div class="cmn_btn_ml" id="insertBtn">등록</div>
								</div> 
								<div class="update">
									<div class="cmn_btn_ml mtb" id="updateBtn">수정</div><br/>
									<div class="cmn_btn_ml mtb" id="cancelBtn">취소</div>
								</div>
							</td>
						</tr>

				<tr>
					<th>번호</th>
					<th>금액</th>
					<th>지출/수입</th>
					<th>내역</th>
					<th>일자</th>
					<th>&nbsp;</th>
				</tr>
			</thead>
			<tbody>
				
			</tbody>
		</table>
		<!-- 페이징 -->
		<div class="paging_area"></div>
		<form action="#" id="searchForm">
			<input type="hidden" name="page" id="page" value="1" />
		
		</form>
	</div>
</div>
</body>
</html>

 

PshAccController.java

package com.spring.sample.web.testa.Controller;

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

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

import com.fasterxml.jackson.databind.ObjectMapper;
import com.spring.sample.common.service.IPagingService;
import com.spring.sample.web.testa.dao.IACDao;

@Controller
public class PshAccController {
	@Autowired
	public IACDao dao;

	@Autowired
	public IPagingService ips;

	// DB안 붙으니 예외처리 x
	@RequestMapping(value = "ACC")
	public ModelAndView ACC(ModelAndView mav) {
		mav.setViewName("testa/pshAcc/pshacc");

		return mav;
	}

	@RequestMapping(value = "/ACCAction/{gbn}", method = RequestMethod.POST, produces = "text/json;charset=UTF-8")
	@ResponseBody
	public String ACCAction(@PathVariable String gbn, @RequestParam HashMap<String, String> params) throws Throwable {
		ObjectMapper mapper = new ObjectMapper();
		Map<String, Object> model = new HashMap<String, Object>();

		int cnt = 0;

		try {
			switch (gbn) {
			case "insert":
				System.out.println(params.toString());
				cnt = dao.insert("acc.insertAcc", params);
				break;
			case "update":
				cnt = dao.update("acc.updateAcc", params);
				break;
			case "delete":
				cnt = dao.update("acc.deleteAcc", params);
				break;
			}

			if (cnt > 0) {
				model.put("msg", "success");
			} else {
				model.put("msg", "fail");
			}
		} catch (Exception e) {
			e.printStackTrace();
			model.put("msg", "error");
		}

		return mapper.writeValueAsString(model);
	}

	@RequestMapping(value = "/ACCList", method = RequestMethod.POST, produces = "text/json;charset=UTF-8")
	@ResponseBody
	public String ACCList(@RequestParam HashMap<String, String> params) throws Throwable {
		ObjectMapper mapper = new ObjectMapper();
		Map<String, Object> model = new HashMap<String, Object>();

		// 페이지 받아오게 되어있음
		int cnt = dao.getInt("acc.getAccCnt", params);

		HashMap<String, Integer> pd = ips.getPagingData(Integer.parseInt(params.get("page")), cnt, 10, 5);

		params.put("start", Integer.toString(pd.get("start")));
		params.put("end", Integer.toString(pd.get("end")));

		List<HashMap<String, String>> list = dao.getList("acc.getAccList", params);
		HashMap<String, String> sumlist = dao.getMap("acc.getsumList");

		model.put("list", list);
		model.put("sumlist", sumlist);
		model.put("pd", pd);

		System.out.println(params.toString());

		return mapper.writeValueAsString(model);
	}
}

 

ACC_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">
<mapper namespace="acc">
	<insert id="insertAcc" parameterType="hashmap">
		 INSERT INTO ACCBOOK(NO, PRICE, ACC_TYPE, INFO, DT) VALUES (ACCBOOK_SEQ.NEXTVAL, #{price}, #{accType}, #{info}, #{dt})
	</insert>
	<update id="updateAcc" parameterType="hashmap">
		UPDATE ACCBOOK SET INFO = #{info},
		PRICE = #{price},
		ACC_TYPE = #{accType},
		DT = #{dt}
		WHERE NO = #{no}
	</update>
	<update id="deleteAcc" parameterType="hashmap">
		UPDATE ACCBOOK SET DEL = 0
		WHERE NO = #{no}
	</update>
	<select id="getAccCnt" parameterType="hashmap" resultType="Integer">
		SELECT COUNT(*) AS CNT
			FROM ACCBOOK 
		WHERE DEL = 1

	</select>
	<select id="getAccList" parameterType="hashmap" resultType="hashmap">
		 SELECT A.NO, A.PRICE, A.ACC_TYPE, A.INFO, A.DT
        FROM (SELECT NO, PRICE, CASE WHEN ACC_TYPE  = '0' THEN '지출'
            ELSE '수입' END AS ACC_TYPE, INFO,
              TO_CHAR(DT,'YYYY-MM-DD') AS DT, 
               ROW_NUMBER() OVER(ORDER BY NO DESC) AS RNUM
	      FROM ACCBOOK  
	      WHERE DEL = 1) A
          	WHERE A.RNUM BETWEEN #{start} AND #{end}
  
	</select>
	
	<select id="getsumList" parameterType="hashmap" resultType="hashmap">

    SELECT MAX(DECODE(ACC_TYPE, 0, SUM, NULL)) AS "지출", MIN(DECODE(ACC_TYPE, 1, SUM, NULL)) AS "수입", MIN(DECODE(ACC_TYPE, 1, SUM, NULL))
       - (MAX(DECODE(ACC_TYPE, 0, SUM, NULL))) AS "총액"
  	FROM (SELECT SUM(PRICE) AS SUM ,ACC_TYPE FROM ACCBOOK WHERE DEL = 1 GROUP BY ACC_TYPE)
  
	</select>
</mapper>

 

IACDao.java

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

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

public interface IACDao {
	// 숫자 취득
	public int getInt(String sql) throws Throwable; // 값 안 주고, 쿼리만 부를때

	public int getInt(String sql, HashMap<String, String> params) throws Throwable; // 값까지 줄때

	// 문자열 취득
	public String getString(String sql) throws Throwable;

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

	// HashMap 취득
	public HashMap<String, String> getMap(String sql) throws Throwable;

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

	// List 취득
	public List<HashMap<String, String>> getList(String sql) throws Throwable;

	public List<HashMap<String, String>> getList(String sql, HashMap<String, String> params) throws Throwable;

	// 등록
	public int insert(String sql) throws Throwable;

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

	// 수정
	public int update(String sql) throws Throwable;

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

	// 삭제
	public int delete(String sql) throws Throwable;

	public int delete(String sql, HashMap<String, String> params) throws Throwable;
}

ACDao.java

package com.spring.sample.web.testa.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 ACDao implements IACDao {
	@Autowired
	public SqlSession sqlSession;

	@Override
	public int getInt(String sql) throws Throwable {
		return sqlSession.selectOne(sql);
	}

	@Override
	public int getInt(String sql, HashMap<String, String> params) throws Throwable {
		return sqlSession.selectOne(sql, params);
	}

	@Override
	public String getString(String sql) throws Throwable {
		return sqlSession.selectOne(sql);
	}

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

	@Override
	public HashMap<String, String> getMap(String sql) throws Throwable {
		return sqlSession.selectOne(sql);
	}

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

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

	@Override
	public List<HashMap<String, String>> getList(String sql, HashMap<String, String> params) throws Throwable {
		return sqlSession.selectList(sql, params);
	}

	@Override
	public int insert(String sql) throws Throwable {
		return sqlSession.insert(sql);
	}

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

	@Override
	public int update(String sql) throws Throwable {
		return sqlSession.update(sql);
	}

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

	@Override
	public int delete(String sql) throws Throwable {
		return sqlSession.delete(sql);
	}

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

 

 

오늘 시험의 변수는 쿼리였다..

저 빨간색 테두리 부분을 구현하기 위해 쿼리를 이렇게...

 

 

controller에서 이렇게 받음

처음에 

위에 getAccList받은것처럼 list형식으로 했다가 map으로 변경했는데, 안되서 왜 안되지 했는데..

HashMap<String, String> sumlist = dao.getMap("acc.getsumList", params) 

params를 빼야하는데 안 뻈던거

 

HashMap<String, String> sumlist = dao.getMap("acc.getsumList")

이렇게 하니 잘 됨!!!

 

 

jsp에서는 테이블 2개를 각각 하나씩 그려주고

 

reload될때 그려줌

728x90

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

국비 TIL(Today I Learned) 20220926(월)~20220928(수)  (0) 2022.09.29
국비 TIL(Today I Learned) 20220927(화) 디버깅, 배포  (0) 2022.09.27
국비 TIL(Today I Learned) 20220901 갤러리 실습  (0) 2022.09.01
국비 TIL(Today I Learned) 20220825 AOP(Aspect Oriented Programming) : 관점지향 프로그래밍  (0) 2022.08.25
국비 TIL(Today I Learned) 20220824 시험, CRUD 게시판 만들기, SELL테이블로, 팀별프로젝트  (0) 2022.08.24

'TIL/academy'의 다른글

  • 현재글국비 TIL(Today I Learned) 20220902 시험

관련글

댓글

티스토리툴바