사이드 프로젝트 [국비의 모든것] - 게시글 추천, 댓글 추천 기능 구현

목차

• UI/UX
• 요구사항
• 추천서비스 개발
• 결과 쿼리

---

UI/UX

게시글 추천 누르기 전

게시글 추천 누른 후

 

---

요구사항

1. 추천은 게시글 추천, 댓글 추천을 할 수 있습니다.
2. 추천을 한 상태에서 한번 더 추천을 누르면(요청을 한다면) 추천을 취소합니다.

---

추천서비스 개발

먼저 요청을 처리하는 LikeController를 만들어 다음과 같은 코드를 넣어줍니다.

 

-  LikeController.java

private final LikeService likeService;

/** 게시글에서 추천 클릭시 처리 API */
@PostMapping("/board")
public BaseResponse<LikeResponse> boardLike(@RequestParam Long memberId, @RequestParam Long boardId) {
    String deleteOrInsert = likeService.boardLike(memberId, boardId);
    LikeResponse likeResponse = LikeResponse.builder()
            .deleteOrInsert(deleteOrInsert)
            .build();
    return new BaseResponse<>(likeResponse);
}

/api/v1/like/board 로 Post요청을하면 회원 번호와 게시글번호를 받고,

LikeService에서 게시글 추천 비즈니스 로직 처리를 하여 추천을 Insert하였는지 Delete하였는지 return해줍니다.

 

- LikeService.java

/** 게시물 추천 */
@Transactional
public String boardLike(Long memberId, Long boardId) {
    Boolean exist = boardLikeRepository.existsByBoardIdAndMemberId(boardId, memberId);
    if(exist) { // 추천 삭제
        Long id = boardLikeRepository.findByBoardIdAndMemberId(boardId, memberId).getId();
        Board board = boardRepository.findById(boardId).orElseThrow(() -> new BaseException(NOT_FOUND_BOARD));
        boardLikeRepository.deleteById(id);

        //활동내역 삭제
        Long activityId = activityRepository.findByMemberIdAndBoardIdAndDivision(memberId, boardId, BOARD_LIKE).getId();
        activityRepository.deleteById(activityId);

        board.likeMinus();
        return "delete";
    } else { //추천 생성
        Member member = memberRepository.findById(memberId).orElseThrow(() -> new BaseException(NOT_FOUND_MEMBER));
        Board board = boardRepository.findById(boardId).orElseThrow(() -> new BaseException(NOT_FOUND_BOARD));
        BoardLike boardLike = BoardLike.builder()
                .member(member)
                .board(board)
                .build();
        boardLikeRepository.save(boardLike).getId();

        //활동내역 넣어주기
        Activity activity = Activity.builder()
                .member(member)
                .board(board)
                .division(BOARD_LIKE)
                .build();

        Activity saveActivity = activityRepository.save(activity);

        board.likePlus();
        return "insert";
    }
}

하나의 트랜잭션 안에서 할 일은 다음과 같습니다.

 

회원아이디와, 게시글번호로 추천이 존재하는지 확인하여(existsByBoardIdAndMemberId 메소드 호출)

if-else문으로 나누어 추천과 추천 삭제 로직을 처리하였습니다.

 

추천이 존재한다면(추천)

1. 추천엔티티를 조회하여 DB에서 delete합니다.

2. 활동내역을 지워줍니다. 이 부분에서 활동내역에 추천을 취소했다는 정보를 넣어줄 수 있지만 그냥 활동내역을 삭제 처리해주었습니다.

3. 게시글 엔티티에서 추천 수를 -1 감소시킵니다.

 

=> Transaction Commit 시점에

1. 추천 존재여부 select,

2. 추천 select

3. 게시글 조회 select

4. 게시글 추천수 updat

5. 활동내역 조회 select

6. 활동내역 삭제 delete

7. 추천 삭제 delete

 

총 7개의 쿼리를 실행하게 됩니다.

 

추천이 존재하지 않는다면(추천 취소)

1. 게시글번호, 회원번호를 통해 추천엔티티를 생성하여 데이터베이스에 insert합니다.

