package foundation.persist;

import java.math.BigDecimal;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import foundation.data.entity.Entity;
import foundation.data.entity.EntitySet;
import foundation.data.entity.EntityTree;
import foundation.data.getter.EntityGetter;
import foundation.data.getter.EntitySetGetter;
import foundation.data.getter.EntityTreeGetter;
import foundation.data.getter.ObjectLoader;
import foundation.data.getter.ValueLoader;
import foundation.data.object.Sequence;
import foundation.data.object.SequenceValue;
import foundation.persist.source.ConnectionAgent;
import foundation.persist.source.DataSourceManager;
import foundation.persist.source.NamedDataSource;
import foundation.server.config.DBaseType;


public class SQLRunner {
	
	protected static Logger logger;
	protected static ThreadLocal<ConnectionAgent> activeConn;

	static {
		logger = LogManager.getLogger(SQLRunner.class);
		activeConn = new ThreadLocal<ConnectionAgent>();
	}
	
	public static synchronized String beginTrans() throws SQLException {
		ConnectionAgent conn = activeConn.get();
		
		if (conn == null) {
			conn = DataSourceManager.getConnection();
			conn.setAutoCommit(false);
			
			activeConn.set(conn);
			return conn.getToken();
		}
		
		return "none";
	}
	
	public static void commit(String token) throws Exception {
		ConnectionAgent conn = activeConn.get();
		
		if (conn == null) {
			return;
		}
		
		if ((token != null) && !token.equals(conn.getToken())) {
			return;
		}
		
		try {
			conn.commit();
		}
		finally {
			try {
				conn.close();
			}
			catch (Exception e) {
			}
			
			activeConn.remove();	
		}
	}
	
	public static void commit() throws Exception {
		commit(null);
	}
	
	public static void rollback(String token) throws Exception {
		ConnectionAgent conn = activeConn.get();
		
		if (conn == null) {
			return;
		}
		
		if ((token != null) && !token.equals(conn.getToken())) {
			return;
		}
		
		try {
			conn.rollback();
		}
		finally {
			try {
				conn.close();
			}
			catch (Exception e) {
			}
			
			activeConn.remove();	
		}
	}
	
	public static void rollback() throws Exception {
		rollback(null);
	}
	
	public static int execSQL(NamedSQL namedSQL) throws Exception {
		return execSQL(null, namedSQL);
	}
	
