TIL 0528

먼지·2024년 5월 29일
MVC패턴TIL
0

Today I Learned

목록 보기
68/89
post-thumbnail

DAO

회원 번호별 총 구매액

//회원번호별 총구매액
	public int getTotalByMem_num(int mem_num)
	                                  throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		int total = 0;
		try {
			//커넥션풀로부터 커넥션 할당
			conn = DBUtil.getConnection();
			//SQL문 작성
			sql = "SELECT SUM(sub_total) FROM (SELECT mem_num,"
				+ "order_quantity * price AS sub_total FROM zcart "
				+ "JOIN zitem USING(item_num)) WHERE mem_num=?";
			//PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			//?에 데이터 바인딩
			pstmt.setInt(1, mem_num);
			//SQL문 실행
			rs = pstmt.executeQuery();
			if(rs.next()) {
				total = rs.getInt(1);
			}
		}catch(Exception e) {
			throw new Exception(e);
		}finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}		
		return total;
	}

장바구니 목록

	//장바구니 목록
	public List<CartVO> getListCart(int mem_num)
	                                   throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<CartVO> list = null;
		String sql = null;
		try {
			//커넥션풀로부터 커넥션 할당
			conn = DBUtil.getConnection();
			//SQL문 작성
			sql = "SELECT * FROM zcart c JOIN zitem i USING(item_num) "
				+ "WHERE mem_num=? ORDER BY c.reg_date DESC";
			//PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			//?에 데이터 바인딩
			pstmt.setInt(1, mem_num);
			//SQL문 실행
			rs = pstmt.executeQuery();
			list = new ArrayList<CartVO>();
			while(rs.next()) {
				CartVO cart = new CartVO();
				cart.setCart_num(rs.getInt("cart_num"));
				cart.setItem_num(rs.getInt("item_num"));
				cart.setOrder_quantity(rs.getInt("order_quantity"));
				cart.setMem_num(rs.getInt("mem_num"));
				
				//상품 정보를 담기위해 ItemVO 객체 생성
				ItemVO item = new ItemVO();
				item.setName(rs.getString("name"));
				item.setPrice(rs.getInt("price"));
				item.setPhoto1(rs.getString("photo1"));
				item.setQuantity(rs.getInt("quantity"));
				item.setStatus(rs.getInt("status"));
				
				//ItemVO를 CartVO에 저장
				cart.setItemVO(item);
				
				//동일 상품(item_num이 같은 상품)의 총구매 금액 구하기
				cart.setSub_total(
					cart.getOrder_quantity()*item.getPrice());
				
				list.add(cart);
			}
		}catch(Exception e) {
			throw new Exception(e);
		}finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}		
		return list;
	}

장바구니 상세

	//장바구니 상세
	public CartVO getCart(CartVO cart)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		CartVO cartSaved = null;
		String sql = null;
		try {
			//커넥션풀로부터 커넥션 할당
			conn = DBUtil.getConnection();
			//SQL문 작성
			sql = "SELECT * FROM zcart WHERE item_num=? AND mem_num=?";
			//PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			//?에 데이터 바인딩
			pstmt.setInt(1, cart.getItem_num());
			pstmt.setInt(2, cart.getMem_num());
			//SQL문 실행
			rs = pstmt.executeQuery();
			if(rs.next()) {
				cartSaved = new CartVO();
				cartSaved.setCart_num(rs.getInt("cart_num"));
				cartSaved.setItem_num(rs.getInt("item_num"));
				cartSaved.setOrder_quantity(rs.getInt("order_quantity"));
			}
		}catch(Exception e) {
			throw new Exception(e);
		}finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}		
		return cartSaved;
	}

장바구니 수정 - 상품 번호, 회원 번호 별

	//장바구니 수정 (상품번호와 회원번호별 수정)
	public void updaetCartByItem_num(CartVO cart)
	                                     throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try {
			//커넥션풀로부터 커넥션 할당
			conn = DBUtil.getConnection();
			//SQL문 작성
			sql = "UPDATE zcart SET order_quantity=? "
				+ "WHERE item_num=? AND mem_num=?";
			//PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			//?에 데이터 바인딩
			pstmt.setInt(1, cart.getOrder_quantity());
			pstmt.setInt(2, cart.getItem_num());
			pstmt.setInt(3, cart.getMem_num());
			//SQL문 실행
			pstmt.executeUpdate();
		}catch(Exception e) {
			throw new Exception(e);
		}finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}
}

