package com.connor.jingdiao.util;

import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

public class SqlTIUtil {
	public static Connection connection = null;
	public static PreparedStatement ps = null;
	public static ResultSet rs = null;
//	public static Registry reg = Registry.getRegistry(SqlUtil.class);
	public static int ORACLE = 1;
	public static int SQLSERVER = 2;

//	public static Connection getConnection(int dbType) {
//		if (dbType == ORACLE) {
//			return getOracleConnection();
//		} else if (dbType == SQLSERVER) {
//			return getSqlserverConnection();
//		}
//		return null;
//	}

	public static Date getDate(java.sql.Date date) {
		if (date == null) {
			return null;
		}
		return new Date(date.getTime());
	}

	/**
	 * 链接数据库
	 
	public static Connection getSqlserverConnection() {
		String driver = reg.getString("SQL.SQLSERVER.DRIVER");
		String url = reg.getString("SQL.SQLSERVER.URL");
		String user = reg.getString("SQL.SQLSERVER.USER");
		String password = reg.getString("SQL.SQLSERVER.PASSWORD");
		System.out.println("连接数据库信息：");
		System.out.println("DRIVER: " + driver);
		System.out.println("URL: " + url);
		System.out.println("USER: " + user);
		if (connection != null) {
			try {
				connection.close();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			connection = null;
		}
		try {
			// 加载驱动程序
			Class.forName(driver);
			connection = DriverManager.getConnection(url, user, password);
			connection.setAutoCommit(false);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return connection;
	}*/

