[Practice] 게시판 API 만들기 (6)

1. 문제

• 게시판별 작성된 게시글의 개수를 리턴하는 API
• 현재 사용 가능한 게시판에 대해 게시글의 개수를 리턴

 

 

 

2. 풀이

- BoardService.java

package com.example.jpa.sample.board.service;

import com.example.jpa.sample.board.entity.BoardType;
import com.example.jpa.sample.board.model.BoardTypeCount;
import com.example.jpa.sample.board.model.BoardTypeInput;
import com.example.jpa.sample.board.model.BoardTypeUsing;
import com.example.jpa.sample.board.model.ServiceResult;

import java.util.List;

public interface BoardService {

    ServiceResult addBoard(BoardTypeInput boardTypeInput);

    ServiceResult updateBoard(Long id, BoardTypeInput boardTypeInput);

    ServiceResult deleteBoard(Long id);

    List<BoardType> getAllBoardType();

    ServiceResult setBoardTypeUsing(Long id, BoardTypeUsing boardTypeUsing);

    List<BoardTypeCount> getBoardTypeCount();
}

- BoardServiceImpl.java

package com.example.jpa.sample.board.service;

import com.example.jpa.sample.board.entity.BoardType;
import com.example.jpa.sample.board.model.BoardTypeCount;
import com.example.jpa.sample.board.model.BoardTypeInput;
import com.example.jpa.sample.board.model.BoardTypeUsing;
import com.example.jpa.sample.board.model.ServiceResult;
import com.example.jpa.sample.board.repository.BoardTypeCustomRepository;
import com.example.jpa.sample.board.repository.BoardRepository;
import com.example.jpa.sample.board.repository.BoardTypeRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@RequiredArgsConstructor
@Service
public class BoardServiceImpl implements BoardService {

    private final BoardTypeRepository boardTypeRepository;
    private final BoardRepository boardRepository;
    private final BoardTypeCustomRepository boardTypeCustomRepository;

    @Override
    public ServiceResult addBoard(BoardTypeInput boardTypeInput) {
        BoardType boardType = boardTypeRepository.findByBoardName(boardTypeInput.getName());

        if(boardType != null && boardTypeInput.getName().equals(boardType.getBoardName())) { // 동일한 게시판 제목이 있을 경우
            return ServiceResult.fail("이미 동일한 게시판이 존재합니다.");
        }

        BoardType addBoardType = BoardType.builder()
                .boardName(boardTypeInput.getName())
                .regDate(LocalDateTime.now())
                .build();

        boardTypeRepository.save(addBoardType);

        return ServiceResult.success();
    }

    @Override
    public ServiceResult updateBoard(Long id, BoardTypeInput boardTypeInput) {
        Optional<BoardType> optionalBoardType = boardTypeRepository.findById(id);
        if(!optionalBoardType.isPresent()) {
            return ServiceResult.fail("수정할 게시판 타입이 없습니다.");
        }

        BoardType boardType = optionalBoardType.get();

        if(boardTypeInput.getName().equals(boardType.getBoardName())) { // 동일한 게시판 제목이 있을 경우
            return ServiceResult.fail("수정할 이름이 동일한 게시판명 입니다.");
        }

        boardType.setBoardName(boardTypeInput.getName());
        boardType.setUpdateDate(LocalDateTime.now());
        boardTypeRepository.save(boardType);

        return ServiceResult.success();
    }

    @Override
    public ServiceResult deleteBoard(Long id) {
        Optional<BoardType> optionalBoardType = boardTypeRepository.findById(id);
        if(!optionalBoardType.isPresent()) {
            return ServiceResult.fail("삭제할 게시판 타입이 없습니다.");
        }

        BoardType boardType = optionalBoardType.get();
        if(boardRepository.countByBoardType(boardType) > 0) {
            return ServiceResult.fail("삭제할 게시판 타입의 게시글이 존재합니다.");
        }

        boardTypeRepository.delete(boardType);

        return ServiceResult.success();
    }

    @Override
    public List<BoardType> getAllBoardType() {

        return boardTypeRepository.findAll();
    }

    @Override
    public ServiceResult setBoardTypeUsing(Long id, BoardTypeUsing boardTypeUsing) {

        Optional<BoardType> optionalBoardType = boardTypeRepository.findById(id);
        if(!optionalBoardType.isPresent()) {
            return ServiceResult.fail("삭제할 게시판 타입이 없습니다.");
        }

        BoardType boardType = optionalBoardType.get();

        boardType.setUsingYn(boardTypeUsing.isUsingYn());
        boardTypeRepository.save(boardType);

        return ServiceResult.success();

    }

