22-05-18(수) 077일차 [Oracle, Spring Framework, Mybatis Framework] 서브쿼리, 장바구니 만들어서 넣기

국비지원교육 (22.01-22.07)/강의노트

22-05-18(수) 077일차 [Oracle, Spring Framework, Mybatis Framework] 서브쿼리, 장바구니 만들어서 넣기

옌炎 2022. 6. 17. 19:30

728x90

수업내용

1교시 (09:30-10:20)

조언
- 면접보고 비대면으로 듣는데 마이크 연결이 안되어서 못들었음

2교시 (10:30-11:20)

날짜와 시간
time.ewha.or.kr/domestic.html

윈도우 10 :
제어판 - 날짜 및 시간 - 인터넷 시간

설정 변경을 클릭 후 ntp 서버 주소를 입력합니다.

참고로 NTP 서버 목록은 다음과 같습니다.
LG U+ : time.bora.net
KT : ntp.kornet.net
google : time.google.com

윈도우 + R
service.msc
msc : Microsoft Common Console

서비스(윈도우 서비스창)
Window Time 항목 더블 클릭

Window Time 항목 더블 클릭

Window Time 속성(로컴컴퓨터)
시작 유형(E) : 자동으로 설정

시작 선택

복구 탭

첫째 실패(F) : 서비스 다시 시작
둘째 실패(S) : 서비스 다시 시작
후속 실패(U) : 동작하지 않음

3교시 (11:30-12:20)

장바구니 만들어서 넣기

mybatis-congif.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 type="a.b.c.com.kosmo.product.vo.KosmoProductVO" alias="kosmoproductvo"/>
		<typeAlias type="a.b.c.com.kosmo.cart.vo.KosmoCartVO" alias="kosmocartvo" />
	</typeAliases>
</configuration>

spring_cart.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="a.b.c.com.kosmo.cart.dao.KosmoCartDAO">

<insert id="kosmoCartInsert" parameterType="kosmocartvo" >
	
	INSERT INTO KOSMO_CART (	KCNUM, KPID, KPNAME, 
								KPFILE, KPCNT, KPPRICE, 
								KPPRICESUM, KPNUM, KMNUM, 
								DELETEYN, INSERTDATE, UPDATEDATE)
            			VALUES( #{kcnum}, 
            					#{kpid}, 
            					#{kpname},
								#{kpfile},            					             					
            					#{kpcnt}, 
            					#{kpprice},
            					#{kppricesum}, 
            					#{kpnum}, 
            					#{kmnum},
            					'Y', 
            					SYSDATE, 
            					SYSDATE)
</insert>

<select id="kosmoCartSelectAll" parameterType="kosmocartvo" resultType="kosmocartvo">	
	
		SELECT 	A.KCNUM 		KPNUM
		       ,A.KPID 			KPID
		       ,A.KPNAME 		KPNAME
		       ,A.KPCNT 		KPCNT
		       ,A.KPFILE 		KPFILE
		       ,A.KPPRICE 		KPPRICE
		       ,A.KPPRICESUM 	KPPRICESUM
		       ,A.KPNUM 		KPNUM
		       ,A.KMNUM 		KMNUM
		       ,A.DELETEYN 	DELETEYN
		       ,TO_CHAR(A.INSERTDATE, 'YYYY-MM-DD') INSERTDATE
		       ,TO_CHAR(A.UPDATEDATE, 'YYYY-MM-DD') UPDATEDATE	  
		FROM 	KOSMO_CART A
		WHERE 	A.DELETEYN = 'Y'
		AND 	A.KMNUM = #{kmnum} 	
		ORDER BY 1 DESC  
	
</select>

</mapper>

spring_chabun.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="a.b.c.com.kosmo.common.dao.SpringChabunDAO">

<select id="getProductChabun" resultType="kosmoproductvo">
	SELECT /*+ INDEX_DESC(A SYS_C0012888) */ NVL(MAX(SUBSTR(A.KPNUM, -4)), 0) + 1  KPNUM FROM KOSMO_PRODUCT A
</select>

