sqloperation.java

一个用于实现不同数据库数据同步的程序

import java.sql.*;
import javax.sql.*;
import java.io.*;
import java.awt.*;
import com.ibm.db2.jcc.*;
import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.util.*;
import javax.swing.*;
import java.math.BigDecimal;

public class sqlOperation
{
	private String databaseName="";private String url="";private String driver="";private String user="";private String password="";///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////



	public void updateTable(String sql)
	{
		Connection conn=null;
		Statement statement=null;
		ResultSet rs=null;
		try
		{
			Class.forName(driver).newInstance();
			conn=DriverManager.getConnection(url,user,password);
			statement=conn.createStatement(); 
			statement.executeUpdate(sql);
			conn.close();
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}

	public String StringFromDB(String sqlSentence,String columnName)
	{
		String ts="";
	
		ResultSet rs=null;
		Statement statement=null;
		Connection conn=null;

		try
		{
			Class.forName(driver).newInstance();
			conn=DriverManager.getConnection(url,user,password);
			statement=conn.createStatement(); 					
			rs=statement.executeQuery(sqlSentence);
			while(rs.next())
			{
				ts=rs.getString(columnName);
			}
			conn.close();
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}

		return ts;
	}

	public ResultSet ResultSetFromDB(String sqlSentence)
	{
		ResultSet rs=null;
		Statement statement=null;
		Connection conn=null;

		try
		{
			Class.forName(driver).newInstance();
			conn=DriverManager.getConnection(url,user,password);
			statement=conn.createStatement(); 					
			rs=statement.executeQuery(sqlSentence);
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}

		return rs;
	}

	
	public String multiSqlOperation(String txtAddress,String pno)
	{
		int start=0,mid=0,i=0;
		dealMessage cutingMessage=new dealMessage();
		String s="";
		String temp="";
		String sno="";
		String sqlSentence="";
		Key k=null;

		Connection conn;
		PreparedStatement ps;
		try
		{
			Class.forName(driver).newInstance();
			conn=DriverManager.getConnection(url,user,password);

			File file=new File(txtAddress+"MissionManageTemp.txt");
			FileReader fr=null;
			//k=new Key(txtAddress);

			if(!file.exists())
			{
				System.out.println("文件不存在");
				System.exit(1);
			}
			else
			{
				try
				{
					//k.decrypt(txtAddress+"MissionManageTemp.txt");
					fr=new FileReader(file);
				}
				catch(FileNotFoundException e){}

				BufferedReader br=new BufferedReader(fr);
				
				try
				{
					while((s=br.readLine())!=null)
					{		
						mid=s.indexOf("\t");
						if(mid>0)
						{
							start=0;
							temp=cutingMessage.cutMessage(s,start,mid);
							if(temp.equals(pno))
							{
								while((s=br.readLine())!=null)
								{
									if(s.indexOf("\t")<1)
									{s=s.trim();
										/*start=s.indexOf("\t");
										mid=s.length();
										s=cutingMessage.cutMessage(s,start,mid);*/
										s=cutingMessage.changingSQL(s);

										System.out.println(s);
										ps=conn.prepareStatement(s);
										i+=ps.executeUpdate();
										ps.close();
									}
									else
									{
										break;
									}
								}
								break;
							}
						}
					}
					fr.close();
					//k.encryption(txtAddress+"MissionManageTemp.txt");

				}catch(IOException e){}
			}

				
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		
		s=checkDBupdate(txtAddress,pno);
		return s;
	}


	public String checkDBupdate(String txtAddress,String pno)
	{
		String erroMesg="NO";
		
		File file=new File(txtAddress+"MissionManageTemp.txt");
		FileReader fr=null;
		String s="",temp="",sno="",columnName="",temp2="";
		int start=0,mid=0,end=0;
		dealMessage cutingMessage=new dealMessage();
		Connection conn=null;
		Statement statement=null;
		String sql=null;
		ResultSet rs=null;
		int account=0;
		//Key k=null;

		if(!file.exists())
		{
			System.out.println("文件不存在");
			System.exit(1);
		}
		else
		{
			try
			{
				//k=new Key(txtAddress);
				//k.decrypt(txtAddress+"MissionManageTemp.txt");
				fr=new FileReader(file);
			}
			catch(FileNotFoundException e){}

			BufferedReader br=new BufferedReader(fr);
				

			try
			{
				Class.forName(driver).newInstance();
				conn=DriverManager.getConnection(url,user,password);
			}catch(Exception e){}


			try
			{
				while((s=br.readLine())!=null)
				{
					mid=s.indexOf("\t");
					if(mid>1)
					{
						temp=cutingMessage.cutMessage(s,start,mid);
						if(temp.equals(pno))
						{
							while((s=br.readLine())!=null)
							{
								if((s.indexOf("-----"))!=-1)
								{
									break;
								}
								else
								{
									mid=s.indexOf("\t");
									end=s.length();
									s=cutingMessage.cutMessage(s,mid,end);	

									mid=s.indexOf("\t");
									temp=cutingMessage.cutMessage(s,start,mid);

									end=s.length();
									s=cutingMessage.cutMessage(s,mid,end);

									mid=s.indexOf("\t");
									if(mid==-1)
									{
										sno=s;
									}
									else
									{
										sno=cutingMessage.cutMessage(s,start,mid);
										end=s.length();
										s=cutingMessage.cutMessage(s,mid,end);
									}
			
									
									if(temp.equals("alter"))
									{
										mid=s.indexOf("\t");
											
										while(true)
										{
											columnName=cutingMessage.cutMessage(s,start,mid);

											temp2=StringFromDB("select * from card where SNUMBER='"+sno+"'",columnName);

											end=s.length();
											s=cutingMessage.cutMessage(s,mid,end);

											mid=s.indexOf("\t");
											end=s.length();
											s=cutingMessage.cutMessage(s,mid,end);

											mid=s.indexOf("\t");
											if(mid!=-1)
											{
												temp=cutingMessage.cutMessage(s,start,mid);
												end=s.length();
												s=cutingMessage.cutMessage(s,mid,end);
												mid=s.indexOf("\t");
											}
											else
											{
												temp=s;mid=-100;
											}

											if(!temp.equals(temp2))
											{
												if(erroMesg.equals("NO"))
												{
													erroMesg=sno;
												}
												else
												{
													erroMesg+="\t"+sno;
												}
											}
											if(mid==-100)
											{
												break;
											}
											
										}

									}
									else
									{
										if(temp.equals("new"))
										{

											sql="select * from card where SNUMBER='"+sno+"','";
											s=s.replace("\t","','");s+="'";
											sql+=s;
											rs=statement.executeQuery(sql);

											if(!rs.next())
											{
												if(erroMesg.equals("NO"))
												{
													erroMesg=sno;
												}
												else
												{
													erroMesg+="\t"+sno;
												}
											}
									
									
										}
										else
										{
											if(temp.equals("delete"))
											{

												sql="select * from card where SNUMBER='"+sno+"'";
												rs=statement.executeQuery(sql);

												if(rs.next())
												{
													if(erroMesg.equals("NO"))
													{
														erroMesg=sno;
													}
													else
													{
														erroMesg+="\t"+sno;
													}
												}
											}
										}
									}
								}
							}
						}

					}	
				}
				conn.close();
				fr.close();
				//k.encryption(txtAddress+"MissionManageTemp.txt");
			}catch(Exception e){}

		}
			
		return erroMesg;
	}

}