1371
No
cart_view 쿼리
-- 주문 뷰 생성 ( 주문, 주문상세 회원, 상품 조인 ) create or replace view order_view as select d.odseq, o.oseq, o.id, o.indate, d.pseq,d.quantity, m.name mname, m.zip_num, m.address, m.phone, p.name pname, p.price2, d.result from tbl_orders o, tbl_order_detail d, tbl_member m, tbl_product p where o.oseq=d.oseq and o.id = m.id and d.pseq = p.pseq;
MyPageAction
package net.macaronics.web.controll;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import net.macaronics.web.controll.action.Action;
import net.macaronics.web.dao.OrderDAO;
import net.macaronics.web.dto.MemberVO;
import net.macaronics.web.dto.OrderVO;
public class MyPageAction implements Action {
@Override
public void execute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String url="mypage/mypage.jsp";
HttpSession session=request.getSession();
MemberVO loginUser =(MemberVO)session.getAttribute("loginUser");
if(loginUser==null){
url="MacaronicsServlet?command=login_form";
}else{
OrderDAO orderDAO =OrderDAO.getInstance();
//주문한 목록 중 미처리인 주문 번호 oseq 번호를 가져오기
// CREATE OR REPLACE VIEW ORDER_VIEW
// AS
// SELECT d.odseq, // tbl_order_detail 주문 상테이블의 primary key
// o.oseq, //TBL_ORDERS 주문 테이블의 주문 번호 primary key
// o.id,
// o.indate,
// d.pseq,
// d.quantity,
// m.name mname,
// m.zip_num,
// m.address,
// m.phone,
// p.name pname,
// p.price2,
// d.result
// FROM tbl_orders o,
// tbl_order_detail d,
// tbl_member m,
// tbl_product p
// WHERE o.oseq = d.oseq AND o.id = m.id AND d.pseq = p.pseq
//
List<Integer> oseqList=orderDAO.selectSeqOrdering(loginUser.getId());
//현재 진행 중인 주문 목록 대표 상품 외 2건식으로 출력하기기
ArrayList<OrderVO> orderList=new ArrayList<>();
//주문중인 번호 상품 oseq 를 반복문을 돌린다.
for(int oseq :oseqList){
//미처리 항목 1
List<OrderVO> orderListing=orderDAO.listOrderById(loginUser.getId(), "1", oseq);
//목록중 첫번째 값만 orderVO 객체에 담는다.
OrderVO orderVO=orderListing.get(0);
orderVO.setPname(orderVO.getPname() + " 외 "+ orderListing.size() + " 건");
//주문 된 상품 목록의 전체 가격을 price2 에 담는다.
int totalPrice=0;
for(OrderVO ovo : orderListing){
totalPrice +=ovo.getPrice2()*ovo.getQuantity();
}
orderVO.setPrice2(totalPrice);
orderList.add(orderVO);
}
request.setAttribute("title", loginUser.getName()+" 진행 중인 주문 내역");
request.setAttribute("orderList", orderList);
}
request.getRequestDispatcher(url).forward(request, response);
}
}
//현재 진행중인 주문처리 목록 상세 보기를 위한 tbl_orders 테이블의 주문번호 불러오기
public List<Integer> selectSeqOrdering(String id){
List<Integer> oseqList=new ArrayList<>();
try{
sqlSession=MybatisService.getFactory().openSession();
oseqList=sqlSession.selectList("order.selectSeqOrdering", id);
}catch(Exception e){
e.printStackTrace();
}finally{
MybatisService.sessionClose(sqlSession);
}
return oseqList;
}
//주문 목록 불러오기
public List<OrderVO> listOrderById(String id, String result, int oseq){
List<OrderVO> orderList=new ArrayList<>();
try{
sqlSession =MybatisService.getFactory().openSession();
Map<String, Object> map =new HashMap<>();
map.put("id", id);
map.put("result", result);
map.put("oseq", oseq);
orderList=sqlSession.selectList("order.listOrderById" , map);
}catch(Exception e){
e.printStackTrace();
}finally{
MybatisService.sessionClose(sqlSession);
}
return orderList;
}
Mybatis
<?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="order">
<!-- id="태그의 식별자" resultType="sql 명령어의 리턴타입(레코드의 자료형)" 샵{변수} => 입력매개변수 -->
<insert id="insertOrder">
insert into tbl_orders(oseq, id) values(orders_seq.nextval, #{id})
</insert>
<select id="selectMaxOseq" resultType="int">
select max(oseq) from tbl_orders
</select>
<insert id="insertOrderDetail">
insert into tbl_order_detail ( odseq, oseq, pseq, quantity)
values( order_detail_seq.nextval, #{oseq}, #{pseq}, #{quantity})
</insert>
<select id="listOrderById" resultType="net.macaronics.web.dto.OrderVO">
select * from order_view where id=#{id} and result=#{result} and oseq=#{oseq}
</select>
<select id="selectSeqOrdering" resultType="int">
select distinct oseq from order_view where id=#{id} and result='1' order by oseq desc
</select>
</mapper>
mypage.jsp
<%@ 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>
<jsp:include page="../include/Header.jsp" />
</head>
<body>
<jsp:include page="../include/HeaderMenu.jsp" />
<!-- catg header banner section -->
<section id="aa-catg-head-banner">
<img src="/dailyShop/img/slider/4.jpg" alt="fashion img" height="300" style="margin-left: auto; margin-right: auto; display: block;">
<div class="aa-catg-head-banner-area">
<div class="container">
</div>
</div>
</section>
<!-- / catg header banner section -->
<!-- product category -->
<section id="aa-product-category">
<div class="container">
<div class="row">
<div class="col-lg-9 col-md-9 col-sm-8 col-md-push-3">
<div class="aa-product-catg-content">
<div class="aa-product-catg-body">
<div class="table-responsive">
<table class="table table-striped">
<tr class="danger">
<th>주문일자</th>
<th>주문번호</th>
<th>상품명</th>
<th>결제 금액</th>
<th>주문 상세</th>
</tr>
<tr>
<c:forEach items="${orderList}" var="orderVO">
<tr>
<td><fmt:formatDate value="${orderVO.indate }" type="date" /></td>
<td>${orderVO.oseq }</td>
<td>${orderVO.pname }</td>
<td><fmt:formatNumber value="${orderVO.price2 }" type="currency" /></td>
<td><a href="MacaronicsServlet?command=order_detail&oseq=${orderVO.oseq}" class="btn btn-warning">조회</a></td>
</tr>
</c:forEach>
</tr>
</table>
</div>
</div>
<div class="text-center">
<input type="button" value="쇼핑 계속하기" class="btn btn-primary"
onclick="location.href='MacaronicsServlet?command=index'" >
</div>
</div>
</div>
<div class="col-lg-3 col-md-3 col-sm-4 col-md-pull-9">
<aside class="aa-sidebar">
<!-- single sidebar -->
<div class="aa-sidebar-widget">
<h3>나의 페이지</h3>
<ul class="aa-catg-nav">
<li><a href="MacaronicsServlet?command=cart_list">장바구니(cart)내역</a></li>
<li><a href="MacaronicsServlet?command=mypage">진행중인 주문내역</a></li>
<li><a href="MacaronicsServlet?command=order_all">총 주문내역</a></li>
</ul>
</div>
</aside>
</div>
</div>
</div>
</section>
<!-- / product category -->
<jsp:include page="../include/Footer.jsp" />
제작 : macaronics.net - Developer Jun Ho Choi
소스 : https://github.com/braverokmc79/jsp_sin
${request.getContextPath() } 처리를 안한 부분이 있으므로
루트 설정( http://macaronics.net/index.php/m01/jsp/view/1352) 및 server.xml 에서 DB 컨넥션 설정은 필수 설정이다.
댓글 ( 4)
댓글 남기기