saic_tools.java

一个java的ETL工具

import java.util.ArrayList;
import java.util.StringTokenizer;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;


public class SAIC_Tools
{
	public static ArrayList CSV_to_AL(String sentCSV)
	{
		ArrayList returnAL = new ArrayList();

		StringTokenizer st = new StringTokenizer(sentCSV, ",");
		while (st.hasMoreTokens())
		{
			returnAL.add(st.nextToken());
		}
		returnAL.trimToSize();

		return returnAL;
	}

	public static String replace(String content, String oldString, String newString)
	{
		if (!oldString.equals(newString))
		{
			int x, y = oldString.length();
			while (content.indexOf(oldString) != -1)
			{
				StringBuffer buf = new StringBuffer(content);
				x = content.indexOf(oldString);
				content = buf.replace(x, x+y, newString).toString();
			}
		}
		return content;
	}


	public static void populate(Connection connection, String TableName, String csvDestinationFields, String csvSourceFields, String whereClause)
	{
		if ( !parametricInsert(connection, tableName, csvDestinationFields, csvSourceFields, whereClause) )
			if ( parametricUpdate(connection, sentTableName, csvDestinationFields, csvSourceFields, whereClause) )
				System.out.println("successful Update of ....");
			else
				System.out.println("Insert and Update failed");
		else
			System.out.println("successful Insert of ....");
	}

	public static boolean parametricInsert(Connection connection, String tableName, String csvDestinationFields, String csvSourceFields, String whereClause)
	{
		//  csvSourceFields typical structure:   "MERGE(A,B,d), C, D, E"

		String insertSQL = "";
		String csvValues;
		ResultSet tempResultSet;

		// build csvValues

		// convert CSV to ArrayList
		// iterate through csvSourceFields
		// get a ResultSet for each group of fields associated with a table
		// get values needed from result set
		// if MERGE, SPLIT or REPLACE is needed make method calls below (or as callable statements)
		// concatentate values into csvValues string

		insertSQL = "INSERT INTO " + tableName + " " + csvDestinationFields + "VALUES (" + csvValues + ") WHERE " + whereClause;

		Statement statement = connection.createStatement();  // you may want to use a Prepared or Callable statement in HSQL
		statement.executeUpdate(insertSQL);
	}

	public static boolean parametricUpdate(Connection connection, String tableName, String csvDestinationFields, String csvSourceFields, String whereClause)
	{
		String updateSQL = "";

		updateSQL = "UPDATE ...";

	}

	public static String fieldStringMerge(String csvFieldValues, String delimiter)
	{
		String mergedValue = "";


		return mergedValue;
	}

	public static boolean fieldStringSplit(String fieldValue, String delimiter)
	{
		String csvSplitValues = "";

		return csvSplitValues;
	}

	public static String fieldStringReplace(String csvFieldValues, String searchString, String replaceString)
	{
		String csvNewFieldValues = "";

		return csvNewFieldValues;
	}
}