	public static Connection getSqlserverConnection(String driver, String url, String user, String password) {
//		String driver = reg.getString("SQL.SQLSERVER.DRIVER");
//		String url = reg.getString("SQL.SQLSERVER.URL");
//		String user = reg.getString("SQL.SQLSERVER.USER");
//		String password = reg.getString("SQL.SQLSERVER.PASSWORD");
		System.out.println("连接数据库信息：");
//		System.out.println("DRIVER: " + driver);
//		System.out.println("URL: " + url);
//		System.out.println("USER: " + user);
		if (connection != null) {
			try {
				connection.close();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			connection = null;
		}
		try {
			// 加载驱动程序
			Class.forName(driver);
			connection = DriverManager.getConnection(url, user, password);
			connection.setAutoCommit(false);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return connection;
	}

	public static Connection getOracleConnection(String url, String user, String password) {
		String driver = "oracle.jdbc.driver.OracleDriver";
		System.out.println("连接数据库信息：");
		System.out.println("DRIVER: " + driver);
		System.out.println("URL: " + url);
		System.out.println("USER: " + user);
		if (connection != null) {
			try {
				connection.close();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			connection = null;
		}
		try {
			// 加载驱动程序
			Class.forName(driver);
			// 1.getConnection()方法，连接MySQL数据库！！
			connection = DriverManager.getConnection(url, user, password);
			connection.setAutoCommit(false);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return connection;
	}

	/*
	public static Connection getOracleConnection() {
		// String driver = reg.getString("SQL.ORACLE.DRIVER");
		String url = reg.getString("SQL.ORACLE.URL");
		String user = reg.getString("SQL.ORACLE.USER");
		String password = reg.getString("SQL.ORACLE.PASSWORD");
//		if(prefs!=null&&prefs.length==5) {
//			url = "jdbc:oracle:thin:@"+prefs[0]+":"+prefs[1]+":"+prefs[2];
//			user = prefs[3];
//			password = prefs[4];
//		}
		return getOracleConnection(url, user, password);
	}*/

	/**
	 * 私有构造器不能new对象，防止误用对象
	 */
	private SqlTIUtil() {
	}

	/**
	 * 拼接SELECT 语句
	 * 
	 * @param tableName
	 * @param selectElement
	 * @param args
	 * @return
	 */
	public static String getSelectSql(String tableName, String[] selectElement, String... args) {
		StringBuffer valuesSB = new StringBuffer("SELECT   ");
		if (selectElement != null) {
			for (String element : selectElement) {
				valuesSB.append(element).append(",");
			}
			valuesSB.delete(valuesSB.length() - 1, valuesSB.length());
		}
		valuesSB.append(" FROM ");
		valuesSB.append(tableName);
		valuesSB.append(" WHERE");
		for (int i = 0; i < args.length; i++) {
			valuesSB.append(" ");
			valuesSB.append(args[i]);
			valuesSB.append("=? ");
			valuesSB.append("AND");
		}

		valuesSB.delete(valuesSB.length() - 3, valuesSB.length());
		return valuesSB.toString();
	}

	/**
	 * 拼接SELECT 语句
	 * 
	 * @param tableName
	 * @param args
	 * @return
	 */
	public static String getSelectSql(String tableName, String... args) {
		StringBuffer valuesSB = new StringBuffer("SELECT * FROM ");
		valuesSB.append(tableName);
		valuesSB.append(" WHERE");
		for (int i = 0; i < args.length; i++) {
			valuesSB.append(" ");
			valuesSB.append(args[i]);
			valuesSB.append("=? ");
			valuesSB.append("AND");
		}

		valuesSB.delete(valuesSB.length() - 3, valuesSB.length());
		return valuesSB.toString();
	}

	/**
	 * 拼接更新语句
	 * 
	 * @param tableName
	 * @param args
	 * @param args2
	 * @return
	 */
	public static String getUpdataSQL(String tableName, String[] args, String[] args2) {
		StringBuffer updateSB = new StringBuffer("UPDATE ");
		updateSB.append(tableName);
		updateSB.append(" SET ");
		// 拼接更新语句
		for (int i = 0; i < args.length; i++) {
			if (args[i].toUpperCase().equals("CREATED_DATE") || args[i].toUpperCase().equals("LAST_UPDATE_DATE")) {
				updateSB.append(args[i]).append("=to_date(?,'yyyy-MM-dd HH24:mi:ss') ,");
			} else {
				updateSB.append(args[i]).append("=? ,");
			}
		}
		updateSB.delete(updateSB.length() - 2, updateSB.length());
		;
		updateSB.append(" WHERE ");
		for (int i = 0; i < args2.length; i++) {
			updateSB.append(args2[i]).append("=? AND ");
		}
		updateSB.delete(updateSB.length() - 4, updateSB.length());
		return updateSB.toString();
	}

	/**
	 * 拼接SQL的insert语句
	 * 
	 * @param tableName
	 * @param args
	 * @return
	 */
	public static String getInsertSql(String tableName, String... args) {
		StringBuffer insertSql = new StringBuffer("insert into ");
		StringBuffer values = new StringBuffer("values(");

		if (tableName != null && args != null && args.length > 0) {
			insertSql.append(tableName);
			insertSql.append("(");
			for (int i = 0; i < args.length; i++) {
				insertSql.append(args[i]);
				insertSql.append(", ");
				if (args[i].toUpperCase().equals("LAST_UPDATE_DATE") || args[i].toUpperCase().equals("CREATED_DATE")) {
					values.append("to_date(?,'yyyy-MM-dd HH24:mi:ss'), ");
				} else {
					values.append("?, ");
				}

			}
		} else {
			return null;
		}
		insertSql.delete(insertSql.length() - 2, insertSql.length());
		values.delete(values.length() - 2, values.length());
		insertSql.append(") ").append(values).append(")");
		return insertSql.toString();
	}

	/**
	 * 得到创建表格数据库语句
	 * 
	 * @param tableName
	 * @param args
	 *            参数的名称
	 * @param args2
	 *            参数的类型
	 * @return 拼接后的创建数据库的语句
	 */
	public final static String GetCreateTableSQL(String tableName, String[] args, String[] args2) {

		if (args == null || args2 == null || args.length != args2.length) {
			System.out.println("THE INPUT PRAGREMS IS ERROR");
			return null;
		}
		StringBuffer createSQL = new StringBuffer("create table ");
		createSQL.append(tableName);
		createSQL.append("(");
		for (int i = 0; i < args.length; i++) {
			createSQL.append(args[i] + " ");
			createSQL.append(args2[i] + ", ");
		}
		createSQL.delete(createSQL.length() - 2, createSQL.length());
		createSQL.append(")");
		return createSQL.toString();
	}

	/**
	 * 得到Statement
	 */
	public final static PreparedStatement getPs(String sql) throws Exception {
		return getPs(sql, null);
	}

	/**
	 * 得到Statement
	 */
	public final static PreparedStatement getPs(Object[] argments, String sql) throws Exception {
		return getPs(sql, argments);
	}

	/**
	 * 得到Statement
	 */
	public final static PreparedStatement getPs(String sql, Object[] argments) throws Exception {
		SqlUtil.ps = SqlUtil.connection.prepareStatement(sql);
		if (argments != null) {
			for (int i = 0; i < argments.length; i++) {
				SqlUtil.ps.setObject(i + 1, argments[i]);
			}
		}
		return SqlUtil.ps;
	}

	/**
	 * 增加
	 */
	public final static int write(String sql) {
		return write(sql, null);
	}

	/**
	 * 增加
	 */
	public final static int write(Object[] argments, String sql) {
		return write(sql, argments);
	}

	/**
	 * 增加
	 */
	public final static int write(String sql, Object[] argments) {

		return update(sql, argments);
	}

	/**
	 * 删除
	 */
	public final static int delete(String sql) {
		return delete(sql, null);
	}

	/**
	 * 删除
	 */
	public final static int delete(Object[] argments, String sql) {
		return delete(sql, argments);
	}

	/**
	 * 删除
	 */
	public final static int delete(String sql, Object[] argments) {
		return update(sql, argments);
	}

	/**
	 * 修改
	 */
	public final static int update(String sql) {
		return update(sql, null);
	}

	/**
	 * 修改
	 */
	public final static int update(String[] argments, String sql) {
		return update(sql, argments);
	}

	/**
	 * 修改(增删改最终都要调用此方法，此处进行异常处理，关闭除Connection以为的对象)
	 */
	public final static int update(String sql, Object[] argments) {
		int i = -1;
		try {
			i = SqlUtil.getPs(argments, sql).executeUpdate();
			SqlUtil.connection.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			free(SqlUtil.rs, SqlUtil.ps);
		}
		return i;
	}

	/**
	 * 查询
	 */
	public final static ResultSet read(String sql) throws Exception {
		return read(sql, null);
	}

	/**
	 * 查询
	 */
	public final static ResultSet read(Object[] argments, String sql) throws Exception {
		return read(sql, argments);
	}

	/**
	 * 查询(所有查询最终都要调用此方法，方法里面不处理异常，抛出后由调用者处理，方便在finally语句中释放资源)
	 * 
	 * @throws SQLException
	 */
	public final static ResultSet read(String sql, Object[] argments) throws Exception {
		System.out.println("argments=" + argments.length);
		return SqlUtil.rs = SqlUtil.getPs(argments, sql).executeQuery();
	}

	/**
	 * 创建表
	 */
	public final static boolean createTable(String sql) {
		return go(sql, null);
	}

	/**
	 * 删除表
	 */
	public final static boolean dropTable(String sql) {
		return go(sql, null);
	}

	/**
	 * 修改表
	 */
	public final static boolean alterTable(String sql) {
		return go(sql, null);
	}

	/**
	 * 此方法用来执行DDL语句(创建表,修改表,删除表)
	 */
	private final static boolean go(String sql, Object[] argments) {
		boolean flag = false;
		try {
			flag = SqlUtil.getPs(sql, argments).execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			free(SqlUtil.rs, SqlUtil.ps);
		}
		if (flag) {
			try {
				SqlUtil.connection.commit();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return flag;
	}

	/**
	 * 释放资源
	 */
	public final static void free(ResultSet resultSet) {
		free(resultSet, null, null);
	}

	/**
	 * 释放资源
	 */
	public final static void free(Statement statement) {
		free(null, statement, null);
	}

	/**
	 * 释放资源
	 */
	public final static void free(Connection connection) {
		free(null, null, connection);
	}

	/**
	 * 释放资源
	 */
	public final static void free(ResultSet resultSet, Statement statement) {
		free(resultSet, statement, null);
	}

	/**
	 * 释放资源
	 */
	public final static void free(Statement statement, Connection connection) {
		free(null, statement, connection);
	}

	/**
	 * 释放资源(不传参数默认部分)
	 */
	public final static void free() {
		free(SqlUtil.rs, SqlUtil.ps);
	}

	/**
	 * 释放资源(全部释放)
	 */
	public final static void freeAll() {
		free(SqlUtil.rs, SqlUtil.ps, SqlUtil.connection);
	}

	/**
	 * 释放资源(传满参数也要全部释放)
	 */
	public final static void free(ResultSet resultSet, Statement statement, Connection connection) {
		try {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} finally {
			try {
				if (statement != null) {
					try {
						statement.close();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			} finally {
				if (connection != null) {
					try {
						connection.close();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		}
	}
	public static List<HashMap<String, Object>> getDBInfo(Connection connection, String sql) throws Exception {
		if (connection == null) {
			throw new Exception("无法获取数据库连接信息");
		}

		List<HashMap<String, Object>> vecData = new ArrayList<HashMap<String, Object>>();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(sql);

		ResultSetMetaData meta = rs.getMetaData();
		System.out.println("meta："+meta.getColumnCount());
		String[] columnNames = new String[meta.getColumnCount()];
		int[] columnTypes = new int[meta.getColumnCount()];

		for (int i = 0; i < columnNames.length; i++) {
			columnNames[i] = meta.getColumnName(i + 1);
			columnTypes[i] = meta.getColumnType(i + 1);
			System.out.println("columnTypes："+i+"=="+columnNames[i]);
		}

		while (rs.next()) {
			HashMap<String, Object> dataTable = new HashMap<String, Object>();

			for (int i = 1; i <= columnNames.length; i++) {
				Object value = null;
				switch (columnTypes[i-1]) {
				case Types.NVARCHAR:
				case Types.VARCHAR:
					value = rs.getString(i);//columnNames[i]
					if (value == null) {
						value = "";
					}
					break;
				case Types.INTEGER:
				case Types.NUMERIC:
					value = rs.getInt(i);
					break;
				case Types.DATE:
					Timestamp timestamp = rs.getTimestamp(i);
					if (timestamp != null) {
						value = new Date(timestamp.getTime());
					}
					break;
				case Types.TIMESTAMP:
					value = rs.getTimestamp(i);
					break;
				case Types.CLOB:
					Clob clob = rs.getClob(i);
					if (clob != null) {
						Reader inStream = clob.getCharacterStream();
						char[] c = new char[(int) clob.length()];
						inStream.read(c);
						value = new String(c);
						inStream.close();
					}
					if (value == null) {
						value = "";
					}
					break;
				default:
					value = "";
					break;
				}
//				if (value == null) {
//					value = "";
//				}
				dataTable.put(columnNames[i-1], value);
//				System.out.println("data："+columnNames[i]+"=="+columnTypes[i]);
			}
			vecData.add(dataTable);
		}
		rs.close();
		statement.close();
		return vecData;
	}
	public static List<Object[]> getArrayDBInfo(Connection connection, String sql) throws Exception {
		if (connection == null) {
			throw new Exception("无法获取数据库连接信息");
		}

		List<Object[]> vecData = new LinkedList<Object[]>();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(sql);

		ResultSetMetaData meta = rs.getMetaData();
		String[] columnNames = new String[meta.getColumnCount()];
		int[] columnTypes = new int[meta.getColumnCount()];

		for (int i = 0; i < columnNames.length; i++) {
			columnNames[i] = meta.getColumnName(i + 1);
			columnTypes[i] = meta.getColumnType(i + 1);
		}

		while (rs.next()) {
			Object[] result = new Object[columnNames.length];

			for (int i = 0; i < columnNames.length; i++) {
				Object value = null;
				switch (columnTypes[i]) {
				case Types.VARCHAR:
					value = rs.getString(columnNames[i]);
					if (value == null) {
						value = "";
					}
					break;
				case Types.INTEGER:
				case Types.NUMERIC:
					value = rs.getInt(columnNames[i]);
					break;
				case Types.DATE:
					Timestamp timestamp = rs.getTimestamp(columnNames[i]);
					if (timestamp != null) {
						value = new Date(timestamp.getTime());
					}
					break;
				case Types.TIMESTAMP:
					value = rs.getTimestamp(columnNames[i]);
					break;
				case Types.CLOB:
					Clob clob = rs.getClob(columnNames[i]);
					if (clob != null) {
						Reader inStream = clob.getCharacterStream();
						char[] c = new char[(int) clob.length()];
						inStream.read(c);
						value = new String(c);
						inStream.close();
					}
					if (value == null) {
						value = "";
					}
					break;
				default:
					value = "";
					break;
				}
//				if (value == null) {
//					value = "";
//				}
//				dataTable.put(columnNames[i], value);
//				System.out.println("data："+columnNames[i]+"=="+columnTypes[i]);
				result[i] = value;
			}
			vecData.add(result);
		}
		rs.close();
		statement.close();
		return vecData;
	}
}
