DB 테이블에서 객체 필드 뽑아내기

DB 테이블을 이용해서 Entity 객체를 생성할때, 눈으로 하나하나 보면서 만들면 시간이 오래걸리고, 오타가 생길수도 있습니다.

SQL을 이용해서 쉽게 field를 도출할 수 있습니다.

SELECT   GROUP_CONCAT(CONCAT(
                    '/* ',
                    CASE WHEN LENGTH(COLUMN_COMMENT) > 0 THEN 
                        COLUMN_COMMENT 
                    ELSE 
                        COLUMN_NAME 
                    END,
                    ' */',
                    CHAR(13),
                    CONCAT(
                        TRIM(CN1), 
                        TRIM(CONCAT(UPPER(SUBSTRING(CN2, 1, 1)) , SUBSTRING(CN2, 2, LENGTH(CN2)))), 
                        TRIM(CONCAT(UPPER(SUBSTRING(CN3, 1, 1)) , SUBSTRING(CN3, 2, LENGTH(CN3)))),
                        TRIM(CONCAT(UPPER(SUBSTRING(CN4, 1, 1)) , SUBSTRING(CN4, 2, LENGTH(CN4)))),
                        TRIM(CONCAT(UPPER(SUBSTRING(CN5, 1, 1)) , SUBSTRING(CN5, 2, LENGTH(CN5))))
                    ), 
                    CASE 
                        WHEN LOWER(DATA_TYPE) = 'varchar' THEN ' :string'
                        WHEN INSTR(LOWER(DATA_TYPE), 'int') > 0 THEN  ' :number ' 
                        WHEN LOWER(DATA_TYPE) = 'text' THEN ' :string ' 
                        WHEN LOWER(DATA_TYPE) = 'datetime' THEN ' :date '
                        ELSE ' :object '
                    END, 
                    ';',
                    CHAR(13)
                ) SEPARATOR '') as field
FROM
(
SELECT   B.COLUMN_NAME,  
                CASE WHEN LENGTH(B.COLUMN_NAME) - LENGTH(REPLACE(B.COLUMN_NAME, '_', '')) + 1 >= 1 THEN 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(LCASE(B.COLUMN_NAME), '_', 1), '_', -1) 
                ELSE ' ' END AS CN1,
                CASE WHEN LENGTH(B.COLUMN_NAME) - LENGTH(REPLACE(B.COLUMN_NAME, '_', '')) + 1 >= 2 THEN 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(LCASE(B.COLUMN_NAME), '_', 2), '_', -1) 
                ELSE ' ' END AS CN2,
                CASE WHEN LENGTH(B.COLUMN_NAME) - LENGTH(REPLACE(B.COLUMN_NAME, '_', '')) + 1 >= 3 THEN 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(LCASE(B.COLUMN_NAME), '_', 3), '_', -1) 
                ELSE ' ' END AS CN3,
                CASE WHEN LENGTH(B.COLUMN_NAME) - LENGTH(REPLACE(B.COLUMN_NAME, '_', '')) + 1 >= 4 THEN 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(LCASE(B.COLUMN_NAME), '_', 4), '_', -1) 
                ELSE ' ' END AS CN4,
                CASE WHEN LENGTH(B.COLUMN_NAME) - LENGTH(REPLACE(B.COLUMN_NAME, '_', '')) + 1 >= 5 THEN 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(LCASE(B.COLUMN_NAME), '_', 5), '_', -1) 
                ELSE ' ' END AS CN5,
              B.DATA_TYPE, 
              B.COLUMN_COMMENT
FROM information_schema.TABLES A
INNER JOIN information_schema.COLUMNS B ON (A.TABLE_NAME = B.TABLE_NAME) 
WHERE 	A.TABLE_NAME = '[테이블]'
AND A.TABLE_SCHEMA = '[데이터베이스]'
GROUP BY B.ORDINAL_POSITION
ORDER BY B.ORDINAL_POSITION
) A;

결과 예제

// 공통 코드 테이블 결과

