[MyBatis] MS-Sql Merge Into문 사용 / Mssql Dual 테이블

[MyBatis] MS-Sql Merge Into문 사용 / Mssql Dual 테이블

2023. 1. 31. 17:18ㆍDev.Program/USELESS:<

728x90

MERGE INTO문을 사용하면 데이트 존재여부에 따라 UPDATE, DELETE, INSERT 등을 한 번에 할 수 있다.

기본적으로 Mssql의 Merge Into문이다.

MERGE INTO (변경할 테이블)
USING (비교할 테이블|서브 쿼리)
ON (조건문)
WHEN MATCHED THEN
	(조건을 만족할 경우 쿼리문)
	UPDATE SET 컬럼명1 = '값1', 컬럼명2 = '값2'
	DELETE
WHEN NOT MATCHED THEN
	(조건을 만족하지 않을 경우 쿼리문)
	INSERT ( 컬럼1, 컬럼2 )
    VALUES ( '값1', '값2' );

> 이 기본 쿼리문을 기준으로 myBatis에 코드를 짜보자!

	<update id="mergeToken" parameterType="reservation.domain.ZoomOAuth">
		<![CDATA[
		MERGE INTO OAUTH_TOKEN a
		USING DUAL
		ON (a.FLAG = #{flag})
		WHEN MATCHED THEN
			UPDATE SET
				REFRESH_TOKEN = #{refresh_token},
				UDATE = GETDATE(), 
				UUSER = #{ssn}
			WHERE FLAG = #{flag}
		WHEN NOT MATCHED THEN
			INSERT (      
						AUTH
						,REFRESH_TOKEN
						,FLAG
						,UDATE
						,UUSER
					) 
			VALUES (
						#{auth}
						,#{refresh_token}
						,#{flag}
						,GETDATE()
						,#{ssn}
					)
		]]>
	</update>

> 처음으로 짠 코드! myBatis에선 <update> 태그를 써준다.

OAUTH_TOKEN 테이블에서 a.FLAG = #{내가넘긴Flag값)에 해당하는 데이터가

있을 경우(MATCHED) UPDATE, 없을 경우(NOT MATCHED) INSERT 를 해주는 코드.

com.microsoft.sqlserver.jdbc.SQLServerException: 키워드 'WHERE' 근처의 구문이 잘못되었습니다.

2023-01-31 16:18:51.763 [http-nio-8080-exec-9] ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [/_test] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 키워드 'WHERE' 근처의 구문이 잘못되었습니다.
### The error may exist in file [D:\workspace-spring-tool-suite-4-4.9.0.RELEASE\_test\target\classes\mybatis-mapper\reservation\ZoomOAuthMapper.xml]
### The error may involve reservation.mapper.ZoomOAuthMapper.mergeToken-Inline
### The error occurred while setting parameters
### SQL: MERGE INTO OAUTH_TOKEN a   USING DUAL   ON (a.FLAG = ?)   WHEN MATCHED THEN    UPDATE SET     REFRESH_TOKEN = ?,     UDATE = GETDATE(),      UUSER = ?    WHERE FLAG = ?   WHEN NOT MATCHED THEN    INSERT (             AUTH       ,REFRESH_TOKEN       ,FLAG       ,UDATE       ,UUSER      )     VALUES (       ?       ,?       ,?       ,GETDATE()       ,?      )
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 키워드 'WHERE' 근처의 구문이 잘못되었습니다.
; bad SQL grammar []; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 키워드 'WHERE' 근처의 구문이 잘못되었습니다.] with root cause
com.microsoft.sqlserver.jdbc.SQLServerException: 키워드 'WHERE' 근처의 구문이 잘못되었습니다.

> 그대로 넘겼는데 오류남ㅎ WHERE 근처의 구문이 잘못됐단다.

> 여기가 잘못된 듯 하다!

