package com.sitech.ismp.coll.busi.util;

/**
 * JDBC数据库连接池管理通用类
 * @author huangkai
 *
 */

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public final class JDBCPoolManager {

	private static Logger logger = Logger.getLogger("BUSI_COLL");
	/** 实例 */
	private static JDBCPoolManager instance = null; 
	
	/** 池名与池对象 */
	private static HashMap<String, ComboPooledDataSource> mapPool = new HashMap<String, ComboPooledDataSource>(); 

	/**
	 * 获取实例
	 * 
	 * @return
	 */
	public synchronized static JDBCPoolManager getInstance() {
		if (instance == null) {
			instance = new JDBCPoolManager();
		}
		
		return instance;
	}

	/**
	 * 取得连接池中的连接
	 * 
	 * @param name
	 * @return
	 */
	public synchronized final Connection getConnection(String name) {
		logger.debug("getConnection(" + name + ")");
		logAllPoolStatus();
		Connection conn = null;
		if (mapPool != null && mapPool.get(name) != null) {
			try {
				ComboPooledDataSource cpds = (ComboPooledDataSource) mapPool.get(name);
				if (cpds == null) {
					return null;
				}
				conn = cpds.getConnection();
				conn.setAutoCommit(true);
				logger.debug(getPoolStats(name));
			} catch (SQLException e) {
				logger.error("Exception while get JDBC connection!!", e);
			}
		}
		return conn;
	}

	/**
	 * 释放连接
	 * 
	 * @param c
	 */
	public synchronized final void freeConnection(Connection c) {
		try {
			if (c != null) {
				c.close();
			}
			logger.debug("freeConnection");
		} catch (SQLException e) {
			logger.error("释放数据库连接发生异常！");
			logger.error(e);
			e.printStackTrace();
		}
	}

	/**
	 * 创建一个连接池
	 * 
	 * @param name
	 * @param driver
	 * @param url
	 * @param user
	 * @param passwd
	 */
	public synchronized final void createPool(TblDBLinkInfo tblDBLinkInfo) {
		String name = tblDBLinkInfo.getLinkName();
		String driver = tblDBLinkInfo.getClassName();
		String url = tblDBLinkInfo.getUrl();
		String user = tblDBLinkInfo.getUserid();
		String passwd = tblDBLinkInfo.getPasswd();
		logger.info("createPool[" + name + "]");
		if(mapPool.containsKey(name)) {
			return;
		}
		
		try {
			ComboPooledDataSource cpds = new ComboPooledDataSource("JDBCPoolManager");
			cpds.setDriverClass(driver);
			cpds.setJdbcUrl(url);
			cpds.setUser(user);
			cpds.setPassword(passwd);
			cpds.setMinPoolSize(tblDBLinkInfo.getMinPoolSize());
			cpds.setMaxPoolSize(tblDBLinkInfo.getMaxPoolSize());
			cpds.setMaxStatements(tblDBLinkInfo.getMaxStatements());
			cpds.setMaxIdleTime(tblDBLinkInfo.getMaxIdleTime());
			mapPool.put(name, cpds);
			logAllPoolStatus();
		} catch (Exception e) {
			logger.error("Exception while create ComboPooledDataSource", e);
		}
	}

	/**
	 * 释放连接池
	 */
	public synchronized final void freePool(String name) {
		try {
			if (mapPool != null && mapPool.get(name) != null) {
				ComboPooledDataSource cpds = (ComboPooledDataSource) mapPool.get(name);
				if (cpds != null) {
					cpds.close();
				}
				mapPool.remove(name);
			}
		} catch (Exception e) {
			logger.error("释放连接池" + name + "时发生异常！");
			logger.error(e);
			e.printStackTrace();
		}
	}

	/**
	 * 释放所有连接池
	 */
	public synchronized final void freeAllPool() {
		if (mapPool != null) {
			for (String name : mapPool.keySet()) {
				freePool(name);
			}
		}
	}

	/**
	 * 记录整个连接池的所有对象信息
	 */
	public final void logAllPoolStatus() {
		if (mapPool != null) {
			logger.debug("mapPool.size()=" + mapPool.size());
			for (String name : mapPool.keySet()) {
				logger.debug("Pool[" + name + "]=" + mapPool.get(name)
						+ ", state:" + getPoolStats(name));
				
				try {
					mapPool.get(name).sampleThreadPoolStackTraces();
					mapPool.get(name).sampleThreadPoolStatus();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				
			}
		}
	}

	/**
	 * 获取连接池状态信息
	 * 
	 * @param name
	 * @return
	 */
	public final String getPoolStats(String name) {
		DataSource ds = null;
		if (mapPool != null && mapPool.get(name) != null) {
			try {
				ds = (DataSource) mapPool.get(name);
			} catch (Exception e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		
		if (ds == null) {
			return "N/A";
		} else {
			return ds.toString();
		}
	}

	/**
	 * 例子 main
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		TblDBLinkInfo dbLink = new TblDBLinkInfo();

		try {
			dbLink.setLinkName("bamdb");
			dbLink.setUrl("jdbc:oracle:thin:@10.161.1.179:1521:BAM1");
			dbLink.setClassName("oracle.jdbc.driver.OracleDriver");
			dbLink.setUserid("dbbamadm");
			dbLink.setPasswd("dbba07");

			JDBCPoolManager.getInstance().createPool(dbLink);

			dbLink.setLinkName("bnmsdb");
			dbLink.setUrl("jdbc:oracle:thin:@10.161.6.22:1521:bnmsdb");
			dbLink.setClassName("oracle.jdbc.driver.OracleDriver");
			dbLink.setUserid("ismp");
			dbLink.setPasswd("simpd7jl");

			JDBCPoolManager.getInstance().createPool(dbLink);

			Connection conn = null;

			conn = JDBCPoolManager.getInstance().getConnection("bnmsdb");
			for (int n = 1; n < 20; n++) {
				Statement statement = conn.createStatement();
				ResultSet rs = statement.executeQuery("select count(*) from TB_CDE_KPI");
				int c = rs.getMetaData().getColumnCount();
				while (rs.next()) {
					System.out.println();
					for (int i = 1; i <= c; i++) {
						System.out.print(rs.getObject(i));
					}
				}
				rs.close();
			}

			conn = JDBCPoolManager.getInstance().getConnection("bamdb");
			for (int n = 1; n < 20; n++) {
				Statement statement = conn.createStatement();
				ResultSet rs = statement.executeQuery("select count(*) from SKPIDETAIL2TABLE");
				int c = rs.getMetaData().getColumnCount();
				while (rs.next()) {
					System.out.println();
					for (int i = 1; i <= c; i++) {
						System.out.print(rs.getObject(i));
					}
				}
				rs.close();
			}
			System.out.println(JDBCPoolManager.getInstance().getPoolStats("bnmsdb"));
			JDBCPoolManager.getInstance().freePool("bnmsdb");
			JDBCPoolManager.getInstance().freePool("bamdb");
			conn = JDBCPoolManager.getInstance().getConnection("bamdb");
			System.out.println("AfterFree: bamdb=" + conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}