[Spring] 상품별 장바구니 목록 prod-cart-member

JSP

list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<script type="text/javascript" src="/resources/js/jquery.min.js"></script>
<div class="row">
<div class="col-md-6 card" style="width: 45%">
	<div class="card-header">
		<h3>상품 테이블</h3>
	</div>
	<div class="card-body row" style="justify-content: space-between;">
				<table class="table table-bordered">
			<thead>
				<tr>
					<th>순번</th>
					<th>상품명</th>
					<th>판매가</th>
					<th>거래처</th>
				</tr>
			</thead>
			<c:choose>
			    <c:when test="${param.currentPage == null || param.currentPage == '1'}">
			        <c:set var="rowNumber" value="0"/>
			    </c:when>
			    <c:otherwise>
			        <c:set var="rowNumber" value="${(param.currentPage-1) * 10}"/> <!-- 다른 페이지에 대한 로직 -->
			    </c:otherwise>
			</c:choose>			
			<tbody id="prodTable">
				<c:forEach var="buyer" items="${data.content}">
					<c:forEach var="prod" items="${buyer.prodVoList}">
						<tr class="click" data-prod-id="${prod.prodId}">
							<td>${rowNumber + 1}</td>
							<td>${prod.prodName}</td>
							<td><fmt:formatNumber value="${prod.prodSale}" pattern="#,###" type="number"/></td>
							<td>${buyer.buyerName}</td>
						</tr>
						 <c:set var="rowNumber" value="${rowNumber + 1}"/>
					</c:forEach>
				</c:forEach>
			</tbody>
		</table>
		<div class="card-footer clearfix" style="width: 100%">${data.pagingArea}</div>
	</div>
</div>

<div class="col-md-6 card" style="width: 45%">
	<div class="card-header">
		<h3>상품 별 장바구니 목록 현황</h3>
	</div>
	<div class="card-body">
				<table class="table table-bordered">
			<thead>
				<tr>
					<th>순번</th>
					<th>카트 번호</th>
					<th>수량</th>
					<th>회원 아이디</th>
					<th>회원명</th>
				</tr>
			</thead>
			<tbody id="cartlist">
				<tr>
					<td colspan="5">상품을 선택해주세요</td>
				</tr>
			</tbody>
		</table>
	</div>
</div>
</div>
<script>
$(function(){
	$('.click').on('click',function(){
		$('#prodTable tr').css({"background": "white", "color":"black"});
		$(this).css({"background": "#007bff", "color":"white"});
		let prodId = $(this).data("prodId");
		$.ajax({
			url : "/prod/listAjax",
			data : JSON.stringify({"prodId" : prodId}),
			contentType : 'application/json',
			dataType : "json",
			type : "post",
			success : function(res){
				let str = "";
				console.log(res[0]);
				let indx = 1;
				$.each(res[0].cartVoList, function(i, vo){
					str += "<tr> <td>"+ indx++ +"</td>";
					str += "<td>"+ vo.cartNo +"</td>";
					str += "<td>"+ vo.cartQty +"</td>";
					str += "<td>"+ vo.cartMember +"</td>";
					str += "<td>"+ vo.memVo.memName +"</td></tr>";
				})
				
				$('#cartlist').html(str);
			}
		})
	})	
})
</script>

JAVA

ProdVO.java

package kr.or.ddit.vo;

import java.util.Date;
import java.util.List;

import lombok.Data;

@Data
public class ProdVO {             
	private String prodId;
	private String prodName;
	private String prodLgu;
	private String prodBuyer;
	private int prodCost;
	private int prodPrice;
	private int prodSale;
	private String prodOu;
	private String prodDetail;
	private String prodImg;
	private int prodTotalstock;
	private Date prodInsdate;
	private String prodOutline;
	private int prodProperstock;
	private String prodSize;
	private String prodColor;
	private String prodDelivery;
	private String prodUnit;
	private int prodQtyin;
	private int prodQtysale;
	private int prodMileage;
	
	private List<CartVO> cartVoList;
}

CartVO.java

package kr.or.ddit.vo;

import lombok.Data;

@Data
public class CartVO {
	private String cartProd;
	private int cartQty;
	private String cartMember;
	private String cartNo;
	
	private MemberVO memVo;
}

MemberVO.java

package kr.or.ddit.vo;

import java.util.Date;

import lombok.Data;

@Data
public class MemberVO {
	private String memId;
	private String memPass;
	private String memName;
	private String memRegno1;
	private String memRegno2;
	private Date memBir;
	private String memZip;
	private String memAdd1;
	private String memAdd2;
	private String memHometel;
	private String memComtel;
	private String memHp;
	private String memMail;
	private String memJob;
	private String memLike;
	private String memMemorial;
	private Date memMemorialday;
	private int memMileage;
	private String memDelete;
}

ProdController.java

