postgresql 여러 테이블 수정

-- codeCommon.selectCodeTableList : 코드 테이블 목록
SELECT COUNT(*) OVER() cnt
, CONCAT(n.nspname,'.', c.relname) AS tbname
, OBJ_DESCRIPTION(c.OID) AS tbkrname
FROM pg_catalog.pg_class c
INNER JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.OID
WHERE c.relkind = 'r'
AND nspname IN ( 'sppdb', 'spsdb' )

-- codeCommon.selectTableLayout : 코드 테이블 Layout
SELECT PS.SCHEMANAME AS SCHEMA
, PS.RELNAME
, PA.ATTNAME
, CASE WHEN POSITION('(' IN PD.DESCRIPTION) > 1 THEN SUBSTRING(PD.DESCRIPTION, 1, POSITION('(' IN PD.DESCRIPTION)-1)
ELSE PD.DESCRIPTION END AS ATTKRNAME -- 한글컬럼명 괄호 전까지만 자르기
, CASE WHEN PA.ATTNUM = ANY(PC.CONKEY) THEN 'PK' ELSE '' END AS PK
, TBL.DATA_TYPE
, TBL.CHARACTER_MAXIMUM_LENGTH AS MAXLENGTH
FROM PG_STAT_USER_TABLES PS
, INFORMATION_SCHEMA.COLUMNS TBL
, PG_DESCRIPTION PD
, PG_ATTRIBUTE PA
, PG_CATALOG.PG_CONSTRAINT PC
WHERE PS.SCHEMANAME IN ('sppdb', 'spsdb')
AND TBL.TABLE_SCHEMA = PS.SCHEMANAME
AND TBL.TABLE_NAME = RELNAME
AND PA.ATTNAME = TBL.COLUMN_NAME
AND PS.RELID = PD.OBJOID
AND PD.OBJSUBID != 0
AND PD.OBJOID = PA.ATTRELID
AND PD.OBJSUBID = PA.ATTNUM
AND PS.RELID = PC.CONRELID
AND PS.RELNAME = #{relname}

-- codeCommon.mergeCodeTable : 등록/수정
WITH MERGE_CODE_TABLE AS (
UPDATE ${selCodeTable}
SET ${mergeSetSql}
WHERE ${mergeWhereSql}
RETURNING *
)
INSERT INTO ${selCodeTable} ( ${insertColSql} )
SELECT ${insertValSql}
WHERE NOT EXISTS (SELECT * FROM MERGE_CODE_TABLE);

‐------‐--------------
Service.java

/**--------------------------------------------------------------------
* ■ 코드 테이블 등록/수정 서비스 ■ljpark ■
--------------------------------------------------------------------**/
@Transactional
public void mergeCodeTable(CodeCommon codeCommon) throws GlobalException {
String mergeSetSql = "";
String mergeWhereSql = "";
String insertColSql = "";
String insertValSql = "";

List<Map<String,String>> lst = codeCommon.getLstTableLayout();
for(int j=0; j<lst.size(); j++) {
Map<String,String> map = lst.get(j);
if (map.get("attname").equals("reg_dt")) continue;

if (map.get("pk").equals("PK")) {
mergeWhereSql += ((mergeWhereSql.equals(""))? "":" and ");
if (map.get("data_type").equals("character varying") || map.get("data_type").equals("character")) {
mergeWhereSql += map.get("attname") + " = '" + codeCommon.getTableDataMap().get(map.get("attname")) + "'";
} else if (map.get("data_type").equals("smallint") || map.get("data_type").equals("integer") ) {
mergeWhereSql += map.get("attname") + " = " + codeCommon.getTableDataMap().get(map.get("attname"));
}
} else {
mergeSetSql += ((mergeSetSql.equals(""))? "":",");
if (map.get("data_type").equals("smallint") || map.get("data_type").equals("integer") || map.get("data_type").equals("boolean") ) {
mergeSetSql += map.get("attname") + " = " + codeCommon.getTableDataMap().get(map.get("attname")) + "";
} else if (map.get("data_type").equals("character varying")) {
mergeSetSql += map.get("attname") + " = '" + codeCommon.getTableDataMap().get(map.get("attname")) + "'";
}

if (map.get("attname").equals("adm_no")) {
mergeSetSql += map.get("attname") + " = " + codeCommon.getAdminNo();
} else if ( map.get("attname").equals("updt_dt") || map.get("attname").equals("upd_dt") ) {
mergeSetSql += map.get("attname") + " = now() ";
}
}
if ( map.get("attname").equals("updt_dt") || map.get("attname").equals("upd_dt") ) continue; // insert문에는 필요 없음.
insertColSql += ((insertColSql.equals(""))? "":",") + map.get("attname");
insertValSql += ((insertValSql.equals(""))? "":",");
if (map.get("data_type").equals("smallint") || map.get("data_type").equals("integer") || map.get("data_type").equals("boolean") ) {
insertValSql += codeCommon.getTableDataMap().get(map.get("attname"));
} else if (map.get("data_type").equals("character varying") || map.get("data_type").equals("character")) {
insertValSql += "'" + codeCommon.getTableDataMap().get(map.get("attname")) + "'";
}
}

codeCommon.setMergeSetSql(mergeSetSql);
codeCommon.setMergeWhereSql(mergeWhereSql);
codeCommon.setInsertColSql(insertColSql);
codeCommon.setInsertValSql(insertValSql);

try {
Map map = (Map) dao.selectOne("codeCommon.selectCodeOne", codeCommon);
if (map != null && map.size() > 0) {
throw new GlobalException("이미 등록되어 있는 코드입니다.", true);
}
if (codeCommon.getSelCodeTable().substring(0, 5).equals("spsdb")) {
if (0 > daoSpsdb.update("codeCommon.mergeCodeTable", codeCommon)) {
throw new GlobalException("코드 테이블 등록/수정 실패", true);
}
} else {
if (0 > dao.update("codeCommon.mergeCodeTable", codeCommon)) {
throw new GlobalException("코드 테이블 등록/수정 실패", true);
}
}
} catch (BadSqlGrammarException ex) {
throw new GlobalException(ex.getSQLException().toString() , true);
}
}