쌍용교육 -JSP수업 69일차 - ch06_mvcPageMVC(14)

CartDAO 내용추가

package kr.cart.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import kr.cart.vo.CartVO;
import kr.item.vo.ItemVO;
import kr.util.DBUtil;

public class CartDAO {
	//싱글턴 패턴
	private static CartDAO instance = new CartDAO();
	
	public static CartDAO getInstance() {
		return instance;
	}
	private CartDAO() {}
	
	//장바구니 등록
	public void insertCart(CartVO cart) throws Exception{
		Connection conn = null;
	      PreparedStatement pstmt = null;
	      String sql =null;
	      try {
	         //커넥션풀로부터 커넥션 할당
	         conn = DBUtil.getConnection();
	         
	         //SQL문 작성
	         sql="INSERT INTO zcart (cart_num,item_num,order_quantity,"
	         		+ "mem_num) VALUES(zcart_seq.nextval,?,?,?)";
	         //PreparedStatment 객체 생성
	         pstmt = conn.prepareStatement(sql);
	         //?에 데이터 바인딩
	         pstmt.setInt(1, cart.getItem_num());
	         pstmt.setInt(2, cart.getOrder_quantity());
	         pstmt.setInt(3, cart.getMem_num());
	         
	         pstmt.executeUpdate();
	      }catch(Exception e) {
	         throw new Exception(e);
	      }finally {
	         DBUtil.executeClose(null, pstmt, conn);
	      }
		
	}
	//회원번호별 총 구매액
	public int getTotalByMem_num(int mem_num) throws Exception{
		Connection conn = null;
	  	PreparedStatement pstmt = null;
	    String sql =null;
	    ResultSet rs = 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=?";
	         //PreparedStatment 객체 생성
	         pstmt = conn.prepareStatement(sql);
	         
	         //?에 데이터 바인딩
	         pstmt.setInt(1, mem_num);
	         //SQL문 실행   
	         rs = pstmt.executeQuery();
	         
	         if(rs.next()) { //위에 sql문 작성에서 sum을 썼기 떄문에 행은 무조건 1개만 나온다. 그러므로 if를 쓰고 getInt(1)을 쓴다.
	        	 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;
	      String sql =null;
	      List<CartVO> list = null;
	      ResultSet rs = 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 ";
	         
	         //PreparedStatment 객체 생성
	         pstmt = conn.prepareStatement(sql);
	         
	         //?에 데이터 바인딩
	         pstmt.setInt(1, mem_num);
	         
	         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()); //setSub_total은 테이블에는 없지만 사용하기위해 VO에 만들어준것
	        	
	        	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;
	    String sql =null;
	    ResultSet rs = null;
	    CartVO cartSaved = null;
	      try {
	         //커넥션풀로부터 커넥션 할당
	         conn = DBUtil.getConnection();
	         //SQL문 작성
	         sql="SELECT * FROM zcart WHERE item_num=? AND mem_num =?";
	         //PreparedStatment 객체 생성
	         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 updateCart(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 cart_num=?";
				//PreparedStatement 객체 생성
				pstmt = conn.prepareStatement(sql);
				//?에 데이터 바인딩
				pstmt.setInt(1, cart.getOrder_quantity());
				pstmt.setInt(2, cart.getCart_num());
				//SQL문 실행
				pstmt.executeUpdate();
			}catch(Exception e) {
				throw new Exception(e);
			}finally {
				DBUtil.executeClose(null, pstmt, conn);
			}
		}
	//장바구니 수정(상품번호와 회원번호별 수정)
	public void updateCartByItem_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=?";
	         //PreparedStatment 객체 생성
	         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);
	      }
	}
	//장바구니 삭제
	public void deleteCart(int cart_num)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try {
			//커넥션풀로부터 커넥션 할당
			conn = DBUtil.getConnection();
			//SQL문 작성
			sql = "DELETE FROM zcart WHERE cart_num=?";
			//PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			//?에 데이터 바인딩
			pstmt.setInt(1, cart_num);
			//SQL문 실행
			pstmt.executeUpdate();
		}catch(Exception e) {
			throw new Exception(e);
		}finally {
			
		}
	}
}

/*
  	  Connection conn = null;
  	  PreparedStatement pstmt = null;
      String sql =null;
      try {
         //커넥션풀로부터 커넥션 할당
         conn = DBUtil.getConnection();
         
         //SQL문 작성
         sql="";
         
         //PreparedStatment 객체 생성
         pstmt = conn.prepareStatement(sql);
         
         //?에 데이터 바인딩
         
         //SQL문 실행   
         
      }catch(Exception e) {
         throw new Exception(e);
      }finally {
         DBUtil.executeClose(null, pstmt, conn);
      }
  */

