테이블에 대한 JAVA 코드 조각을 생성해 주는 SQL 쿼리

소개

테이블 구조에 따라서 JAVA Beans(DTO) 클래스 정의나 MyBatis의 Mapper에 정의해야 하는 resultMap의 property, HTML의 input 태그 구문을 생성해 주는 SQL 쿼리 입니다.

구문형식

MySQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

SELECT   concat('private String ', VALUE_NAME, ';') AS beans,   concat('<result property="', VALUE_NAME, '" column="', COLUMN_NAME, '"/>') as mapper,   concat('<input type="text" name="', COLUMN_NAME, '" id="', COLUMN_NAME, '"/>') as html FROM (     SELECT         if (             instr(VALUE_NAME, '_') > 0,             replace(VALUE_NAME,                 substring(VALUE_NAME, instr(VALUE_NAME, '_'), 2),                 upper(substring(VALUE_NAME, instr(VALUE_NAME, '_')+1, 1))),             VALUE_NAME         ) AS VALUE_NAME, COLUMN_NAME     FROM (         SELECT             if (                 instr(COLUMN_NAME, '_') > 0,                 replace(COLUMN_NAME,                     substring(COLUMN_NAME, instr(COLUMN_NAME, '_'), 2),                     upper(substring(COLUMN_NAME, instr(COLUMN_NAME, '_')+1, 1))),                 COLUMN_NAME             ) AS VALUE_NAME, COLUMN_NAME         FROM           INFORMATION_SCHEMA.COLUMNS         WHERE           TABLE_SCHEMA = '데이터베이스 이름'           AND TABLE_NAME = '테이블 이름'         ORDER BY           TABLE_NAME, ORDINAL_POSITION     ) as TBL ) as TBL2;

 

사용예시

다음과 같은 SQL 구문을 실행합니다.

활용예시

MySQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

SELECT   concat('private String ', VALUE_NAME, ';') AS beans,   concat('<result property="', VALUE_NAME, '" column="', COLUMN_NAME, '"/>') as mapper,   concat('<input type="text" name="', COLUMN_NAME, '" id="', COLUMN_NAME, '"/>') as html FROM (     SELECT         if (             instr(VALUE_NAME, '_') > 0,             replace(VALUE_NAME,                 substring(VALUE_NAME, instr(VALUE_NAME, '_'), 2),                 upper(substring(VALUE_NAME, instr(VALUE_NAME, '_')+1, 1))),             VALUE_NAME         ) AS VALUE_NAME, COLUMN_NAME     FROM (         SELECT             if (                 instr(COLUMN_NAME, '_') > 0,                 replace(COLUMN_NAME,                     substring(COLUMN_NAME, instr(COLUMN_NAME, '_'), 2),                     upper(substring(COLUMN_NAME, instr(COLUMN_NAME, '_')+1, 1))),                 COLUMN_NAME             ) AS VALUE_NAME, COLUMN_NAME         FROM           INFORMATION_SCHEMA.COLUMNS         WHERE           TABLE_SCHEMA = 'myschool'           AND TABLE_NAME = 'bbs_document'         ORDER BY           TABLE_NAME, ORDINAL_POSITION     ) as TBL ) as TBL2;