dbcompare.java

导出ORACLE数据库对象DDL语句的程序

			
			String priKey = "";
			String name,type;
			if(moveSrc)
				srcSet.next();
			if(moveDes)
				desSet.next();

			for(int i=0;i<outputs.size();i++){
				TagField field = (TagField) outputs.get(i);
				if(field!=null){
					name = field.getName();
					type = field.getType();
					if(type.equalsIgnoreCase("int")){
						int srcValue = 0,desValue = 0;
							srcValue = srcSet.getInt(i+1);
						
							desValue = desSet.getInt(i+1);

						if(srcValue!=desValue){
							MYSTYLE = "ALTER";
							scanResult.append(prefix
							 + "ALTER "
							 + priKey
							 + " "
							 + field.getName()
							 + " "
							 + Integer.toString(srcValue)
							 +"-->"
							 + Integer.toString(desValue)
							 +";\n");
							executeUpdate(tagElement,i,"ALTER",
										  priKey,Integer.toString(srcValue),Integer.toString(desValue));
						}
					}else if(type.equalsIgnoreCase("string")||type.equals("")){
						String srcValue = null,desValue = null;
						try {
							srcValue = srcSet.getString(i+1);
						} catch (SQLException e) {
							moveSrc = false;
							isSrcNull = true;
						}
						try {
							desValue = desSet.getString(i+1);
						} catch (SQLException e) {
							moveDes = false;
							isDesNull = true;
						}
						if(isSrcNull&&isDesNull){
							return resultStr;
						}
						else if(isSrcNull){
								MYSTYLE = "ADD";
								desValue = desSet.getString(i+1);
								scanResult.append(prefix
								 + "ADD "
								 + tagElement.getName()
								 + " "
								 + desValue
								 +"\n");
								 priKey = desValue;
//								executeUpdate(tagElement,i,"ADD",
//										  priKey,"N/A",desValue);
//								 for(int j=1;j<outputs.size();j++){
//								 	field = (TagField) outputs.get(j);
//								 	if(field!=null){
//								 		name = field.getName();
//								 		type = field.getType();
//										if(type.equalsIgnoreCase("int")){
//											int intValue = desSet.getInt(j+1);
//											scanResult.append(field.getName()
//											+ " "
//											+ intValue
//											+ "\n"
//											);
//											executeUpdate(tagElement,j,"ADD",
//														  priKey,"0",Integer.toString(intValue));
//										}else if(type.equalsIgnoreCase("string")){
//											String strValue = desSet.getString(j+1);
//											if(strValue!=null){
//												scanResult.append(field.getName()
//												+ " "
//												+ strValue
//												+ "\n"
//												);
//												executeUpdate(tagElement,j,"ADD",
//															  priKey,"N/A",strValue);
//											}
//										}
//								 	}
//								 }
//							scanResult.append(");\n");
//							if(resultStr.indexOf(desValue)<0)
//								resultStr = resultStr + desValue + "|";
							break;
						}
						else if(isDesNull){
								MYSTYLE = "DELETE";
								srcValue = srcSet.getString(i+1);
								scanResult.append(prefix
								 + "DELETE "
								 + tagElement.getName()
								 + " "
								 + srcValue
								 +"\n");
								 priKey = srcValue;
//							executeUpdate(tagElement,i,"DELETE",
//										  priKey,srcValue,"N/A");
//							for(int j=1;j<outputs.size();j++){
//								field = (TagField) outputs.get(j);
//								if(field!=null){
//									name = field.getName();
//									type = field.getType();
//									if(type.equalsIgnoreCase("int")){
//										int intValue = srcSet.getInt(j+1);
//										scanResult.append(field.getName()
//										              	  + " "
//													      + intValue
//													      + "\n"
//													      );
//										executeUpdate(tagElement,j,"DELETE",
//													  priKey,Integer.toString(intValue),"0");
//									}else if(type.equalsIgnoreCase("string")){
//										String strValue = srcSet.getString(j+1);
//										if(strValue!=null){
//										scanResult.append(field.getName()
//													      + " "
//													      + strValue
//													      + "\n"
//													      );
//											executeUpdate(tagElement,j,"DELETE",
//														  priKey,strValue,"N/A");
//										}
//									}
//								}
//							}		
//							scanResult.append(");\n");	
//							if(resultStr.indexOf(srcValue)<0)
//								resultStr = resultStr + srcValue + "|";
							break;	 
						}else{
						if(i==0){
							if(srcValue.compareTo(desValue)<0){
								MYSTYLE = "DELETE";
								scanResult.append(prefix
								 +"DELETE "
								 +tagElement.getName()
								 +" "
								 +srcValue
								 +"\n");
								 moveDes = false;
								 moveSrc = true;
								 priKey = srcValue;
//								executeUpdate(tagElement,i,"DELETE",
//											  priKey,srcValue,"N/A");
//								for(int j=1;j<outputs.size();j++){
//									field = (TagField) outputs.get(j);
//									if(field!=null){
//										name = field.getName();
//										type = field.getType();
//										if(type.equalsIgnoreCase("int")){
//											int intValue = srcSet.getInt(j+1);
//											scanResult.append(field.getName()
//											+ " "
//											+ intValue
//											+ "\n"
//											);
//											executeUpdate(tagElement,j,"DELETE",
//														  priKey,Integer.toString(intValue),"0");
//											
//										}else if(type.equalsIgnoreCase("string")){
//											String strValue = srcSet.getString(j+1);
//											if(strValue!=null){
//												scanResult.append(field.getName()
//												+ " "
//												+ strValue
//												+ "\n"
//												);
//												executeUpdate(tagElement,j,"DELETE",
//															  priKey,strValue,"N/A");
//											}
//										}
//									}
//								}
//								scanResult.append(");\n");	
//								if(resultStr.indexOf(srcValue)<0)
//									resultStr = resultStr + srcValue + "|";
								break;						 
							}else if(srcValue.compareTo(desValue)==0){
								moveSrc = true;
								moveDes = true;
								priKey = srcValue;
								MYSTYLE = "SAME";
								if(resultStr.indexOf(srcValue)<0)
									resultStr = resultStr + srcValue + "|";
							}else{
								MYSTYLE = "ADD";
								scanResult.append(prefix
								 +"ADD "
								 +tagElement.getName()
								 +" "
								 +desValue
								 +"\n");
								 moveSrc = false;
								 moveDes = true;
								 priKey = desValue;
//								executeUpdate(tagElement,i,"ADD",
//											  priKey,"N/A",desValue);
//								for(int j=1;j<outputs.size();j++){
//									field = (TagField) outputs.get(j);
//									if(field!=null){
//										name = field.getName();
//										type = field.getType();
//										if(type.equalsIgnoreCase("int")){
//											int intValue = desSet.getInt(j+1);
//											scanResult.append(field.getName()
//											+ " "
//											+ intValue
//											+ "\n"
//											);
//											executeUpdate(tagElement,j,"ADD",
//														  priKey,"0",Integer.toString(intValue));
//										}else if(type.equalsIgnoreCase("string")){
//											String strValue = desSet.getString(j+1);
//											if(strValue!=null){
//												scanResult.append(field.getName()
//												+ " "
//												+ strValue
//												+ "\n"
//												);
//												executeUpdate(tagElement,j,"ADD",
//															  priKey,"N/A",strValue);
//											}
//										}
//									}
//								}	
//								scanResult.append(");\n");
//								if(resultStr.indexOf(desValue)<0)
//									resultStr = resultStr + desValue + "|";
								break;
							}
						}else{
							if(srcValue==null)
								srcValue="null";
							if(desValue==null)
								desValue="null";
							if(!srcValue.equals(desValue)){
								MYSTYLE = "ALTER";
								scanResult.append(prefix
								+ "ALTER "
								+ priKey
								+ " "
								+ field.getName()
								+ " "
								+ srcValue
								+"-->"
								+ desValue
								+";\n");
								executeUpdate(tagElement,i,"ALTER",
										      priKey,srcValue,desValue);
							}
						}
						}
					}
				}
			}
			if(MYSTYLE.equals("ALTER")){
				executeUpdate(tagElement,-1,"MYSTYLE",
							  priKey,"DIFF","DIFF");
			}else if(MYSTYLE.equals("SAME")){
				executeUpdate(tagElement,-1,"MYSTYLE",
							  priKey,"SAME","SAME");
			}else if(MYSTYLE.equals("ADD")){
				executeUpdate(tagElement,-1,"MYSTYLE",
							  priKey,"NONE","HAVE");
			}else if(MYSTYLE.equals("DELETE")){
				executeUpdate(tagElement,-1,"MYSTYLE",
							  priKey,"HAVE","NONE");
			}
		}