	public static int execSQL(NamedDataSource dataSource, NamedSQL namedSQL) throws Exception {
		boolean closeFlag = true; ConnectionAgent conn = null;
		Statement stmt = null; int result = 0;
		
		//1. 根据数据库类型生成特定的SQL
		String sql = namedSQL.toString();
		logger.debug(sql);
		
		try {
			//2. 获取 connection
			if (dataSource != null) {
				conn = activeConn.get();
				
				if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
					closeFlag = false;
				}
				else {
					conn = dataSource.getConnectionAgent();
				}
			}
			else {
				conn = activeConn.get();
				closeFlag = conn == null;
				
				if (conn == null) {
					NamedDataSource defaultSource = DataSourceManager.getMain();
					conn = defaultSource.getConnectionAgent();
				}
			}
		
			//3. 执行SQL
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			result = stmt.executeUpdate(sql);
		} 
		catch (SQLException e) {
			onError(sql, e);
			throw e;
		}
		finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			} finally {
				if (closeFlag && conn != null) {
					conn.close();
				}
			}
		}
		
		return result;		
	}
	
	public static void getData(NamedSQL namedSQL, ILoadable loadable, Object ...args) throws Exception {
		getData(null, namedSQL, loadable, args);
	}
	
	public static void getData(IStepLoadable loadable, Object ...args) throws Exception {
		getData(null, loadable, args);
	}
	
	public static void getData(NamedDataSource dataSource, NamedSQL namedSQL, ILoadable loadable, Object ...args) throws Exception {
		boolean closeFlag = true; ConnectionAgent conn = null;
		Statement stmt = null; ResultSet rslt = null;
		
		//1. 根据数据库类型生成特定的SQL
		String sql = namedSQL.toString();
		logger.debug(sql);
		
		try {
			//2. 获取 connection
			if (dataSource != null) {
				conn = activeConn.get();
				
				if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
					closeFlag = false;
				}
				else {
					conn = dataSource.getConnectionAgent();
				}
			}
			else {
				conn = activeConn.get();
				closeFlag = conn == null;
				
				if (conn == null) {
					NamedDataSource defaultSource = DataSourceManager.getMain();
					conn = defaultSource.getConnectionAgent();
				}
			}
		
			//3. 执行SQL		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rslt = stmt.executeQuery(sql);
			
			loadable.load(rslt, args);
		} 
		catch (SQLException e) {
			logger.error("getDataFromDB error:" + e.getMessage());
			throw e;
		} 
		finally {
			try {
				if (rslt != null) {
					rslt.close();
				}
			} catch (SQLException e) {
			}
			finally {
				try {
					if (stmt != null) {
						stmt.close();
					}
				} catch (SQLException e) {
				}
				finally {
					if (closeFlag && conn != null) {
						conn.close();
					}
				}
			}
		}
	}
	
	public static void getData(NamedDataSource dataSource, IStepLoadable loadable, Object ...args) throws Exception {
		boolean closeFlag = true; ConnectionAgent conn = null;
		Statement stmt = null; ResultSet rslt = null;
		
		try {
			//1. 获取 connection
			if (dataSource != null) {
				conn = activeConn.get();
				
				if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
					closeFlag = false;
				}
				else {
					conn = dataSource.getConnectionAgent();
				}
			}
			else {
				conn = activeConn.get();
				closeFlag = conn == null;
				
				if (conn == null) {
					NamedDataSource defaultSource = DataSourceManager.getMain();
					conn = defaultSource.getConnectionAgent();
				}
			}
		
			//3. 执行SQL
			while (loadable.hasNextForLoad()) {
				//1. 根据数据库类型生成特定的SQL
				NamedSQL stepSQL = loadable.getStepSQL();
				String sql = stepSQL.toString();
				logger.debug(sql);
				
				stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
				try {
					try {
						rslt = stmt.executeQuery(sql);
						loadable.loadResultSet(rslt, args);
					}
					finally {
						if (rslt != null) {
							rslt.close();
						}
					}						
				}
				finally {
					if (stmt != null) {
						stmt.close();
					}
				}
			}
		}
		catch (SQLException e) {
			logger.error("getDataFromDB error:" + e.getMessage());
			throw e;
		} 
		finally {
			if (closeFlag && conn != null) {
				conn.close();
			}
		}
	}
	
	public static long getSequence(NamedDataSource dataSource, Sequence sequence, SequenceValue value) throws Exception {
		long result = 0;
		boolean closeFlag = true; ConnectionAgent conn = null;
		
		try {
			//1. 获取 connection
			if (dataSource != null) {
				conn = activeConn.get();
				
				if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
					closeFlag = false;
				}
				else {
					conn = dataSource.getConnectionAgent();
				}
			}
			else {
				conn = activeConn.get();
				closeFlag = conn == null;
				
				if (conn == null) {
					NamedDataSource defaultSource = DataSourceManager.getMain();
					conn = defaultSource.getConnectionAgent();
				}
			}
		
			result = sequence.exec(conn.getConn(), value);
		} 
		catch (SQLException e) {
			logger.error("getDataFromDB error:" + e.getMessage());
			throw e;
		} 
		finally {
			if (closeFlag && conn != null) {
				conn.close();
			}
		}
		
		return result;
	}
	
	public static void saveData(NamedSQL namedSQL1, NamedSQL namedSQL2, IDoubleSavable savable, Object ...args) throws Exception {
		saveData(null, namedSQL1, namedSQL2, savable, args);
	}
	
	public static void saveData(NamedSQL namedSQL, ISavable savable, Object ...args) throws Exception {
		saveData(null, namedSQL, savable, args);
	}
	
	public static void saveData(NamedDataSource dataSource, NamedSQL namedSQL, ISavable savable, Object ...args) throws Exception {
		boolean closeFlag = true; ConnectionAgent conn = null;
		PreparedStatement stmt = null; ResultSet rslt = null;
		
		//1. 根据数据库类型生成特定的SQL
		String sql = namedSQL.toString();
		logger.debug(sql);
		
		try {
			//2. 获取 connection
			if (dataSource != null) {
				conn = activeConn.get();
				
				if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
					closeFlag = false;
				}
				else {
					conn = dataSource.getConnectionAgent();
				}
			}
			else {
				conn = activeConn.get();
				closeFlag = conn == null;
				
				if (conn == null) {
					NamedDataSource defaultSource = DataSourceManager.getMain();
					conn = defaultSource.getConnectionAgent();
				}
			}
		
			//3. 执行SQL
			stmt = conn.prepareStatement(sql);
			savable.saveData(stmt, args);
		} 
		catch (SQLException e) {
			throw e;
		} 
		finally {
			try {
				if (rslt != null) {
					rslt.close();
				}
			} catch (SQLException e) {
			}
			finally {
				try {
					if (stmt != null) {
						stmt.close();
					}
				} catch (SQLException e) {
				}
				finally {
					if (closeFlag && conn != null) {
						conn.close();
					}
				}
			}
		}
	}
	
	public static void saveData(NamedDataSource dataSource, NamedSQL namedSQL1, NamedSQL namedSQL2, IDoubleSavable savable, Object ...args) throws Exception {
		PreparedStatement stmt1 = null, stmt2 = null;
		boolean closeFlag = true; ConnectionAgent conn = null;
		
		//1. 根据数据库类型生成特定的SQL
		String sql1 = namedSQL1.toString();
		logger.debug(sql1);
	
		String sql2 = namedSQL2.toString();
		logger.debug(sql2);
		
		try {
			//2. 获取 connection
			if (dataSource != null) {
				conn = activeConn.get();
				
				if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
					closeFlag = false;
				}
				else {
					conn = dataSource.getConnectionAgent();
				}
			}
			else {
				conn = activeConn.get();
				closeFlag = conn == null;
				
				if (conn == null) {
					NamedDataSource defaultSource = DataSourceManager.getMain();
					conn = defaultSource.getConnectionAgent();
				}
			}
			
			//3. 执行SQL
			stmt1 = conn.prepareStatement(sql1);
			stmt2 = conn.prepareStatement(sql2);
			
			savable.save(stmt1, stmt2, args);
		} 
		catch (SQLException e) {
			logger.error("getDataFromDB error:" + e.getMessage());
			throw e;
		} finally {
			try {
				if (stmt1 != null) {
					stmt1.close();
				}
			} catch (SQLException e) {
			}
			finally {
				try {
					if (stmt2 != null) {
						stmt2.close();
					}
				} catch (SQLException e) {
				}
				finally {
					if (closeFlag && conn != null) {
						conn.close();
					}
				}
			}
		}
	}
	
	public static void saveData(NamedSQL namedSQL, IStepSavable stepSavable, Object ...args) throws Exception {
		saveData(null, namedSQL, stepSavable, args);
	}
	
	public static void saveData(NamedDataSource dataSource, NamedSQL namedSQL, IStepSavable stepSavable, Object ...args) throws Exception {
		boolean closeFlag = true; ConnectionAgent conn = null;
		PreparedStatement stmt = null;
		
		//1. 根据数据库类型生成特定的SQL
		String sql = namedSQL.toString();
		logger.debug(sql);
		
		try {
			//2. 获取 connection
			if (dataSource != null) {
				conn = activeConn.get();
				
				if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
					closeFlag = false;
				}
				else {
					conn = dataSource.getConnectionAgent();
				}
			}
			else {
				conn = activeConn.get();
				closeFlag = conn == null;
				
				if (conn == null) {
					NamedDataSource defaultSource = DataSourceManager.getMain();
					conn = defaultSource.getConnectionAgent();
				}
			}
		
			//3. 执行SQL
			while (stepSavable.hasNextForSave()) {
				stmt = conn.prepareStatement(sql);
				
				try {
					stepSavable.save(stmt, args);
				}
				finally {
					if (stmt != null) {
						stmt.close();
					}
				}
			}
		} 
		catch (SQLException e) {
			logger.debug(sql);
			logger.error("getDataFromDB error:" + e.getMessage());
			throw e;
		} 
		finally {
			if (closeFlag && conn != null) {
				conn.close();
			}
		}
	}
	
	public static EntitySet getEntitySet(NamedSQL namedSQL) throws Exception {
		EntitySet result = null;
		
		EntitySetGetter entitySetLoader = new EntitySetGetter(null);
		getData(namedSQL, entitySetLoader);
		result = entitySetLoader.getDataSet();
		
		return result;
	}
	
	public static EntitySet getEntitySet(NamedSQL namedSQL, EntitySetGetter entitySetLoader) throws Exception {
		return getEntitySet(null, namedSQL, entitySetLoader);
	}
		
	public static EntitySet getEntitySet(NamedDataSource dataSource, NamedSQL namedSQL, EntitySetGetter entitySetLoader) throws Exception {
		EntitySet result = null;
		
		getData(dataSource, namedSQL, entitySetLoader);
		result = entitySetLoader.getDataSet();
		
		return result;
	}

	public static EntityTree getEntityTree(NamedSQL namedSQL) throws Exception {
		EntityTree result = null;
		
		EntityTreeGetter entityTreeLoader = new EntityTreeGetter(null);
		getData(namedSQL, entityTreeLoader);
		result = entityTreeLoader.getEntityTree();
		
		return result;
	}
	
	public static EntityTree getEntityTree(NamedDataSource dataSource, NamedSQL namedSQL, EntityTreeGetter entityTreeLoader) throws Exception {
		EntityTree result = null;
		
		getData(dataSource, namedSQL, entityTreeLoader);
		result = entityTreeLoader.getEntityTree();
		
		return result;
	}
	
	public static Entity getEntity(NamedSQL namedSQL) throws Exception {
		return getEntity(namedSQL, new EntityGetter());
	}
	
	public static Entity getEntity(NamedDataSource dataSource, NamedSQL namedSQL) throws Exception {
		return getEntity(dataSource, namedSQL, new EntityGetter());
	}
	
	public static Entity getEntity(NamedSQL namedSQL, EntityGetter entityLoader) throws Exception {
		return getEntity(null, namedSQL, entityLoader);
	}
	
	public static Entity getEntity(NamedDataSource dataSource, NamedSQL namedSQL, EntityGetter entityLoader) throws Exception {
		Entity result = null;
		
		getData(dataSource, namedSQL, entityLoader);
		result = entityLoader.getEntity();
		
		return result;
	}
	
	public static Object getObject(NamedDataSource dataSource, NamedSQL namedSQL, ObjectLoader objectLoader) throws Exception {
		Object result = null;
		
		getData(dataSource, namedSQL, objectLoader);
		result = objectLoader.getObject();
		
		return result;
	}
	
	public static String getString(NamedSQL namedSQL) throws Exception {
		return getString(null, namedSQL);
	}
	
	public static String getString(NamedDataSource dataSource, NamedSQL namedSQL) throws Exception {
		String result = null;
		
		ValueLoader valueLoader = new ValueLoader();
		getData(dataSource, namedSQL, valueLoader);
		result = valueLoader.getString();
		
		return result;
	}
	
	public static int getInteger(NamedSQL namedSQL) throws Exception {
		return getInteger(null, namedSQL);
	}
	
	public static int getInteger(NamedDataSource dataSource, NamedSQL namedSQL) throws Exception {
		int result = 0;
		
		ValueLoader valueLoader = new ValueLoader();
		getData(dataSource, namedSQL, valueLoader);
		result = valueLoader.getInt();
		
		return result;
	}
	
	public static BigDecimal getBigDecimal(NamedSQL namedSQL) throws Exception {
		return getBigDecimal(null, namedSQL);
	}

	public static BigDecimal getBigDecimal(NamedDataSource dataSource, NamedSQL namedSQL) throws Exception {
		BigDecimal result = null;
		
		ValueLoader valueLoader = new ValueLoader();
		getData(dataSource, namedSQL, valueLoader);
		result = valueLoader.getBigDecimal();
		
		return result;
	}
	
	public static Date getDate(NamedSQL namedSQL) throws Exception {
		return getDate(null, namedSQL);
	}
	
	public static Date getDate(NamedDataSource dataSource, NamedSQL namedSQL) throws Exception {
		Date result = null;
		
		ValueLoader valueLoader = new ValueLoader();
		getData(dataSource, namedSQL, valueLoader);
		result = valueLoader.getDate();
		
		return result;
	}
	
	public static void getTableMetaData(NamedDataSource dataSource, IMetaDataLoader loader, NamedSQL sql) throws Exception {
		getFieldsBySQL(dataSource, loader, sql);
	}
	
	public static List<String> getTableList() throws SQLException {
		return getTableList(null);
	}
	
	public static List<String> getTableList(NamedDataSource dataSource) throws SQLException {
		List<String> result = new ArrayList<String>();
		boolean closeFlag = true; ConnectionAgent conn = null; ResultSet rslt = null;
		
		try {
			//1. 获取 connection
			if (dataSource != null) {
				conn = activeConn.get();
				
				if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
					closeFlag = false;
				}
				else {
					conn = dataSource.getConnectionAgent();
				}
			}
			else {
				conn = activeConn.get();
				closeFlag = conn == null;
				
				if (conn == null) {
					NamedDataSource defaultSource = DataSourceManager.getMain();
					conn = defaultSource.getConnectionAgent();
				}
			}
			
			DatabaseMetaData metaData = conn.getMetaData();
			rslt = metaData.getTables(null, null, null, new String[]{"TABLE"});
	        
	        while(rslt.next()){
	            String tableName = rslt.getString("TABLE_NAME");
	            result.add(tableName);
	        }
		} 
		catch (Exception e) {
		} 
		finally {
			try {
				if (rslt != null) {
					rslt.close();					
				}
			} 
			catch (SQLException e) {
			} 
			finally {
				if (closeFlag && conn != null) {
					conn.close();
				}
			}
		}
		
		return result;
	}
	
	public static void getFieldsBySQL(NamedDataSource dataSource, IMetaDataLoader loader, NamedSQL namedSQL) throws Exception {
		boolean closeFlag = true; ConnectionAgent conn = null;
		Statement stmt = null; ResultSet rslt = null;
		
		//1. 根据数据库类型生成特定的SQL
		String sql = namedSQL.toString();
		logger.debug(sql);
		
		try {
			//2. 获取 connection
			if (dataSource != null) {
				conn = activeConn.get();
				
				if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
					closeFlag = false;
				}
				else {
					conn = dataSource.getConnectionAgent();
				}
			}
			else {
				conn = activeConn.get();
				closeFlag = conn == null;
				
				if (conn == null) {
					NamedDataSource defaultSource = DataSourceManager.getMain();
					conn = defaultSource.getConnectionAgent();
				}
			}
		
			//3. 执行SQL
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			
			rslt = stmt.executeQuery(sql);
			ResultSetMetaData result = rslt.getMetaData();
			loader.load(result);
		} 
		catch (Exception e) {
			onError(sql, e);
			throw e;
		} 
		finally {
			try {
				if (rslt != null) {
					rslt.close();					
				}
			} 
			catch (SQLException e) {
			} 
			finally {
				try {
					if (stmt != null) {
						stmt.close();						
					}
				} 
				catch (SQLException e) {
				} 
				finally {
					if (closeFlag && conn != null) {
						conn.close();
					}
				}
			}
		}
	}
	
	public static void getFieldsByMeta(NamedDataSource dataSource, IMetaDataLoader loader, String tableName) throws Exception {
		boolean closeFlag = true; ConnectionAgent conn = null; ResultSet rslt = null;
		
		try {
			//1. 获取 connection
			if (dataSource != null) {
				conn = activeConn.get();
				
				if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
					closeFlag = false;
				}
				else {
					conn = dataSource.getConnectionAgent();
				}
			}
			else {
				conn = activeConn.get();
				closeFlag = conn == null;
				
				if (conn == null) {
					NamedDataSource defaultSource = DataSourceManager.getMain();
					conn = defaultSource.getConnectionAgent();
				}
			}

			DatabaseMetaData databaseMetaData = conn.getMetaData();
			rslt = databaseMetaData.getColumns(null, "%", tableName, null);
			
			loader.load(rslt);
		} 
		catch (Exception e) {
			throw e;
		} 
		finally {
			try {
				if (rslt != null) {
					rslt.close();					
				}
			} 
			catch (SQLException e) {
			} 
			finally {
				if (closeFlag && conn != null) {
					conn.close();
				}
			}
		}
	}
	
	public static boolean isTableExists(String tableName) throws Exception {
		if (tableName == null) {
			return false;
		}
		
		return isTableExists(null, tableName);
	}
	
	public static boolean isTableExists(NamedDataSource dataSource, String tableName) throws Exception {
		String sql = null; Statement stmt = null; ResultSet rslt = null;
		boolean closeFlag = true; ConnectionAgent conn = null;
		
		try {
			//1. 获取 connection
			if (dataSource != null) {
				conn = activeConn.get();
				
				if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
					closeFlag = false;
				}
				else {
					conn = dataSource.getConnectionAgent();
				}
			}
			else {
				conn = activeConn.get();
				closeFlag = conn == null;
				
				if (conn == null) {
					NamedDataSource defaultSource = DataSourceManager.getMain();
					conn = defaultSource.getConnectionAgent();
				}
			}

			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			
			DBaseType type = conn.getDBaseType();
			tableName = tableName.toLowerCase();
			
			if (type.isSQLServer()) {
				sql = "select * from sys.all_objects where object_id = OBJECT_ID(N'" + tableName + "') and type = 'U'";
			}
			else if (type.isMySQL()) {
				sql = "select * from information_schema.tables where table_name = '" + tableName + "' and table_type = 'BASE TABLE'";
			}
			else if (type.isOracle()){
				sql = "select 1 from user_tables where table_name ='" + tableName.toUpperCase() + "'";
			}
			else {
				throw new Exception("not support data base");
			}
			
			rslt = stmt.executeQuery(sql);
			
			if (rslt.next()) {
				return true;
			}
			
			return false;
		} 
		catch (Exception e) {
			onError(sql, e);
			throw e;
		} 
		finally {
			try {
				if (rslt != null) {
					rslt.close();					
				}
			} 
			catch (SQLException e) {
			} 
			finally {
				try {
					if (stmt != null) {
						stmt.close();						
					}
				} 
				catch (SQLException e) {
				} 
				finally {
					if (closeFlag && conn != null) {
						conn.close();
					}
				}
			}
		}
	}
	
	public static void formatOracleTables() throws SQLException {
		List<String> tableList = new ArrayList<String>();
		ResultSet rslt = null; String tableName = null;
		
		ConnectionAgent conn = DataSourceManager.getConnection();
		
		try {
			//1. 获取表列表
			DatabaseMetaData metaData = conn.getMetaData();
			rslt = metaData.getTables(null, "C##GRAND", null, new String[]{"TABLE"});
	        
	        while(rslt.next()){
	            tableName = rslt.getString("TABLE_NAME");
	            tableList.add(tableName);
	        }
	        
	        //2. 依次修改每个表
	        for (String table: tableList) {
	        	formatOracleOneTable(conn, table);
	        }
		}
		catch (Exception e) {
		} 
		finally {
			conn.close();
		}		
	}
	
	private static void formatOracleOneTable(ConnectionAgent conn, String tableName) throws SQLException {
		if (tableName == null) {
			return;
		}
		
		String fieldName = null;
		
		try {
			Statement stmt = null; ResultSet rslt = null;
			String upperTable = tableName.toUpperCase();
			
			//1. 修改表名
			if (!tableName.equals(upperTable)) {
				stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
				stmt.executeUpdate("ALTER TABLE \"" + tableName + "\" RENAME TO " + tableName);
			}
			
			//2. 修改字段
			if (stmt == null) {
				stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			}

			rslt = stmt.executeQuery("select * from " + tableName);
			ResultSetMetaData resultMeta = rslt.getMetaData();
			
			int max = resultMeta.getColumnCount();
			String upperField;
			
			Statement updateStmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			
			for (int i = 1; i <= max; i++) {
				fieldName = resultMeta.getColumnLabel(i);
				upperField = fieldName.toUpperCase();
				
				if (fieldName.equals(upperField)) {
					continue;
				}
				
				updateStmt.executeUpdate("ALTER TABLE " + tableName + " RENAME COLUMN \"" + fieldName + "\" TO " + fieldName);
			}			
		}
		catch (Exception e) {
			logger.error("format table:{}, field:{} error", tableName, fieldName);
			e.printStackTrace();
		} 		
	}

	protected static void onError(String sql, Exception e) {
		logger.error("executor exec error: " + e.getMessage());
	}
}