mysql, java 환경에서 DB 테이블 정보를 활용하여 각종 DTO 생성하기

java에서 DB 관련 작업을 할 때 유용하게 활용할 수 있는 쿼리를 공유합니다.

DB 테이블의 컬럼을 하나씩 타이핑 하는 것이 아니라 DB의 시스템 테이블 정보를 통해 여러 유형의 DTO를 빠르고 정확하게 생성합니다.

프로젝트를 수행하다보면 backend에서 아래와 같은 형태의 파일을 작성해서 DB 또는 FE와 데이터를 주고 받게 됩니다.

  @Getter
  public static class Request {

    @Schema(description = "")
    private String code;
    @Schema(description = "")
    private String name;
    ...
  }

간단한 내용은 타이핑하면 되는데 테이블에 컬럼이 많거나 화면의 항목이 많은 경우 객체를 만드는 것도 일이 됩니다.

이 작업을 간단하게 만들기 위해서 우리는 DB 테이블 정보를 활용해서 만드는 방법을 살펴보겠습니다.

핵심은 2가지 입니다.

컬럼명에 _(언더바)를 지우고 다음 단어를 대문자로 변환하는 작업
테이블 컬럼명은 주로 snake case로 작성
Backend 소스에서는 주로 camel case로 작성
snake case => camel case 작업을 하면 됩니다.
Data type을 자바 자료형으로 변환하는 작업

이 두가지를 쿼리로 어떻게 수행하는지 확인해보겠습니다.

컬럼명에 _(언더바)를 지우고 다음 단어를 대문자로 변환하는 작업

우선 _(언더바)를 기준으로 컬럼명을 나눕니다. 컬럼명이 얼마나 길지 알 수 없지만 10개 정도를 할당하겠습니다. 이런 정보를 갖고 오기위해서 INFORMATION_SCHEMA 시스템 테이블을 활용하겠습니다.

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,
B.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.TABLES A
INNER JOIN INFORMATION_SCHEMA.COLUMNS B ON (A.TABLE_NAME = B.TABLE_NAME)
WHERE   1=1
AND     A.TABLE_NAME = 'XXXX_TABLE'
AND     A.TABLE_SCHEMA = 'XXXX_TABLE_SCHEMA'
ORDER BY B.ORDINAL_POSITION;

위 쿼리를 통해서 _(언더바)를 기준으로 10번째에 해당하는 값 까지를 가져옵니다. CN1, CN2, ... CN10

그리고 이 쿼리를 FROM으로 두고 이어 붙이는 작업을 해주면 됩니다.

SELECT
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))))
) AS 'COLUMN_NAME'
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,
             B.CHARACTER_MAXIMUM_LENGTH
      FROM INFORMATION_SCHEMA.TABLES A
               INNER JOIN INFORMATION_SCHEMA.COLUMNS B ON (A.TABLE_NAME = B.TABLE_NAME)
      WHERE 1 = 1
        AND A.TABLE_NAME = 'XXXX_TABLE'
        AND A.TABLE_SCHEMA = 'XXXX_TABLE_SCHEMA'
      ORDER BY B.ORDINAL_POSITION) A

핵심만 다시 정리하자면 2개의 쿼리 구간일 것 같습니다.

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))))
) AS '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,
...

실행하면 아래와 같이 camelcase로 컬럼명을 얻을 수 있습니다.

Data type을 자바 자료형으로 변환하는 작업

사실 이 부분은 이미 알고 계시겠죠. 정말 간단합니다. INFORMATION_SCHEMA.COLUMNS에서 가져온 DATA_TYPE을 어떻게 변환하느냐 입니다.

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,

int 말로 long으로 해야 할 수도 있지만, 방법을 알았다면 언제든지 변경해서 반영해주면 됩니다.

기타 등등

줄바꿈이 필요할 때가 있습니다.

concat('', CHAR(13)),

설명이 필요할 때가 있습니다.

concat('@Schema(description = "', COLUMN_COMMENT, '")'),

도움이 되셨으면 좋겠습니다.

저작자표시 비영리 변경금지

'기능과 기술' 카테고리의 다른 글

생산성 향상을 위한 자동화 프로그램을 제작합니다. (1)	2024.03.23
ChatGPT AIPRM 제거 방법 (3)	2024.01.31
파이썬으로 내가 만든 로직을 스케줄링하기 - schedule (0)	2023.12.17
DataGrip - mysql backup +_+ (0)	2023.05.11
WebStorm - vue 디버깅 (0)	2023.05.09

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

미국주식 자동매매

mysql, java 환경에서 DB 테이블 정보를 활용하여 각종 DTO 생성하기

컬럼명에 _(언더바)를 지우고 다음 단어를 대문자로 변환하는 작업

Data type을 자바 자료형으로 변환하는 작업

기타 등등

'기능과 기술' 카테고리의 다른 글

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

mysql, java 환경에서 DB 테이블 정보를 활용하여 각종 DTO 생성하기

컬럼명에 _(언더바)를 지우고 다음 단어를 대문자로 변환하는 작업

Data type을 자바 자료형으로 변환하는 작업

기타 등등

'기능과 기술' 카테고리의 다른 글

'기능과 기술' Related Articles

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역