/* id */
id :number ;
/* 등록자id */
regnId :string;
/* 수정자id */
modnId :string;
/* 공통그룹코드 */
cmmGrpCd :string;
/* 공통코드명 */
cmmCdNm :string;
/* 공통코드값 */
cmmCdVal :string;
/* 등록일시 */
regDtm :string;
/* 수정일시 */
modDtm :string;
/* 상위공통그룹코드 */
uprCmmGrpCd :string;
/* 순서 */
seq :number ;

모든 테이블에 대한 필드 뽑기

TABLE 명을 넣지 않고, DB 아래의 모든 테이블과 해당 필드를 표기하도록 약간만 수정한 버젼입니다.

SELECT   TABLE_NAME, GROUP_CONCAT(CONCAT(
                    '/* ',
                    CASE WHEN LENGTH(COLUMN_COMMENT) > 0 THEN 
                        COLUMN_COMMENT 
                    ELSE 
                        COLUMN_NAME 
                    END,
                    ' */',
                    CHAR(13),
                    '@Column()',
                    CHAR(13),
                    CONCAT(
                        TRIM(CN1), 
                        TRIM(CONCAT(UPPER(SUBSTRING(CN2, 1, 1)) , SUBSTRING(CN2, 2, LENGTH(CN2)))), 
                        TRIM(CONCAT(UPPER(SUBSTRING(CN3, 1, 1)) , SUBSTRING(CN3, 2, LENGTH(CN3)))),
                        TRIM(CONCAT(UPPER(SUBSTRING(CN4, 1, 1)) , SUBSTRING(CN4, 2, LENGTH(CN4)))),
                        TRIM(CONCAT(UPPER(SUBSTRING(CN5, 1, 1)) , SUBSTRING(CN5, 2, LENGTH(CN5))))
                    ), 
                    CASE 
                        WHEN LOWER(DATA_TYPE) = 'varchar' THEN ' :string'
                        WHEN INSTR(LOWER(DATA_TYPE), 'int') > 0 THEN  ' :number ' 
                        WHEN LOWER(DATA_TYPE) = 'text' THEN ' :string ' 
                        WHEN LOWER(DATA_TYPE) = 'datetime' THEN ' :date '
                        ELSE ' :object '
                    END, 
                    ';',
                    CHAR(13),
                    CHAR(13)
                ) SEPARATOR '') as field
FROM
(
SELECT  A.TABLE_NAME, B.ORDINAL_POSITION, B.COLUMN_NAME,  
                CASE WHEN LENGTH(B.COLUMN_NAME) - LENGTH(REPLACE(B.COLUMN_NAME, '_', '')) + 1 >= 1 THEN 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(LCASE(B.COLUMN_NAME), '_', 1), '_', -1) 
                ELSE ' ' END AS CN1,
                CASE WHEN LENGTH(B.COLUMN_NAME) - LENGTH(REPLACE(B.COLUMN_NAME, '_', '')) + 1 >= 2 THEN 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(LCASE(B.COLUMN_NAME), '_', 2), '_', -1) 
                ELSE ' ' END AS CN2,
                CASE WHEN LENGTH(B.COLUMN_NAME) - LENGTH(REPLACE(B.COLUMN_NAME, '_', '')) + 1 >= 3 THEN 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(LCASE(B.COLUMN_NAME), '_', 3), '_', -1) 
                ELSE ' ' END AS CN3,
                CASE WHEN LENGTH(B.COLUMN_NAME) - LENGTH(REPLACE(B.COLUMN_NAME, '_', '')) + 1 >= 4 THEN 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(LCASE(B.COLUMN_NAME), '_', 4), '_', -1) 
                ELSE ' ' END AS CN4,
                CASE WHEN LENGTH(B.COLUMN_NAME) - LENGTH(REPLACE(B.COLUMN_NAME, '_', '')) + 1 >= 5 THEN 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(LCASE(B.COLUMN_NAME), '_', 5), '_', -1) 
                ELSE ' ' END AS CN5,
              B.DATA_TYPE, 
              B.COLUMN_COMMENT
FROM information_schema.TABLES A
INNER JOIN information_schema.COLUMNS B ON (A.TABLE_NAME = B.TABLE_NAME) 
WHERE  A.TABLE_SCHEMA = 'bemypet'
) A
GROUP BY TABLE_NAME
ORDER BY ORDINAL_POSITION
;

참고자료

• MYSQL테이블을 VO로 만들기 위한 쿼리