Action

List

package kr.cart.action;

import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import kr.cart.dao.CartDAO;
import kr.cart.vo.CartVO;
import kr.controller.Action;

public class ListAction implements Action{

	@Override
	public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
		HttpSession session = request.getSession();
		Integer user_num = 
				(Integer)session.getAttribute("user_num");
		if(user_num == null) {//로그인 되지 않은 경우
			return "redirect:/member/loginForm.do";
		}
		//로그인 된 경우
		CartDAO dao = CartDAO.getInstance();
		//회원번호별 총구매액
		int all_total = dao.getTotalByMem_num(user_num);
		
		List<CartVO> list = null;
		if(all_total>0) {
			list = dao.getListCart(user_num);
		}
			
		request.setAttribute("all_total", all_total);
		request.setAttribute("list", list);	
		
		return "/WEB-INF/views/cart/list.jsp";
	}

}

View

Header - 추가 작성

<c:if test="${!empty user_num}">
		<li>
			<a href="${pageContext.request.contextPath}/cart/list.do">장바구니</a>
		</li>
		<li>
			<a href="${pageContext.request.contextPath}/member/myPage.do">MY페이지</a>
		</li>
</c:if>

List

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>장바구니</title>
<link rel="stylesheet" href="${pageContext.request.contextPath}/css/style.css" type="text/css">
<script type="text/javascript" src="${pageContext.request.contextPath}/js/jquery-3.7.1.min.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/js/shop.cart.js"></script>
</head>
<body>
<div class="page-main">
	<jsp:include page="/WEB-INF/views/common/header.jsp"/>
	<div class="content-main">
		<h2>장바구니</h2>
		<c:if test="${empty list}">
		<div class="result-display">
			장바구니에 담은 상품이 없습니다.
		</div>
		</c:if>
		<c:if test="${!empty list}">
		<form id="cart_order" action="${pageContext.request.contextPath}/order/orderForm.do" method="post">
			<table>
				<tr>
					<th>상품명</th>
					<th>수량</th>
					<th>상품가격</th>
					<th>합계</th>
				</tr>
				<c:forEach var="cart" items="${list}">
				<tr>
					<td>
						<a href="${pageContext.request.contextPath}/item/detail.do?item_num=${cart.item_num}">
							<img src="${pageContext.request.contextPath}/upload/${cart.itemVO.photo1}" width="80">
							${cart.itemVO.name}
						</a>
					</td>
					<td class="align-center">
						<c:if test="${cart.itemVO.status == 1 or cart.itemVO.quantity < cart.order_quantity}">[판매중지]</c:if>
						<c:if test="${cart.itemVO.status == 2 and cart.itemVO.quantity >= cart.order_quantity}">
						<input type="number" name="order_quantity"
						  min="1" max="${cart.itemVO.quantity}"
						  value="${cart.order_quantity}" 
						  class="quantity-width">
						  <br>
						  <input type="button" value="변경" class="cart-modify"
						   data-cartnum="${cart.cart_num}"
						   data-itemnum="${cart.item_num}">	
						</c:if>
					</td>
					<td class="align-center">
						<fmt:formatNumber value="${cart.itemVO.price}"/>원
					</td>
					<td class="align-center">
						<fmt:formatNumber value="${cart.sub_total}"/>원
						<br>
						<input type="button" value="삭제" class="cart-del"
						  data-cartnum="${cart.cart_num}">
					</td>
				</tr>
				</c:forEach>
				<tr>
					<td colspan="3" class="align-right"><b>총구매금액</b></td>
					<td class="align-center"><fmt:formatNumber value="${all_total}"/>원</td>
				</tr>
			</table>    
			<div class="align-center">
				<input type="submit" value="구매하기">
			</div>   
		</form>
		</c:if>
	</div>
</div>	
</body>
</html>

Properties

Cart

/cart/list.do=kr.cart.action.ListAction
profile
먼지
Lucky Things🍀
이전 포스트

TIL 0527

다음 포스트

TIL 0529

0개의 댓글