2. 활동내역 엔티티를 생성하여 DB에 insert합니다.

3. 게시글 엔티티에서 추천 수를 +1 증가시킵니다.

 

=> Transaction Commit 시점에

1. 추천 존재여부 select

2. 회원 select

3. 게시글 select

4. 게시글 추천 insert

5. 활동내역 insert

6. 게시글 추천 수 update

 

총 6개의 쿼리를 실행하게 됩니다.

 

게시글 추천 기능과 마찬가지로 댓글 추천기능도 똑같이 하여 개발하면 되기 때문에 생략하겠습니다.

---

결과 쿼리

위에서의 예상대로 추천 생성/삭제 쿼리가 데이터베이스에 알맞은 횟수로 나가게 되는지 로그를 확인해보겠습니다.

 

다음은 추천을 생성하는 쿼리 로그입니다.

 

1. 추천 존재여부 select

    select
        boardlike0_.board_like_id as col_0_0_ 
    from
        board_like boardlike0_ 
    left outer join
        board board1_ 
            on boardlike0_.board_id=board1_.board_id 
    left outer join
        member member2_ 
            on boardlike0_.member_id=member2_.member_id 
    where
        board1_.board_id=? 
        and member2_.member_id=? fetch first ? rows only

 

2. 회원 select

    select
        member0_.member_id as member_id1_12_0_,
        member0_.academy_member_id as academy_member_id11_12_0_,
        member0_.email as email2_12_0_,
        member0_.email_accept as email_accept3_12_0_,
        member0_.join_date as join_date4_12_0_,
        member0_.login_id as login_id12_12_0_,
        member0_.nickname as nickname5_12_0_,
        member0_.point as point6_12_0_,
        member0_.profile_image as profile_image7_12_0_,
        member0_.status as status8_12_0_,
        member0_.user_role as user_role9_12_0_,
        member0_.username as username10_12_0_ 
    from
        member member0_ 
    where
        member0_.member_id=?

 

3. 게시글 select

    select
        board0_.board_id as board_id1_4_0_,
        board0_.comment_count as comment_count2_4_0_,
        board0_.content as content3_4_0_,
        board0_.first_category as first_category4_4_0_,
        board0_.like_count as like_count5_4_0_,
        board0_.member_id as member_id10_4_0_,
        board0_.second_category as second_category6_4_0_,
        board0_.subject as subject7_4_0_,
        board0_.views as views8_4_0_,
        board0_.write_date as write_date9_4_0_,
        board0_1_.academy_image as academy_image1_0_0_,
        board0_1_.address as address2_0_0_,
        board0_1_.homepage as homepage3_0_0_,
        board0_1_.phone as phone4_0_0_,
        board0_1_.representative_name as representative_nam5_0_0_,
        board0_2_.academy_name as academy_name1_9_0_,
        board0_2_.core_technology as core_technology2_9_0_,
        board0_2_.curriculum_end_date as curriculum_end_dat3_9_0_,
        board0_2_.curriculum_period as curriculum_period4_9_0_,
        board0_2_.curriculum_start_date as curriculum_start_d5_9_0_,
        board0_2_.recruitment_end_date as recruitment_end_da6_9_0_,
        board0_2_.recruitment_period as recruitment_period7_9_0_,
        board0_2_.recruitment_start_date as recruitment_start_8_9_0_,
        board0_2_.recruits_count as recruits_count9_9_0_,
        board0_2_.url as url10_9_0_,
        board0_3_.must_read as must_read1_13_0_,
        case 
            when board0_1_.board_id is not null then 1 
            when board0_2_.board_id is not null then 2 
            when board0_3_.board_id is not null then 3 
            when board0_.board_id is not null then 0 
        end as clazz_0_,
        member1_.member_id as member_id1_12_1_,
        member1_.academy_member_id as academy_member_id11_12_1_,
        member1_.email as email2_12_1_,
        member1_.email_accept as email_accept3_12_1_,
        member1_.join_date as join_date4_12_1_,
        member1_.login_id as login_id12_12_1_,
        member1_.nickname as nickname5_12_1_,
        member1_.point as point6_12_1_,
        member1_.profile_image as profile_image7_12_1_,
        member1_.status as status8_12_1_,
        member1_.user_role as user_role9_12_1_,
        member1_.username as username10_12_1_ 
    from
        board board0_ 
    left outer join
        academy board0_1_ 
            on board0_.board_id=board0_1_.board_id 
    left outer join
        curriculum board0_2_ 
            on board0_.board_id=board0_2_.board_id 
    left outer join
        notice board0_3_ 
            on board0_.board_id=board0_3_.board_id 
    left outer join
        member member1_ 
            on board0_.member_id=member1_.member_id 
    where
        board0_.board_id=?

