Mysql DTO,entity,VO 자동 생성하기

SELECT   CONCAT(
                    '/* ',
                    CASE WHEN LENGTH(COLUMN_COMMENT) > 0 THEN 
                        COLUMN_COMMENT 
                    ELSE 
                        COLUMN_NAME 
                    END,
                    ' */ ',
                    CHAR(13),
						  '@Column',
                    CHAR(13),
							CASE WHEN IS_NULLABLE = 'NO' 
								AND LOWER(DATA_TYPE) != 'datetime'  
								AND LOWER(DATA_TYPE) != 'date'
							THEN '@NotNull' ELSE '' END,
							CASE WHEN IS_NULLABLE = 'NO'
								AND LOWER(DATA_TYPE) != 'datetime'  
								AND LOWER(DATA_TYPE) != 'date'
							THEN CHAR(13) ELSE '' END,
                    'private',
                    CASE 
                        WHEN LOWER(DATA_TYPE) = 'varchar' THEN ' String '
                        WHEN INSTR(LOWER(DATA_TYPE), 'int') > 0 THEN  ' int ' 
                        WHEN LOWER(DATA_TYPE) = 'text' THEN ' String ' 
                        WHEN LOWER(DATA_TYPE) = 'datetime' THEN ' Timestamp '
                        WHEN LOWER(DATA_TYPE) = 'date' THEN ' String '
                        WHEN LOWER(DATA_TYPE) = 'float' THEN ' double '
                        WHEN LOWER(DATA_TYPE) = 'double' THEN ' double '
                        WHEN LOWER(DATA_TYPE) = 'decimal' THEN ' double '
                        ELSE ' String '
                    END, 
                    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)))),
                        TRIM(CONCAT(UPPER(SUBSTRING(CN6, 1, 1)) , SUBSTRING(CN6, 2, LENGTH(CN6)))),
                        TRIM(CONCAT(UPPER(SUBSTRING(CN7, 1, 1)) , SUBSTRING(CN7, 2, LENGTH(CN7)))),
                        TRIM(CONCAT(UPPER(SUBSTRING(CN8, 1, 1)) , SUBSTRING(CN8, 2, LENGTH(CN8)))),
                        TRIM(CONCAT(UPPER(SUBSTRING(CN9, 1, 1)) , SUBSTRING(CN9, 2, LENGTH(CN9)))),
                        TRIM(CONCAT(UPPER(SUBSTRING(CN10, 1, 1)) , SUBSTRING(CN10, 2, LENGTH(CN10))))
                    ), 
                    ';',
                    CHAR(13),
                                        CHAR(13)
                )
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,
                CASE WHEN LENGTH(B.COLUMN_NAME) - LENGTH(REPLACE(B.COLUMN_NAME, '_', '')) + 1 >= 6 THEN 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(LCASE(B.COLUMN_NAME), '_', 6), '_', -1) 
                ELSE ' ' END AS CN6,
                CASE WHEN LENGTH(B.COLUMN_NAME) - LENGTH(REPLACE(B.COLUMN_NAME, '_', '')) + 1 >= 7 THEN 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(LCASE(B.COLUMN_NAME), '_', 7), '_', -1) 
                ELSE ' ' END AS CN7,
                CASE WHEN LENGTH(B.COLUMN_NAME) - LENGTH(REPLACE(B.COLUMN_NAME, '_', '')) + 1 >= 8 THEN 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(LCASE(B.COLUMN_NAME), '_', 8), '_', -1) 
                ELSE ' ' END AS CN8,
                CASE WHEN LENGTH(B.COLUMN_NAME) - LENGTH(REPLACE(B.COLUMN_NAME, '_', '')) + 1 >= 9 THEN 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(LCASE(B.COLUMN_NAME), '_', 9), '_', -1) 
                ELSE ' ' END AS CN9,
                CASE WHEN LENGTH(B.COLUMN_NAME) - LENGTH(REPLACE(B.COLUMN_NAME, '_', '')) + 1 >= 10 THEN 
                    SUBSTRING_INDEX(SUBSTRING_INDEX(LCASE(B.COLUMN_NAME), '_', 10), '_', -1) 
                ELSE ' ' END AS CN10,
              B.DATA_TYPE, 
              B.COLUMN_COMMENT,
              B.IS_NULLABLE
FROM information_schema.TABLES A
INNER JOIN information_schema.COLUMNS B ON (A.TABLE_NAME = B.TABLE_NAME) 
WHERE 	A.TABLE_NAME = '{TABLE}'
AND A.TABLE_SCHEMA = '{DB}'
ORDER BY B.ORDINAL_POSITION
) A
;

 

 

어노테이션 처리

기본적으로 @Column이 추가되며

null허용 여부에 따라 @NotNull이 추가됩니다

 

DATA_TYPE에 따라 추가 및 원하는 형식으로 변경 가능합니다

 

해당 쿼리를 수정한 후 쿼리를 날리면

 

이 쿼리를 복사 후 붙여 넣으면

 

필드 별로 추가한 comment까지 이쁘게 작성됩니다

 

 

원본 : https://tiger5net.tistory.com/1250