QueryUtil

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintStream;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

/**
 * 
 */

/**
 * @author 
 *
 */
public class CopyOfQueryUtil {

	private static Map<String, String[]> connectionConfig = new LinkedHashMap<String, String[]>();
	private static Map<String, Connection> connectionPool = new HashMap<String, Connection>();
	private static Map<String, Statement> statementPool = new HashMap<String, Statement>();
	private static Map<String, Connection> activeConnectionMap = new HashMap<String, Connection>();
	private static int STATUS_NEED_ENV = 0;
	private static int STATUS_SELECTING_ENV = 1;
	private static int STATUS_ENV_CONFIRMED = 2;
	private static int STATUS_COPY_MODE = 3;
	private static int STATUS = STATUS_NEED_ENV;
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	private static SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
	private static BufferedReader reader;
	private static Map<String, List<Map<String, Object>>> cache = new HashMap<String, List<Map<String, Object>>>();
	static {
		connectionConfig.put("a", new String[]{"jdbc:oracle:thin:@xxxxx:xxxx:xxxx", "xxxx", "xxxx"});
		connectionConfig.put("b", new String[]{"jdbc:oracle:thin:@xxxxx:xxxx:xxxx", "xxxx", "xxxx"});
	}
	
	
	public static void query(String sql, File file, String cacheName) throws SQLException {
		for (Iterator<String> i = activeConnectionMap.keySet().iterator(); i.hasNext();) {
			String envName = i.next();
			Statement stmt = statementPool.get(envName);
			if (stmt == null) {
				stmt = activeConnectionMap.get(envName).createStatement();
				statementPool.put(envName, stmt);
			}
			ResultSet rs = stmt.executeQuery(sql);
			outputResultSet(rs, file, envName, cacheName);
			rs.close();
		}
	}
	
	public static void query(String sql, String cacheName) throws SQLException {
		query(sql, null, cacheName);
	}
	
	public static void query(String sql) throws SQLException {
		query(sql, null, null);
	}
	
	
	public static void update(String sql) throws SQLException {
		for (Iterator<String> i = activeConnectionMap.keySet().iterator(); i.hasNext();) {
			String envName = i.next();
			Statement stmt = statementPool.get(envName);
			if (stmt == null) {
				stmt = activeConnectionMap.get(envName).createStatement();
				statementPool.put(envName, stmt);
			}
			int ret = stmt.executeUpdate(sql);
			
			System.out.println(ret + " records updated in " + envName);
		}
	}
	
	public static void commit() throws SQLException {
		for (Iterator<String> i = activeConnectionMap.keySet().iterator(); i.hasNext();) {
			String envName = i.next();
			Connection conn = activeConnectionMap.get(envName);
			conn.commit();
			System.out.println("commit done for " + envName);
		}
	}
	
