[플젝] 2PicGames - 장바구니

서현서현·2022년 5월 30일
0

DB, SQL

목록 보기
26/27

구조

1.SqlMap

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="cart">
		
	<insert id="insertCart" parameterClass="CartVO">
		insert into CART values(fn_cart_id, #mem_id#, sysdate, #cart_price#)
	</insert>

	
	<insert id="insertGameCart" parameterClass="String">
	insert into cart_game values(fn_cartgame_id, #gameId#)
	</insert>


<!-- 	<select id="getCartList">
		select * from cart
	</select>
	 -->
	
	<select id="getPrice" parameterClass="String" resultClass="String">
		select game_price
		from game
		where game_id = #gameId#
	</select>
	
	<!-- VO에 쓸데없는 컬럼은 넣지 말것!!!  -->
	<resultMap class="CartVO" id="CartMap">
		<result property="game_id" column="game_id"/>
		<result property="cart_price" column="cart_price"/>
		<result property="cartNo" column="cart_no"/>
	</resultMap>
	
	<select id="getCart" parameterClass="String" resultMap="CartMap">
		select distinct a.cart_price, b.game_id, a.cart_no
		from cart a
		inner join cart_game b
		on(a.CART_NO = b.CART_NO)
		and a.mem_id= #mem_id#
	</select>
	
	<!-- 사용자의 결제하지않은 장바구니 모두 불러오기 -->
	<resultMap class="CartVO" id="CartMap">
		<result property="cart_price" column="cart_price"/>
		<result property="cartNo" column="cart_no"/>
	</resultMap>
	<select id="selectCart" parameterClass="String" resultMap="CartMap">
		select cart_no, cart_price
		from cart
		where not cart_no in (
		    select a.cart_no
		    from cart a
		    inner join orders b
		    on(a.cart_no = b.cart_no)
		)
		and mem_id = #mem_id#
	</select>
	<!-- 사용자의 결제하지않은 장바구니에 해당하는 게임정보 불러오기 -->
	<select id="getSelectGameId" parameterClass="String" resultClass="String">
		select game_id
		from cart_game
		where cart_no = #cartNo#
	</select>
	
	
	<!--  헐 이걸로 존재검사도 겸하자!!  -->
	<select id="checkExist" parameterClass="CartVO" resultClass="String">
		select a.cart_no
		from cart a
		inner join cart_game b
		on(a.CART_NO = b.CART_NO)
		where a.mem_id = #mem_id# and b.game_id = #game_id#
	</select>
	
	
	<!-- 흠 매개변수로 cart_no가 엄청 많을수도 있음... 여러번 수행해야함 바깥에서 반복문으로 넣어볼게... -->
	<delete id="deleteCart" parameterClass="String">
		delete from cart
		where cart_no = #cartNo#
	</delete>
	
	<delete id="deleteCartGame" parameterClass="String">
		delete from cart_game
		where cart_no = #cartNo#
	</delete>
	
	
	<!-- 주문완료 테이블에 있는 CartNo를 조회하는 쿼리 -->
	<select id="orderList" resultClass="String">
		select cart_no
		from orders
	</select>
	<!-- 주문중단시 사용할 delete연산.... -->
<!-- 	<delete id="deleteOrder" parameterClass="String">
		delete from order
		where cart_no = #cartNo#
	</delete> -->
	
	
</sqlMap>

2.CartVO

package orders.cart.vo;

public class CartVO {
	private String cartNo;
	private String mem_id;
	private String cart_price;
	private String game_id;
	
	
	public String getGameId() {
		return game_id;
	}
	public void setGameId(String gameId) {
		this.game_id = gameId;
	}
	public String getCartNo() {
		return cartNo;
	}
	public void setCartNo(String cartNo) {
		this.cartNo = cartNo;
	}
	public String getMemId() {
		return mem_id;
	}
	public void setMemId(String memId) {
		this.mem_id = memId;
	}
	public String getCartPrice() {
		return cart_price;
	}
	public void setCartPrice(String cartPrice) {
		this.cart_price = cartPrice;
	}


}

3.CartDAO

ICartDAO

package orders.cart.dao;

import java.util.List;


import orders.cart.vo.CartVO;
import orders.vo.OrderVO;

public interface ICartDAO {
	
	public int insertCart(CartVO vo);
	
	public int insertCartGame(String gameId);

	public String getPrice(String gameId);
	
	public List<CartVO> getCartList(String mem_id);
	
	public String checkExist(CartVO vo);
	
	public int deleteCart(String cartNo);
	public int deleteCartGame(String cartNo);

	public List<String> orderList();
	
	public List<CartVO> getSelectCart(String mem_id);
	public String getSelectGame(String cartNo);
	
	
}

CartDAOImpl

package orders.cart.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ibatis.sqlmap.client.SqlMapClient;

import config.SqlMapClientFactory;
import orders.cart.vo.CartVO;

public class CartDAOImpl implements ICartDAO {

	private static ICartDAO dao;
	private SqlMapClient smc;
	
	private CartDAOImpl() {
		smc = SqlMapClientFactory.getInstance();
	}
	
	public static ICartDAO getInstance() {
		if(dao == null) {
			dao= new CartDAOImpl();
		}
		return dao;
	}
	
	
	
	
	//////////////////////////인서트 관련/////////////////////////////////////////////////////////
	
	@Override
	public int insertCart(CartVO vo) {
		
		int cnt =0;
		try {
			cnt = smc.update("cart.insertCart", vo);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cnt;
	}

	@Override
	public int insertCartGame(String gameId) {
		// TODO Auto-generated method stub
		int cnt =0;
		try {
			cnt = (int) smc.update("cart.insertGameCart",gameId);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cnt;
	}

	@Override
	public String getPrice(String gameId) {
		String price= null;
		
		try {
			price = (String) smc.queryForObject("cart.getPrice",gameId);
			System.out.println("게임의 가격 찾아오기 : "+price);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return price;
	}

	
	
	////////////////////////////조회 관련/////////////////////////////////////////////////////////
	
	
	@Override
	public List<CartVO> getCartList(String mem_id) {
		List<CartVO> cartList = new ArrayList<CartVO>();
		
		try {
					
			cartList = smc.queryForList("cart.getCart",mem_id);

				} catch (SQLException ex) {
					ex.printStackTrace();
					throw new RuntimeException("회원의 장바구니 조회중 예외 발생!",ex);
				}
		
		return cartList;
	}

	
	@Override
	public List<String> orderList() {
		
		List<String> orderLsit = null;
		
		try {
					
			orderLsit = (List<String>) smc.queryForList("cart.orderList");

				} catch (SQLException ex) {
					ex.printStackTrace();
					throw new RuntimeException("주문완료한 카트넘버 쿼리해오는중 예외 발생!",ex);
				}
		
		return orderLsit;
	}
	
	
	///////////////////////////삭제 관련/////////////////////////////////////////////////////////
	
	
	// 삭제할 cartNo를 검색하는 메소드
	@Override
	public String checkExist(CartVO vo) {
		
		String check = null;
		
		try {
					
			check = (String) smc.queryForObject("cart.checkExist",vo);

				} catch (SQLException ex) {
					ex.printStackTrace();
					throw new RuntimeException("카트넘버 쿼리해오는중 예외 발생!",ex);
				}
		
		return check;
	}

	
	
	// cartNo를 이용해 장바구니목록에서 삭제하는 메소드
	// 두 테이블 모두 삭제해야하니 delete도 두번
	// 심지어 cartNo가 상당히 많을수도 있으니 for문을 이용해 List안에 들어있는 cartNo의 갯수만큼 반복문 실행
	@Override
	public int deleteCart(String cartNo) {
		int cnt = 0;

		try {
			cnt = smc.delete("cart.deleteCart",cartNo);
			
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new RuntimeException("Cart테이블 게임 삭제중 예외 발생",ex);
		}
		
		return cnt;
	}

	@Override
	public int deleteCartGame(String cartNo) {
		int cnt = 0;

		try {
			cnt = smc.delete("cart.deleteCartGame",cartNo);
			
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new RuntimeException("CartGme테이블 게임 삭제중 예외 발생",ex);
		}
		
		return cnt;
	}

	
	
	@Override
	public List<CartVO> getSelectCart(String mem_id) {
		List<CartVO> selectCartList = new ArrayList<CartVO>();
		
		try {
					
			selectCartList = smc.queryForList("cart.selectCart",mem_id);

				} catch (SQLException ex) {
					ex.printStackTrace();
					throw new RuntimeException("회원이 주문완료한 게임을 제외한 장바구니목록 조회중 예외발생!",ex);
				}
		
		return selectCartList;
	}

	@Override
	public String getSelectGame(String cartNo) {
		String selectGame = "";
		
		try {
					
			selectGame = (String) smc.queryForObject("cart.getSelectGameId",cartNo);
			System.out.println("셀렉트겜네임 쿼리 잘되나 >>"+selectGame);

				} catch (SQLException ex) {
					ex.printStackTrace();
					throw new RuntimeException("주문완료 제외 장바구니의 게임아이디 조회중 예외!",ex);
				}
		
		return selectGame;
	}

}

4.CartService

ICartService

package orders.cart.service;

import java.util.List;

import orders.cart.vo.CartVO;
import orders.vo.OrderVO;

public interface ICartService {
	
	public String getPrice(String gameId);
	public int insertCart(CartVO vo);
	public int insertCartGame(String gameId);
	
	public List<CartVO> getCartList(String memId);
	
	public String checkExist(CartVO vo);
	
	public int deleteCart(String cartNo);
	public int deleteCartGame(String cartNo);
	
	public List<String> orderList();
	
	public List<CartVO> getSelectCart(String mem_id);
	public String getSelectGame(String cartNo);
	
	
	
}

CartServiceImpl

package orders.cart.service;

import java.util.List;

import orders.cart.dao.CartDAOImpl;
import orders.cart.dao.ICartDAO;
import orders.cart.vo.CartVO;
import orders.vo.OrderVO;

public class CartServiceImpl implements ICartService {

	private static ICartService service;
	private ICartDAO dao;
	
	private CartServiceImpl() {
		dao= CartDAOImpl.getInstance();
	}
	
	public static ICartService getInstance() {
		if(service == null) {
			service = new CartServiceImpl();
		}
		return service;
	}
	
	
	
	
	
	@Override
	public int insertCart(CartVO vo) {
		return dao.insertCart(vo);
	}
	@Override
	public int insertCartGame(String gameId) {
		return dao.insertCartGame(gameId);
	}
	@Override
	public String getPrice(String gameId) {
		return dao.getPrice(gameId);
	}

	
	
	
	
	
	
	
	@Override
	public List<CartVO> getCartList(String memId) {
		return dao.getCartList(memId);
	}

	
	
	
	
	@Override
	public String checkExist(CartVO vo) {
		return dao.checkExist(vo);
	}

	
	
	@Override
	public int deleteCart(String cartNo) {
		return dao.deleteCart(cartNo);
	}

	@Override
	public int deleteCartGame(String cartNo) {
		return dao.deleteCartGame(cartNo);
	}

	
	@Override
	public List<String> orderList() {
		return dao.orderList();
	}

	@Override
	public List<CartVO> getSelectCart(String mem_id) {
		// TODO Auto-generated method stub
		return dao.getSelectCart(mem_id);
	}

	@Override
	public String getSelectGame(String cartNo) {
		return dao.getSelectGame(cartNo);
				
	}
	
}

5.CartController

CartDetailController

package orders.cart.controller;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import games.game.vo.FreeGameVO;
import games.game.vo.GameVO;
import members.member.vo.MemberVO;
import orders.cart.service.CartServiceImpl;
import orders.cart.service.ICartService;
import orders.cart.vo.CartVO;
import orders.service.IOrderService;
import orders.service.OrderServiceImpl;
import wishList.service.IWishListService;
import wishList.service.WishListServiceImpl;
import wishList.vo.WishListVO;

@WebServlet("/cart/cartDetail.do")
public class CartDetailController extends HttpServlet {

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		System.out.println("\n\n겟카트컨트롤러에 들어옴");
		System.out.println("멤버 아이디 잘들어오나확인해볼게");

		// 장바구니 안의 gameID, price를 쿼리해와서 jsp에 뿌려줄거임
		ICartService cartService = CartServiceImpl.getInstance();

		HttpSession session = req.getSession();
		if(session.getAttribute("loginMember") == null) {
			req.setAttribute("msg", "장바구니 조회는 로그인후에 가능합니다.");
			req.getRequestDispatcher("/main/main.jsp").forward(req, resp);
			return;
		}
		
		MemberVO member = (MemberVO) session.getAttribute("loginMember");

		
		 String memId = member.getMem_id();
		 System.out.println("멤버 아이디 잘들어오나?????"+memId);

		// 애초에 오더에 없는 카트만 불러오는 쿼리
		List<CartVO> selectCartList = cartService.getSelectCart(memId);
		// 게임정보도 띄워줘야하니 위 리스트 VO에 들어있는 cartNo로 정보 서치해주기
		List<String> selectGameList = new ArrayList<String>();
		for(CartVO vo : selectCartList) {
			selectGameList.add(cartService.getSelectGame(vo.getCartNo()));
		}
		
		
		for(CartVO vo : selectCartList) {
			System.out.println("카트리스트:"+vo.getCartNo());
		}
		
		for(String vo : selectGameList) {
			System.out.println("겜리스트:"+vo);
		}

		// 주문완료된거 제외한 사용자의장바구니 (cartPrice와 cartNo 가짐)
		req.setAttribute("selectCartList", selectCartList);
		// 주문완료된거 제외한 사용자의장바구니에 해당하는 게임들 (gameId 가짐)
		req.setAttribute("selectGameList", selectGameList);
		// 사용자가 주문할수있는 카트 내 게임에 대한 게임ID를 가져다 게임정보를 가져오자
		
		IOrderService orderService = OrderServiceImpl.getInstance();
		List<GameVO> gameInfoList = new ArrayList<GameVO>();
		for(String vo : selectGameList) {
			gameInfoList.add(orderService.getGamesInfo(vo));
		}
		
		
		// 한시적무료게임과 그 게임의 가격
		IWishListService wishService = WishListServiceImpl.getInstance();
		List<FreeGameVO> freeGameList = new ArrayList<FreeGameVO>();
		for(String vo : selectGameList) {
			if(wishService.freeGameInWish(vo) != null) {
				freeGameList.add(wishService.freeGameInWish(vo));
			}
		}
		for(FreeGameVO vo : freeGameList) {
			System.out.println("[리스트] 장바구니에 있는 무료게임 아이디 : "+vo.getGame_id());
		}
		
		req.setAttribute("freeGameList", freeGameList);
		
		
		req.setAttribute("gameInfoList", gameInfoList);
		
		req.getRequestDispatcher("/cart/cartDetail.jsp").forward(req, resp);

	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

	}

}

InsertCartController

package orders.cart.controller;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import members.member.vo.MemberVO;
import orders.cart.service.CartServiceImpl;
import orders.cart.service.ICartService;
import orders.cart.vo.CartVO;
import wishList.service.IWishListService;
import wishList.service.WishListServiceImpl;
import wishList.vo.WishListVO;

@WebServlet("/cart/insertCart.do")
public class InsertCartController extends HttpServlet{
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		HttpSession session = req.getSession();
		MemberVO member = (MemberVO) session.getAttribute("loginMember");
		
		// gameId랑 memId가 넘어온다고 치고
		String memId = member.getMem_id();
		
		String gameId = req.getParameter("gameId");
		System.out.println("insert카트에서 세션의 아이디 잘 들어오나?"+memId);
		
		ICartService cartService = CartServiceImpl.getInstance();
		CartVO vo = new CartVO();
		// vo.setCart_no(cart_no); 함수로 오라클에서 자동생성
		//vo.setCart_price(cartPrice); 가격정보는 어디서 받아오는거지...
		vo.setMemId(memId);
		vo.setGameId(gameId);
		// vo에 넣어줄 가격은 어디서 얻어?? >> 쿼리 하나 추가하자
		
		String price = cartService.getPrice(gameId);
		
		// price 잘 찍히는지 보고... Vo에 적재
		vo.setCartPrice(price);
		
		
		// 카트에 인서트 하기전에 이미 넣었던 게임인지 확인하기!!!!!!!!!!!!!!!!!!!!!!!!
		
		String check = cartService.checkExist(vo);
		int cnt1=0;
		int cnt2=0;
		if(check != null) {
			System.out.println("장바구니에 이미 존재함");
			
		} else {
			
			cnt1 = cartService.insertCart(vo); // memId, gameId, price 넣음 sysdate는 설정할필요없고..
			
			cnt2 = cartService.insertCartGame(gameId);	

		}
		
		
		String msg ="";
		
		if(cnt1>0&&cnt2>0) {
			msg = "장바구니 담기 성공";
		}else {
			msg="장바구니 담기 실패";
		}
		
		//System.out.println(msg+"!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
		
		session.setAttribute("msg", msg);
		
		//와 여기 오류 알았다 RedirectUrl에 
		String redirectUrl = req.getContextPath()+"/detailGame.do?id="+gameId;
		resp.sendRedirect(redirectUrl);
		
	}

	
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		doGet(req, resp);
	}
	
	
}

DeleteCartController

package orders.cart.controller;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import members.member.vo.MemberVO;
import orders.cart.service.CartServiceImpl;
import orders.cart.service.ICartService;
import orders.cart.vo.CartVO;

@WebServlet("/cart/deleteCart.do")
public class DeleteCartController extends HttpServlet{

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		
		HttpSession session = req.getSession();
		MemberVO vvo = (MemberVO) session.getAttribute("loginMember");
		String memId = vvo.getMem_id();	
		System.out.println(memId);
		
		String gameId = req.getParameter("gameId");
		
		CartVO vo = new CartVO();
		vo.setGameId(gameId);
		vo.setMemId(memId);
		
		ICartService cartService = CartServiceImpl.getInstance();
		String cartNo = null;
		
		cartNo = cartService.checkExist(vo);
		
		
		// 이제 삭제를 해보자
		int cnt1 =0;
		int cnt2 =0;
		
		if(cartNo != null) {
			// 이미 테이블에 존재할때 삭제 ㄱㄴ
			cnt1 = cartService.deleteCartGame(cartNo);
			cnt2 = cartService.deleteCart(cartNo);			
		}else {
			System.out.println("테이블에 해당 사용자의 게임이 존재하지 않음..........");
		}
		
		String msg ="";
		
		if(cnt1>0 && cnt2>0) {
			msg = "성공";
		}else {
			msg="실패";
		}
		
		session.setAttribute("msg", msg);
		
		String redirectUrl = req.getContextPath()+"/cart/cartDetail.do";
		resp.sendRedirect(redirectUrl);
	
		
		
		
	}

	
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		doGet(req, resp);
	}
	
	
}
profile
서현서현
이전 포스트

[플젝] 2PicGames - 장바구니 구현시 고려사항

다음 포스트

[플젝] 2PicGames - 주문완료테이블 컬럼문제

0개의 댓글