ModifyCartAction.java

package kr.cart.action;

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

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

import org.codehaus.jackson.map.ObjectMapper;

import kr.cart.dao.CartDAO;
import kr.cart.vo.CartVO;
import kr.controller.Action;
import kr.item.dao.ItemDAO;
import kr.item.vo.ItemVO;

public class ModifyCartAction implements Action{

	@Override
	public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
		Map<String,String> mapAjax = 
				          new HashMap<String,String>();
		
		HttpSession session = request.getSession();
		Integer user_num = 
				(Integer)session.getAttribute("user_num");
		if(user_num==null) {
			mapAjax.put("result", "logout");
		}else {
			//전송된 데이터 인코딩 타입 지정
			request.setCharacterEncoding("utf-8");
			//전송된 데이터 반환
			int item_num = Integer.parseInt(
					     request.getParameter("item_num"));
			int order_quantity = Integer.parseInt(
					     request.getParameter("order_quantity"));
			
			ItemDAO itemDAO = ItemDAO.getInstance();
			ItemVO item = itemDAO.getItem(item_num);
			if(item.getStatus()==1) {//상품 미표시
				mapAjax.put("result", "noSale");
			}else if(item.getQuantity() < order_quantity) {
				//상품 재고 수량보다 장바구니에 담은 구매 수량이 더 많음
				mapAjax.put("result", "overQuantity");
			}else {
				//표시 상품이며 재고가 부족하지 않음
				CartVO cart = new CartVO();
				cart.setCart_num(Integer.parseInt(
						request.getParameter("cart_num")));
				cart.setOrder_quantity(order_quantity);
				
				CartDAO cartDAO = CartDAO.getInstance();
				//구매 수량 변경
				cartDAO.updateCart(cart);
				
				mapAjax.put("result", "success");
			}			
		}
		//JSON 문자열로 변환
		ObjectMapper mapper = new ObjectMapper();
		String ajaxData = mapper.writeValueAsString(mapAjax);
		
		request.setAttribute("ajaxData", ajaxData);
		
		return "/WEB-INF/views/common/ajax_view.jsp";
	}
	
}

DeleteCartAction.java

package kr.cart.action;

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

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

import org.codehaus.jackson.map.ObjectMapper;

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

public class DeleteCartAction implements Action{

	@Override
	public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
		Map<String,String> mapAjax = new HashMap<String,String>();
		
		HttpSession session = request.getSession();
		Integer user_num = 
				(Integer)session.getAttribute("user_num");
		if(user_num==null) {
			mapAjax.put("result", "logout");
		}else {
			//전송된 데이터 인코딩 타입 지정
			request.setCharacterEncoding("utf-8");
			
			CartDAO dao = CartDAO.getInstance();
			dao.deleteCart(Integer.parseInt(
					request.getParameter("cart_num")));
			mapAjax.put("result", "success");
		}
		//JSON 데이터로 변환
		ObjectMapper mapper = new ObjectMapper();
		String ajaxData = mapper.writeValueAsString(mapAjax);
		
		request.setAttribute("ajaxData", ajaxData);
		
		return "/WEB-INF/views/common/ajax_view.jsp";
	}

}

table.sql 내용추가

--주문
create table zorder(
 order_num number not null,
 order_total number(9) not null,
 payment number(1) not null, --결제방식
 status number(1) default 1 not null, --배송 상태
 receive_name varchar2(30) not null,
 receive_post varchar2(5) not null,
 receive_address1 varchar2(90) not null,
 receive_address2 varchar2(90) not null,
 receive_phone varchar2(15) not null,
 notice varchar2(4000),
 reg_date date default sysdate not null,
 modify_date date,
 mem_num number not null,
 constraint zorder_pk primary key (order_num),
 constraint zorder_fk foreign key (mem_num)
                         references zmember (mem_num)
);
create sequence zorder_seq;

create table zorder_detail(
 detail_num number not null,
 item_num number not null,
 item_name varchar2(30) not null,
 item_price number(9) not null,
 item_total number(9) not null,
 order_quantity number(7) not null,
 order_num number not null,
 constraint zorder_detail_pk primary key (detail_num),
 constraint zorder_detail_fk foreign key (order_num)
                             references zorder (order_num)
);
create sequence zorder_detail_seq;

kr.order.vo => OrderVO.java

package kr.order.vo;

import java.sql.Date;

