Table 의 컬럼 들을 VO 타입으로 변환하는 법

개요

회사에서 맡은 업무가 기존의 있는 DB를 가지고 구식화 된 시스템을 spring MVC 기반으로 새롭게 관리 시스템을 만드는 것이다

그래서 테이블들을 VO로 만들어 주고 그걸 가지고 mybatis 를 이용해 데이터를 받아온뒤 컨트롤러를 통해 html 파일에 데이터들을 뿌려준다.

이때, 컬럼들이 4~50개 쯤 되는 테이블을 쓸 때 이걸 하나하나 private String .....  이래야 한다고? 난 비효율적인 짓은 ㅇ\절대 안한다.

그래서 블로그를 뒤져봤다.

분명히 DB기능 중에 이런 기능이 있을거야라는 생각을 품고서....

 

본문

 

*컬럼명이 모두 소문자로 나오게 한 경우

SELECT   CONCAT(
                    '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 ' String '
                        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))))
                    ), 
                    ';',
                    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,
              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 = 'tblmember'
AND A.TABLE_SCHEMA = 'uspot_test'
ORDER BY B.ORDINAL_POSITION
) A;

 

코드를 내가 아는 선에서 해석하자면 DB의 프로시저라는 기능을 이용하여 내가 원하는 문장을 조합하여서 표출 해주는 기능같다.(필자는 프로시저를 아직 공부한적없으니 틀린부분이 있다면 배운다는 자세로 지적 받겠다.감사합니다.)

private 라는 것과 int 일때는 똑같이 int datatime 이나 char 이나 varchar 같은 경우는 String 을 그 후에는 컬럼명을 달아줘서 출력해주는 기능으로 보인다.

엄청 유용한 기능이니 꼭 공부해야겠다.

코드를 분석하면서 개선하는 사항으로는 컬럼명이 소문자로 나오게 하신것 같은데 때에 따라 대소문자 구별이 필요하므로

그런 경우를 대비하여 개선해보았다.

 

 

*컬럼명이 대소문자 구별해서 나오게 한 경우

SELECT   CONCAT(
                    '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 ' String '
                        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))))
                    ), 
                    ';',
                    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(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(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(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(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(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 = 'tblmember'
AND A.TABLE_SCHEMA = 'uspot_test'
ORDER BY B.ORDINAL_POSITION
) A;

 

마치며

일단 내가 해본건 maria DB, Mysql , Mssql 정도였다 oracle 같은 경우는 안해봤지만 아마 간단한 부분만 수정하면 잘 작동할것 같다. 안되면 뭐 다른거 쓰지뭐,,,

 

이런 정보를 알려준 블로그 글을 첨부하며 글을 마치겠다. 감사합니다(혹시 문제가 될 시 글 삭제하겠습니다.)

 

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