[mybatis] mybatis oracle merge into

mapper.xml

<!--게시글 순번 시퀀스 조회-->
<select id="selectMaxBoardSno" resultType="Long">
    SELECT BOARD_SNO_SEQ.NEXTVAL AS boardSno
      FROM DUAL
</select>

<!--게시글 등록/수정(merge) -->
<insert id="mergeBoard" parameterType="BoardVo">
    /* 게시글 등록/수정 */
    MERGE INTO BOARD_INFO A
      /* 비교 조건(boardSno가 같은지 비교) */
      USING DUAL ON(A.BOARD_SNO = #{boardSno})
    WHEN MATCHED THEN
    /* update(boardSno가 일치하면 update) */
    UPDATE
        <set>
            <if test="title != null and title != ''">
            TITLE = #{title}
            </if>
            <if test="content != null and content != ''">
            , CONTENT = #{content}
            </if>
            , MOD_DATE = SYSDATE
        </set>
     WHERE BOARD_SNO = #{boardSno}
    /* insert(boardSno가 불일치 할 경우 insert) */
    WHEN NOT MATCHED THEN
    INSERT
        (
            BOARD_SNO
          , TITLE
          , CONTENT
          , USER_ID
          , REG_DATE
        )
    VALUES
        (
            #{boardSno}
          , #{title}
          , #{content}
          , #{userId}
          , SYSDATE
        )
</insert>

 

mapper(interfacle)

/* 게시글 순번 시퀀스 조회 */
Long selectMaxBoardSno();

/* 게시글 등록/수정 */
int mergeBoard(BoardVo boardVo);

service

/**
 * 게시글 등록/수정
 * @param boardVo
 * @param br
 */
public ResponseEntity mergeBoard(BoardVo boardVo, BindingResult br) {
    //parameter 검증 실패
    if (br.hasErrors()) {
        invokeErrors(this.getClass().getName(), br);
    }
    //결과 코드값
    Map<Object, Object> resultMap = new HashMap<>();
    //화면에서 넘어온 게시글 순번이 없는 경우(게시글 등록)엔 시퀀스 값으로 세팅
    if(boardVo.getBoardSno() == null) boardVo.setBoardSno(boardMapper.selectMaxBoardSno());
    //쿼리 성공 시
    int count = boardMapper.mergeBoard(boardVo);
    //쿼리 성공 시
    if (count > 0) {
        resultMap.put("no", boardVo.getBoardSno());
        resultMap.put("code", ResultCode.SUCCESS.getCode());
    //쿼리 실패 시
    } else {
        resultMap.put("code", ResultCode.FAIL.getCode());
    }
    return new ResponseEntity<>(resultMap, HttpStatus.OK);
}

controller(Rest)

package study.dev.thboard3.board.controller;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.ResponseEntity;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.*;
import study.dev.thboard3.board.model.BoardVo;
import study.dev.thboard3.board.service.BoardService;

import javax.validation.Valid;

@RestController
@RequiredArgsConstructor
@Slf4j
@RequestMapping("/api/board")
public class BoardRestController {

private final BoardService boardService;

/**
 * 게시글 등록/수정
 * @param boardVo
 * @param br
 * @return
 * @throws Exception
 */
@PostMapping("/reg")
public ResponseEntity reg(@RequestBody @Valid BoardVo boardVo, BindingResult br) throws Exception {
    return boardService.mergeBoard(boardVo, br);
}

/**
 * 게시글 상세
 * @param boardSno
 * @return
 * @throws Exception
 */
@GetMapping("/{boardSno}")
public ResponseEntity detail(@PathVariable Long boardSno) throws Exception {
    return boardService.getBoardDetail(boardSno);
}
}