4. 활동내역 insert

    insert 
    into
        activity
        (board_id, division, member_id, activity_id) 
    values
        (?, ?, ?, ?)

 

5. 게시글 추천 insert

    insert 
    into
        board_like
        (board_id, member_id, board_like_id) 
    values
        (?, ?, ?)

 

6. 게시글 추천수 update

    update
        board 
    set
        comment_count=?,
        first_category=?,
        like_count=?,
        member_id=?,
        second_category=?,
        subject=?,
        views=?,
        write_date=?,
        content=? 
    where
        board_id=?

 

의도한대로 총 6번의 쿼리가 실행되게됩니다.

 

 

 

다음은 추천을 취소하고난 뒤 발생한 쿼리 로그를 살펴보겠습니다.

 

1. 추천 존재여부 select

    select
        boardlike0_.board_like_id as col_0_0_ 
    from
        board_like boardlike0_ 
    left outer join
        board board1_ 
            on boardlike0_.board_id=board1_.board_id 
    left outer join
        member member2_ 
            on boardlike0_.member_id=member2_.member_id 
    where
        board1_.board_id=? 
        and member2_.member_id=? fetch first ? rows only

2. 추천 select

    select
        boardlike0_.board_like_id as board_like_id1_6_0_,
        board1_.board_id as board_id1_4_1_,
        member2_.member_id as member_id1_12_2_,
        boardlike0_.board_id as board_id2_6_0_,
        boardlike0_.member_id as member_id3_6_0_,
        board1_.comment_count as comment_count2_4_1_,
        board1_.content as content3_4_1_,
        board1_.first_category as first_category4_4_1_,
        board1_.like_count as like_count5_4_1_,
        board1_.member_id as member_id10_4_1_,
        board1_.second_category as second_category6_4_1_,
        board1_.subject as subject7_4_1_,
        board1_.views as views8_4_1_,
        board1_.write_date as write_date9_4_1_,
        board1_1_.academy_image as academy_image1_0_1_,
        board1_1_.address as address2_0_1_,
        board1_1_.homepage as homepage3_0_1_,
        board1_1_.phone as phone4_0_1_,
        board1_1_.representative_name as representative_nam5_0_1_,
        board1_2_.academy_name as academy_name1_9_1_,
        board1_2_.core_technology as core_technology2_9_1_,
        board1_2_.curriculum_end_date as curriculum_end_dat3_9_1_,
        board1_2_.curriculum_period as curriculum_period4_9_1_,
        board1_2_.curriculum_start_date as curriculum_start_d5_9_1_,
        board1_2_.recruitment_end_date as recruitment_end_da6_9_1_,
        board1_2_.recruitment_period as recruitment_period7_9_1_,
        board1_2_.recruitment_start_date as recruitment_start_8_9_1_,
        board1_2_.recruits_count as recruits_count9_9_1_,
        board1_2_.url as url10_9_1_,
        board1_3_.must_read as must_read1_13_1_,
        case 
            when board1_1_.board_id is not null then 1 
            when board1_2_.board_id is not null then 2 
            when board1_3_.board_id is not null then 3 
            when board1_.board_id is not null then 0 
        end as clazz_1_,
        member2_.academy_member_id as academy_member_id11_12_2_,
        member2_.email as email2_12_2_,
        member2_.email_accept as email_accept3_12_2_,
        member2_.join_date as join_date4_12_2_,
        member2_.login_id as login_id12_12_2_,
        member2_.nickname as nickname5_12_2_,
        member2_.point as point6_12_2_,
        member2_.profile_image as profile_image7_12_2_,
        member2_.status as status8_12_2_,
        member2_.user_role as user_role9_12_2_,
        member2_.username as username10_12_2_ 
    from
        board_like boardlike0_ 
    left outer join
        board board1_ 
            on boardlike0_.board_id=board1_.board_id 
    left outer join
        academy board1_1_ 
            on board1_.board_id=board1_1_.board_id 
    left outer join
        curriculum board1_2_ 
            on board1_.board_id=board1_2_.board_id 
    left outer join
        notice board1_3_ 
            on board1_.board_id=board1_3_.board_id 
    left outer join
        member member2_ 
            on boardlike0_.member_id=member2_.member_id 
    where
        board1_.board_id=? 
        and member2_.member_id=?

 

