中间表导数

表与表之间的数据复制更新

 

public static void insertMsBook_1_copy(List dataList, DBBeanBase dbBean,User user) throws Exception{
		
			String property[] = new String[]{"INS_FID","DID","CHK_DATE_R","MS_TYPE_NAME","CHK_DATE_L","CHK_DATE","CHK_DATE_SP","MS_BELONG_EQ_CODE","MS_ETP_CODE", "FA_OLD_COST","CHK_PRD","MS_ACC","MS_INS_PST","MS_INS_SITE","MS_RES","FA_CODE","MS_TYPE","MS_USE","MS_FACT_DATE","MS_FACT_CODE","IS_SEQ","MS_PROD_ORG","MS_CODE","MS_BELONG_EQ_NAME","MS_ABC","EARLY_WAR","MS_RANGE","MS_NAME","MS_STATE","CHK_TYPE","MS_MODEL","MS_BEG_DATE","UPDATE_TIME"};
			BeanInfo beanInfo = Introspector.getBeanInfo(DetialDataVo.class);
			
			//将数据插入中间表并过滤企业内部编号为空的数据
			DetialDataVo dataVo = null;

			for(int i=0;i<dataList.size();i++){
				StringBuffer dataSql = new StringBuffer();
				StringBuffer propSql = new StringBuffer();
				dataVo = (DetialDataVo)dataList.get(i);
				dataVo.setMS_CODE("");
				if(dataVo.getMS_ETP_CODE().trim()!="" && dataVo.getMS_ETP_CODE().trim() != null){
				     for(int k=0;k<property.length;k++){
				    	 PropertyDescriptor pd = new PropertyDescriptor(property[k], DetialDataVo.class);
				    	 propSql.append(property[k]).append(",");
				    	 if("CHK_PRD".equals(property[k]) || "FA_OLD_COST".equals(property[k]) ||  "EARLY_WAR".equals(property[k]) ||  "UPDATE_TIME".equals(property[k])){
				    		 dataSql.append("").append(pd.getReadMethod().invoke(dataVo, null)).append(",");
				    	 }else if("MS_FACT_DATE".equals(property[k]) || "MS_BEG_DATE".equals(property[k]) || "CHK_DATE_R".equals(property[k]) || "CHK_DATE_L".equals(property[k]) || "CHK_DATE".equals(property[k]) || "CHK_DATE_SP".equals(property[k])){
				    		 dataSql.append("'").append(StringUtil.replace((String)pd.getReadMethod().invoke(dataVo, null), "/", "-").trim()).append("',");
				    	 }else{
				    		 dataSql.append("'").append(pd.getReadMethod().invoke(dataVo, null)).append("',");
				    	 } 
				     }
				     if(dataSql.length()>0){
							dataSql.deleteCharAt(dataSql.length()-1);
							propSql.deleteCharAt(propSql.length()-1);
							
							StringBuffer insertSql = new StringBuffer();
							insertSql.append("insert into CDA_FID_MS_BOOK_1_TEMP(").append(propSql.toString()).append(")").append(" values(").append(dataSql.toString()).append(")");
							if(dbBean.executeUpdate(insertSql.toString()) == -1){
								log.error("Excel导入插入中间表出错 1" + insertSql.toString());
								dbBean.rollback();
								throw new Exception("Excel导入插入中间表出错1 " + insertSql.toString());
							}
							log.info(insertSql.toString());
				     }	
						
				}
				
			}

			//根据存储表来更新中间表的MS_CODE
			StringBuffer updateSql = new StringBuffer("UPDATE CDA_FID_MS_BOOK_1_TEMP A SET MS_CODE = ( SELECT MS_CODE FROM CDA_FID_MS_BOOK_1 B WHERE 1=1 AND A.MS_ETP_CODE = B.MS_ETP_CODE )");
			if(dbBean.executeUpdate(updateSql.toString()) == -1){
				log.error("根据存储表更新中间表错误 : " + updateSql.toString());
				dbBean.rollback();
				throw new Exception("根据存储表更新中间表错误 : " + updateSql.toString());
			}
            
			
			//将中间表更新到存储表中(如果中间表记录MS_CODE不为空则执行更新操作,为空则执行插入操作)		
			StringBuffer querySql = new StringBuffer();
			querySql.append("select COALESCE(MS_CODE,'') as MS_CODE,MS_ETP_CODE  from CDA_FID_MS_BOOK_1_TEMP");
			if(log.isDebugEnabled()){
				log.debug("查询中间表中的数据 : " + querySql);
			}
			if(dbBean.executeQuery(querySql.toString())==-1){
				log.error("查询中间表中的数据出错 : " + querySql);
				dbBean.rollback();
				throw new Exception("查询中间表中的数据出错");
			}

			DBResult dbResult = dbBean.getSelectDBResult();
			
			
			for(int i=0;i<dbResult.getRows();i++){
				StringBuffer inSql = new StringBuffer();
				StringBuffer upSql = new StringBuffer();
				inSql.append("insert into CDA_FID_MS_BOOK_1(INS_FID,DID,CHK_DATE_R,MS_TYPE_NAME,CHK_DATE_L,CHK_DATE,CHK_DATE_SP,MS_BELONG_EQ_CODE,MS_ETP_CODE,FA_OLD_COST,CHK_PRD,MS_ACC,MS_INS_PST,MS_INS_SITE,MS_RES,FA_CODE,MS_TYPE,MS_USE,MS_FACT_DATE,MS_FACT_CODE,IS_SEQ,MS_PROD_ORG,MS_CODE,MS_BELONG_EQ_NAME,MS_ABC,EARLY_WAR,MS_RANGE,MS_NAME,MS_STATE,CHK_TYPE,MS_MODEL,MS_BEG_DATE,UPDATE_TIME) select INS_FID,DID,CHK_DATE_R,MS_TYPE_NAME,CHK_DATE_L,CHK_DATE,CHK_DATE_SP,MS_BELONG_EQ_CODE,MS_ETP_CODE,FA_OLD_COST,CHK_PRD,MS_ACC,MS_INS_PST,MS_INS_SITE,MS_RES,FA_CODE,MS_TYPE,MS_USE,MS_FACT_DATE,MS_FACT_CODE,IS_SEQ,MS_PROD_ORG,MS_CODE,MS_BELONG_EQ_NAME,MS_ABC,EARLY_WAR,MS_RANGE,MS_NAME,MS_STATE,CHK_TYPE,MS_MODEL,MS_BEG_DATE,UPDATE_TIME from CDA_FID_MS_BOOK_1_TEMP where 1=1 ");
				upSql.append("update CDA_FID_MS_BOOK_1 A set ( A.INS_FID,A.DID,A.CHK_DATE_R,A.MS_TYPE_NAME,A.CHK_DATE_L,A.CHK_DATE,A.CHK_DATE_SP,A.MS_BELONG_EQ_CODE,A.MS_ETP_CODE,A.FA_OLD_COST,A.CHK_PRD,A.MS_ACC,A.MS_INS_PST,A.MS_INS_SITE,A.MS_RES,A.FA_CODE,A.MS_TYPE,A.MS_USE,A.MS_FACT_DATE,A.MS_FACT_CODE,A.IS_SEQ,A.MS_PROD_ORG,A.MS_CODE,A.MS_BELONG_EQ_NAME,A.MS_ABC,A.EARLY_WAR,A.MS_RANGE,A.MS_NAME,A.MS_STATE,A.CHK_TYPE,A.MS_MODEL,A.MS_BEG_DATE,A.UPDATE_TIME) = (select B.INS_FID,B.DID,B.CHK_DATE_R,B.MS_TYPE_NAME,B.CHK_DATE_L,B.CHK_DATE,B.CHK_DATE_SP,B.MS_BELONG_EQ_CODE,B.MS_ETP_CODE,B.FA_OLD_COST,B.CHK_PRD,B.MS_ACC,B.MS_INS_PST,B.MS_INS_SITE,B.MS_RES,B.FA_CODE,B.MS_TYPE,B.MS_USE,B.MS_FACT_DATE,B.MS_FACT_CODE,B.IS_SEQ,B.MS_PROD_ORG,B.MS_CODE,B.MS_BELONG_EQ_NAME,B.MS_ABC,B.EARLY_WAR,B.MS_RANGE,B.MS_NAME,B.MS_STATE,B.CHK_TYPE,B.MS_MODEL,B.MS_BEG_DATE,B.UPDATE_TIME from CDA_FID_MS_BOOK_1_TEMP B where 1=1  ");
				String msCode = dbResult.getString(i, "MS_CODE");
				if(StringUtils.isEmpty(msCode)){
					//执行insert操作
					inSql.append(" and MS_ETP_CODE = '").append(dbResult.getString(i,"MS_ETP_CODE")).append("'");
					if(log.isDebugEnabled()){
						log.debug("插入CDA_FID_MS_BOOK_1表 : " + inSql.toString());
					}
					if(dbBean.executeUpdate(inSql.toString())==-1){
						log.error("插入CDA_FID_MS_BOOK_1表出错 : " + inSql.toString());
						//dbBean.rollback();
						throw new Exception("插入CDA_FID_MS_BOOK_1表出错 ");
					}
					
					//插入后执行更新,给刚插入的赋值MS_CODE
					String updateMscode = "update CDA_FID_MS_BOOK_1 set MS_CODE = '" + new FreeReportListExportServletForJl().getEqCode() + "' where MS_ETP_CODE = '" + dbResult.getString(i,"MS_ETP_CODE") + "'";
					if(log.isDebugEnabled()){
						log.debug("更新CDA_FID_MS_BOOK_M_1中的MS_CODE ： " + updateMscode);
					}
					if(dbBean.executeUpdate(updateMscode)==-1){
						log.error("更新CDA_FID_MS_BOOK_M_1中的MS_CODE出错 ：" + updateMscode);
						dbBean.rollback();
						throw new Exception("更新CDA_FID_MS_BOOK_M_1中的MS_CODE出错");
					}
				}else{
					//执行update操作
					upSql.append(" AND B.MS_CODE = '").append(dbResult.getString(i, "MS_CODE")).append("' )  WHERE A.MS_CODE = '").append(dbResult.getString(i, "MS_CODE")).append("'");
					if(log.isDebugEnabled()){
						log.debug("更新CDA_FID_MS_BOOK_1表 : " + upSql.toString());
					}
					if(dbBean.executeUpdate(upSql.toString())==-1){
						log.error("更新CDA_FID_MS_BOOK_1表出错 : " + upSql.toString());
						dbBean.rollback();
						throw new Exception("更新CDA_FID_MS_BOOK_1表 ");
					}
				}
			}
			
			//删除中间表的数据
			StringBuffer delSql = new StringBuffer();
			delSql.append("delete from CDA_FID_MS_BOOK_1_TEMP ");
			if(dbBean.executeUpdate(delSql.toString())==-1){
				log.error("删除CDA_FID_MS_BOOK_1_TEMP数据出错" + delSql);
				dbBean.rollback();
				throw new Exception("删除CDA_FID_MS_BOOK_1_TEMP数据出错");
			}
		
	}