    @Override
    public List<BoardTypeCount> getBoardTypeCount() {
        return boardTypeCustomRepository.getBoardTypeCount();
    }

}

- BoardTypeCustomRepository.java

package com.example.jpa.sample.board.repository;

import com.example.jpa.sample.board.model.BoardTypeCount;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import java.util.List;
import java.util.stream.Collectors;

@RequiredArgsConstructor
@Repository
public class BoardTypeCustomRepository {

    private final EntityManager entityManager;

    public List<BoardTypeCount> getBoardTypeCount() {

        String sql = " " +
                "SELECT bt.id, bt.board_name, bt.reg_date, bt.using_yn," +
                "(SELECT COUNT(*) FROM board b WHERE b.board_type_id = bt.id) as board_count " +
                "FROM board_type bt ";

//        List<BoardTypeCount> boardTypeCountList = entityManager.createNativeQuery(sql).getResultList();
        List<Object[]> resultList = entityManager.createNativeQuery(sql).getResultList();

        return resultList.stream().map(e -> new BoardTypeCount(e)).collect(Collectors.toList());
    }

}

- BoardTypeCount.java

package com.example.jpa.sample.board.model;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigInteger;
import java.sql.Timestamp;
import java.time.LocalDateTime;

@AllArgsConstructor
@NoArgsConstructor
@Builder
@Data
public class BoardTypeCount {

    private Long id;
    private String boardName;
    private LocalDateTime regDate;
    private boolean usingYn;

    private Long boardCount;

    public BoardTypeCount(Object[] arrObj) {
        this.id = ((BigInteger)arrObj[0]).longValue();
        this.boardName = ((String)arrObj[1]);
        this.regDate = ((Timestamp)arrObj[2]).toLocalDateTime();
        this.usingYn = ((Boolean)arrObj[3]);
        this.boardCount = ((BigInteger)arrObj[4]).longValue();
    }
}

- ApiBoardController.java

package com.example.jpa.sample.board.controller;

import com.example.jpa.sample.board.entity.BoardType;
import com.example.jpa.sample.board.model.BoardTypeCount;
import com.example.jpa.sample.board.model.BoardTypeInput;
import com.example.jpa.sample.board.model.BoardTypeUsing;
import com.example.jpa.sample.board.model.ServiceResult;
import com.example.jpa.sample.board.service.BoardService;
import com.example.jpa.sample.notice.model.ResponseError;
import com.example.jpa.sample.user.model.ResponseMessage;
import lombok.RequiredArgsConstructor;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.validation.Errors;
import org.springframework.web.bind.annotation.*;

import javax.validation.Valid;
import java.util.List;

@RequiredArgsConstructor
@RestController
public class ApiBoardController {

    private final BoardService boardService;

    // 문제 1
    @PostMapping("/api/board/type")
    public ResponseEntity<?> addBoardType(@RequestBody @Valid BoardTypeInput boardTypeInput, Errors errors) {

        if(errors.hasErrors()) {
            List<ResponseError> responseErrorList = ResponseError.of(errors.getAllErrors());
            return new ResponseEntity<>(ResponseMessage.fail("입력값이 정확하지 않습니다.", responseErrorList), HttpStatus.BAD_REQUEST);
        }

        ServiceResult result = boardService.addBoard(boardTypeInput);

        if(!result.isResult()) {
            return ResponseEntity.ok().body(ResponseMessage.fail(result.getMessage()));
        }

        return ResponseEntity.ok().build();
    }

    // 문제 2
    @PutMapping("/api/board/type/{id}")
    public ResponseEntity<?> updateBoardType(@PathVariable Long id, @RequestBody @Valid BoardTypeInput boardTypeInput, Errors errors) {

        if(errors.hasErrors()) {
            List<ResponseError> responseErrorList = ResponseError.of(errors.getAllErrors());
            return new ResponseEntity<>(ResponseMessage.fail("입력값이 정확하지 않습니다.", responseErrorList), HttpStatus.BAD_REQUEST);
        }

        ServiceResult result = boardService.updateBoard(id, boardTypeInput);

        if(!result.isResult()) {
            return ResponseEntity.ok().body(ResponseMessage.fail(result.getMessage()));
        }

        return ResponseEntity.ok().build();
    }

