리뷰 게시판 구현

 

▶ 데이터 베이스 설계

CREATE TABLE `company_review` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int DEFAULT NULL, -- 클라이언트 ID
  `freelancer_id` int DEFAULT NULL, -- 프리랜서 ID
  `work_quality_score` int NOT NULL, -- 작업물 퀄리티 평가 (1~5)
  `timeliness_score` int NOT NULL, -- 개발 시간 준수 정도 (1~5)
  `feedback_score` int NOT NULL, -- 개발 의견 반영 정도 (1~5)
  `willingness_score` int NOT NULL, -- 함께 하고 싶은 정도 (1~5)
  `overall_score` int NOT NULL, -- 종합 평점 (1~5)
  `review_date` timestamp DEFAULT CURRENT_TIMESTAMP, -- 리뷰 작성 날짜
  `response_data` TEXT DEFAULT NULL, -- 평가 내용 (TEXT 형식)
  PRIMARY KEY (`id`),
  CONSTRAINT `clients_review_chk_1` CHECK (`work_quality_score` BETWEEN 1 AND 5),
  CONSTRAINT `clients_review_chk_2` CHECK (`timeliness_score` BETWEEN 1 AND 5),
  CONSTRAINT `clients_review_chk_3` CHECK (`feedback_score` BETWEEN 1 AND 5),
  CONSTRAINT `clients_review_chk_4` CHECK (`willingness_score` BETWEEN 1 AND 5),
  CONSTRAINT `clients_review_chk_5` CHECK (`overall_score` BETWEEN 1 AND 5)
);

 

 