package kr.or.ddit.controller;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import kr.or.ddit.service.ProdService;
import kr.or.ddit.util.ArticlePage;
import kr.or.ddit.vo.ProdVO;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@RequestMapping("/prod")
@Controller
public class ProdController {
	
	@Autowired
	ProdService service;
	
	@ModelAttribute
	public void prodInit(Model model) {
		model.addAttribute("title", "상품 정보");
	}
	
	@GetMapping("/list")
	public String list(Model model, @RequestParam(value="currentPage", required = false, defaultValue = "1") int currentPage) {
		Map<String, Object> map = new HashMap<>();
		int size = 10;
		int total = this.service.getTotal();
		map.put("currentPage", currentPage);
		map.put("size", size);
		List<ProdVO> list = this.service.list(map);
		map.clear();
//		model.addAttribute("data", list);
		model.addAttribute("data", new ArticlePage<ProdVO>(total, currentPage, size, list, map));
		return "prod/list";
	}
	
	@ResponseBody
	@PostMapping("/listAjax")
	public List<ProdVO> listAjax(@RequestBody ProdVO vo){
		List<ProdVO> list = this.service.cartlist(vo);
		return list;
	}
}

ProdService.java

package kr.or.ddit.service;

import java.util.List;
import java.util.Map;

import kr.or.ddit.vo.ProdVO;

public interface ProdService{

	public List<ProdVO> list(Map<String, Object> map);

	public int getTotal();

	public List<ProdVO> cartlist(ProdVO vo);

}

ProdServiceImpl.java

package kr.or.ddit.service.impl;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import kr.or.ddit.dao.ProdDao;
import kr.or.ddit.service.ProdService;
import kr.or.ddit.vo.ProdVO;

@Service
public class ProdServiceImpl implements ProdService{

	@Autowired
	ProdDao dao;
	
	@Override
	public List<ProdVO> list(Map<String,Object> map) {
		return this.dao.list(map);
	}

	@Override
	public int getTotal() {
		return this.dao.getTotal();
	}

	@Override
	public List<ProdVO> cartlist(ProdVO vo) {
		return this.dao.cartlist(vo);
	}

}

ProdDao.java

package kr.or.ddit.dao;

import java.util.List;
import java.util.Map;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import kr.or.ddit.vo.ProdVO;

@Repository
public class ProdDao {

	@Autowired
	SqlSessionTemplate sst;
	
	public List<ProdVO> list(Map<String, Object> map) {
		return this.sst.selectList("prod.prodList", map);
	}

	public int getTotal() {
		return this.sst.selectOne("prod.prodListTotal");
	}

	public List<ProdVO> cartlist(ProdVO vo) {
		return this.sst.selectList("prod.cartlist", vo);
	}

}

Mybatis

Type Alias

<?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>
   <!-- 
   [마이바티스] 스프링에서 "_"를 사용한 컬럼명을 사용 시(BOOK 테이블의 BOOK_ID)
   카멜케이스로 읽어줌(bookId)
   ex) 테이블 컬러명이 member_id인 경우 jsp화면단에서 이 값을 사용 시 memberId로 사용
   -->
   <settings>
      <setting name="mapUnderscoreToCamelCase" value="true"/>
   </settings>
   <!-- 자주 사용하는 타입의 별칭을 세팅 -->
   <typeAliases>
       <typeAlias type="kr.or.ddit.vo.BookVO" alias="bookVo" /> 
       <typeAlias type="kr.or.ddit.vo.LprodVO" alias="LprodVo" /> 
       <typeAlias type="kr.or.ddit.vo.JdbcBoardVO" alias="boardVo" /> 
       <typeAlias type="kr.or.ddit.vo.BuyprodVO" alias="buyprodVo" /> 
       <typeAlias type="kr.or.ddit.vo.ProdVO" alias="prodVo" /> 
       <typeAlias type="kr.or.ddit.vo.BuyerVO" alias="buyerVo" /> 
       <typeAlias type="kr.or.ddit.vo.CartVO" alias="cartVo" /> 
       <typeAlias type="kr.or.ddit.vo.MemberVO" alias="memVo" /> 
   </typeAliases>
</configuration>

