重大改版
 鱼翔空 2013-06-16 综合应用|   0 988 
文章评分 90 次,平均分 5.0

我们在做项目的时候,数据分为公共数据,和过程数据,公共数据永远都审批以后最新的数据.过程数据是在每次申报的时候可以修改的数据.

每次申报的时候都需要将公共数据拷贝到过程表中.

/**
 * 数据拷贝处理工具类
 * 
 * @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+"";
    }
}


 

除特别注明外,本站所有文章均为我要编程原创,转载请注明出处来自http://5ycode.com/article/216.html

关于
该用户很懒!
切换注册

登录

忘记密码 ?

切换登录

注册

扫一扫二维码分享