Spring [09] 책 게시판 1-(3)

리뷰 삭제

• 리뷰 작성한 본인만 삭제할 수 있게 하기

bookView.jsp

<c:if test="${cus.id eq review.customer_id}">
  <td><a href="/s01/reviewRemove/${review.id}/${book.id}" class="btn btn-warning btn-sm">삭제</a></td>
</c:if>
<c:if test="${cus.id ne review.customer_id}">
  <td><button class="btn btn-warning btn-sm disabled">삭제</button></td>
</c:if>

리뷰 작성

• 한 책의 리뷰는 한 사람이 중복으로 작성하지 못하게 만들기

review table

book_id, customer_id를 PK로 지정?

• id (PK) 삭제해주기
• book_id, customer_id (PK) 지정해주기
• 둘은 상속형 PK (부모 Table인 book, customer table에서 가져온 FK를 PK로 사용함)
• Table끼리 비식별자 관계 -> 식별자 관계로 변함

PK 변경 후 리뷰를 삭제하려면?

• book_id, customer_id를 where절로 넣어주어야 한다.
• sql문이 길어지고 복잡해진다.
• id (alternate key)를 넣어주는 게 좋겠다.

id (alternate key) 추가

Review (entity)

• id 데이터타입 변경해주기
  

ReviewMapper.xml

    <!-- 동적 SQL-->
    <insert id="save" parameterType="kr.smhrd.entity.Review">
        <selectKey keyProperty="id" resultType="int" order="BEFORE">
            SELECT IFNULL(MAX(ID), 0)+1 FROM REVIEW
        </selectKey>
        INSERT INTO REVIEW(ID, BOOK_ID, CONTENT, RATING, CUSTOMER_ID)
        VALUES(#{id}, #{book_id}, #{content}, #{rating}, #{customer_id})
    </insert>
    <select id="findAll" parameterType="Long" resultType="kr.smhrd.entity.Review">
        SELECT * FROM REVIEW WHERE BOOK_ID = #{book_id}
    </select>

장바구니

cart table

bookView.jsp

• 장바구니 담기 기능
• 로그인시에만 가능하게 하기
• 최초값 1, 최소값 1

<td>
  수량 : <input type="number" value="1" min="1" id="quantityInput"/>
  <c:if test="${empty cus}">
    <button class="btn btn-sm btn-primary disabled">장바구니 담기</button>
  </c:if>
  <c:if test="${!empty cus}">
    <button class="btn btn-sm btn-primary" onclick="addToCart()">장바구니 담기</button>
  </c:if>
</td>

• function 만들기

    <script>
       function addToCart(){
          <!-- 책ID, 고객ID, 수량 -->
          let book_id = ${book.id};
          let customer_id = ${cus.id};
          let quantity = document.getElementById("quantityInput").value;
          console.log(book_id);
          console.log(customer_id);
          console.log(quantity);
          <!-- GET -->
          <!-- location.href="/s01/addToCart"; -->
          <!-- POST Form 이용 -->
          <!-- ajax 비동기 전송 (중요) $.ajax(), fetch()~then(), async/await -->
          document.getElementById("quantity").value=quantity;
          document.getElementById("cartData").submit();
        }
    </script>

• form 만들기

    <form id="cartData" action="/s01/addToCart" method="post">
      <input type="hidden" id="book_id" name="book_id" value="${book.id}"/>
      <input type="hidden" id="customer_id" name="customer_id" value="${cus.id}"/>
      <input type="hidden" id="quantity" name="quantity"/>
    </form>

Cart (entity)

package kr.smhrd.entity;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import java.util.Date;

@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
public class Cart {
    private Long id;
    private Long book_id; // 책 ID
    private Long customer_id; // 고객 ID
    private int quantity; // 수량
    private Date created_at; // 구매일자
}

CartRepository (interface)

package kr.smhrd.repository;

import kr.smhrd.entity.Cart;
import org.springframework.stereotype.Repository;

@Repository
public interface CartRepository {
    public void save(Cart cart);
}

CartMapper.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="kr.smhrd.repository.CartRepository">
    <insert id="save" parameterType="kr.smhrd.entity.Cart">
        INSERT INTO CART(CUSTOMER_ID, BOOK_ID, QUANTITY)
        VALUES (#{customer_id}, #{book_id}, #{quantity})
    </insert>
</mapper>

CartService

package kr.smhrd.controller;

import kr.smhrd.service.CartService;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Controller;

@Controller
@RequiredArgsConstructor
public class CartController {

    private final CartService cartService;
}

CartController

package kr.smhrd.controller;

import kr.smhrd.entity.Cart;
import kr.smhrd.service.CartService;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;

@Controller
@RequiredArgsConstructor
public class CartController {

    private final CartService cartService;
    
    @PostMapping("/addToCart")
    public String addToCart(Cart cart){
        cartService.addToCart(cart);
        return "redirect:/bookList"; // bookList보기
    }
}

장바구니 보기

bookList.jsp

• 장바구니 보기 버튼

<button class="btn btn-sm btn-primary" onclick="goToCart()">장바구니 보기</button>

• function

  <script>
    function goToCart(){
      location.href="/s01/cartList/"+${cus.id};
    }
  </script>

• 장바구니에 책 이름도 필요함
• Cart Table 뿐만 아니라 Book Table도 필요함
• join해야 하기 때문에 둘을 합친 entity를 하나 생성해주자

CartBook (entity)

package kr.smhrd.entity;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import java.util.Date;

@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
public class CartBook {
    // Cart entity
    private Long id;
    private Long book_id; // 책 ID
    private Long customer_id; // 고객 ID
    private int quantity; // 수량
    private Date created_at; // 구매일자

    // Book entity
    // private Long id;   //중복
    private String title;
    private int price;
    private String author;
    private int page;
} 

CartRepository (interface)

@Repository
public interface CartRepository { 
    public List<CartBook> findByCustomerId(Long customer_id);
} 

• JOIN 제대로 되는지 체크
  
  

CartMapper.xml

    <select id="findByCustomerId" resultType="kr.smhrd.entity.CartBook">
        SELECT
        C.ID, C.CUSTOMER_ID, C.BOOK_ID, C.QUANTITY, C.CREATED_AT,B.TITLE, B.PRICE, B.AUTHOR, B.PAGE
        FROM CART C
        INNER JOIN BOOK B
        ON C.BOOK_ID = B.ID
        WHERE C.CUSTOMER_ID = #{customer_id};
    </select>

CartService

    public List<CartBook> cartView(Long customer_id){
        return cartRepository.findByCustomerId(customer_id);
    }

CartController

    // 장바구니 목록
    @GetMapping("/cartList/{customer_id}")
    public String cartList(@PathVariable Long customer_id, Model model){
        List<CartBook> cartList = cartService.cartView(customer_id);
        model.addAttribute("cartList", cartList);
        return "cartList"; // cartList.jsp
    }

cartList.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="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>cartList.jsp</title>
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/css/bootstrap.min.css">
  <script src="https://cdn.jsdelivr.net/npm/jquery@3.7.1/dist/jquery.slim.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body> 
	<div class="container mt-4">
	  <h2>Book ERP System</h2>
	  <div class="panel panel-default">
	    <div class="panel-heading"><span class="badge badge-pill badge-warning">${cus.name}</span>님의 장바구니🛍</div>
	    <div class="panel-body mt-3">
	      <table class="table table-bordered">
	        <thead>
	          <tr>
	            <th>책번호</th>
	            <th>책이름</th>
	            <th>수량</th>
	            <th>가격</th>
	            <th>금액</th>
	          </tr>
	        </thead>
	        <tbody>
	        <c:set var="totalAmount" value="0"/>
	        <c:if test="${!empty cartList}">
	          <c:forEach var="cart" items="${cartList}">
	            <c:set var="totalAmount" value="${totalAmount+(cart.quantity*cart.price)}"/>
                  <tr>
                    <td>${cart.book_id}</td>
                    <td>${cart.title}</td>
                    <td>${cart.quantity}</td>
                    <td><fmt:formatNumber value="${cart.price}"/></td>
                    <td><fmt:formatNumber value="${cart.quantity*cart.price}"/></td>
                  </tr>
	          </c:forEach>
	        </c:if>
	        <c:if test="${empty cartList}">
	          <tr>
	            <td colspan="5">장바구니가 비었습니다.</td>
	          </tr>
	        </c:if>
	        </tbody>
	      </table>
	      <!-- 총액 표시 -->
          <div class="text-right">
            <strong>총 금액 : <fmt:formatNumber value="${totalAmount}"/>원</strong>
          </div>
	      <!-- 리스트, 구매 버튼 -->
	      <div class="card-footer text-center">
	        <a href="/s01/bookList" class="btn btn-sm btn-secondary">리스트</a>
	        <button class="btn btn-sm btn-primary">구매버튼</button>
	      </div>

	    </div>
	    <div class="panel-footer">AISCHOOL2024 - Fighting :]</div>
	  </div>
	</div>
</body>
</html>

• 다른 방법으로 원화 표시하기
  

  <strong>총 금액 : <fmt:formatNumber value="${totalAmount}" type="currency"/></strong>
  <fmt:setLocale value="ko_kr"/>

장바구니 삭제

CartList.jsp

• 장바구니 삭제 버튼 생성

<td><a href="/s01/removeCart/${cart.id}/${cus.id}" class="btn btn-sm btn-danger">취소</a></td>
<!-- cus.customer_id == cart.customer_id -->

CartRepository (interface)

@Repository
public interface CartRepository { 
    public void deleteById(Long id);
}

CartMapper.xml

    <delete id="deleteById">
        DELETE FROM CART WHERE ID = #{id}
    </delete>

CartService

    public void cartRemove(Long id){
        cartRepository.deleteById(id);
    }

CartController

    // 장바구니 삭제
    @GetMapping("/removeCart/{cart_id}/{customer_id}")
    public String removeCart(@PathVariable Long cart_id, @PathVariable Long customer_id){
        cartService.cartRemove(cart_id);
        return "redirect:/cartList/" + customer_id;
    }

장바구니 목록 중복 확인

bookView의 장바구니
book_id와 customer_id로 quantity가 존재하면 quantity를 업데이트 시키면 된다.

count로 개수 확인하기

CartRepository (interface)

@Repository
public interface CartRepository { 
    public int cartCount(Cart cart);
}

CartMapper.xml

    <select id="cartCount" resultType="int" parameterType="kr.smhrd.entity.Cart">
        SELECT COUNT(*) FROM CART
        WHERE CUSTOMER_ID = #{customer_id} AND BOOK_ID = #{book_id}
    </select>

장바구니 수량 업데이트

CartRepository (interface)

@Repository
public interface CartRepository { 
    public void cartQuantity(Cart cart);
}

CartMapper.xml

    <update id="cartQuantity" parameterType="kr.smhrd.entity.Cart">
        UPDATE CART SET QUANTITY=QUANTITY+#{quantity}
        WHERE CUSTOMER_ID = #{customer_id} AND BOOK_ID = #{book_id}
    </update>

CartService

public class CartService {
    private final CartRepository cartRepository;

    public void addToCart(Cart cart){
        // save, update
        int count = cartRepository.cartCount(cart);
        if(count>0){
            // update
            cartRepository.cartQuantity(cart);
        }else {
            cartRepository.save(cart);
        }

• 장바구니에 책 넣기
  
• 동일한 책 장바구니 더 넣을 경우
  -> 중복되지 않고 수량이 늘어난다
  

장바구니 수량 변경

• 수량 변경시 업데이트 되게 해야 함

cartList.jsp

• 장바구니 목록 가져오는 for문 안에서 cart_id(장바구니번호)와 cart_quantity(수량) 가져와야 함
• varStatus 이용

<c:forEach items="${배열명}"
	var="변수"
	varStatus="상태용 변수"> 
</c:forEach>

• ${status.current} : 현재 for문의 해당하는 번호
• ${status.index} : 0부터의 순서
• ${status.count} : 1부터의 순서
• ${status.first} : 첫 번째인지 여부
• ${status.last} : 마지막인지 여부
• ${status.begin} : for문의 시작 번호
• ${status.end} : for문의 끝 번호
• ${status.step} : for문의 증가값

<c:if test="${!empty cartList}">
  <c:forEach var="cart" items="${cartList}" varStatus="status">
    <c:set var="totalAmount" value="${totalAmount+(cart.quantity*cart.price)}"/>
      <tr>
        <td>${cart.book_id}</td>
        <td>${cart.title}</td>
        <td>
          <input type="number" onchange="quantityUpdate(${status.index})" id="cartNumber${status.index}" value="${cart.quantity}" class="form-control" min="1" />
          <input type="hidden" id="cartId${status.index}" value="${cart.id}"/>
        </td>
        <td><fmt:formatNumber value="${cart.price}"/></td>
        <td><fmt:formatNumber value="${cart.quantity*cart.price}"/></td>
        <td><a href="/s01/removeCart/${cart.id}/${cus.id}" class="btn btn-sm btn-danger">취소</a></td>
        <!-- cus.customer_id == cart.customer_id -->
      </tr>
  </c:forEach>
</c:if>

• form 보내기

    <form id="form" action="/s01/cartQuantityUpdate" method="post">
      <input type="hidden" id="quantity" name="quantity"/>
      <input type="hidden" id="cart_id" name="id"/>
      <input type="hidden" id="customer_id" name="customer_id" value="${cus.id}"/>
    </form>

• script 생성

  <script>
    function quantityUpdate(index){
        // cart.id, 변경된 수량 가져오기
        let quantity = document.getElementById("cartNumber"+index).value;
        let cart_id = document.getElementById("cartId"+index).value;
        console.log(quantity, cart_id);
        location.href="/s01/cartQuantityUpdate";
        document.getElementById("quantity").value=quantity;
        document.getElementById("cart_id").value=cart_id;
        document.getElementById("form").submit();
    }
  </script>

CartRepository (interface)

@Repository
public interface CartRepository { 
    public void cartQuantityUpdate(Cart cart);
}

CartMapper.xml

    <update id="cartQuantityUpdate" parameterType="kr.smhrd.entity.Cart">
        UPDATE CART SET QUANTITY=#{quantity}
        WHERE ID=#{id}
    </update>

CartService

  public void cartQuantityUpdate(Cart cart){
        cartRepository.cartQuantityUpdate(cart);
    }

CartController

    // 장바구니 수량 변경
    @PostMapping("/cartQuantityUpdate")
    public String cartQuantityUpdate(Cart cart){
        cartService.cartQuantityUpdate(cart);
        return "redirect:/cartList/" + cart.getCustomer_id();
    }

DB에서도 변경됨 확인함