<select id="getCartChabun" resultType="kosmocartvo">
	SELECT /*+ INDEX_DESC(A SYS_C0011101) */ NVL(MAX(SUBSTR(A.KCNUM, -4)), 0) + 1  KCNUM FROM KOSMO_CART A
</select>

</mapper>

KosmoCartVO.java

package a.b.c.com.kosmo.cart.vo;

public class KosmoCartVO {
	
	private String kcnum;
	private String kpid;
	private String kpname;
	private String kpfile;
	private String kpcnt;
	private String kpprice;
	private String kppricesum;	
	private String kpnum;
	private String kmnum;
	private String deleteyn;
	private String insertdate;
	private String updatedate;
	
	// 생성자 
	public KosmoCartVO() {
		
	}

	public KosmoCartVO(String kcnum, String kpid, String kpname, String kpfile, String kpcnt, String kpprice,
			String kppricesum, String kpnum, String kmnum, String deleteyn, String insertdate, String updatedate) {
		
		this.kcnum = kcnum;
		this.kpid = kpid;
		this.kpname = kpname;
		this.kpfile = kpfile;
		this.kpcnt = kpcnt;
		this.kpprice = kpprice;
		this.kppricesum = kppricesum;
		this.kpnum = kpnum;
		this.kmnum = kmnum;
		this.deleteyn = deleteyn;
		this.insertdate = insertdate;
		this.updatedate = updatedate;
	}

	// setter/getter
	public String getKcnum() {
		return kcnum;
	}

	public String getKpid() {
		return kpid;
	}

	public String getKpname() {
		return kpname;
	}

	public String getKpfile() {
		return kpfile;
	}

	public String getKpcnt() {
		return kpcnt;
	}

	public String getKpprice() {
		return kpprice;
	}

	public String getKppricesum() {
		return kppricesum;
	}

	public String getKpnum() {
		return kpnum;
	}

	public String getKmnum() {
		return kmnum;
	}

	public String getDeleteyn() {
		return deleteyn;
	}

	public String getInsertdate() {
		return insertdate;
	}

	public String getUpdatedate() {
		return updatedate;
	}

	public void setKcnum(String kcnum) {
		this.kcnum = kcnum;
	}

	public void setKpid(String kpid) {
		this.kpid = kpid;
	}

	public void setKpname(String kpname) {
		this.kpname = kpname;
	}

	public void setKpfile(String kpfile) {
		this.kpfile = kpfile;
	}

	public void setKpcnt(String kpcnt) {
		this.kpcnt = kpcnt;
	}

	public void setKpprice(String kpprice) {
		this.kpprice = kpprice;
	}

	public void setKppricesum(String kppricesum) {
		this.kppricesum = kppricesum;
	}

	public void setKpnum(String kpnum) {
		this.kpnum = kpnum;
	}

	public void setKmnum(String kmnum) {
		this.kmnum = kmnum;
	}

	public void setDeleteyn(String deleteyn) {
		this.deleteyn = deleteyn;
	}

	public void setInsertdate(String insertdate) {
		this.insertdate = insertdate;
	}

	public void setUpdatedate(String updatedate) {
		this.updatedate = updatedate;
	}

	
}

SpringChabunDAO.java

package a.b.c.com.kosmo.common.dao;

import a.b.c.com.kosmo.product.vo.KosmoProductVO;
import a.b.c.com.kosmo.cart.vo.KosmoCartVO;

public interface SpringChabunDAO {

	public KosmoProductVO getProductChabun();
	public KosmoCartVO getCartChabun();
	
}

SpringChabunDAOImpl.java

package a.b.c.com.kosmo.common.dao;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import a.b.c.com.kosmo.cart.vo.KosmoCartVO;
import a.b.c.com.kosmo.product.vo.KosmoProductVO;

@Repository
public class SpringChabunDAOImpl implements SpringChabunDAO {

	Logger logger = LogManager.getLogger(SpringChabunDAOImpl.class);
	
	@Autowired(required=false)
	private SqlSessionTemplate sqlSession;
	
	@Override
	public KosmoProductVO getProductChabun() {
		// TODO Auto-generated method stub
		return sqlSession.selectOne("getProductChabun");
	}
	
	@Override
	public KosmoCartVO getCartChabun() {
		// TODO Auto-generated method stub
		return sqlSession.selectOne("getCartChabun");
	}
}

