generategtextsql.java

这个程序是用来比较csv文件, 然后自动生成sql语句, 运行命令是runGenText csv1 csv2

/*
 * Created on Aug 31, 2004
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */

import com.Ostermiller.util.ExcelCSVParser;

import java.io.IOException;
import java.io.FileNotFoundException;
import java.io.FileReader;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import java.util.Locale;


/**
 * @author jmorrey
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class GenerateGTEXTSQL {

	public static void main(String[] args) {
		String srcFile1 = "";
		String srcFile2 = "";
		FileReader fileRead1 = null;
		FileReader fileRead2 = null;
		String[][] srcArray1;
		String[][] srcArray2;
		int srcp = 0;
		int dstp = 0;
				
		try{
			srcFile1 = args[0];
			srcFile2 = args[1];
		}
		catch (Exception exe){
			System.out.println("usage: java GenerateGTEXTSQL [srcFile1] [srcFile2]");
			System.exit(-1);
		}
		
		try{
			fileRead1 = new FileReader(srcFile1);
			fileRead2 = new FileReader(srcFile2);
		} catch (FileNotFoundException exception){
			System.out.println("The " + srcFile1 + " file is missing from the current working directory.");
			System.exit(-1);
		}
		
		try{
			srcArray1 = ExcelCSVParser.parse(fileRead1);
			srcArray2 = ExcelCSVParser.parse(fileRead2);

			int len1 = srcArray1.length;
			int len2 = srcArray2.length;
			
			//for (int i=0;i<len1;i++)
			for (int i=1;i<len1;i++)
			{
				srcp = srcp + 1;
				dstp = dstp + 1;
				
				String[] fields1 = srcArray1[i];
				boolean isUpdated = false;
				boolean isInserted = true;
				boolean isDeleted = false;
				String sql = "";
				String setSql = "";
				
				for (int j=0;j<len2;j++)
				{
					
					String[] fields2 = srcArray2[j];
					
					if( (fields1[0] != null && fields2[0] != null && (fields1[0].trim()).equals(fields2[0].trim()) ) &&
					    (fields1[6] != null && fields2[6] != null && (fields1[6].trim()).equals(fields2[6].trim()) ) &&
					    (fields1[7] != null && fields2[7] != null && (fields1[7].trim()).equals(fields2[7].trim()) ) )
					{
						setSql = "";
						if( fields1[2] != null && fields2[2] != null && !(fields1[2].trim()).equals(fields2[2].trim()) )
						{
							if("".equals(setSql))
							{
								setSql = setSql + " SET CATEGORY_NAME = '" + fields1[2] + "' ";
							} else 
							{
								setSql = setSql + " , CATEGORY_NAME = '" + fields1[2] + "' ";
							}
							isUpdated = true;
						}
						if( fields1[1] != null && fields2[1] != null && !(fields1[1].trim()).equals(fields2[1].trim()) )
						{
							if("".equals(setSql))
							{
								setSql = setSql + " SET STRING_VALUE = '" + fields1[1] + "' ";
							} else 
							{
								setSql = setSql + " , STRING_VALUE = '" + fields1[1] + "' ";
							}
							isUpdated = true;
						}
						isInserted = false;
					}
				}
				if(isUpdated)
				{
					sql = "UPDATE GUI_TEXT " + setSql + " WHERE STRING_KEY= '" + fields1[0].trim() +
					      "' AND LANG_CODE = '" + fields1[7].trim() + 
					      "' AND COUNTRY_CODE = '" + fields1[6].trim() + "'; \n";
					System.out.println(sql);
				}

				if(isInserted)
				{
					sql = "INSERT INTO GUI_TEXT (STRING_KEY, STRING_VALUE, CATEGORY_NAME, REC_DATE, REC_FUL_NAM, REC_STATUS, COUNTRY_CODE, LANG_CODE) \n";
					sql += "VALUES ('" + 
					          fields1[0] + "', '" +
					          fields1[1] + "', '" +
					          fields1[2] + "', SYSDATE, '" +
					          fields1[4] + "', '" +
					          fields1[5] + "', '" +
					          fields1[6] + "', '" +
					          fields1[7] + "'); \n";
					System.out.println(sql);
				}
			}
			
			//for delete records
			//for (int i=0;i<len1;i++)
			for (int i=1;i<len2;i++)
			{
				
				String[] fields2 = srcArray2[i];
				boolean isUpdated = false;
				boolean isInserted = true;
				boolean isDeleted = true;
				String sql = "";
				String setSql = "";
				
				for (int j=0;j<len1;j++)
				{
					
					String[] fields1 = srcArray1[j];
					
					if( (fields1[0] != null && fields2[0] != null && (fields1[0].trim()).equals(fields2[0].trim()) ) )
					{
						isDeleted = false;
					}
				}
				if(isDeleted)
				{
					sql = "DELETE FROM XUI_TEXT WHERE STRING_KEY = '" + fields2[0].trim() + "'; \n";
					System.out.println(sql);
					sql = "DELETE FROM GUI_TEXT WHERE STRING_KEY = '" + fields2[0].trim() + "'; \n";
					System.out.println(sql);
				}

			}

		} catch (IOException exception){
			System.out.println("There was a problem reading the file.");
			System.out.println(exception + ":" + exception.getMessage());
			System.exit(-1);
		} catch (Exception exception1){
			System.out.println(srcp);
			System.out.println(dstp);
			System.out.println(exception1 + ":" + exception1.getMessage());
			System.exit(-1);
		}
		
	}
	
}