Mybatis+MySQL - sql function호출 하기

-개요 이자 문제점

 

하단의 코드는 게시판을 삽입하는 *mapper.xml 코드이다. 

oracle에서는 아래처럼 하면 되지만 , mysql에서는 시퀀스 사용이 불가능하다.

( mysql 에서 그나마 사용 가능한 비스무리 한건 auto_increment 를 적용해서 nextval 효과를 내는것) 

<insert id="insertBoard" parameterType="ITboardDTO">
          insert into itboard (bidx,board_ref, ~~생략~~) values
         (itboard_bidx.nextval,(itboard_bidx.currval,~~생략~~)
</insert>

 

나는 mysql 에서 오라클의 시퀀스(nextval,currval)의 기능을 이용하고 싶다.

 

-방법1   MAX(auto_increment적용된 컬럼) 이용

 

하단의 코드는 사전작업으로 , bidx라는 컬럼에 auto_increment 효과를 주었다. 

<insert id="insertBoard" parameterType="ITboardDTO">
          insert into itboard (board_ref, ~~생략~~) values
          ((select max(bidx)+1 from itboard),~~생략~~)
</insert>

결과로는 bidx는 하나증가된 값을 가지고 , 

이와 같은 값을 board_ref 가 가지게 된다.

( 3번째줄의 (select max(bidx)+1 from itboard)  서브쿼리가 하나의 트랜잭션으로 독립적으로 취급된다면 

레이스 컨디션 상태라고 말할수 있겠지만 , 2~3줄을 합쳐서 하나의 트랜잭션인것 같다..  그러면 문제없음. )

https://droptable.tistory.com/33 ( 사이트를 보면 맨 밑 예시는 분명 레이스 컨디션이 발생할만한 상황이다 )

 

레이스 컨디션이라는 용어는 하단의 사이트에서 자세히 알 수 있다.

http://blog.naver.com/PostView.nhn?blogId=kojaejung&logNo=40109677766 

트랜잭션의 독립성은 하단의 주소를 보자.

 https://junghn.tistory.com/entry/DataBase기초-트랜잭션이란-무엇인가-Transaction 

 

 

-방법2    시퀀스테이블,시퀀스생성프로시져,함수  이용하기

 

그냥 MAX(auto_increment적용된 컬럼) 요거를 사용할 수도 있었지만 찜찜해서 방법2로 진행하기로 하였다.

 

하단의 샘플은 SQLYOG에서 작성한 쿼리문이고 , 게시글들을 카테고리별로 구별하여 curval,nextval에 이용하게 될것이다.

순서는 1 . 시퀀스테이블 생성 ,  2. 프로시져정의 3. 두개의 함수(nextval , curval) 정의이다.

# mysql 에서  curval,nextval 구현하기
CREATE TABLE BOARD_SEQUENCES
(
    CATEGORY TINYINT(1)
  , CURRVAL BIGINT UNSIGNED
)
ENGINE = INNODB
 
DELIMITER $$
    CREATE PROCEDURE create_boardsequence(IN THE_CATEGORY TINYINT)
    MODIFIES SQL DATA
    DETERMINISTIC
    BEGIN
        DELETE FROM board_sequences WHERE CATEGORY = THE_CATEGORY;
        INSERT INTO board_sequences VALUES(THE_CATEGORY, 0);
   END
DELIMITER //
    CREATE FUNCTION `boardnextval` (THE_CATEGORY TINYINT(1))
    RETURNS BIGINT UNSIGNED
    MODIFIES SQL DATA
    DETERMINISTIC
    BEGIN
        DECLARE ret BIGINT UNSIGNED;
        UPDATE board_sequences SET currval = currval +1 WHERE CATEGORY = THE_CATEGORY;
        SELECT currval INTO ret FROM board_sequences WHERE CATEGORY = THE_CATEGORY LIMIT 1;
        RETURN ret;
   END
DELIMITER //
    CREATE FUNCTION `boardcurval` (THE_CATEGORY TINYINT(1))
    RETURNS BIGINT UNSIGNED
    DETERMINISTIC
    BEGIN
        DECLARE ret BIGINT UNSIGNED;
        SELECT currval INTO ret FROM board_sequences WHERE CATEGORY = THE_CATEGORY LIMIT 1;
        RETURN ret;
   END
CALL create_boardsequence('1');

2~6 :    시퀀스 테이블을 정의 해주었다.   

9~16 :  인자로 들어오는 THE_CATEGORY가  BOARD_SEQUENCES테이블의  CATEGORY에 할당되며,

          BOARD_SEQUENCES테이블의  CURRVAL을 0으로 초기화 한다.

 

프로시져의 구성.

MODIFIES SQL DATA :
MODIFIES SQL DATA 는 함수혹은 프로시져가 db에 저장된 data를 수정할수있는 명령문을 포함한다는 것을 의미한다.
이것은 함수혹은 프로시저에 DELETE, UPDATE, INSERT, REPLACE 또는 DDL과 같은 명령문이 포함 된 경우 넣어준다.
DETERMINISTIC : 요약하면 함수혹은 프로시져의 인자값이 바뀌지 않는 이상 함수혹은프로시저의 호출은 한번만 한다는 것이다.

NO DETERMINISTIC 으로 설정한다면 조건에 따른 모든 경우에 함수 혹은 프로시져가 호출됨. 자세한 것은 하단의 주소를 참고한다.
http://intomysql.blogspot.com/2010/12/stored-function-not-deterministic.html 참고

프로시져를 생성하는 방법에 대해서는 하단의 주소를 참고한다.

https://m.blog.naver.com/alcmskfl17/221858522028

IN, OUT, INOUT 에 대한 설명은 하단의 주소를 참고한다.(어색하지만 잘 설명되어있음)

https://blog.duveen.me/14

 

17~27 : nextval을 구현한 함수이다.

          board_sequences 테이블에서 curval함수의 입력된 인자가 CATEGORY와 동일한 레코드를 찾아서,

          currval을 하나 증가시켜준다.

          25~26번째 줄은 증가된 currval을 리턴시키는 작업이다.

 

추가로 SELECT INTO reto ~  쿼리에서  INTO는 나온결과물을 reto에 대입시킨다는 의미이다. 자세한건 하단의 주소를 참고하자.

https://newpower.tistory.com/160

29~36 : curval을 구현한 함수이다.

37 : CALL create_boardsequence('1');  CALL을 붙여 프로시져를 호출하였다.

      시퀀스테이블에 CATEGORY=1, CURRVAL=0 인 레코드가 생성되었다. 

 

#사용법예시 INSERT INTO test (id,pw,NAME) VALUES ( boardnextval('1') , boardcurval('1'),boardcurval('1') );

예시의 결과레코드는  3개의 컬럼 모두 같은 값이 나온다.

 

이제 Mybatis  *mapper.xml 내용을 보자

  <insert id="insertBoard" parameterType="ITboardDTO">
          insert into itboard (bidx,board_ref, ~~생략~~) values
          (boardnextval(#{category}),boardcurval(#{category}), ~~생략~~ )
  </insert>

3 : Mybatis+mysql 에서 sql function을 수행하는 것은 간단하다.  3번째줄 처럼 '함수명(#{인자})' 로 사용하면 된다.

결과는 내가 원하는 대로 나왔다.  bidx와 board_ref는 같은 값을 가지게 되었다.

 

mybatis+mysql 를 사용하는 환경에서 sql function 기능을 사용할때는 윗 방법대로 사용하면 되지만,

만약 sql 프로시져 기능을 사용하려 한다면 

태그 내부에 statementType="CALLABLE" 이라는 속성을 추가해 줘야 프로시져로 인식하여 프로시져를 수행한다.

옆사이트에 예시가 있다. https://krespo.net/164

 

 

 

-참고

https://proudin.tistory.com/28