3. 게시글 조회 select

    select
        activity0_.activity_id as activity_id1_2_0_,
        member1_.member_id as member_id1_12_1_,
        board2_.board_id as board_id1_4_2_,
        activity0_.activity_date as activity_date2_2_0_,
        activity0_.board_id as board_id4_2_0_,
        activity0_.comments_id as comments_id5_2_0_,
        activity0_.division as division3_2_0_,
        activity0_.member_id as member_id6_2_0_,
        member1_.academy_member_id as academy_member_id11_12_1_,
        member1_.email as email2_12_1_,
        member1_.email_accept as email_accept3_12_1_,
        member1_.join_date as join_date4_12_1_,
        member1_.login_id as login_id12_12_1_,
        member1_.nickname as nickname5_12_1_,
        member1_.point as point6_12_1_,
        member1_.profile_image as profile_image7_12_1_,
        member1_.status as status8_12_1_,
        member1_.user_role as user_role9_12_1_,
        member1_.username as username10_12_1_,
        board2_.comment_count as comment_count2_4_2_,
        board2_.content as content3_4_2_,
        board2_.first_category as first_category4_4_2_,
        board2_.like_count as like_count5_4_2_,
        board2_.member_id as member_id10_4_2_,
        board2_.second_category as second_category6_4_2_,
        board2_.subject as subject7_4_2_,
        board2_.views as views8_4_2_,
        board2_.write_date as write_date9_4_2_,
        board2_1_.academy_image as academy_image1_0_2_,
        board2_1_.address as address2_0_2_,
        board2_1_.homepage as homepage3_0_2_,
        board2_1_.phone as phone4_0_2_,
        board2_1_.representative_name as representative_nam5_0_2_,
        board2_2_.academy_name as academy_name1_9_2_,
        board2_2_.core_technology as core_technology2_9_2_,
        board2_2_.curriculum_end_date as curriculum_end_dat3_9_2_,
        board2_2_.curriculum_period as curriculum_period4_9_2_,
        board2_2_.curriculum_start_date as curriculum_start_d5_9_2_,
        board2_2_.recruitment_end_date as recruitment_end_da6_9_2_,
        board2_2_.recruitment_period as recruitment_period7_9_2_,
        board2_2_.recruitment_start_date as recruitment_start_8_9_2_,
        board2_2_.recruits_count as recruits_count9_9_2_,
        board2_2_.url as url10_9_2_,
        board2_3_.must_read as must_read1_13_2_,
        case 
            when board2_1_.board_id is not null then 1 
            when board2_2_.board_id is not null then 2 
            when board2_3_.board_id is not null then 3 
            when board2_.board_id is not null then 0 
        end as clazz_2_ 
    from
        activity activity0_ 
    left outer join
        member member1_ 
            on activity0_.member_id=member1_.member_id 
    left outer join
        board board2_ 
            on activity0_.board_id=board2_.board_id 
    left outer join
        academy board2_1_ 
            on board2_.board_id=board2_1_.board_id 
    left outer join
        curriculum board2_2_ 
            on board2_.board_id=board2_2_.board_id 
    left outer join
        notice board2_3_ 
            on board2_.board_id=board2_3_.board_id 
    where
        member1_.member_id=? 
        and board2_.board_id=? 
        and activity0_.division=?

 