//		return resultStr.substring(0,resultStr.length());
	}
/*
 * 将差异结果存入数据库
 * curTag 为当前比较目标的TagElement
 * index 为当前比较的outField在TagElement的位置
 * compType 为差异类型
 * priKey 为当前TagElement的主键值
 * srcValue 为当前outField的源值
 * desValue 为当前outField的目标值
 */
	private void executeUpdate(TagElement curTag,int index,String compType,String priKey,String srcValue,String desValue) throws TLException{
		String sql = curTag.getUpdateSql();
//		System.out.println("EXE UPDATEsql:"+sql);
		if(sql==null||sql.trim().length()==0)
			return;
		Vector inputs = curTag.getInput();
		Vector outputs = curTag.getOutput();
		PreparedStatement updateStat = null;
		String tempParams="";
		try {
			updateStat = DbCompare.storageCon.prepareStatement(sql);
			int count = 0;
			updateStat.setString(++count,srcDBUrl);
			updateStat.setString(++count,desDBUrl);
			String type="",name = "",value="";
			for(int i=0;i<inputs.size();i++){
				TagField inputField = (TagField) inputs.get(i);
				if(inputField!=null){
					name = inputField.getName();
					value = inputField.getValue();
					if(name.equalsIgnoreCase("owner")){
						updateStat.setString(++count,srcOwner);
						updateStat.setString(++count,desOwner);
					}else{
						updateStat.setString(++count,value);
						updateStat.setString(++count,value);
					}
				}
			}
			if(compType.equalsIgnoreCase("add")){
				updateStat.setString(++count,"N/A");
				updateStat.setString(++count,priKey);
			}else if(compType.equalsIgnoreCase("delete")){
				updateStat.setString(++count,priKey);
				updateStat.setString(++count,"N/A");
			}else if(compType.equalsIgnoreCase("alter")){
				updateStat.setString(++count,priKey);
				updateStat.setString(++count,priKey);
			}else{
				updateStat.setString(++count,priKey);
				updateStat.setString(++count,priKey);
			}
			tempParams+=priKey;
			if(index>=0){
				TagField outField = (TagField) outputs.get(index);
				name = outField.getName();
				type = outField.getType();
			}else{
				name = compType;
				type = "string";
			}
			updateStat.setString(++count,name);
			updateStat.setString(++count,type);
			tempParams+=name+type;
			if(type.equalsIgnoreCase("int")){
				updateStat.setInt(++count,Integer.parseInt(srcValue));
				updateStat.setInt(++count,Integer.parseInt(desValue));
				
			}else if(type.equalsIgnoreCase("string")){
				updateStat.setString(++count,srcValue);
				updateStat.setString(++count,desValue);
			
			}
			tempParams+="srcValue"+srcValue;
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			throw new TLException("executeUpdate()执行错误:输入参数个数或者类型不正确\n"
									+e.getMessage()+"\n"+"sql:"+sql);
		}
		
		try {
			updateStat.executeUpdate();
			DbCompare.storageCon.commit();
			updateStat.close();
		} catch (SQLException e1) {
			System.out.println(tempParams);
			e1.printStackTrace();
			throw new TLException("executeUpdate()执行错误:插入新纪录时发生错误\n"
									+e1.getMessage()+"\n"+"sql:"+sql);
		}
	}
}