[MY-SQL] JAVA 개발시 자동 쿼리문

1. JAVA + ibatis 개발시 자동으로 쿼리문을 만들어준다. SELECT CONCAT(COLUMN_NAME,',' )as '컬럼', CONCAT( lower(substr( initcap(replace(COLUMN_NAME,'_',' ')),1,1)), replace(substr( initcap(replace(COLUMN_NAME,'_',' ')),2),' ' ,'') ,',') as '변환 컬럼1 ', CONCAT(COLUMN_NAME,' as ', lower(substr( initcap(replace(COLUMN_NAME,'_',' ')),1,1)), replace(substr( initcap(replace(COLUMN_NAME,'_',' ')),2),' ' ,'') ,',') as '변환 컬럼2 ', CONCAT('/**',COLUMN_COMMENT,' */private String ', lower(substr( initcap(replace(COLUMN_NAME,'_',' ')),1,1)), replace(substr( initcap(replace(COLUMN_NAME,'_',' ')),2),' ' ,'') ,' = "";') as 'JAVA 이용 ' , CONCAT('#', lower(substr( initcap(replace(COLUMN_NAME,'_',' ')),1,1)), replace(substr( initcap(replace(COLUMN_NAME,'_',' ')),2),' ' ,'') ,'#,') as 'insert 이용 ' , CONCAT(COLUMN_NAME,'=#',lower(substr( initcap(replace(COLUMN_NAME,'_',' ')),1,1)), replace(substr( initcap(replace(COLUMN_NAME,'_',' ')),2),' ' ,''), '#') as 'UPDATE 이용 ' , CONCAT('<isNotEmpty prepend="," property="', lower(substr( initcap(replace(COLUMN_NAME,'_',' ')),1,1)), replace(substr( initcap(replace(COLUMN_NAME,'_',' ')),2),' ' ,'') ,'">' ,COLUMN_NAME, '</isNotEmpty>') as 'INSERT 이용 ', CONCAT('<isNotEmpty prepend="," property="', lower(substr( initcap(replace(COLUMN_NAME,'_',' ')),1,1)), replace(substr( initcap(replace(COLUMN_NAME,'_',' ')),2),' ' ,'') ,'">' ,COLUMN_NAME,'=#',lower(substr( initcap(replace(COLUMN_NAME,'_',' ')),1,1)), replace(substr( initcap(replace(COLUMN_NAME,'_',' ')),2),' ' ,''), '# </isNotEmpty>') as 'INSERT UPDATE 이용 ' , DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_NAME = '테이블명' ORDER BY ORDINAL_POSITION;

복사해서 이용하면 된다.

2.위 커리문을 실행하기 위해 필요한 함수 CREATE FUNCTION `smartetoos`.`initcap`(x char(30)) RETURNS char(30) CHARSET utf8 BEGIN SET @str=''; SET @l_str=''; WHILE x REGEXP ' ' DO SELECT SUBSTRING_INDEX(x, ' ', 1) INTO @l_str; SELECT SUBSTRING(x, LOCATE(' ', x)+1) INTO x; SELECT CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(@l_str,1,1)),LOWER(SUBSTRING(@l_str,2)))) INTO @str; END WHILE; RETURN LTRIM(CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(x,1,1)),LOWER(SUBSTRING(x,2))))); END