▶ 맵퍼 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="com.damoa.repository.interfaces.CompanyReviewRepository">

    <!-- 모든 기업 리뷰 list 데이터 및 user_tb username 요청 쿼리 -->
    <select id="findAllByCompanyReviews" resultType="com.damoa.dto.review.CompanyMainDTO">
        SELECT cr.*, u.username AS freelancer_name
        FROM company_review cr
        LEFT JOIN user_tb u ON cr.freelancer_id = u.id
        ORDER BY cr.id DESC
    </select>

    <!-- {id}를 기준으로 상세 리뷰 데이터 및 user_tb username 요청 쿼리 -->
    <select id="findByCompanyReviewId" parameterType="com.damoa.dto.review.CompanyMainDTO">
        SELECT cr.*, u.username As freelancer_name
        FROM company_review cr
        JOIN user_tb u ON cr.freelancer_id = u.id
        WHERE cr.id = #{id}
    </select>

    <!-- GCP company 리뷰 데이터 MySQL로 insert-->
    <insert id="insertCompanyReview" parameterType="com.damoa.repository.model.CompanyReview">
        INSERT INTO company_review (writer_id, freelancer_id, work_quality_score,
        timeliness_score, feedback_score, willingness_score,
        overall_score, review_date, response_data)
        VALUES (#{writerId}, #{freelancerId}, #{workQualityScore},
        #{timelinessScore}, #{feedbackScore}, #{willingnessScore},
        #{overallScore}, #{reviewDate}, #{responseData})
    </insert>

    <select id="findCompanyReview" resultType="com.damoa.dto.admin.CompanyReviewDTO">
        select c.id, u.username, p.project_name, p.main_tasks, c.overall_score, c.response_data
        from company_review as c
        join user_tb as u on u.id = c.company_id
        join freelancer_tb as f on c.freelancer_id = f.id
        join project_tb as p on p.user_id = c.company_id
        limit #{limit} offset #{offset}
    </select>

    <select id="countCompanyReview" resultType="int">
        select count(*)
        from company_review as c
        join user_tb as u on u.id = c.company_id
        join freelancer_tb as f on c.freelancer_id = f.id
        join project_tb as p on p.user_id = c.company_id;
    </select>

    <select id="companyReviewDetail" resultType="com.damoa.dto.admin.CompanyReviewDetailDTO">
        select * from company_review where id = #{id}
    </select>

    <delete id="delete">
        delete from company_review where id = #{id}
    </delete>

    <select id="getDailyCompanyReviewData" resultType="com.damoa.dto.DailyCompanyReviewDTO">
        SELECT DATE_FORMAT(review_date, '%m-%d') AS date, COUNT(*) AS count
        FROM company_review
        WHERE review_date BETWEEN #{startDate} AND #{endDate}
        GROUP BY DATE_FORMAT(review_date, '%m-%d')
    </select>



</mapper>

 

▶ 레포지토리

package com.damoa.repository.interfaces;

import com.damoa.dto.admin.CompanyReviewDTO;
import com.damoa.dto.admin.CompanyReviewDetailDTO;
import com.damoa.dto.review.CompanyMainDTO;
import com.damoa.repository.model.CompanyReview;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.web.bind.annotation.RequestParam;
import com.damoa.dto.DailyCompanyReviewDTO;
import com.damoa.repository.model.CompanyReview;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Optional;

/*
* company 리뷰 기능
* */
@Mapper
public interface CompanyReviewRepository {

    // company 리뷰 데이터 호출
    List<CompanyMainDTO> findAllByCompanyReviews();

    // company 리뷰 id pk 호출
    Optional<CompanyMainDTO> findByCompanyReviewId(int id);

    // GCP company 리뷰 데이터 호출 후 MySQL로 데이터 insert
    void insertCompanyReview(CompanyReview companyReview);

    List<CompanyReviewDTO> findCompanyReview(@Param("limit") int limit, @Param("offset") int offset);

    int countCompanyReview();

    CompanyReviewDetailDTO companyReviewDetail(int id);
    // 일별 기업 리뷰 등록 수 데이터 가져오기
    List<DailyCompanyReviewDTO> getDailyCompanyReviewData(@Param("startDate") String startDate, @Param("endDate") String endDate);

    int delete(int id);
}

 

▶ 기업 리뷰 서비스 및 프리랜서 리뷰 서비스

package com.damoa.service;

import com.damoa.dto.admin.CompanyReviewDTO;
import com.damoa.dto.admin.CompanyReviewDetailDTO;
import com.damoa.dto.admin.FreelancerReviewDTO;
import com.damoa.dto.DailyCompanyReviewDTO;
import com.damoa.dto.DailyFreelancerReviewDTO;
import com.damoa.dto.admin.FreelancerReviewDetailDTO;
import com.damoa.dto.review.CompanyMainDTO;
import com.damoa.dto.review.FreelancerMainDTO;
import com.damoa.repository.interfaces.CompanyReviewRepository;
import com.damoa.repository.interfaces.FreelancerReviewRepository;
import com.damoa.repository.interfaces.UserRepository;
import lombok.RequiredArgsConstructor;
import lombok.extern.log4j.Log4j2;
import org.springframework.stereotype.Service;
import org.springframework.ui.Model;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;

@Log4j2
@Service
@RequiredArgsConstructor    // DI : 의존성 주입 & final
public class ReviewService {

    private final UserRepository userRepository;
    private final CompanyReviewRepository companyReviewRepo;
    private final FreelancerReviewRepository freelancerReviewRepo;

    // 클라이언트 && 프리랜서 리뷰 데이터 렌더링
    public void getAllReviews(Model model) {
        // 1. 클라이언트와 프리랜서 데이터 가져옴
        List<CompanyMainDTO> companyReviews = companyReviewRepo.findAllByCompanyReviews();
        List<FreelancerMainDTO> freelancerReviews = freelancerReviewRepo.findAllByFreelancerReviews();

        model.addAttribute("companyReviews", companyReviews);
        model.addAttribute("freelancerReviews", freelancerReviews);
        log.info("companyReviews: {}, freelancerReviews: {}", companyReviews, freelancerReviews);
    }

    // 프리랜서 리뷰 데이터 호출 메서드
    public void getFreelancerReviews(Model model) {
        List<FreelancerMainDTO> freelancerReview = freelancerReviewRepo.findAllByFreelancerReviews();
        model.addAttribute("freelancerReview", freelancerReview);
        model.addAttribute("totalReviews", freelancerReview.size());
    }

    // 회사 리뷰 데이터 호출 메서드
    public void getCompanyReviews(Model model) {
        List<CompanyMainDTO> companyReview = companyReviewRepo.findAllByCompanyReviews();
        model.addAttribute("companyReview", companyReview);
        model.addAttribute("totalReviews", companyReview.size());
    }

    // 프리랜서 리뷰 상세 조회 기능
    public void getByFreelancerId(int id, Model model) {
        Optional<FreelancerMainDTO> freelancerReview = freelancerReviewRepo.findByFreelancerReviewId(id);

        model.addAttribute("freelancerReview", freelancerReview.get()); // Optional에서 값 추출
    }

    // 회사 리뷰 상세 조회 기능
    public void getByCompanyId(int id, Model model) {
        Optional<CompanyMainDTO> companyReview = companyReviewRepo.findByCompanyReviewId(id);

        model.addAttribute("companyReview", companyReview.get()); // Optional에서 값 추출 .get() 안쓰면 내부 객체 접근 불가
        log.info("companyReview: {}", companyReview);
    }

    public List<CompanyReviewDTO> getComapanyReviews(int limit, int offset){
        List<CompanyReviewDTO> companyReviews = companyReviewRepo.findCompanyReview(limit,offset);
        return  companyReviews;

    }

    public int countReview(){
        return companyReviewRepo.countCompanyReview();
    }

    public List<FreelancerReviewDTO> findFreelancerReview(int limit, int offset){
        List<FreelancerReviewDTO> freelancerReviews = freelancerReviewRepo.findFreelancerReview(limit, offset);
        return freelancerReviews;

    }

    public int countFreelancerReview(){
        return freelancerReviewRepo.countFreelancerReview();
    }

    public CompanyReviewDetailDTO getCompanyDetails(int id){
       CompanyReviewDetailDTO companyReviewDetailDTO = companyReviewRepo.companyReviewDetail(id);
        return companyReviewDetailDTO;

    }


    // 일별 기업 리뷰 등록 수 데이터
    public List<DailyCompanyReviewDTO> getDailyCompanyReviewData() {
        LocalDate endDate = LocalDate.now();
        LocalDate startDate = endDate.minusMonths(1);
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");

        return companyReviewRepo.getDailyCompanyReviewData(startDate.format(formatter), endDate.format(formatter));
    }

    // 일별 프리랜서 리뷰 등록 수 데이터
    public List<DailyFreelancerReviewDTO> getDailyFreelancerReviewData() {
        LocalDate endDate = LocalDate.now();
        LocalDate startDate = endDate.minusMonths(1);
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");

        return freelancerReviewRepo.getDailyFreelancerReviewData(startDate.format(formatter), endDate.format(formatter));
    }

    public int deleteCompanyReview(int id){
        return companyReviewRepo.delete(id);
    }

    public int deleteFreelancerReview(int id){
        return freelancerReviewRepo.delete(id);
    }

    public FreelancerReviewDetailDTO getFreelancerDetails(int id){
        return freelancerReviewRepo.freelancerReviewDetail(id);
    }

}

 

▶ 어드민 컨트롤러

 

@Controller
@RequestMapping("/admin")
@RequiredArgsConstructor
public class AdminController {
  /**
     * http://localhost:8080/admin/list/company
     *
     * @param model
     * @return
     */
    @GetMapping("/list/company/{currentPageNum}") // URL의 {type} 부분을 변수로 처리
    public String companyReviewList(@PathVariable(required = false) Integer currentPageNum, Model model) {
        int pageSize = 6;
        int offset;

        int totallist = reviewService.countReview(); // 총몇개의 row 인지 확인
        int totalPages = (int) Math.ceil((double) totallist / (double) pageSize); // 2.1 = 13 / 6

        if (currentPageNum == null || currentPageNum <= 1) {
            currentPageNum = 2;
            offset = 0;
        } else {
            offset = (currentPageNum - 1) * pageSize;
        }
        int nextPageNum;
        int beforePageNum;

        if (currentPageNum >= totalPages - 1 && totalPages > 3) {
            currentPageNum = totalPages - 1;
            nextPageNum = currentPageNum + 1;
            beforePageNum = currentPageNum - 1;
        } else if (currentPageNum >= totalPages - 1) {
            currentPageNum = 2;
            nextPageNum = 3;
            beforePageNum = 1;
        }

        List<CompanyReviewDTO> list = reviewService.getComapanyReviews(pageSize, offset);

        model.addAttribute("list", list);
        model.addAttribute("totallist", totallist);
        model.addAttribute("totalPages", totalPages);
        model.addAttribute("currentPageNum", currentPageNum);
        model.addAttribute("beforePageNum", currentPageNum - 1);
        model.addAttribute("nextPageNum", currentPageNum + 1);

        return "admin/company_list";
    }
    
}