[Spring][쇼핑몰 프로젝트][27] 업로드 이미지 정보 등록 - 3

프로젝트 Github : https://github.com/sjinjin7/Blog_Project
프로젝트 포스팅 색인(index) : https://kimvampa.tistory.com/188

목표

<selectkey> 태그 내의 쿼리문 보완

 저번 포스팅에서 <selectkey> 태그 내의 쿼리문을 LAST_INSERT_ID(MySQL), 시퀀스의 currval 명령을 활용하여 보완하는 것이 목표입니다.

 

그림 0 보완 할 대상 쿼리문

 

 

 

순서

1. 현재 <selectkey> 쿼리 문제점

2. 보완

 2.1 MySQL

 2.2 Oracle

 

 

 

1. 현재 <selectkey> 쿼리 문제점

 현재의 코드는 <selectkey>의 order 속성값이 "BEFORE" 이어서 INSERT문이 실행이 되기 전에 <selectkey> 태그 내부의 쿼리문이 먼저 실행이 되는데, 쿼리 문의 명령은 'bookId'값 중 가장 큰 값을 찾아서 +1 된 값을  BookVO의 "bookId"변수에 반환을 합니다.

 

 문제는 가장 최근에 등록한 정보(vam_table의 행(row))를 삭제 한 후, 새로운 정보를 추가했을 때 새로 등록되는 'bookId'값과 반환받은 'bookId'값이 일치하지 않는다는 점입니다. 

 

 예를 들어 'bookId'가 3107인 행을 지우고 새로운 행을 추가했을때, 'bookId'가 3108인 행이 추가되지만 <selectkey>를 통해 반환받은 결과는 3107 혹은 더 작은 값입니다.

 

그림 1-1 삽입명령 실행 전

 

그림 1-2 Junit 테스트

 

그림 1-3 새로운 행 삽입 결과

 

그림 1-4 <selectkey>반환 결과

 

 

 

 

2. 보완

 <selectkey> 태그를 작성하는 이유는 등록한 행의 'bookId'의 값을 반환 받기 위해서입니다.

 

 가장 손쉬운 해결책은 INSERT문이 수행이 된 후, 테이블에서 가장 큰 'bookId'값을 가져오는 것입니다. 따라서 아래와 같이 <selectkey>를 수정하면 됩니다.

 

  		<selectKey resultType="int" keyProperty="bookId" order="AFTER">
  		
  			SELECT MAX(bookId) FROM vam_book
  		
  		</selectKey>  	

 

 기존 코드에서 달라진 점은 order 속성 값이 "AFTER"로, SELECT 대상이 'MAX(bookId) + 1'에서 MAX(bookId)로 수정되었다는 점입니다.

 

 코드를 해석을 하면 order 속성값 AFTER로 인해서 본 쿼리문(INSERT)이 실행이 된 뒤에 <selectkey> 태그 내의 쿼리문이 실행이 되며 vam_book테이블의 'bookId'값 중 가장 큰 값을 BookVO에 있는 bookId 변수에 반환시킨다는 의미입니다.

 

---

물론 위의 방식을 사용해도 전혀 문제가 없습니다. 그렇지만 좀 더 한발 나아가서 DB에서 만들어 내는 기본키 값을 명확히 지정해서 가져오는 방법에 대해서 알아보고, 그 방식을 통해 <selectkey> 태그의 쿼리문을 작성해보겠습니다.

 

2-1 MySQL 

 vam_book 테이블의 'bookId' 칼럼 값은 MySQL이 제공하는 AUTO_INCREMENT 명령어를 통해서 자동으로 +1 더해진 값을 삽입해주고 있습니다. 

 

 MySQL과 MariDB에선 "LAST_INSERT_ID()" 명령어를 제공해줍니다. 해당 명령어의 기능은 가장 최근에 실행된 INSERT문의 결과에서 AUTO_INCREMNET로 인해 생성된 열(Column)의 값을 반환해줍니다. 명령 수행 후 INSERT문의 실행이 없다면 LAST_INSERT_ID()값은 변경되지 않습니다.

 

 해당 명령어의 사용방법은 단순히 아래와 같이 코드를 작성하면 실행이 됩니다.

 