SpringChabunService.java

package a.b.c.com.kosmo.common.service;

import a.b.c.com.kosmo.product.vo.KosmoProductVO;
import a.b.c.com.kosmo.cart.vo.KosmoCartVO;

public interface SpringChabunService {

	public KosmoProductVO getProductChabun();
	
	public KosmoCartVO getCartChabun();
}

SpringChabunServiceImpl.java

package a.b.c.com.kosmo.common.service;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import a.b.c.com.kosmo.common.dao.SpringChabunDAO;
import a.b.c.com.kosmo.product.vo.KosmoProductVO;
import a.b.c.com.kosmo.cart.vo.KosmoCartVO;

@Service
@Transactional
public class SpringChabunServiceImpl implements SpringChabunService {

	Logger logger = LogManager.getLogger(SpringChabunServiceImpl.class);
	
	@Autowired(required=false)
	private SpringChabunDAO springChabunDAO;
	
	@Override
	public KosmoProductVO getProductChabun() {
		// TODO Auto-generated method stub
		logger.info("SpringChabunServiceImpl getProductChabun >>> : ");
		return springChabunDAO.getProductChabun();
	}

	@Override
	public KosmoCartVO getCartChabun() {
		// TODO Auto-generated method stub
		logger.info("SpringChabunServiceImpl getCartChabun >>> : ");
		
		return springChabunDAO.getCartChabun();
	}
}

4교시 (12:30-13:20)

장바구니 만들어서 넣기

KosmoCartDAO.java

package a.b.c.com.kosmo.cart.dao;

import java.util.List;

import a.b.c.com.kosmo.cart.vo.KosmoCartVO;

public interface KosmoCartDAO {
	
	public int kosmoCartInsert(KosmoCartVO kcvo);
	public List<KosmoCartVO> kosmoCartSelectAll(KosmoCartVO kcvo);
}

KosmoCartDAOImpl.java

package a.b.c.com.kosmo.cart.dao;

import java.util.List;

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

import a.b.c.com.kosmo.cart.vo.KosmoCartVO;

@Repository
public class KosmoCartDAOImpl implements KosmoCartDAO {
	Logger logger = LogManager.getLogger(KosmoCartDAOImpl.class);
	
	@Autowired(required=false)
	private SqlSession sqlSession;

	@Override
	public int kosmoCartInsert(KosmoCartVO kcvo) {
		// TODO Auto-generated method stub
		logger.info("KosmoCartDAOImpl kosmoCartInsert 함수 진입 >>> : ");			
		return (Integer)sqlSession.insert("kosmoCartInsert", kcvo);
	}

	@Override
	public List<KosmoCartVO> kosmoCartSelectAll(KosmoCartVO kcvo) {
		// TODO Auto-generated method stub
		logger.info("KosmoCartDAOImpl kosmoCartInsert 함수 진입 >>> : ");	
		return sqlSession.selectList("kosmoCartSelectAll", kcvo);
	}
}

KosmoCartService.java

package a.b.c.com.kosmo.cart.service;

import java.util.List;

import a.b.c.com.kosmo.cart.vo.KosmoCartVO;

public interface KosmoCartService {

	public int kosmoCartInsert(KosmoCartVO kcvo);
	public List<KosmoCartVO> kosmoCartSelectAll(KosmoCartVO kcvo);
}

KosmoCartServiceImpl.java

package a.b.c.com.kosmo.cart.service;

import java.util.List;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import a.b.c.com.kosmo.cart.dao.KosmoCartDAO;
import a.b.c.com.kosmo.cart.vo.KosmoCartVO;

@Service
@Transactional
public class KosmoCartServiceImpl implements KosmoCartService {
	Logger logger = LogManager.getLogger(KosmoCartServiceImpl.class);;
	
	// 서비스에서 DAO 연결하기 
	// 필드 @Autowired 어노테이션으로  DI (의존성 주입하기)
	@Autowired(required=false)
	private KosmoCartDAO kosmoCartDAO;

