Spring [08] 책 게시판 1-(2)

책 상세보기 평균 리뷰

ReviewRepository.java (interface)

	// JPQL방식
    @Select("select avg(rating) from review where book_id=#{book_id}")
    public Double getAvgRating(Long book_id);

ReviewService.java

    public Double getAvgRating(Long book_id){
        return reviewRepository.getAvgRating(book_id);
    }

BookController.java

    @GetMapping("/bookView/{id}") // 이름은 지정해놓은 'id'랑 동일하게 안 가져와도 된다.
    public String bookView(@PathVariable Long id, Model model){
        Book book = bookService.view(id);
        model.addAttribute("book", book);
        // 리뷰 정보 가져오기
        List<Review> reviews = reviewService.getByReviews(id);
        model.addAttribute("reviews", reviews);
        // 평점 가져오기
        Double avgRating = reviewService.getAvgRating(id);
        if (avgRating!=null){
            model.addAttribute("avgRating", avgRating);
        } else {
            model.addAttribute("avgRating", "0.0");
        }
        return "bookView"; // bookView.jsp
    }

BookView.jsp

<h3>리뷰 리스트 (평점평균 : <span class="badge badge-pill badge-danger">${avgRating}</span>/5)</h3>

리뷰 삭제

BookView.jsp

<td><a href="/s01/reviewRemove/${review.id}/${book.id}" class="btn btn-secondary btn-sm">삭제</a></td>

ReviewRepository.java (interface)

    public void reviewDelete(Long id);

ReviewMapper.xml

    <delete id="reviewDelete" parameterType="Long">
        DELETE FROM REVIEW WHERE ID = #{id}
    </delete>

ReviewService.java

    public void reviewRemove(Long id){
        reviewRepository.reviewDelete(id);
    }

ReviewController.java

    @GetMapping("/reviewRemove/{review_id}/{book_id}")
    public String reviewRemove(@PathVariable Long review_id, @PathVariable Long book_id){
        reviewService.reviewRemove(review_id);
        return "redirect:/bookView/" + book_id;
    }

로그인

customer Table 생성

customer Table 생성 후
review table과 1:N 관계로 연결되었기 때문에 기존 review 데이터 제거 후 Synchronized Model 꼭 해주기

Review (entity)

• customer_id 꼭 넣어주기
  

bookList.jsp

• 상단에 로그인 기능 넣기
• https://www.w3schools.com/bootstrap4/bootstrap_forms.asp

	  <c:if test="${empty cus}">
	    <div class="d-flex mt-3 mb-3 justify-content-center">
            <form class="form-inline" action="/s01/login" method="post">
              <label for="username">아이디 :</label>
              <input type="text" class="form-control" placeholder="Enter username" id="username" name="username">
              <label for="password">비밀번호 :</label>
              <input type="password" class="form-control" placeholder="Enter password" id="password" name="password">
              <button type="submit" class="btn btn-sm btn-info">로그인</button>
            </form>
        </div>
      </c:if>

• Cutomer (entity)
• CustomerController
• CustomerService
• CustomerRepository (interface)
• CustomerMapper.xml

Customer.java (entity)

• Customer 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 Customer {
    private Long id;
    private String username;
    private String password;
    private String name;
    private int age;
    private String rating;
    private String job;
    private int reserves;
    private Date created_at;
}

CustomerRepository (interface)

package kr.smhrd.repository;

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

@Repository
public interface CustomerRepository {
  
    public Customer findByUsernameAndPassword(Customer customer);
}

CustomerMapper.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.CustomerRepository">
    <select id="findByUsernameAndPassword" resultType="kr.smhrd.entity.Customer">
        SELECT * FROM CUSTOMER
        WHERE USERNAME = #{username} AND PASSWORD = #{password}
    </select>
</mapper>

CustomerService

package kr.smhrd.service;

import kr.smhrd.entity.Customer;
import kr.smhrd.repository.CustomerRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;

@Service
@RequiredArgsConstructor
public class CustomerService {

    private final CustomerRepository customerRepository;

    public Customer login(Customer customer){
        return customerRepository.findByUsernameAndPassword(customer);
    }
}

CustomerController

package kr.smhrd.controller;

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

import javax.servlet.http.HttpSession;

@Controller
@RequiredArgsConstructor
public class CustomerController {
    private final CustomerService customerService;

    @PostMapping("/login")
    public String login(Customer customer, HttpSession session){
        Customer cus = customerService.login(customer);
        if (cus != null) {
            // 인증 성공 - 권한처리(X)
            // SpringSecurity : 인증, 권한 처리하는 방법 제공
            session.setAttribute("cus", cus);
        }
        return "redirect:/bookList";
    }
}

bookList.jsp

• 로그인 상태시 화면 추가

      <!-- 로그인 상태시 -->
      <c:if test="${not empty cus}">
	    <div class="d-flex mt-3 mb-3 justify-content-center">
            <form class="form-inline" action="/s01/logout" method="post">
              <label>Welcom:<span class="badge badge-pill badge-primary">${cus.name}</span>님 방문을 환영합니다.</label>
              <label>Reserves:${cus.reserves}</label>
              <button type="submit" class="btn btn-sm btn-info">로그아웃</button>
            </form>
        </div>
      </c:if>

로그아웃

CustomerController

    @PostMapping("/logout")
    public String logout(HttpSession session){
        session.invalidate();
        return "redirect:/bookList";
    }

bookList.jsp

• 로그아웃 상태시 화면

리뷰 작성자

bookView.jsp

ReviewMapper.xml

• entity에 customer_id 추가했다면
• mapper에도 customer_id 추가해주기