我们在做项目的时候,数据分为公共数据,和过程数据,公共数据永远都审批以后最新的数据.过程数据是在每次申报的时候可以修改的数据.
每次申报的时候都需要将公共数据拷贝到过程表中.
/**
* 数据拷贝处理工具类
*
* @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+"";
}
}
