我们在做项目的时候,数据分为公共数据,和过程数据,公共数据永远都审批以后最新的数据.过程数据是在每次申报的时候可以修改的数据.
每次申报的时候都需要将公共数据拷贝到过程表中.
/** * 数据拷贝处理工具类 * * @Company 中海纪元 * @author ducc * @date 2012-11-4 上午10:32:40 * @Team 智慧住建事业部 */ @Repository public class DataCopyDao extends EntityHibernateDao { /**数据拷贝主方法 * @author ducc * @param sourceTab 源数据表 * @param targetTab 目的数据表 * @param sequenceId 主键生产ID * @param source_target_column_map 非同名字段需要进行特殊映射的字段内容 * @param sourceQueryCondition 源数据检索条件 * @param appendConstantColumn 需要追加的额外常量列 * @param ignoreForginColumn 不管的外键 * @return */ @SuppressWarnings("unchecked") public Map<String, String> dataCopy(String sourceTab, String targetTab, String sequenceId, Map<String, String> source_target_column_map, String sourceQueryCondition, Map<String, String> appendConstantColumn, Map<String, String> ignoreForginColumn, String pk_suffix) throws DAOException { Map<String, String> hashmap = new HashMap<String, String>(); // 查询表结构 StringBuffer sqlString = new StringBuffer( "SELECT " + "t.table_name \"table_name\", t.column_name \"column_name\",c.comments \"comments\",t.data_type \"data_type\",t.nullable \"nullable\"" + "from " + "user_tab_cols t, user_col_comments c " + "where" + " t.column_name=c.column_name and t.table_name= c.table_name and t.table_name = ? order by t.COLUMN_NAME"); // 查询表中是否有外键关联 StringBuffer constrainsql = new StringBuffer( "select " + "cu.table_name || ',' || cu.column_name as constrain " + "from " + "user_cons_columns cu, user_constraints au " + "where" + " cu.constraint_name = au.constraint_name and au.constraint_type = 'R' and au.table_name = ?"); List<TabColumObj> sourceTabColumObjs = this .getSession() .createSQLQuery(sqlString.toString()) .setString(0, sourceTab) .setResultTransformer( Transformers.aliasToBean(TabColumObj.class)).list(); List<TabColumObj> targetTabColumObjs = this .getSession() .createSQLQuery(sqlString.toString()) .setString(0, targetTab) .setResultTransformer( Transformers.aliasToBean(TabColumObj.class)).list(); List<String> lisconstrainObjs = this.getSession() .createSQLQuery(constrainsql.toString()) .setString(0, sourceTab).list(); StringBuffer sqltmp = null; StringBuffer target_colums = new StringBuffer();// 目的表的列 StringBuffer source_colums = new StringBuffer();// 目的表的列 String columname = "";// 列名 // 循环拼接 insert into a selct * from b 类似的sql语句 sqltmp = new StringBuffer("insert into "); sqltmp.append(targetTab + " ("); for (int i = 0; i < sourceTabColumObjs.size(); i++) { // 按照表进行inser 语句的拼接 for (int j = 0; j < targetTabColumObjs.size(); j++) { if (sourceTabColumObjs .get(i) .getColumn_name() .equalsIgnoreCase( targetTabColumObjs.get(j).getColumn_name())) {// 同名字段赋值 if (lisconstrainObjs.size() > 0) { columname = sourceTabColumObjs.get(i).getColumn_name(); if (lisconstrainObjs.contains(sourceTab + "," + columname)) {// 判断是否包含外键包含的话则需要拼接后缀值 source_colums.append(columname); source_colums.append(","); if (ignoreForginColumn.get(columname) != null && !"".equals(ignoreForginColumn .get(columname))) {// 如果包含外键忽略字段值则即使为外键也不需要拼接后缀 if (source_target_column_map .containsKey(columname)) {// 需要映射的字段进行重组 if (source_target_column_map.get(columname) .equals("ignore")) { target_colums.append("''"); } else { target_colums .append(source_target_column_map .get(columname)); } } else { target_colums.append(columname); } } else { if (source_target_column_map .containsKey(columname)) {// 需要映射的字段进行重组 if (source_target_column_map.get(columname) .equals("ignore")) { target_colums.append("''"); } else { target_colums .append(source_target_column_map .get(columname)); } }else{ target_colums.append(columname + " || " + pk_suffix); } } target_colums.append(","); } else { source_colums.append(columname); source_colums.append(","); if (ignoreForginColumn.get(columname) != null && !"".equals(ignoreForginColumn .get(columname))) {// 如果包含外键忽略字段值则即使为外键也不需要拼接后缀 if (source_target_column_map.get(columname) .equals("ignore")) { target_colums.append("''"); } else { target_colums .append(source_target_column_map .get(columname)); } } else { if (columname.equalsIgnoreCase("ID")) {// 为主键的话拼接后缀 target_colums.append(columname + " || " + pk_suffix); } else { if (source_target_column_map .containsKey(columname)) {// 需要映射的字段进行重组 target_colums .append(source_target_column_map .get(columname)); } else { target_colums.append(columname); } } } target_colums.append(","); } } else { columname = sourceTabColumObjs.get(i).getColumn_name(); source_colums.append(columname); source_colums.append(","); if (columname.equalsIgnoreCase("ID")) {// 为主键的话拼接后缀 target_colums .append(columname + " || " + pk_suffix); } else { if (source_target_column_map.containsKey(columname)) {// 需要映射的字段进行重组 target_colums.append(source_target_column_map .get(columname)); } else { target_colums.append(columname); } } target_colums.append(","); } } } } /*循环添加额外补充字段比如常量字段等*/ for(String str : appendConstantColumn.keySet()){ source_colums.append(str); source_colums.append(","); target_colums.append(appendConstantColumn.get(str)); target_colums.append(","); } if(source_colums.length()>0){ sqltmp.append(source_colums.substring(0, source_colums.length() - 1)); } if(target_colums.length()>0){ sqltmp.append(") select " + target_colums.substring(0, target_colums.length() - 1) + " from " + sourceTab); } int num = 0; if ("".equals(sourceQueryCondition)) { num = this.getSession().createSQLQuery(sqltmp.toString()) .executeUpdate(); } else { sqltmp.append(" " + sourceQueryCondition); num = this.getSession().createSQLQuery(sqltmp.toString()) .executeUpdate(); } logger.info("insert sql is :" + sqltmp.toString()); logger.info("导入记录数" + num); return hashmap; } public String returnSeqId() { String pk_suffix = "";// 所有主键后统一体添加后缀 pk_suffix = String.valueOf(this.getSession() .createSQLQuery("select COMMITTABLONGID.NEXTVAL FROM DUAL") .uniqueResult().toString()); return pk_suffix; } /** * 删除数据表 * @param tab * @param delcondition * @return * @throws DAOException */ public String delTab(String tab,String delcondition) throws DAOException{ String delsql = "delete from "+tab+delcondition; int num = this.getSession().createSQLQuery(delsql).executeUpdate(); logger.info("删除记录数:"+num); return num+""; } }
文章评论