	@Override
	public int kosmoCartInsert(KosmoCartVO kcvo) {
		// TODO Auto-generated method stub
		logger.info("KosmoCartServiceImpls kosmoCartInsert 함수 진입 >>> : ");		
		return kosmoCartDAO.kosmoCartInsert(kcvo);
	}

	@Override
	public List<KosmoCartVO> kosmoCartSelectAll(KosmoCartVO kcvo) {
		// TODO Auto-generated method stub
		logger.info("KosmoCartServiceImpls kosmoCartSelectAll 함수 진입 >>> : ");		
		return kosmoCartDAO.kosmoCartSelectAll(kcvo);
	}

}

5교시 (14:30-15:20)

장바구니 만들어서 넣기

NulUtil.java

package a.b.c.com.kosmo.common;

public abstract class NulUtil {
	
	public static String comma_replace(String s) {
		return s.replace(",","");
	}
}

6교시 (15:30-16:20)

장바구니 만들어서 넣기

KosmoCartController.java

package a.b.c.com.kosmo.cart.controller;

import java.util.List;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import a.b.c.com.kosmo.cart.service.KosmoCartService;
import a.b.c.com.kosmo.cart.vo.KosmoCartVO;
import a.b.c.com.kosmo.common.ChabunUtil;
import a.b.c.com.kosmo.common.NulUtil;
import a.b.c.com.kosmo.common.service.SpringChabunService;

@Controller
public class KosmoCartController {
	Logger logger = LogManager.getLogger(KosmoCartController.class);
	
	// 필드 오토와이어드 
	@Autowired(required=false)	
	private SpringChabunService springChabunService;
	
	// 필드 오토와이어드 
	@Autowired(required=false)
	private KosmoCartService kosmoCartService;
	
	@RequestMapping(value="kosmoCartInsert", method=RequestMethod.GET)
	public String kosmoCartInsert(KosmoCartVO kcvo) {	
		logger.info("KosmoCartController kosmoCartInsert 함수 진입 >>> : ");	
		
		// 채번 
		String kcnum = ChabunUtil.getCartChabun("D", springChabunService.getCartChabun().getKcnum());
		kcvo.setKcnum(kcnum);
		logger.info("KosmoCartController kosmoCartInsert 함수 진입 >>> : kcnum " + kcnum);
		
		logger.info("kcvo.getKcnum() >>> : " + kcvo.getKcnum());
		logger.info("kcvo.getKpid() >>> : " + kcvo.getKpid());
		logger.info("kcvo.getKpname() >>> : " + kcvo.getKpname());
		logger.info("kcvo.getKpfile() >>> : " + kcvo.getKpfile());
		kcvo.setKpprice(NulUtil.comma_replace(kcvo.getKpprice()));
		logger.info("kcvo.getKpprice() >>> : " + kcvo.getKpprice());
		kcvo.setKppricesum(NulUtil.comma_replace(kcvo.getKppricesum()));
		logger.info("kcvo.getKppricesum() >>> : " + kcvo.getKppricesum());
		logger.info("kcvo.getKpnum() >>> : " + kcvo.getKpnum());
		logger.info("kcvo.getKmnum() >>> : " + kcvo.getKmnum());
		
		// 서비스 호출
		int nCnt = kosmoCartService.kosmoCartInsert(kcvo);
				
		if (nCnt > 0) {
			logger.info("KosmoProductController kosmoProductInsert nCnt >>> : " + nCnt);
			return "cart/kosmoCartInsert";
		}
					
		return "prodcut/kosmoProductSelectAll";
	}
	
	// 카트 목록 
	@RequestMapping(value="kosmoCartSelectAll", method=RequestMethod.GET)
	public String kosmoCartSelectAll(KosmoCartVO kcvo, Model model) {
		logger.info("KosmoCartController kosmoCartSelectAll 함수 진입 >>> : ");
		
		kcvo.setKmnum("1234");
		logger.info("kcvo.getKmnum() >>> : " + kcvo.getKmnum());
		
		// 페이징 처리
		
		// 서비스 호출
		List<KosmoCartVO> listAll = kosmoCartService.kosmoCartSelectAll(kcvo);		
		if (listAll.size() > 0) { 
			logger.info("KosmoCartController KosmoCartController listAll.size() >>> : " + listAll.size());		
			model.addAttribute("listAll", listAll);
			return "cart/kosmoCartSelectAll";
		}		
		return "product/kosmoProductSelectAll";
	}
}

