울면서개발하기테이블 정보로 MyBatis CRUD SQL 자동 생성하기 (Oracle 딕셔너리 활용)
화면 하나를 만들 때마다 테이블의 SELECT·INSERT·UPDATE·DELETE 를 손으로 치는 일은 지루하고 오타가 나기 쉽다. 컬럼·PK·코멘트는 이미 데이터 딕셔너리에 있으니, SQL 로 MyBatis 매퍼용 문장을 뽑아 쓰면 된다.
결과 미리 보기
예시 테이블 ORDER_ITEM(PK: ORDER_NO, ITEM_SEQ)에 대해 아래 네 문장이 만들어진다.
SELECT A.ORDER_NO AS ORDER_NO /* 주문번호 */
, A.ITEM_SEQ AS ITEM_SEQ /* 품목순번 */
, A.PRODUCT_CD AS PRODUCT_CD /* 상품코드 */
, A.QTY AS QTY /* 수량 */
, A.UNIT_PRICE AS UNIT_PRICE /* 단가 */
, A.NOTE AS NOTE /* 비고 */
, A.CREATED_BY AS CREATED_BY /* 등록자 */
, A.CREATED_IP AS CREATED_IP /* 등록IP */
, A.CREATED_AT AS CREATED_AT /* 등록일시 */
, A.CREATED_PGM AS CREATED_PGM /* 등록프로그램 */
, A.UPDATED_BY AS UPDATED_BY /* 수정자 */
, A.UPDATED_IP AS UPDATED_IP /* 수정IP */
, A.UPDATED_AT AS UPDATED_AT /* 수정일시 */
, A.UPDATED_PGM AS UPDATED_PGM /* 수정프로그램 */
FROM ORDER_ITEM A
WHERE A.ORDER_NO = #{ORDER_NO}
AND A.ITEM_SEQ = #{ITEM_SEQ}
ORDER BY A.ORDER_NO
, A.ITEM_SEQINSERT INTO ORDER_ITEM (
ORDER_NO
, ITEM_SEQ
, PRODUCT_CD
, QTY
, UNIT_PRICE
, NOTE
, CREATED_BY
, CREATED_IP
, CREATED_AT
, CREATED_PGM
, UPDATED_BY
, UPDATED_IP
, UPDATED_AT
, UPDATED_PGM
) VALUES (
#{ORDER_NO, jdbcType=VARCHAR}
, #{ITEM_SEQ, jdbcType=NUMERIC}
, #{PRODUCT_CD, jdbcType=VARCHAR}
, #{QTY, jdbcType=NUMERIC}
, #{UNIT_PRICE, jdbcType=NUMERIC}
, #{NOTE, jdbcType=VARCHAR}
, #{sysUserId}
, #{sysIp}
, SYSDATE
, #{sysPgmId}
, #{sysUserId}
, #{sysIp}
, SYSDATE
, #{sysPgmId}
)UPDATE ORDER_ITEM
SET PRODUCT_CD = #{PRODUCT_CD, jdbcType=VARCHAR}
, QTY = #{QTY, jdbcType=NUMERIC}
, UNIT_PRICE = #{UNIT_PRICE, jdbcType=NUMERIC}
, NOTE = #{NOTE, jdbcType=VARCHAR}
, UPDATED_BY = #{sysUserId}
, UPDATED_IP = #{sysIp}
, UPDATED_AT = SYSDATE
, UPDATED_PGM = #{sysPgmId}
WHERE ORDER_NO = #{ORDER_NO}
AND ITEM_SEQ = #{ITEM_SEQ}DELETE
FROM ORDER_ITEM
WHERE ORDER_NO = #{ORDER_NO}
AND ITEM_SEQ = #{ITEM_SEQ}생성 SQL
마지막 줄 근처의 테이블 이름만 바꿔서 실행한다. 결과는 네 개의 CLOB 컬럼(SELECT_SQL, INSERT_SQL, UPDATE_SQL, DELETE_SQL)으로 나온다.
WITH cols AS (
SELECT c.table_name
, c.column_id
, c.column_name
, REPLACE(cm.comments, '*/', '* /') AS comments -- 주석이 깨지지 않게
, CASE WHEN pk.column_name IS NOT NULL THEN 1 ELSE 0 END AS pk_yn
, CASE WHEN c.column_name IN ('CREATED_BY', 'CREATED_IP', 'CREATED_AT', 'CREATED_PGM')
THEN 1 ELSE 0 END AS created_yn
-- INSERT·UPDATE 에 들어갈 값
, CASE c.column_name
WHEN 'CREATED_AT' THEN 'SYSDATE'
WHEN 'UPDATED_AT' THEN 'SYSDATE'
WHEN 'CREATED_BY' THEN '#{sysUserId}'
WHEN 'UPDATED_BY' THEN '#{sysUserId}'
WHEN 'CREATED_IP' THEN '#{sysIp}'
WHEN 'UPDATED_IP' THEN '#{sysIp}'
WHEN 'CREATED_PGM' THEN '#{sysPgmId}'
WHEN 'UPDATED_PGM' THEN '#{sysPgmId}'
ELSE '#{' || c.column_name || ', jdbcType='
|| CASE
WHEN c.data_type IN ('CHAR', 'NCHAR') THEN 'CHAR'
WHEN c.data_type = 'NUMBER' THEN 'NUMERIC'
WHEN c.data_type = 'DATE'
OR c.data_type LIKE 'TIMESTAMP%' THEN 'TIMESTAMP'
WHEN c.data_type IN ('CLOB', 'NCLOB') THEN 'CLOB'
WHEN c.data_type = 'BLOB' THEN 'BLOB'
ELSE 'VARCHAR'
END
|| '}'
END AS val
FROM user_tab_columns c
LEFT JOIN user_col_comments cm
ON cm.table_name = c.table_name
AND cm.column_name = c.column_name
LEFT JOIN (
SELECT cc.table_name, cc.column_name
FROM user_constraints k
JOIN user_cons_columns cc
ON cc.constraint_name = k.constraint_name
AND cc.table_name = k.table_name
WHERE k.constraint_type = 'P'
) pk
ON pk.table_name = c.table_name
AND pk.column_name = c.column_name
WHERE c.table_name = 'ORDER_ITEM' -- 대상 테이블
)
, w AS (
SELECT cols.*
, MAX(LENGTH(column_name)) OVER () AS w_all -- SELECT 정렬 폭
, MAX(CASE WHEN pk_yn = 1 THEN LENGTH(column_name) END) OVER () AS w_pk -- WHERE 정렬 폭
, MAX(CASE WHEN pk_yn = 0 AND created_yn = 0
THEN LENGTH(column_name) END) OVER () AS w_set -- SET 정렬 폭
FROM cols
)
SELECT /* SELECT */
TO_CLOB('SELECT ')
|| LISTAGG('A.' || RPAD(column_name, w_all) || ' AS ' || RPAD(column_name, w_all)
|| CASE WHEN comments IS NOT NULL THEN ' /* ' || comments || ' */' END
, CHR(10) || ' , ') WITHIN GROUP (ORDER BY column_id)
|| CHR(10) || ' FROM ' || MAX(table_name) || ' A'
|| CHR(10) || ' WHERE '
|| LISTAGG(CASE WHEN pk_yn = 1
THEN 'A.' || RPAD(column_name, w_pk) || ' = #{' || column_name || '}' END
, CHR(10) || ' AND ') WITHIN GROUP (ORDER BY column_id)
|| CHR(10) || ' ORDER BY '
|| LISTAGG(CASE WHEN pk_yn = 1 THEN 'A.' || column_name END
, CHR(10) || ' , ') WITHIN GROUP (ORDER BY column_id)
AS select_sql
/* INSERT */
, TO_CLOB('INSERT INTO ' || MAX(table_name) || ' (')
|| CHR(10) || ' '
|| LISTAGG(column_name, CHR(10) || ' , ') WITHIN GROUP (ORDER BY column_id)
|| CHR(10) || ') VALUES (' || CHR(10) || ' '
|| LISTAGG(val, CHR(10) || ' , ') WITHIN GROUP (ORDER BY column_id)
|| CHR(10) || ')'
AS insert_sql
/* UPDATE */
, TO_CLOB('UPDATE ' || MAX(table_name))
|| CHR(10) || ' SET '
|| LISTAGG(CASE WHEN pk_yn = 0 AND created_yn = 0
THEN RPAD(column_name, w_set) || ' = ' || val END
, CHR(10) || ' , ') WITHIN GROUP (ORDER BY column_id)
|| CHR(10) || ' WHERE '
|| LISTAGG(CASE WHEN pk_yn = 1
THEN RPAD(column_name, w_pk) || ' = #{' || column_name || '}' END
, CHR(10) || ' AND ') WITHIN GROUP (ORDER BY column_id)
AS update_sql
/* DELETE */
, TO_CLOB('DELETE')
|| CHR(10) || ' FROM ' || MAX(table_name)
|| CHR(10) || ' WHERE '
|| LISTAGG(CASE WHEN pk_yn = 1
THEN RPAD(column_name, w_pk) || ' = #{' || column_name || '}' END
, CHR(10) || ' AND ') WITHIN GROUP (ORDER BY column_id)
AS delete_sql
FROM w;동작 요점
LISTAGG는NULL을 건너뛴다. 그래서CASE WHEN pk_yn = 1 THEN ... END처럼 조건에 맞는 컬럼만 골라 이어 붙일 수 있다.- 맨 앞을
TO_CLOB(...)로 시작해 이어 붙인 결과 전체를 CLOB 으로 만든다. VARCHAR2 끼리 이어 붙이다 4000바이트(확장 설정 시 32767바이트)를 넘으면ORA-01489가 나기 때문이다. - 다만
LISTAGG하나의 결과는 여전히 VARCHAR2 한도 안이어야 한다. 컬럼이 수백 개인 테이블이라 한 덩어리가 4000바이트를 넘으면, 코멘트를 빼거나XMLAGG로 CLOB 집계를 해야 한다. - 감사(등록·수정) 컬럼 이름과 파라미터 이름(
sysUserId,sysIp,sysPgmId)은 프로젝트 규칙에 맞게 바꾼다. 이 컬럼들은 화면 값이 아니라 공통 처리(인터셉터 등)에서 채워 주는 값이라고 가정했다. UPDATE 의SET에서는 등록 컬럼(CREATED_*)과 PK 를 뺀다. - 다른 스키마의 테이블이라면
USER_*대신ALL_*뷰를 쓰고, 조인 조건과WHERE에owner를 함께 건다.
jdbcType 을 붙이는 이유
Oracle 드라이버는 타입을 모르는 NULL 바인드를 받으면 부적합한 열 유형: 1111(Invalid column type: 1111) 오류를 낸다. MyBatis 가 NULL 값의 타입을 OTHER(1111)로 넘기기 때문이다. 비어 있을 수 있는 값에 jdbcType 을 붙여 두면 이 오류가 나지 않는다. 설정에서 jdbcTypeForNull 을 NULL 로 지정하는 방법도 있다.
Oracle DATE 는 시분초까지 담으므로 jdbcType=DATE(날짜만) 대신 TIMESTAMP 로 넘기게 했다. 날짜만 다루는 컬럼이라면 바꿔도 된다.
주의할 점
- PK 가 없는 테이블은
WHERE뒤가 비어서 UPDATE·DELETE 문이 문법 오류가 난다(전체 행이 바뀌는 문장이 만들어지지는 않는다). 이런 테이블은 조건을 직접 정한다. - 파라미터 이름은 컬럼 이름 그대로(
#{ORDER_NO})다. 파라미터를Map으로 넘기면 그대로 쓰면 되고, DTO 의 camelCase 필드로 넘긴다면#{orderNo}처럼 바꿔야 한다. - 만들어진 SQL 은 시작점일 뿐이다. 조회 조건·정렬·페이징은 화면에 맞게 손본다.
다시 쓰며 바로잡은 점
- 원래 SQL 은 SELECT 의 정렬 폭을 감사 컬럼을 뺀 최대 길이로 잡고 모든 컬럼에
RPAD를 걸었다.RPAD는 지정한 길이보다 긴 문자열을 잘라 버리므로, 감사 컬럼 이름이 다른 컬럼보다 길면A.UPDT_PRO처럼 컬럼 이름이 잘린 SQL 이 만들어졌다. 정렬 폭을 용도별(전체·PK·SET)로 따로 계산하도록 고쳤다. XMLAGG(...).EXTRACT('//text()')로 문자열을 모으던 부분을LISTAGG로 바꿨다.EXTRACT는 사용이 권장되지 않는(deprecated) 함수이고, 이 방식은 코멘트에&,<가 있으면&,<로 바뀌어 나온다.- 지원이 끝난
/*+ RULE */힌트(규칙 기반 옵티마이저는 10g 부터 지원되지 않는다)와 인라인 뷰 안의 의미 없는ORDER BY를 뺐다. - 줄바꿈을
CHR(13)(CR)만 쓰던 것을CHR(10)(LF)으로 바꿨다. CR 만 있으면 편집기에 따라 줄이 바뀌지 않는다. NULL이 들어갈 수 있는 값에jdbcType을 붙였다.- 주석 안에
*/가 든 코멘트가 있으면 생성된 SQL 이 깨지던 것을 막았다.