내 생각으론 저 조건에 해당하는 것만 update 할 거라고 where 조건을 달아준 건데, 이미 조건에 해당하는 거만 가져오기 때문에 WHERE 문이 필요없는 것이었다. 저 WHERE FLAG = #{flag} 구문 지워주고 다시 시도!

	<update id="mergeToken" parameterType="reservation.domain.ZoomOAuth">
		<![CDATA[
		MERGE INTO OAUTH_TOKEN a
		USING DUAL
		ON (a.FLAG = #{flag})
		WHEN MATCHED THEN
			UPDATE SET
				REFRESH_TOKEN = #{refresh_token},
				UDATE = GETDATE(), 
				UUSER = #{ssn}
		WHEN NOT MATCHED THEN
			INSERT (      
						AUTH
						,REFRESH_TOKEN
						,FLAG
						,UDATE
						,UUSER
					) 
			VALUES (
						#{auth}
						,#{refresh_token}
						,#{flag}
						,GETDATE()
						,#{ssn}
					)
		]]>
	</update>

com.microsoft.sqlserver.jdbc.SQLServerException: MERGE 문은 세미콜론(;)으로 종료해야 합니다.

2023-01-31 16:30:23.300 [http-nio-8080-exec-3] ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [/_test] threw exception [Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: MERGE 문은 세미콜론(;)으로 종료해야 합니다.
### The error may exist in file [D:\workspace-spring-tool-suite-4-4.9.0.RELEASE\_test\target\classes\mybatis-mapper\reservation\ZoomOAuthMapper.xml]
### The error may involve reservation.mapper.ZoomOAuthMapper.mergeToken-Inline
### The error occurred while setting parameters
### SQL: MERGE INTO OAUTH_TOKEN a   USING DUAL   ON (a.FLAG = ?)   WHEN MATCHED THEN    UPDATE SET     REFRESH_TOKEN = ?,     UDATE = GETDATE(),      UUSER = ?   WHEN NOT MATCHED THEN    INSERT (             AUTH       ,REFRESH_TOKEN       ,FLAG       ,UDATE       ,UUSER      )     VALUES (       ?       ,?       ,?       ,GETDATE()       ,?      )
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: MERGE 문은 세미콜론(;)으로 종료해야 합니다.
; uncategorized SQLException; SQL state [S0001]; error code [10713]; MERGE 문은 세미콜론(;)으로 종료해야 합니다.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: MERGE 문은 세미콜론(;)으로 종료해야 합니다.] with root cause
com.microsoft.sqlserver.jdbc.SQLServerException: MERGE 문은 세미콜론(;)으로 종료해야 합니다.

> ㅋ 또 오류남

MERGE 문에 세미콜론(;)은 필수란다. 끝에 세미콜론 달아주자. 세미콜론 추가 후 다시 시도!

	<update id="mergeToken" parameterType="reservation.domain.ZoomOAuth">
		<![CDATA[
		MERGE INTO OAUTH_TOKEN a
		USING DUAL
		ON (a.FLAG = #{flag})
		WHEN MATCHED THEN
			UPDATE SET
				REFRESH_TOKEN = #{refresh_token},
				UDATE = GETDATE(), 
				UUSER = #{ssn}
		WHEN NOT MATCHED THEN
			INSERT (      
						AUTH
						,REFRESH_TOKEN
						,FLAG
						,UDATE
						,UUSER
					) 
			VALUES (
						#{auth}
						,#{refresh_token}
						,#{flag}
						,GETDATE()
						,#{ssn}
					);
		]]>
	</update>

com.microsoft.sqlserver.jdbc.SQLServerException: 개체 이름 'DUAL'이(가) 잘못되었습니다.

2023-01-31 16:40:17.121 [http-nio-8080-exec-2] ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [/_test] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 개체 이름 'DUAL'이(가) 잘못되었습니다.
### The error may exist in file [D:\workspace-spring-tool-suite-4-4.9.0.RELEASE\_test\target\classes\mybatis-mapper\reservation\ZoomOAuthMapper.xml]
### The error may involve reservation.mapper.ZoomOAuthMapper.mergeToken-Inline
### The error occurred while setting parameters
### SQL: MERGE INTO OAUTH_TOKEN a   USING DUAL   ON (a.FLAG = ?)   WHEN MATCHED THEN    UPDATE SET     REFRESH_TOKEN = ?,     UDATE = GETDATE(),      UUSER = ?   WHEN NOT MATCHED THEN    INSERT (             AUTH       ,REFRESH_TOKEN       ,FLAG       ,UDATE       ,UUSER      )     VALUES (       ?       ,?       ,?       ,GETDATE()       ,?      );
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 개체 이름 'DUAL'이(가) 잘못되었습니다.
; bad SQL grammar []; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 개체 이름 'DUAL'이(가) 잘못되었습니다.] with root cause
com.microsoft.sqlserver.jdbc.SQLServerException: 개체 이름 'DUAL'이(가) 잘못되었습니다.

> 역시 또 오류;;;

Mssql에는 dual이라는 테이블이 없다. dual은 오라클에서 사용하는 거...

나는 단일 테이블 사용이기 때문에 dummy 테이블이 필요한데...!

SELECT 1

Mssql에서는 그냥 SELECT 1; 이라고 넘기면 바로 조회가 된다.

MERGE INTO OAUTH_TOKEN a
USING SELECT 1
ON (a.FLAG = #{flag})

MERGE INTO OAUTH_TOKEN a
USING SELECT 1 AS dual
ON (a.FLAG = #{flag})

그렇다고 이렇게 DUAL 대신 바로 SELECT 1으로 바꿔서 날린다?

혹은 SELECT 1 AS dual 이라고 alias도 달아줘서 날린다?

com.microsoft.sqlserver.jdbc.SQLServerException: 키워드 'SELECT' 근처의 구문이 잘못되었습니다.

> 바로 이런 오류 만남ㅠ 오류 그만 보고 싶네;

괄호를 안써서 그럴까?

MERGE INTO OAUTH_TOKEN a
USING (SELECT 1 AS dual)
ON (a.FLAG = #{flag})

괄호를 넣어서 하나의 테이블인 마냥,, 다시 날려보자!

com.microsoft.sqlserver.jdbc.SQLServerException: 키워드 'ON' 근처의 구문이 잘못되었습니다.

> 응아냐ㅎ_ㅎ

이게 비교할 테이블 b다 라는 걸 명시적으로 알려줘야하는건가! 생각보다 안똑똑하네; AS b 라고 alias 달아주자.

MERGE INTO OAUTH_TOKEN a
USING (SELECT 1 AS dual) AS b
ON (a.FLAG = #{flag})

(SELECT 1 AS dual) AS b

> Mysql 에서 사용하는 dummy 테이블! Oracle 의 dual 처럼 dummy 서브 쿼리 그대로 복사해서 사용

	<update id="mergeToken" parameterType="kr.co.kmac.pms.reservation.domain.ZoomOAuth">
		<![CDATA[
		MERGE INTO OAUTH_TOKEN a
		USING (SELECT 1 AS dual) AS b
		ON (a.FLAG = #{flag})
		WHEN MATCHED THEN
			UPDATE SET
				REFRESH_TOKEN = #{refresh_token},
				UDATE = GETDATE(), 
				UUSER = #{ssn}
		WHEN NOT MATCHED THEN
			INSERT (      
						AUTH
						,REFRESH_TOKEN
						,FLAG
						,UDATE
						,UUSER
					) 
			VALUES (
						#{auth}
						,#{refresh_token}
						,#{flag}
						,GETDATE()
						,#{ssn}
					);
		]]>
	</update>

com.microsoft.sqlserver.jdbc.SQLServerException: 문자열이나 이진 데이터는 잘립니다.

2023-01-31 16:55:27.420 [http-nio-8080-exec-5] ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [/_test] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: 
### Error updating database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 문자열이나 이진 데이터는 잘립니다.
### The error may exist in file [D:\workspace-spring-tool-suite-4-4.9.0.RELEASE\_test\target\classes\mybatis-mapper\reservation\ZoomOAuthMapper.xml]
### The error may involve reservation.mapper.ZoomOAuthMapper.mergeToken-Inline
### The error occurred while setting parameters
### SQL: MERGE INTO OAUTH_TOKEN a   USING (SELECT 1 AS dual) AS b   ON (a.FLAG = ?)   WHEN MATCHED THEN    UPDATE SET     REFRESH_TOKEN = ?,     UDATE = GETDATE(),      UUSER = ?   WHEN NOT MATCHED THEN    INSERT (             AUTH       ,REFRESH_TOKEN       ,FLAG       ,UDATE       ,UUSER      )     VALUES (       ?       ,?       ,?       ,GETDATE()       ,?      );
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 문자열이나 이진 데이터는 잘립니다.
; 문자열이나 이진 데이터는 잘립니다.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 문자열이나 이진 데이터는 잘립니다.] with root cause
com.microsoft.sqlserver.jdbc.SQLServerException: 문자열이나 이진 데이터는 잘립니다.

> 엥 왜 또 오류ㅠ 오류만 몇 번이야 진짜 삽질 제대로네... 그래도 오류 문구가 바뀌었다.

문자열이나 이진 데이터는 잘립니다???

> 내가 넣으려던 REFRESH_TOKEN은 무려 640자였다...! 글자가 잘려서 알려주는 오류였음.

> 그래서 테이블의 컬럼 크기를 넉넉하게 바꿔줌.

테이블 변경 후 똑같은 요청을 다시 날려보자.

	<update id="mergeToken" parameterType="kr.co.kmac.pms.reservation.domain.ZoomOAuth">
		<![CDATA[
		MERGE INTO OAUTH_TOKEN a
		USING (SELECT 1 AS dual) AS b
		ON (a.FLAG = #{flag})
		WHEN MATCHED THEN
			UPDATE SET
				REFRESH_TOKEN = #{refresh_token},
				UDATE = GETDATE(), 
				UUSER = #{ssn}
		WHEN NOT MATCHED THEN
			INSERT (      
						AUTH
						,REFRESH_TOKEN
						,FLAG
						,UDATE
						,UUSER
					) 
			VALUES (
						#{auth}
						,#{refresh_token}
						,#{flag}
						,GETDATE()
						,#{ssn}
					);
		]]>
	</update>

MERGE INTO OAUTH_TOKEN a
USING (SELECT 1 AS dual) AS b
ON (a.FLAG = 'Z')
WHEN MATCHED THEN 
	UPDATE SET
    	REFRESH_TOKEN = {640자글자}, 
		UDATE = GETDATE(),
        UUSER = 'A000106'
WHEN NOT MATCHED THEN
	INSERT ( AUTH ,REFRESH_TOKEN ,FLAG ,UDATE ,UUSER )
    VALUES ( {72자글자} ,{640자글자} ,'Z' ,GETDATE() ,'A000106' );

>>> 오류 없이 성공! 실제로 데이터베이스로 날려진 최종 쿼리문은 이렇게 날아감 {글자}는 너무 길어서 생략한 거!

데이터 확인해보면 잘 들어간 걸 확인할 수 있다. (*참고로 사용하는 툴은 DBeaver)

처음 쿼리문 날릴 땐 INSERT 두 번째로 날릴 땐, 이미 있는 데이터기 때문에 UPDATE 를 진행한다!

오늘의 삽질기 끝!!!!!

728x90

'Dev.Program > USELESS:<' 카테고리의 다른 글

[SQLServer] java.security 파일 수정 (0)	2023.03.10
[개발서버] localhost url 하드코딩 (0)	2023.02.21
[MyBatis] #{}와 ${} 차이? (1)	2023.01.26
org.apache.ibatis.binding.BindingException (0)	2023.01.18

두 유라 이크 개발

두 유라 이크 개발

태그

최근글

댓글

공지사항

아카이브

'Dev.Program > USELESS:<' 카테고리의 다른 글

관련글

티스토리툴바