public class OrderVO {
	private int order_num;
	private int order_total;	//총구매금액
	private int payment;		//지불방식
	private int status;			//배송상태
	private String receive_name;
	private String receive_post;
	private String receive_address1;
	private String receive_address2;
	private String receive_phone;
	private String notice;
	private Date reg_date;
	private Date modify_date;
	private int mem_num;
	
	private String id;
	private String item_name;		//상품명
	
	public int getOrder_num() {
		return order_num;
	}
	public void setOrder_num(int order_num) {
		this.order_num = order_num;
	}
	public int getOrder_total() {
		return order_total;
	}
	public void setOrder_total(int order_total) {
		this.order_total = order_total;
	}
	public int getPayment() {
		return payment;
	}
	public void setPayment(int payment) {
		this.payment = payment;
	}
	public int getStatus() {
		return status;
	}
	public void setStatus(int status) {
		this.status = status;
	}
	public String getReceive_name() {
		return receive_name;
	}
	public void setReceive_name(String receive_name) {
		this.receive_name = receive_name;
	}
	public String getReceive_post() {
		return receive_post;
	}
	public void setReceive_post(String receive_post) {
		this.receive_post = receive_post;
	}
	public String getReceive_address1() {
		return receive_address1;
	}
	public void setReceive_address1(String receive_address1) {
		this.receive_address1 = receive_address1;
	}
	public String getReceive_address2() {
		return receive_address2;
	}
	public void setReceive_address2(String receive_address2) {
		this.receive_address2 = receive_address2;
	}
	public String getReceive_phone() {
		return receive_phone;
	}
	public void setReceive_phone(String receive_phone) {
		this.receive_phone = receive_phone;
	}
	public String getNotice() {
		return notice;
	}
	public void setNotice(String notice) {
		this.notice = notice;
	}
	public Date getReg_date() {
		return reg_date;
	}
	public void setReg_date(Date reg_date) {
		this.reg_date = reg_date;
	}
	public Date getModify_date() {
		return modify_date;
	}
	public void setModify_date(Date modify_date) {
		this.modify_date = modify_date;
	}
	public int getMem_num() {
		return mem_num;
	}
	public void setMem_num(int mem_num) {
		this.mem_num = mem_num;
	}
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getItem_name() {
		return item_name;
	}
	public void setItem_name(String item_name) {
		this.item_name = item_name;
	}
}

kr.order.vo => OrderDetailVO.java

package kr.order.vo;

public class OrderDetailVO {
	private int detail_num;
	private int item_num;
	private String item_name;
	private int item_price;
	private int item_total;		//동일 상품의 총주문금액
	private int order_quantity;
	private int order_num;
	
	public int getDetail_num() {
		return detail_num;
	}
	public void setDetail_num(int detail_num) {
		this.detail_num = detail_num;
	}
	public int getItem_num() {
		return item_num;
	}
	public void setItem_num(int item_num) {
		this.item_num = item_num;
	}
	public String getItem_name() {
		return item_name;
	}
	public void setItem_name(String item_name) {
		this.item_name = item_name;
	}
	public int getItem_price() {
		return item_price;
	}
	public void setItem_price(int item_price) {
		this.item_price = item_price;
	}
	public int getItem_total() {
		return item_total;
	}
	public void setItem_total(int item_total) {
		this.item_total = item_total;
	}
	public int getOrder_quantity() {
		return order_quantity;
	}
	public void setOrder_quantity(int order_quantity) {
		this.order_quantity = order_quantity;
	}
	public int getOrder_num() {
		return order_num;
	}
	public void setOrder_num(int order_num) {
		this.order_num = order_num;
	}
}

kr.order.dao => OrderDAO.java

package kr.order.dao;

public class OrderDAO {
	//싱글턴 패턴
	private static OrderDAO instance = new OrderDAO();
	
	public static OrderDAO getInstance() {
		return instance;
	}
	private OrderDAO() {}
	
	//주문 등록
	//관리자 - 전체 주문 개수/검색 주문 개수
	//관리자 - 전체 주문 목록/검색 주문 목록
	//사용자 - 전체 주문 개수/검색 주문 개수
	//사용자 - 전체 주문 목록/검색 주문 목록
	//개별 상품 목록
	//주문 삭제(삭제시 재고를 원상 복귀시키 않음, 주문 취소일 때 재고 수량 원상 복귀)
	//관리자/사용자 - 주문 상세
	//관리자/사용자 - 배송지정보 수정
	//관리자 - 배송상태 수정
	//사용자 - 주문 취소
	
}