	public static void close() throws SQLException {
		for (Iterator<String> i = statementPool.keySet().iterator(); i.hasNext();) {
			try {
				String envName = i.next();
				Statement stmt = statementPool.get(envName);
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		for (Iterator<String> i = connectionPool.keySet().iterator(); i.hasNext();) {
			try {
				String envName = i.next();
				Connection conn = connectionPool.get(envName);
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private static List<Map<String, Object>> getResultList(ResultSet rs) throws SQLException {
		int columnCount = rs.getMetaData().getColumnCount();
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		
		Map<String, Object> typeMap = new LinkedHashMap<String, Object>();
		resultList.add(typeMap);
		for (int i = 0; i <columnCount; i++) {
			String columnName = rs.getMetaData().getColumnName(i + 1);
			int type = rs.getMetaData().getColumnType(i + 1);
			typeMap.put(columnName, type);
		}
		
		while (rs.next()) {
			Map<String, Object> data = new LinkedHashMap<String, Object>();
			resultList.add(data);
			for (int i = 0; i < columnCount; i++) {
				String columnName = rs.getMetaData().getColumnName(i + 1);
				String value = null;
				int type = rs.getMetaData().getColumnType(i + 1);
				if (Types.DATE == type) {
					Date date = rs.getDate(i + 1);
					if (date != null) {
						value = dateFormat.format(date);
					}
				} else if (Types.TIMESTAMP == type) {
					Timestamp ts = rs.getTimestamp(i + 1);
					if (ts != null) {
						value = sdf.format(ts);
					}
				} else if (Types.NUMERIC == type) {
					value = rs.getString(i + 1);
				} else {
					try {
						byte[] byteData = rs.getBytes(i + 1);
						if (byteData != null) {
							value = new String(byteData, "utf-8");
						}
					} catch (UnsupportedEncodingException e) {
						e.printStackTrace();
					}
				}
				data.put(columnName, value);
			}
		}
		rs.close();
		return resultList;
	}
	
	private static void outputResultList(List<Map<String, Object>> resultList, File file, String envName) {
		List<List<String>> list = new ArrayList<List<String>>();
		List<String> innerList = new ArrayList<String>();
		List<Integer> lengthList = new ArrayList<Integer>();
		list.add(innerList);
		PrintStream out = null;
		if (file == null) {
			out = System.out;
		} else {
			try {
				out = new PrintStream(new FileOutputStream(file));
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			}
		}
		int columnCount = 0;
		if (resultList.size() > 0) {
			Map<String, Object> firstRecord = resultList.get(0);
			for (Iterator<String> i = firstRecord.keySet().iterator(); i.hasNext();) {
				String content = i.next();
				int length = content.length();
				lengthList.add(length);
				innerList.add(content);
				columnCount++;
			}
		}
		
		for (int i = 0; i < resultList.size(); i++) {
			innerList = new ArrayList<String>();
			list.add(innerList);
			Map<String, Object> data = resultList.get(i);
			
			int columnIndex = 0;
			for (Iterator<String> column = data.keySet().iterator(); column.hasNext();) {
				String columnName = column.next();
				Object content = data.get(columnName);
				
				if (i == 0) {
					int type = (Integer) content;
					if (type == Types.DATE) {
						content = "DATE";
					} else if (type == Types.TIMESTAMP) {
						content = "TIMESTAMP";
					} else if (type == Types.VARCHAR) {
						content = "VARCHAR";
					} else if (type == Types.NUMERIC) {
						content = "NUMERIC";
					} else if (type == Types.TIME) {
						content = "TIME";
					} else {
						throw new RuntimeException("unsupported type " + type);
					}
				}
				
				String contentString = null;
				if (content == null) {
					contentString = "<NULL>";
				} else {
					contentString = content.toString();
				}
				int length = contentString.length();
				if (lengthList.get(columnIndex) < length) {
					lengthList.set(columnIndex, length);
				}
				innerList.add(contentString);
				columnIndex++;
			}
		}
		
		
		if (envName != null) {
			out.println(envName + ":");
		}
		for (int i = 0; i < list.size(); i++) {
			for (int j = 0; j < columnCount; j++) {
				String content = list.get(i).get(j);
				int length = content.length();
				out.print(content);
				for (int n = 0; n < lengthList.get(j) - length + 2; n++) {
					out.print(" ");
				}
				out.print("|");
			}
			out.println();
		}
		out.println();
		if (file != null) {
			out.close();
		}
	}
	
	private static void outputResultSet(ResultSet rs, File file, String envName, String cacheName) throws SQLException {
		List<Map<String, Object>> resultList = getResultList(rs);
		if (cacheName != null) {
			cache.put(cacheName, resultList);
			System.out.println("cache set " + cacheName + " saved.");
		}
		outputResultList(resultList, file, envName);
	}
	
	private static void insertFromCache(String cacheName, String tableName) {
		List<Map<String, Object>> dataList = cache.get(cacheName);
		if (dataList == null) {
			System.out.println(cacheName + " not found in cache.");
		} else {
			List<String> sqlList = constructSql(tableName, dataList);
			
			for (String sql: sqlList) {
				execute(sql);
			}
		}
	}
	
	private static List<String> constructSql(String tableName, List<Map<String, Object>> dataList) {
		List<String> sqlList = new ArrayList<String>();
		String columnList = null;
		if (dataList.size() > 0) {
			columnList = getColomnList(dataList.get(0));
		}
		Map<String, Object> typeMap = dataList.get(0);
		for (int i = 1; i < dataList.size(); i++) {
			StringBuilder sql = new StringBuilder();
			sql.append(String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, columnList, getValueList(dataList.get(i), typeMap)));
			System.out.println(sql.toString());
			sqlList.add(sql.toString());
		}
		return sqlList;
	}
	
	private static String getColomnList(Map<String, Object> data) {
		StringBuilder list = new StringBuilder();
		
		int counter = 0;
		for (Iterator<String> i = data.keySet().iterator(); i.hasNext();) {
			if (counter > 0) {
				list.append(", ");
			}
			list.append(i.next());
			counter++;
		}
		
		return list.toString();
	}
	
	private static String getValueList(Map<String, Object> data, Map<String, Object> typeMap) {
		StringBuilder list = new StringBuilder();
		int counter = 0;
		for (Iterator<String> i = data.keySet().iterator(); i.hasNext();) {
			if (counter > 0) {
				list.append(", ");
			}
			String key = i.next();
			Object value = data.get(key);
			int type = (Integer) typeMap.get(key);
			String result = null;
			if (value == null) {
				result = "NULL";
			} else if (Types.DATE == type) {
				result = String.format("to_date('%s', 'yyyy-mm-dd')", value.toString().substring(0, 10));
			} else if (Types.TIMESTAMP == type) {
				result = String.format("to_timestamp('%s', 'yyyy-mm-dd hh24:mi:ss')", value.toString());
			} else {
				String valueString = value.toString();
				valueString = valueString.replaceAll("'", "''");
				result = String.format("'%s'", valueString);
			}
			list.append(result);
			counter++;
		}
		
		return list.toString();
	}
	
	private static void viewCache(String cacheName) {
		List<Map<String, Object>> dataList = cache.get(cacheName);
		if (dataList == null) {
			System.out.println(cacheName + " not found in cache.");
		} else {
			outputResultList(dataList, null, null);
		}
	}
	
	private static void removeCache(String cacheName) {
		if (cache.containsKey(cacheName)) {
			cache.remove(cacheName);
			System.out.println(cacheName + " removed from cache.");
		} else {
			System.out.println(cacheName + " not found in cache.");
		}
	}
	
	private static void viewCacheList() {
		
		StringBuilder res = new StringBuilder();
		int counter = 0;
		for (Iterator<String> i = cache.keySet().iterator(); i.hasNext();) {
			if (counter > 0) {
				res.append("\n");
			}
			res.append(i.next());
			counter ++;
		}
		if (res.toString().length() == 0) {
			System.out.println("cache is empty.");
		} else {
			System.out.println(res.toString());
		}
	}
	
	private static void execute(String command) {
		try {
			String line = command;
			while (line.endsWith(";")) {
				line = line.substring(0, line.length() - 1);
			}
			if (line.toLowerCase().equals("b") || line.toLowerCase().startsWith("back")) {
				STATUS = STATUS_NEED_ENV;
			} else if (line.startsWith("q")) {
				close();
			} else if (line.toLowerCase().startsWith("commit")) {
				commit();
			} else if (line.toLowerCase().startsWith("cachelist") || line.toLowerCase().startsWith("cl")) {
				viewCacheList();
			} else if (line.toLowerCase().startsWith("r ") || line.toLowerCase().startsWith("remove ")) {
				String cacheName = line.substring(line.indexOf(' ') + 1).trim();
				removeCache(cacheName);
			} else if (line.toLowerCase().startsWith("view ")) {
				String cacheName = line.substring("view ".length()).trim();
				viewCache(cacheName);
			} else if (line.toLowerCase().startsWith("insert from ")) {
				line = line.substring("insert from ".length()).trim();
				if (line.indexOf(' ') > 0) {
					String cacheName = line.substring(0, line.indexOf(' '));
					line = line.substring(line.indexOf(' ') + 1, line.length()).trim();
					if (line.toLowerCase().startsWith("to ")) {
						String tableName = line.substring("to ".length()).trim();
						insertFromCache(cacheName, tableName);
					}
				}
			} else if (line.toLowerCase().startsWith("update ") || line.toLowerCase().startsWith("insert ") || line.toLowerCase().startsWith("delete ")) {
				update(line);
			} else {
				String cacheName = null;
				if (line.toLowerCase().startsWith("cache ")) {
					line = line.substring(line.indexOf(' ') + 1).trim();
					cacheName = line.substring(0, line.indexOf(' ')); 
					line = line.substring(line.indexOf(' ') + 1).trim();
				}
				while (line.charAt(line.length() - 1) == ' ' || line.charAt(line.length() - 1) == ';') {
					line = line.substring(0, line.length() - 1);
				}
				query(line, cacheName);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private static void printEnvList() {
		String str = "Please select env (divided with ,)\n";
		int index = 0;
		for(Iterator<String> i = connectionConfig.keySet().iterator(); i.hasNext();) {
			String envName = i.next();
			str += ++index + ": " + envName + "\n";
		}
		System.out.println(str);
	}
	
	private static boolean selectEnv() {
		String command = getInput();
		String[] array = command.split(",", -1);
		Map<Integer, String> selectedEnvMap = new LinkedHashMap<Integer, String>();
		for (int i = 0; i < array.length; i++) {
			int index = -1;
			try {
				index = Integer.parseInt(array[i].trim());
			} catch (NumberFormatException e) {}
			if (index > 0 && index <= connectionConfig.size()) {
				String envName = getEnvNameByIndex(index);
				selectedEnvMap.put(index, envName);
			}
		}
		boolean succeed = false;
		if (selectedEnvMap.size() > 0) {
			initConnection(selectedEnvMap);
			succeed = true;
		} else {
			System.out.println("Please select valid env.");
		}
		return succeed;
	}
	
	private static String getEnvNameByIndex(int index) {
		String name = null;
		int counter = 0;
		for (Iterator<String> i = connectionConfig.keySet().iterator(); i.hasNext();) {
			String envName = i.next();
			if (++counter == index) {
				name = envName;
				break;
			}
		}
		return name;
	}
	
	private static void initConnection(Map<Integer, String> selectedEnvMap) {
		String str = "[";
		int counter = 0;
		for (Iterator<Integer> i = selectedEnvMap.keySet().iterator(); i.hasNext();) {
			Integer index = i.next();
			String envName = selectedEnvMap.get(index);
			if (counter > 0) {
				str += ", ";
			}
			str += index + ": " + envName;
			counter++;
		}
		str += "] selected\n";
		System.out.println(str);
		activeConnectionMap.clear();
		for (Iterator<Integer> i = selectedEnvMap.keySet().iterator(); i.hasNext();) {
			Integer index = i.next();
			String envName = selectedEnvMap.get(index);
			boolean initFailed = false;
			if (!connectionPool.containsKey(envName)) {
				try {
					initConnection(envName);
				} catch (SQLException e) {
					if (e.getMessage().startsWith("ORA-01017: invalid username/password")) {
						System.out.println("Warning: invalid username/password for env " + envName);
					} else {
						e.printStackTrace();
					}
					initFailed = true;
				}
			}
			if (!initFailed) {
				activeConnectionMap.put(envName, connectionPool.get(envName));
			}
		}
	}
	
	private static void initConnection(final String envName) throws SQLException {
		String[] config = connectionConfig.get(envName);
		try {
			System.out.println("connecting " + envName + "...");
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String jdbcUrl = config[0];
			String user = config[1];
			String password = config[2];
			Connection conn = java.sql.DriverManager.getConnection(jdbcUrl, user, password);
			conn.setAutoCommit(false);
			connectionPool.put(envName, conn);
			System.out.println("done");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	private static String getInput() {
		String line = null;
		try {
			line = reader.readLine();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return line;
	}
	
	public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException, InvalidFormatException {
		
		reader = new BufferedReader(new InputStreamReader(System.in));
		while(true) {
			try {
				if (STATUS_NEED_ENV == STATUS) {
					printEnvList();
					STATUS = STATUS_SELECTING_ENV;
				} else if (STATUS_SELECTING_ENV == STATUS) {
					while (!selectEnv());
					if (activeConnectionMap.size() == 0) {
						STATUS = STATUS_NEED_ENV;
					} else {
						STATUS = STATUS_ENV_CONFIRMED;
					}
				} else if (STATUS_ENV_CONFIRMED == STATUS) {
					String command = getInput();
					if (command.trim().length() > 0) {
						execute(command);
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}