prod_SQL.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="prod">

	<resultMap type="buyerVo" id="buyerMap">
		<result property="buyerId" column="BUYER_ID"/>
		<result property="buyerName" column="BUYER_NAME"/>
		<result property="buyerLgu" column="BUYER_LGU"/>
		<result property="buyerBank" column="BUYER_BANK"/>
		<result property="buyerBankno" column="BUYER_BANKNO"/>
		<result property="buyerBankname" column="BUYER_BANKNAME"/>
		<result property="buyerZip" column="BUYER_ZIP"/>
		<result property="buyerAdd1" column="BUYER_ADD1"/>
		<result property="buyerAdd2" column="BUYER_ADD2"/>
		<result property="buyerComtel" column="BUYER_COMTEL"/>
		<result property="buyerFax" column="BUYER_FAX"/>
		<result property="buyerMail" column="BUYER_MAIL"/>
		<result property="buyerCharger" column="BUYER_CHARGER"/>
		<result property="buyerTelext" column="BUYER_TELEXT"/>
		<collection property="prodVoList" resultMap="prodMap"></collection>
    </resultMap>
	
	<resultMap type="prodVo" id="prodMap">
		<result property="prodId" column="PROD_ID"/>
		<result property="prodName" column="PROD_NAME"/>
		<result property="prodLgu" column="PROD_LGU"/>
		<result property="prodBuyer" column="PROD_BUYER"/>
		<result property="prodCost" column="PROD_COST"/>
		<result property="prodPrice" column="PROD_PRICE"/>
		<result property="prodSale" column="PROD_SALE"/>
		<result property="prodOutline" column="PROD_OUTLINE"/>
		<result property="prodDetail" column="PROD_DETAIL"/>
		<result property="prodImg" column="PROD_IMG"/>
		<result property="prodTotalstock" column="PROD_TOTALSTOCK"/>
		<result property="prodInsdate" column="PROD_INSDATE"/>
		<result property="prodProperstock" column="PROD_PROPERSTOCK"/>
		<result property="prodSize" column="PROD_SIZE"/>
		<result property="prodColor" column="PROD_COLOR"/>
		<result property="prodDelivery" column="PROD_DELIVERY"/>
		<result property="prodUnit" column="PROD_UNIT"/>
		<result property="prodQtyin" column="PROD_QTYIN"/>
		<result property="prodQtysale" column="PROD_QTYSALE"/>
		<result property="prodMileage" column="PROD_MILEAGE"/>
		<collection property="cartVoList" resultMap="cartMap"></collection>
	</resultMap>
	
	<resultMap type="cartVo" id="cartMap">
		<result property="cartProd" column="CART_PROD"/>
		<result property="cartQty" column="CART_QTY"/>
		<result property="cartMember" column="CART_MEMBER"/>
		<result property="cartNo" column="CART_NO"/>
		<collection property="memVo" resultMap="memMap"></collection>
	</resultMap>
	
	<resultMap type="memVo" id="memMap">
		<result property="memId" column="MEM_ID"/>
		<result property="memPass" column="MEM_PASS"/>
		<result property="memName" column="MEM_NAME"/>
		<result property="memRegno1" column="MEM_REGNO1"/>
		<result property="memRegno2" column="MEM_REGNO2"/>
		<result property="memBir" column="MEM_BIR"/>
		<result property="memZip" column="MEM_ZIP"/>
		<result property="memAdd1" column="MEM_ADD1"/>
		<result property="memAdd2" column="MEM_ADD2"/>
		<result property="memHometel" column="MEM_HOMETEL"/>
		<result property="memComtel" column="MEM_COMTEL"/>
		<result property="memHp" column="MEM_HP"/>
		<result property="memMail" column="MEM_MAIL"/>
		<result property="memJob" column="MEM_JOB"/>
		<result property="memLike" column="MEM_LIKE"/>
		<result property="memMemorial" column="MEM_MEMORIAL"/>
		<result property="memMemorialday" column="MEM_MEMORIALDAY"/>
		<result property="memMileage" column="MEM_MILEAGE"/>
		<result property="memDelete" column="MEM_DELETE"/>
	</resultMap>
	
	<select id="prodList"  resultMap="buyerMap" parameterType="hashMap">
	with t as(
		SELECT row_number() over(order by b.buyer_id) rnum
		, A.PROD_ID, A.PROD_NAME, A.PROD_BUYER, A.PROD_SALE, A.PROD_OUTLINE, A.PROD_DETAIL
     , B.BUYER_ID, B.BUYER_NAME
	FROM   BUYER B, PROD A
	WHERE  B.BUYER_ID = A.PROD_BUYER
	)
	select t.*
	from t
	WHERE  T.RNUM BETWEEN (#{currentPage}*#{size}) - (#{size}-1) AND (#{currentPage}*#{size})
	</select>
	
	<select id="prodListTotal" resultType="int">
		SELECT count(*)
		FROM   BUYER B, PROD A
		WHERE  B.BUYER_ID = A.PROD_BUYER
	</select>
	
	
	<select id="cartlist" resultMap="prodMap" parameterType="prodVo">
		select 
	    c.cart_no, c.cart_prod, p.prod_name, c.cart_qty, p.prod_sale, c.cart_member, m.mem_name
		from prod p 
		inner join cart c on c.cart_prod=p.prod_id
		inner join member m on c.cart_member = m.mem_id
		where c.cart_prod = #{prodId}
	</select>

</mapper>