4. 게시글 추천수 update

    update
        board 
    set
        comment_count=?,
        first_category=?,
        like_count=?,
        member_id=?,
        second_category=?,
        subject=?,
        views=?,
        write_date=?,
        content=? 
    where
        board_id=?

5. 활동내역 조회 select

select 
       activity0_.activity_id as activity_id1_2_0_, 
       member1_.member_id as member_id1_12_1_, 
       board2_.board_id as board_id1_4_2_, 
       activity0_.activity_date as activity_date2_2_0_, 
       activity0_.board_id as board_id4_2_0_, 
       activity0_.comments_id as comments_id5_2_0_, 
       activity0_.division as division3_2_0_, 
       activity0_.member_id as member_id6_2_0_, 
       member1_.academy_member_id as academy_member_id11_12_1_, 
       member1_.email as email2_12_1_, 
       member1_.email_accept as email_accept3_12_1_, 
       member1_.join_date as join_date4_12_1_, 
       member1_.login_id as login_id12_12_1_, 
       member1_.nickname as nickname5_12_1_, 
       member1_.point as point6_12_1_, 
       member1_.profile_image as profile_image7_12_1_, 
       member1_.status as status8_12_1_, 
       member1_.user_role as user_role9_12_1_, 
       member1_.username as username10_12_1_, 
       board2_.comment_count as comment_count2_4_2_, 
       board2_.content as content3_4_2_, 
       board2_.first_category as first_category4_4_2_, 
       board2_.like_count as like_count5_4_2_, 
       board2_.member_id as member_id10_4_2_, 
       board2_.second_category as second_category6_4_2_, 
       board2_.subject as subject7_4_2_, 
       board2_.views as views8_4_2_, 
       board2_.write_date as write_date9_4_2_, 
       board2_1_.academy_image as academy_image1_0_2_, 
       board2_1_.address as address2_0_2_, 
       board2_1_.homepage as homepage3_0_2_, 
       board2_1_.phone as phone4_0_2_, 
       board2_1_.representative_name as representative_nam5_0_2_, 
       board2_2_.academy_name as academy_name1_9_2_, 
       board2_2_.core_technology as core_technology2_9_2_, 
       board2_2_.curriculum_end_date as curriculum_end_dat3_9_2_, 
       board2_2_.curriculum_period as curriculum_period4_9_2_, 
       board2_2_.curriculum_start_date as curriculum_start_d5_9_2_, 
       board2_2_.recruitment_end_date as recruitment_end_da6_9_2_, 
       board2_2_.recruitment_period as recruitment_period7_9_2_, 
       board2_2_.recruitment_start_date as recruitment_start_8_9_2_, 
       board2_2_.recruits_count as recruits_count9_9_2_, 
       board2_2_.url as url10_9_2_, 
       board2_3_.must_read as must_read1_13_2_, 
       case when board2_1_.board_id is not null then 1 
       when board2_2_.board_id is not null 
       then 2 when board2_3_.board_id is not null then 3 
       when board2_.board_id is not null then 0 end as clazz_2_ 
from 
	activity activity0_ 
left outer join 
	member member1_ on activity0_.member_id=member1_.member_id 
left outer join 
	board board2_ on activity0_.board_id=board2_.board_id 
left outer join 
	academy board2_1_ on board2_.board_id=board2_1_.board_id 
left outer join 
	curriculum board2_2_ on board2_.board_id=board2_2_.board_id 
left outer join 
	notice board2_3_ on board2_.board_id=board2_3_.board_id 
where 
	member1_.member_id=? and board2_.board_id=? and activity0_.division=?

6. 활동내역 삭제 delete

    delete 
    from
        activity 
    where
        activity_id=?

 

7. 추천 삭제 delete

    delete 
    from
        board_like 
    where
        board_like_id=?

 

 

의도한대로 7개의 쿼리 실행 결과로그를 확인할 수 있습니다!