Springboot + JPA + mariaDB 간단한 procedure 프로시저 만들고 호출하기

사내 프로젝트 도중 자바단에서만 로직이 다 수행되면 나중에 메모리 부하가 심해질 수 있으니

이를 덜어주기 위해서 DB에서 프로시저 만들고 호출해서 사용하도록 만들어보라는 임무(?)를 하달받았다

 

납품일 기준 - 현재 날짜 간 d-day를 계산하여 일괄 변경해주도록 하는 프로시저이다.

 

사내 프로젝트에서 만들어보았던 기억을 토대로 까먹지 않도록 기록하기 위해서 끄적여본다

그때보다 간단하게 만들어 보도록 하겠다

 

나는 mariaDB GUI 툴인 HeidiSQL을 사용하므로 이를 이용해서 프로시저를 만들었다

(직접 쿼리문 입력해서 만드셔도 됩니다)

 

1. 프로시저 생성

heidiSQL 화면 좌측에 보이는 테이블 우클릭 -> 새로 생성 -> 저장 프로시저 클릭

위 화면이 뜰 텐데 상단에 노란색 표시한 부분에 프로시저 이름을 적어준다

지금 내가 만들 프로시저는 딱히 반환시킬 결괏값이 있는 게 아니므로 결괏값 반환하지 않도록 설정함

 

DECALRE는 변수 선언을 위해서 적어줘야 한다

DATEDIFF는 날짜 - 날짜 간의 차이를 구해주는 함수이다

년, 월, 주, 분, 초 등등 여러 가지 다 구할 수 있는 함수이므로 필요에 따라 검색해서 사용하면 좋을 듯하다

자바만 하다가 프로시저 생성 시 문법이 생소하여 처음 개발 시 애를 먹었던 기억이 있다.

위에 보이는 dDay, i, id 등 변수에 @dDay, @i, @id 이렇게 @를 붙여서 사용했던 기억이 있는데

문법 관련해서는 mariaDB 버전에 따라 조금씩 차이가 있는 것 같고 나도 잘 모르기에 

각자 상황에 맞는 문법 검색해서 적용하시길 바라고, DB 버전에 따라 잘 찾아서 적용해야 할 것 같다

 

위 로직 대충 설명하자면 

id 1번부터 제일 마지막 id까지 반복문 돌려서 DATEDIFF를 활용한 날짜 차이를 d_day 컬럼에 update 해주는 로직이다

실무에서 데이터 베이스에 수억 건이 넘는 데이터가 있는 상황이라면 정말 비효율적인 로직이나, 

간단한 예제 차원으로 만들어 본 것이니 양해해주시길 바란다

 

 

 다 만들고 나서 저장을 누르면 이렇게 프로시저가 만들어진다.

두 번째 사진 우측 하단에 보이는 루틴 실행을 누르면 디비에서 프로시저가 동작하게 된다.

 

현재 테이블 내 d_day 컬럼을 보면 모두 0인 상태이다

루틴 실행을 해보면?

 

각 로우들의 d-day가 update 되었다 !

 

2. 프로시저 호출

이제 springBoot 프로젝트에서 호출이 되도록 해야 한다

검색해보니 여러 가지 방법이 많이 나오던데 JPA에서 할 수 있는 최대한 간단한 방법으로 호출해보도록 하겠다

레포지토리에서 @Query 어노테이션 활용하여 nativeQuery 선언 후

DB에서 프로시저 콜 할 때 쓰이는 문법 그대로를 적어주면 된다

그다음 서비스단 -> API로 연결되도록 코드를 짜주면 된다 (이건 간단하니 생략)

 

@GetMapping에 설정해둔 URL 주소로 PostMAN 활용하여 API를 쏴보려고 한다

그전에 비교를 위해서 다시 DB에 있는 d_day 컬럼을 다시 0으로 맞춰주겠다

API 로 호출이 되는지 확인을 위해 d_day 컬럼 0으로 초기화

 

포스트맨으로 프로시저 호출 API send !

 

인텔리제이 하단 콘솔 창에 프로시저 호출한 쿼리문이 찍혔다

 

0으로 초기화해주었던 d_day 컬럼에 값들이 다시 세팅되었다!

 

 

 

참조

https://extbrain.tistory.com/78     DATEDIFF 관련

https://zzarungna.com/1558        프로시저 반복문 문법 관련