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
;