package com.sitech.ismp.coll;

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Vector;

public class SQLTarget extends CollBase {
	Logger logger = Logger.getLogger(SQLTarget.class);
	String ObjectIP = null;

	String DBType = "ORACLE";
//	String Drivers = "oracle.jdbc.driver.OracleDriver";
	String Drivers = "oracle.jdbc.OracleDriver";
	String driverpre = "jdbc:oracle:thin:@";
	String ObjectIPPort = "1521";
	String SIDName = null;
	String user = null;
	String passwd = null;
	String cUrl = null;

	boolean state = false;
    private Connection dbConnection = null;

    public static Map<String, Connection> connectionsMap = new HashMap<String, Connection>();

    public SQLTarget() {
		
	}

	public SQLTarget(String IP, String cUser, String cPasswd) {
		init(IP, "1521", cUser, cPasswd);
	}

	public SQLTarget(String IP, int cPort, String cUser, String cPasswd) {
		init(IP, String.valueOf(cPort), cUser, cPasswd);
	}

	public SQLTarget(String IP, String cPort, String cUser, String cPasswd) {
		init(IP, cPort, cUser, cPasswd);
	}
	
	public SQLTarget(String IP,String cPort,String sid, String cUser, String cPasswd) {
		init(IP, cPort, sid, cUser, cPasswd);
	}

	public void init(String IP) {
		this.ObjectIP = IP;
		setDBIP(IP);
	}

	public void init(String IP, String cSID, String cUser, String cPasswd) {
		KPISet.clear();
		setDBIP(IP);
		// setPort(cPort );
		setSIDName(cSID);
		setUser(cUser);
		setPasswd(cPasswd);
	}

	public void init(String IP, String cPort, String cSID, String cUser,
			String cPasswd) {
		KPISet.clear();
		setDBIP(IP);
		setPort(cPort);
		setSIDName(cSID);
		setUser(cUser);
		setPasswd(cPasswd);
	}

	public void init(String cUrl, String cUser, String cPasswd) {
		KPISet.clear();
		setUrl(cUrl);
		setUser(cUser);
		setPasswd(cPasswd);
	}

    public void initWithClassname(String dbType, String className, String dburl, String dbuser, String passwd) {
        setDBType(dbType);
        setDrivers(className);
        setUrl(dburl);
        setUser(dbuser);
        setPasswd(passwd);
    }

	/*
	 * public void init(String IP,String cSID,String cUser, String cPasswd){
	 * this.ObjectIP = IP; setDBIP(IP ); setSIDName(cSID); setUser(cUser);
	 * setPasswd(cPasswd); }
	 */

    public void setDBType(String DBType) {
        this.DBType = DBType;
    }

    public void setDBIP(String ObjectIP) {
		this.ObjectIP = ObjectIP;
	}

	public void setDrivers(String Drivers) {
		this.Drivers = Drivers;
	}

	public void setPort(String cPort) {
		this.ObjectIPPort = cPort;
	}

	public void setSIDName(String cSIDName) {
		this.SIDName = cSIDName;
	}

	public void setUser(String cUser) {
		this.user = cUser;
	}

	public void setPasswd(String cPasswd) {
		this.passwd = cPasswd;
	}

	public void setUrl(String cUrl) {
		this.cUrl = cUrl;
	}


	/**
	 * 获得数据库连接
	 * @return
	 */
	public Connection getConnection() {
		String url = null;
		if (cUrl == null) {
			url = this.driverpre + ObjectIP + ":" + ObjectIPPort + ":" + SIDName;
		} else {
			url = cUrl;
		}

        dbConnection = connectionsMap.get(ObjectIP + "_" + user);
        if (dbConnection == null) {
            try {
//                if (dbConnection == null || dbConnection.isClosed()) {
                logger.info("-- create new " + DBType + " Connection,Url=" + url);
                Class.forName(Drivers);
                dbConnection = DriverManager.getConnection(url, user, passwd);
                logger.info("-- create new " + DBType + " Connection Success!!!");
//                }
                state = true;
                connectionsMap.put(ObjectIP + "_" + user, dbConnection);
            } catch (Exception e) {
                state = false;
                logger.info("-- create new " + DBType + " Connection Failed!!! url=" + url + " user=" + user + " passwd=" + passwd, e);
            }
        } else {
//            connectionsMap.remove(ObjectIP + "_" + user);
            try {
                if (dbConnection.isClosed()) {
                    logger.info("-- connection lose effectiveness, recreate new " + DBType + " Connection, DBUrl=" + url);
                    Class.forName(Drivers);
                    dbConnection = DriverManager.getConnection(url, user, passwd);
                    logger.info("-- recreate new " + DBType + " Connection Success!!!");
                }
                connectionsMap.put(ObjectIP + "_" + user, dbConnection);
                state = true;
            } catch (Exception e) {
                state = false;
                logger.info("--recreate new " + DBType + " Connection Failed!!! url=" + url + " user=" + user + " passwd=" + passwd, e);
            }
        }
		return dbConnection;
	}

