[Java] MyBatis에서 저장 프로시저(Stored Procedure) 호출 방법 : CALL, MyBatis Parameter

해당 글에서는 MyBatis 내에서 저장 프로시저를 호출하는 방법에 대해 알아봅니다.

 

 

 

💡 [참고] 저장 프로시저에 대해 관심이 있으시면 아래의 글도 도움이 됩니다.

분류	링크
[DB/Postgres] PL/pgSQL 이해-1 : Function, Stored Procedure	https://adjh54.tistory.com/159
[DB/Postgres] PL/pgSQL 구성하기 -2 : DBeaver 기반 구축 및 활용	https://adjh54.tistory.com/168
[DB/Postgres] 저장 프로시저로 Dummy 데이터 만드는 방법	https://adjh54.tistory.com/401
[DB/Postgres] 저장 프로시저 매개변수 사용방법: IN, OUT, INOUT	https://adjh54.tistory.com/408
[DB/Postgres] PL/pgSQL 함수, 프로시저 예외처리 사용방법 : Exception Handling	https://adjh54.tistory.com/413
[DB/Postgres] SQLSTATE Code 목록 : SQL 오류코드	https://adjh54.tistory.com/412
[Java] MyBatis에서 저장 프로시저 호출 방법	https://adjh54.tistory.com/409

 

 

 

1) 상황

---

💡 상황

- SQL 내에서 구성한 저장 프로시저를 MyBatis에서 호출을 하는 경우에 프로시저의 파라미터(IN, OUT, INOUT)를 고려하여서 프로시저를 호출하는 방법에 대해 알아보고, 프로시저의 결과 값이 존재하면 반환받는 방법에 대해서 알아봅니다.

 

 

 

2) CALL

---

💡 CALL

- PostgreSQL 해당 명령어는 저장 프로시저를 실행하기 위한 SQL 명령입니다.

 

 

💡 CALL 명령의 구조

- procedure_name: 실행하려는 프로시저의 이름
- parameter: 프로시저가 필요로하는 매개변수입니다. 매개변수는 쉼표로 구분되며 필요한 매개변수가 없는 경우에는 괄호 안을 비워둘 수 있습니다.

CALL procedure_name ( [ parameter [, ...] ] )

 

 

 

 

 

3) 저장 프로시저 구성 및 호출

---

 

1. 저장 프로시저 구성

---

💡 저장 프로시저 구성

- 해당 프로시저는 ‘PROC_INSERT_FIND_USER’ 이름의 Dummy 데이터로 100건의 사용자를 쌓은 이후에 파라미터로 받은 생성된 아이디를 찾고 몇 명인지 반환해 주는 프로시저입니다.

1. pi_user_name: 동일한 사용자 이름으로 생성을 위해 파라미터로 받습니다(IN)
2. pi_find_id : 난수로 생성된 사용자 아이디를 검색하기 위한 텍스트를 파라미터로 받습니다(IN)
3. po_find_id_cnt: 검색된 아이디를 기반으로 Counting 하는 반환받는 파라미터입니다.(OUT)

create procedure proc_insert_find_user(IN pi_user_name character varying, IN pi_find_id character varying, OUT po_find_id_cnt integer)
    language plpgsql
as
$$
DECLARE
    LOOP_CNT INTEGER;
    USER_CNT INTEGER;
BEGIN
    -- 지역변수로 사용할 변수 지정
    LOOP_CNT := 0;

    -- 반복 구문
    WHILE LOOP_CNT < 100 LOOP
        INSERT INTO TB_USER (user_id, user_pw, user_nm, user_st)
        VALUES(SUBSTRING(gen_random_uuid()::text, 1, 5), '1234', pi_user_name, 'A');
        LOOP_CNT := LOOP_CNT + 1;
    END LOOP;
    

    -- pi_find_id에 해당하는 user_id 찾기
    SELECT COUNT(*) INTO USER_CNT
    FROM TB_USER
    WHERE user_id LIKE '%' || pi_find_id || '%';

    -- 찾은 user_id 개수를 po_find_id_cnt에 할당
    po_find_id_cnt := USER_CNT;
    
END;
$$;

alter procedure proc_insert_find_user(varchar, varchar, out integer) owner to localmaster;

 

 

 

 

2. SQL내에서 프로시저를 호출해 봅니다.

---

💡 프로시저를 호출합니다.

- 프로시저를 호출하였을 때 OUT 값으로 po_find_id_cnt라는 값을 반환받습니다.

CALL proc_insert_find_user('홍길동':: text, 'a':: text, null)

 

 💡 결과

- 아래와 같이 실제 테이블에 확인을 하여서 값을 확인했을 때 프로시저로 수행하여 반환받은 값과 동일함을 확인하였습니다.

 

 

4) Java 프로젝트 내에서 구성 및 호출

---

💡 Java 프로젝트 내에서 구성 및 호출

- 프로젝트 구조에서는 Controller → ServiceImpl → Service(interface) → Mapper.java(interface) → Mapper.xml 형태로 호출되고 구성되어 있습니다.

 

 

1. DummyUserDto

---

💡 DummyUserDto

- 해당 DTO 내에서는 IN 값으로 piUserName, piFindUserId를 사용하기에 정의하였고 OUT 값으로 poUserCnt으로 정의하였습니다.

package com.adjh.multiflexapi.model;

import lombok.AccessLevel;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;

