30초 만

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

1. java로 개발 진행할떄 BEAN이나 Insert, update . insert 문구 만들때 쉽게 만들어주는 쿼리문

/* ibatis 자동 코드 생성 */ SELECT a.COLUMN_NAME||decode(rownum, (COUNT(*) OVER()),' ', ', ') AS 필드, /* */ '/**' || NVL(b.COMMENTS, '')||'*/ ' || 'private String ' || lower(SUBSTR(a.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(a.COLUMN_NAME)), '_',''), 2) || ' = "";' as 자바Bean, upper(A.COLUMN_NAME) || ' as ' ||lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2) as 조회쿼리 , /* */ ' ' AS 인서트 , CASE WHEN A.NULLABLE ='N' THEN decode(rownum, 1, '',',') || a.COLUMN_NAME WHEN A.DATA_DEFAULT IS null THEN '<isNotEmpty prepend="," property="'||lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2)||'">'|| a.COLUMN_NAME || '</isNotEmpty>' ELSE ' ' END AS 인서트_위 , CASE WHEN A.NULLABLE ='N' THEN decode(rownum, 1, '',',') || '#' ||lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2)|| '#' WHEN A.DATA_DEFAULT IS null THEN '<isNotEmpty prepend="," property="'||lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2)||'">'|| '#'||lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2) || '#' || '</isNotEmpty>' ELSE ' ' END AS 인서트_아래 , ' ' AS 업데이트 , CASE WHEN A.NULLABLE ='N' THEN decode(rownum, 1, '',',') ||A.COLUMN_NAME || '=#'||lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2) || '#' WHEN A.DATA_DEFAULT IS null THEN '<isNotEmpty prepend="," property="'||lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2)||'">'||A.COLUMN_NAME || '=#'||lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2) || '#' ||' /* ' || NVL(B.COMMENTS, '') || ' */'||'</isNotEmpty>' ELSE ' ' END AS 업데이트SET , ' ' AS XML, '<result property="'||lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2) || '" column="' ||upper(A.COLUMN_NAME) ||'" columnIndex="'||rownum||'"/>' AS RESULT_SEQ, ' ' AS 공백, CASE WHEN A.DATA_TYPE ='NUMBER' AND A.DATA_PRECISION < 10 then '/**' || NVL(b.COMMENTS, '')||'*/ ' || 'private int ' || lower(SUBSTR(a.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(a.COLUMN_NAME)), '_',''), 2) || ' = "";' when A.DATA_TYPE ='NUMBER' AND A.DATA_PRECISION < 19 then '/**' || NVL(b.COMMENTS, '')||'*/ ' || 'private long ' || lower(SUBSTR(a.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(a.COLUMN_NAME)), '_',''), 2) || ' = "";' ELSE '/**' || NVL(b.COMMENTS, '')||'*/ ' || 'private String ' || lower(SUBSTR(a.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(a.COLUMN_NAME)), '_',''), 2) || ' = "";' END as 자바Bean, 'A.' || upper(A.COLUMN_NAME) || ' as ' ||lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2)||decode(rownum, (COUNT(*) OVER()),' ', ', ') as 조회쿼리1, ' ' AS 업데이트 , A.COLUMN_NAME || '=#'||lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2) || '#' ||' /* ' || NVL(B.COMMENTS, '') || ' */' AS 업데이트SET FROM (SELECT * FROM USER_TAB_COLS where TABLE_NAME=upper('테이블명') order by COLUMN_ID ) a , USER_COL_COMMENTS b WHERE a.TABLE_NAME = b.TABLE_NAME and a.COLUMN_NAME = b.COLUMN_NAME AND a.TABLE_NAME=upper('테이블명') order by A.COLUMN_ID ;

복사 해서 이용하면 된다.