    // 문제 3
    @DeleteMapping("/api/board/type/{id}")
    public ResponseEntity<?> deleteBoardType(@PathVariable Long id) {
        ServiceResult result = boardService.deleteBoard(id);

        if(!result.isResult()) {
            return ResponseEntity.ok().body(ResponseMessage.fail(result.getMessage()));
        }

        return ResponseEntity.ok().body(ResponseMessage.success());
    }

    // 문제 4
    @GetMapping("/api/board/type")
    public ResponseEntity<?> boardType() {
        List<BoardType> boardTypeList = boardService.getAllBoardType();

        return ResponseEntity.ok().body(ResponseMessage.success(boardTypeList));
    }

    // 문제 5
    @PatchMapping("/api/board/type/{id}/using")
    public ResponseEntity<?> usingBoardType(@PathVariable Long id, @RequestBody BoardTypeUsing boardTypeUsing) {
        ServiceResult result = boardService.setBoardTypeUsing(id, boardTypeUsing);

        if(!result.isResult()) {
            return ResponseEntity.ok().body(ResponseMessage.fail(result.getMessage()));
        }

        return ResponseEntity.ok().body(ResponseMessage.success());
    }

    // 문제 6
    @GetMapping("/api/board/type/count")
    public ResponseEntity<?> boardTypeCount() {
        List<BoardTypeCount> boardTypeCountList = boardService.getBoardTypeCount();
        return ResponseEntity.ok().body(boardTypeCountList);
    }
}

---

매핑을 더 간단하게 하는 방법으로는 두가지가 존재한다.

• @SqlResultSetMapping
• 외부 라이브러리 (QLRM)

 

그 중 QLRM을 사용하여 매핑을 하는 방법은 다음과 같다.

- build.gradle

implementation group: 'ch.simas.qlrm', name: 'qlrm', version: '1.7.1'

- BoardTypeCustomRepository.java

package com.example.jpa.sample.board.repository;

import com.example.jpa.sample.board.model.BoardTypeCount;
import lombok.RequiredArgsConstructor;
import org.qlrm.mapper.JpaResultMapper;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.List;
import java.util.stream.Collectors;

@RequiredArgsConstructor
@Repository
public class BoardTypeCustomRepository {

    private final EntityManager entityManager;

    public List<BoardTypeCount> getBoardTypeCount() {

        String sql = " " +
                "SELECT bt.id, bt.board_name, bt.reg_date, bt.using_yn," +
                "(SELECT COUNT(*) FROM board b WHERE b.board_type_id = bt.id) as board_count " +
                "FROM board_type bt ";

//        List<BoardTypeCount> boardTypeCountList = entityManager.createNativeQuery(sql).getResultList();
//        List<Object[]> resultList = entityManager.createNativeQuery(sql).getResultList();
//        return resultList.stream().map(e -> new BoardTypeCount(e)).collect(Collectors.toList());

        Query nativeQuery = entityManager.createNativeQuery(sql);
        JpaResultMapper jpaResultMapper = new JpaResultMapper();

        return jpaResultMapper.list(nativeQuery, BoardTypeCount.class);
    }

}

- BoardTypeCount.java

package com.example.jpa.sample.board.model;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigInteger;
import java.sql.Timestamp;
import java.time.LocalDateTime;

@AllArgsConstructor
@NoArgsConstructor
@Builder
@Data
public class BoardTypeCount {

    private Long id;
    private String boardName;
    private LocalDateTime regDate;
    private boolean usingYn;

    private Long boardCount;

    public BoardTypeCount(Object[] arrObj) {
        this.id = ((BigInteger)arrObj[0]).longValue();
        this.boardName = ((String)arrObj[1]);
        this.regDate = ((Timestamp)arrObj[2]).toLocalDateTime();
        this.usingYn = ((Boolean)arrObj[3]);
        this.boardCount = ((BigInteger)arrObj[4]).longValue();
    }

    public BoardTypeCount(BigInteger id, String boardName, Timestamp regDate, Boolean usingYn, BigInteger boardCount) {
        this.id = id.longValue();
        this.boardName = boardName;
        this.regDate = regDate.toLocalDateTime();
        this.boardCount = boardCount.longValue();
    }
}