/**
 * Dummy User DTO
 *
 * @author : jonghoon
 * @fileName : DummyUserDto
 * @since : 1/27/24
 */
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class DummyUserDto {

    private String piUserName;

    private String piFindUserId;

    private int poUserCnt;

    @Builder(toBuilder = true)
    public DummyUserDto(String piUserName, String piFindUserId, int poUserCnt) {
        this.piUserName = piUserName;
        this.piFindUserId = piFindUserId;
        this.poUserCnt = poUserCnt;
    }
}

 

 

 

2. UserController

---

💡 UserController

- /api/v1/user/dummyUser 엔드포인트로 HTTP Method POST를 이용하면 해당 API가 호출이 됩니다.

@Slf4j
@RestController
@RequestMapping("/api/v1/user")
public class UserController {
    private final UserService userService;

    public UserController(UserService userService) {
        this.userService = userService;
    }

    /**
     * 테스트용 Dummy 사용자를 생성하고 추가된 사용자를 반환 받습니다.
     *
     * @param dummyUserDto UserDto
     * @return ApiResponseWrapper<ApiResponse> : 응답 결과 및 응답 코드 반환
     */
    @PostMapping("/dummyUser")
    public ResponseEntity<ApiResponse<Object>> dummyUser(@RequestBody DummyUserDto dummyUserDto) {
        int result = userService.procInsertFindUser(dummyUserDto);
        ApiResponse<Object> ar = ApiResponse.builder()
                .result(result)
                .resultCode(SuccessCode.INSERT.getStatus())
                .resultMsg(SuccessCode.INSERT.getMessage())
                .build();
        return new ResponseEntity<>(ar, HttpStatus.OK);
    }
}

 

 

3. UserService

---

@Service
public interface UserService {
	int procInsertFindUser(DummyUserDto dummyUserDto);
}

 

 

4. UserServiceImpl

---

💡 UserServiceImpl

- 해당 부분에서는 void 형태의 반환 값을 가지는 procInsertFindUser() 함수를 호출하고 return 값으로 dummyUserDto 내에 값이 반환됩니다.
- 해당 반환된 값을 결과값으로 반환하도록 구성하였습니다.

/**
* Dummy User 를 생성하고 아이디를 기반으로 생성된 사용자 수를 조회합니다.
*
* @param dummyUserDto DummyUserDto
* @return int
*/
@Override
@Transactional
public int procInsertFindUser(DummyUserDto dummyUserDto) {
  UserMapper um = sqlSession.getMapper(UserMapper.class);
  int result = 0;
  try {
      um.procInsertFindUser(dummyUserDto);
      result = dummyUserDto.getPoFindIdCnt();
  } catch (Exception e) {
      throw new BusinessExceptionHandler(e.getMessage(), ErrorCode.INSERT_ERROR)
  }
  return result;
}

 

 

5. UserMapper

---

@Repository
public interface UserMapper {
	void procInsertFindUser(DummyUserDto dummyUserDto);
}

 

 

6. UserMapper.xml

---

💡 UserMapper.xml
- 해당 xml 내에서 select 태그로 프로시저를 구성하였습니다.

1. <select statementType="CALLABLE">

- statementType 속성은 SQL 문장을 실행하는 방법을 결정합니다.
- STATEMENT, PREPARED(Default)또는 CALLABLE 중 하나를 선택할 수 있는데 ‘CALLABLE’는 프로시저를 실행하는 방식입니다. 이를 사용합니다.


2. CALL multiflex_scma.PROC_INSERT_FIND_USER

- 구성한 multiflex_scma 스키마 내에 PROC_INSERT_FIND_USER() 이름의 프로시저를 CALL 명령어를 통해 호출합니다.


3. #{piUserName, mode=IN, jdbcType=VARCHAR},

- 사전에 구성한 DTO의 값 중 하나로 매핑이 되며 mode는 프로시저의 파라미터를 의미하며 입력(IN)으로 지정하며, jdbcType=VARCHAR를 통해 VARCHAR 타입으로 전달합니다.


4. #{piFindUserId, mode=IN, jdbcType=VARCHAR},

- 동일하게 사전에 구성한 DTO의 값중 하나로 매핑이 되며 mode는 프로시저의 파라미터를 의미하며 입력(IN)으로 지정하며, jdbcType=VARCHAR를 통해 VARCHAR 타입으로 전달합니다.


5. #{poFindIdCnt, mode=OUT, jdbcType=INTEGER})

- 프로시저 내의 결과 값으로 ‘poFindIdCnt’ 값 형태로 반환을 받습니다. mode로 프로시저의 파라미터를 출력(OUT)으로 지정하였으며 jdbcType=INTEGER를 통해 INTEGER 타입으로 반환받습니다.

 

<?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.adjh.multiflexapi.mapper.UserMapper">
		<!--Dummy User를 등록하고 사용자 아이디에 따라 결과값을 반환받습니다.    -->
    <select id="procInsertFindUser" statementType="CALLABLE" parameterType="DummyUserDto">
        CALL multiflex_scma.PROC_INSERT_FIND_USER(
                #{piUserName, mode=IN, jdbcType=VARCHAR},
                #{piFindUserId, mode=IN, jdbcType=VARCHAR},
                #{poFindIdCnt, mode=OUT, jdbcType=INTEGER})
    </select>
</mapper>

 

 

 

7. 결과 확인

---

💡 위에서 지정한 API Endpoint로 Request Body 값을 담아서 전송하였습니다.

 

💡 아래와 같이 서버에서 프로시저를 호출하여 처리가 되었습니다.

 

 

 

💡 해당 반환값이랑 실제 DB에 있는 값을 비교해 보았을 때 동일한 결과값임을 확인하였습니다.

 

 

 

 

오늘도 감사합니다. 😀