	public void releaseResources() {
		try {
			if (dbConnection != null && !dbConnection.isClosed()) {
				dbConnection.close();
			}
		} catch (SQLException se) {
			se.printStackTrace();
		}
	}

	public Vector getKPISet(String sql) {
		Vector recordSet = new Vector();
		PreparedStatement pstmt = null;
		ResultSet result = null;
		if (!getState()) {
			return recordSet;
		}

		try {
			pstmt = dbConnection.prepareStatement(sql);
		} catch (SQLException e) {
			throw new RuntimeException("Exception while exec sql :" + sql, e);
		}

		boolean hasResult = false;
		try {
			hasResult = pstmt.execute();
		} catch (SQLException e) {
			logger.error("pstmt.execute() has error!",e);
			e.printStackTrace();
		}
		try {

			if (hasResult) {
				result = pstmt.getResultSet();
				ResultSetMetaData rsmd = result.getMetaData();
				int iColumnCount = rsmd.getColumnCount();
				while (result.next()) {
					Vector vTemp = new Vector();
					for (int i = 0; i < iColumnCount; i++) {
						Object oTemp = result.getObject(i + 1);
						String sTemp = oTemp == null ? "" : oTemp.toString();
						vTemp.addElement(sTemp.trim());
					}
					recordSet.add(vTemp);
				}
                result.close();
			}
			pstmt.close();
		} catch (Exception e) {
			throw new RuntimeException("Exception while exec sql :" + sql, e);
		} finally {
				try {
					if(pstmt!=null){
						pstmt.close();
					}
					if(result!=null){
						result.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}

		}
		return recordSet;
	}

	public boolean getState() {
		getConnection();
		try {
			if (!state) {
                Thread.sleep(5000);// 间隔5s重试第二次连接
				System.err.println("--second--conn error----");
				getConnection();
				if (!state) {
					Thread.sleep(5000);// 再间隔5s重试第三次连接
                    System.err.println("--third--conn error----");
                    getConnection();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		    releaseResources();
		}
		return state;
	}

	public String getSQLKPISetInfo(String sql) {
		if (sql == null || sql.equals(""))
			return "";
		Vector result = getKPISet(sql);
		String sTable = "";
		for (int i = 0; i < result.size(); i++) {
			Vector row = (Vector) result.elementAt(i);
			String sRow = "";
			for (int j = 0; j < row.size(); j++) {
				sRow = sRow + "," + row.elementAt(j);
			}
			sTable = sTable + "\n" + sRow;
		}
		return sTable;
	}

	public Vector getSQLKPISet(String sql) {
		return getSQLKPISet(sql, 0);
	}

	public Vector getSQLKPISet(String sql, int iPos) {
		if (sql == null || sql.equals(""))
			return null;
		Vector result = getKPISet(sql);
		Vector tmp_result = new Vector();
		for (int i = 0; i < result.size(); i++) {
			Vector record = (Vector) result.elementAt(i);
			String kpiinfo = (String) record.elementAt(iPos);
			tmp_result.add(kpiinfo);
		}
		return tmp_result;
	}

	public Vector getColumn(Vector recordSet, int col) {
		Vector ret = new Vector();

		for (int i = 0; i < recordSet.size(); i++) {
			ret.add(((Vector) recordSet.elementAt(i)).elementAt(col));
		}
		return ret;
	}

	public Vector getSQLKPIResult(String sql) {
		if (sql == null || sql.equals("")){
			return null;
        }
		
		long beginTime = System.currentTimeMillis();

		Vector result =  getKPISet(sql);
		
		long used = System.currentTimeMillis() - beginTime;
		logger.info("-- used " + used + "/1000 seconds," + sql);

		return result;
	}

	public String getSQLKPIInfo(String sql) {
		if (sql == null || sql.equals(""))
			return "";
		String kpiinfo = "";
		Vector result = getKPISet(sql);

		if (result.size() > 0) {
			Vector record = (Vector) result.elementAt(0);
			kpiinfo = (String) record.elementAt(0);
		}
		System.out.println("kpiinfo=" + kpiinfo);
		return kpiinfo;
	}

	/**
	 * 释放数据库连接
	 */
	public void release() {
		releaseResources();
	}

}