SELECT LAST_INSERT_ID();

 

그림 1-5

 

 이를 <selectkey>에 적용을 해보겠습니다. MySQL을 사용하는 프로젝트의 AdminMapper.xml 경우 아래와 같이 수정을 하시면 됩니다.

 

  		<selectKey resultType="int" keyProperty="bookId" order="AFTER">
  		
  			SELECT LAST_INSERT_ID()
  		
  		</selectKey>

 

그림 2-5

 

 LAST_INSERT_ID()는 가장 최근에 실행된 INSERT의 열 값을 반환해주기 때문에 <selectkey>의 order 속성 성 값을 "AFTER"로 지정해주었습니다. 

 

 

 

2-2 Oracle

 아쉽게도 Oracle에서는 LAST_INSERT_ID와 같은 명령어를 제공하지 않습니다. 현재 Oracle 프로젝트에서 생성한 vam_book 테이블의 'bookId' 컬럼(Column)은 IDENTITY 기능을 통해 +1된 값이 삽입되고 있습니다. IDENTITY 기능은 오라클 12c 버전부터 제공하는 기능인데 내부적으로 시퀀스와 default value(테이블 생성 시 부여해주는 기본값)를 사용합니다. 따라서 시퀀스(Sequence)에서 현재의 시퀀스 값을 얻기 위해 사용하는 CURRVAL 명령을 IDENTITY가 생성하는 현재의 값을 얻을 수 있습니다.

 

※ 시퀀스 : 일련의 정수들을 Sequence 문장의 옵션들을 통해 자동적으로 발생시키는 것.(한국데이터산업진흥원)

 

---

* 시퀀스의 현재의 값을 얻기 위한 명령은 아래와 같습니다.

 

SELECT 시퀀스객체이름.CURRVAL FROM DUAL;

---

  CURRVAL을 사용하기 위해선 먼저 시퀀스 객체 이름에 대한 정보를 얻어야 합니다. 다음의 명령을 통해서 얻을 수 있습니다. 아래 명령어에서 주의할 점은 '테이블 이름'을 대문자로 작성해주어야 한다는 점입니다.

 

Select TABLE_NAME, COLUMN_NAME, DATA_DEFAULT from USER_TAB_COLUMNS
where TABLE_NAME = '대상 테이블 이름(대문자)';

 

 

Oracle DB에 위의 명령을 실행합니다.

 

그림 2-1

 

그림 2-2

 

 출력된 결과를 통해서 'bookId'의 시퀀스 이름은 ISEQ$$_74040( 또는 "VAM"."ISEQ$$_74040" 도 가능) 임을 알 수 있습니다.

 

 - 앞의 "VAM"은 제가 사용 중인 Oracle DB 아이디입니다.

 - nextval은 시퀀스의 다음 값(+1)을 호출하는 명령입니다. 따라서 vam_book의 행이 추가될 때마다 기본값으로 기존 시퀀스 값의 다음 값(+1)이 추가되는 것을 알 수 있습니다.

 

 

 시퀀스 이름을 통해 CURRVAL 명령을 실행해서 결과를 확인해봅니다.

 

그림 2-3

 

그림 2-4

 

 

 이제 AdminMapper.xml 파일로 돌아와서 <selectkey> 태그 내부 쿼리문에 위의 명령어를 추가해줍니다. <selectkey> 태그의 order 속성은 "AFTER"로 변경해줍니다.

 

  		<selectKey resultType="int" keyProperty="bookId" order="AFTER">
  		
  			SELECT ISEQ$$_74040.currval from dual
  		
  		</selectKey>  	

 

그림 2-6

 

 

 

REFERENCE

• https://mariadb.com/kb/en/last_insert_id/
• https://n1tjrgns.tistory.com/191
• https://stackoverflow.com/questions/34811283/retrieve-oracle-last-inserted-identity

 

 

DATE

• 2020.06.09