7교시 (16:30-17:20)

장바구니 만들어서 넣기

kosmoCartInsert.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>
</head>
<body>
<h3>kosmoCartInsert.jsp</h3>
<hr>
<script>
	location.href="/kosmoSpring/kosmoCartSelectAll.k";
</script>
</body>
</html>

kosmoCartSelectAll.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%@ page import="a.b.c.com.kosmo.cart.vo.KosmoCartVO" %> 
<%@ page import="java.util.List" %>  

<%@ page import="a.b.c.com.kosmo.common.NumUtil" %>

<% request.setCharacterEncoding("UTF-8");%> 
<%

	Object obj = request.getAttribute("listAll");
	List<KosmoCartVO> list = (List<KosmoCartVO>)obj;
	
	int nCnt = list.size();
	System.out.println("nCnt >>> : " + nCnt);	
%>
    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript" src="/kosmoSpring/js/common.js"></script>
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script type="text/javascript">

	$(document).ready(function(){
		
		$(document).on("click", "#chek", function(){				
			if($(this).prop('checked')){			 			
				$('.kcnum').prop('checked',true);				
			}else{
				$('.kcnum').prop('checked',false);
			}
		});
		
	});	

</script>
</head>
<body>
<h3 align="center">장바구니</h3>
<hr>
<form name="productList" id="productList">
<table border="1" align="center">
<thead>
<tr>
	<td align="center">
		<input type="checkbox" name="chek" id="chek" class="chek">
	</td>
	<td align="center">상품명</td>
	<td align="center">가격</td>
	<td align="center">수량</td>
	<td align="center">합계</td>	
	<td align="center">적립금</td>	
	<td align="center">배송비</td>
	<td align="center">관심상품/삭제</td>		
</tr>
</thead>
<%
String kpnum = "";
String kpprice = "";
String kppricesum = "";
for(int i=0; i<nCnt; i++){		
	KosmoCartVO _kcvo = list.get(i);
	kpprice = NumUtil.comma(_kcvo.getKpprice());
	kppricesum = NumUtil.comma(_kcvo.getKppricesum());
%>
<tbody>
<tr>
	<td align="center">
		<input type="checkbox" name="kcnum" id="kcnum" class="kcnum" value=<%= _kcvo.getKcnum() %> >
	</td>		
	<td class="tt">
		<img src="/kosmoSpring/fileupload/product/<%= _kcvo.getKpfile() %>" width="50" height="50">
		<%= _kcvo.getKpid() %> 
	</td>
	<td class="tt"><%= kpprice %> </td>
	<td class="tt"><%= _kcvo.getKpcnt() %> </td>
	<td class="tt"><%= kppricesum %> </td>
	<td class="tt">적립금</td>
	<td class="tt">배송비</td>
	<td class="tt" align="center">		
		<input type="button" value="관심상품" /><br>
		<input type="button" value="삭제" /><br>
	</td>	
</tr>	
<%
}//end of for
%>	
<tr>
<td colspan="8" align="right">총 구매 금액 000,000원</td>
</tr>
<tr>
<td colspan="8" align="right">
	결제 예정금액 <font size="5" style="color:red;">000,000</font>원
</td>
</tr>
<tr>
	<td colspan="8" align="left">		
		<input type="button" value="선택삭제" id='delBtn'>	
		<input type="button" value="장바구니 비우기" id='delAllBtn'>
		 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
		<input type="button" value="쇼핑하기" id='shopingBtn'>							
		<input type="button" value="주문하기" id='buyBtn'>							
	</td>
</tr>					
</tbody>			
</table>
</form>	
</body>
</html>

8교시 (17:30-18:30)

장바구니 만들어서 넣기

index.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>
</head>
<body>
<h3>index.jsp</h3>
<hr>
<a href="kosmoProductInsertForm.k">코스모 상품 입력</a><br>
<a href="kosmoProductSelectAll.k">코스모 상품  전체조회</a><br>
</body>
</html>

Notes

728x90

저작자표시 (새창열림)