package com.wxpn.tutorial.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.Date;

/**
 * 描述: 数据库连接池类
 * Copyright (c) 2005-2008 Wang Xining
 * @author 王夕宁
 * @version 1.0
 */

public class ConnectionPool implements Runnable {

	private boolean _debug = false;

	private Thread runner;

	private Connection[] connPool;

	private int[] connStatus; // (0) available; (1) locked by the client; (2)

	// locked by the housekeeping thread

	private long[] connLockTime;

	private long[] connCreateTime;

	private String[] connID;

	private String dbdriver, dbserver, dbuser, dbpassword;

	private int currConnections, connLast, minconns, maxconns, maxconnMSec;

	// available: set to false on destroy, checked by getConnection()
	private boolean available = true;

	private SQLWarning currSQLWarning;

	/**
	 * Creates a new Connection Broker<br>
	 * 
	 * @param dbdriver
	 *            JDBC driver. e.g. 'oracle.jdbc.driver.OracleDriver'<br>
	 * @param dbserver
	 *            JDBC connect string. e.g.
	 *            'jdbc:oracle:thin:@203.92.21.109:1526:orcl'<br>
	 * @param dbuser
	 *            Database login name. e.g. 'Scott'<br>
	 * @param dbpassword
	 *            Database password. e.g. 'Tiger'<br>
	 * @param minconns
	 *            Minimum number of connections to start with.<br>
	 * @param maxconns
	 *            Maximum number of connections in dynamic pool.<br>
	 * @param logFileString
	 *            Absolute path name for log file. e.g. 'c:\temp\mylog.log' <br>
	 * @param maxconntime
	 *            Time in hours between connection resets. (Reset does a basic
	 *            cleanup)<br>
	 */
	public ConnectionPool(String dbdriver, String dbserver, String dbuser,
			String dbpassword, int minconns, int maxconns, double maxconntime)
			throws IOException {
		connPool = new Connection[maxconns];
		connStatus = new int[maxconns];
		connLockTime = new long[maxconns];
		connCreateTime = new long[maxconns];
		connID = new String[maxconns];
		currConnections = minconns;
		this.maxconns = maxconns;
		this.dbdriver = dbdriver;
		this.dbserver = dbserver;
		this.dbuser = dbuser;
		this.dbpassword = dbpassword;
		maxconnMSec = (int) (maxconntime * 3600 * 1000);
		if (maxconnMSec < 60000) { // Recycle no less than 1 minute.
			maxconnMSec = 60000;
		}

		System.out.println("Starting ConnectionPool:");
		System.out.println("dbdriver = " + dbdriver);
		System.out.println("dbserver = " + dbserver);
		System.out.println("dbuser = " + dbuser);
		System.out.println("minconnections = " + minconns);
		System.out.println("maxconnections = " + maxconns);
		System.out
				.println("Total refresh interval = " + maxconntime + " hours");
		System.out.println("-----------------------------------------");

		init();

		// Fire up the background housekeeping thread
		runner = new Thread(this);
		runner.start();
	} // End ConnectionPool()

	/**
	 * Initialize the pool of connections with the mininum connections: Problems
	 * creating connections may be caused during reboot when the servlet is
	 * started before the database is ready. Handle this by waiting and trying
	 * again. The loop allows 5 minutes for db reboot.
	 */
	private void init() throws IOException {

		boolean connectionsSucceeded = false;
		int dbLoop = 20;

		try {
			for (int i = 1; i < dbLoop; i++) {
				try {
					for (int j = 0; j < currConnections; j++) {
						createConn(j);
					}
					connectionsSucceeded = true;
					break;
				} catch (SQLException e) {
					System.out
							.println("--->Attempt ("
									+ String.valueOf(i)
									+ " of "
									+ String.valueOf(dbLoop)
									+ ") failed to create new connections set at startup: ");
					System.out.println("    " + e);
					System.out.println("    Will try again in 15 seconds...");
					try {
						Thread.sleep(15000);
					} catch (InterruptedException e1) {
					}
				}
			}
			if (!connectionsSucceeded) { // All attempts at connecting to db
				// exhausted
				System.out
						.println("\r\nAll attempts at connecting to Database exhausted");
				throw new IOException();
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new IOException();
		}
	}

	private void createConn(int i) throws SQLException {
		Date now = new Date();
		try {
			Class.forName(dbdriver);
			connPool[i] = DriverManager.getConnection(dbserver, dbuser,
					dbpassword);
			connStatus[i] = 0;
			connID[i] = connPool[i].toString();
			connLockTime[i] = 0;
			connCreateTime[i] = now.getTime();

			System.out.println(now.toString() + "  Opening connection "
					+ String.valueOf(i) + " " + connPool[i].toString() + ":");
		} catch (ClassNotFoundException e2) {
			e2.printStackTrace();
			throw new SQLException(e2.getMessage());
		}
	} // createConn()

	/**
	 * Housekeeping thread. Runs in the background with low CPU overhead.
	 * Connections are checked for warnings and closure and are periodically
	 * restarted. This thread is a catchall for corrupted connections and
	 * prevents the buildup of open cursors. (Open cursors result when the
	 * application fails to close a Statement). This method acts as fault
	 * tolerance for bad connection/statement programming.
	 */
	public void run() {
		Statement stmt = null;
		String currCatalog = null;

		for (;;) {
			// Get any Warnings on connections
			for (int i = 0; i < currConnections; i++) {
				try {
					currSQLWarning = connPool[i].getWarnings();
					if (currSQLWarning != null) {
						System.out.println("Warnings on connection "
								+ String.valueOf(i) + " " + currSQLWarning);
						connPool[i].clearWarnings();
					}
				} catch (SQLException e) {
					System.out.println("Cannot access Warnings: " + e);
				}
			}

			for (int i = 0; i < currConnections; i++) { // Do for each
				// connection
				long age = System.currentTimeMillis() - connCreateTime[i];
				synchronized (connStatus) {
					if (connStatus[i] > 0) { // In use, catch it next time!
						continue;
					}
					connStatus[i] = 2; // Take offline (2 indicates
					// housekeeping lock)
				}

				try { // Test the connection with createStatement call
					if (age > maxconnMSec) { // Force a reset at the max conn
						// time
						throw new SQLException();
					}

					stmt = connPool[i].createStatement();
					connStatus[i] = 0; // Connection is O.K.
					// log("Connection confirmed for conn = " +
					// String.valueOf(i));

					// Some DBs return an object even if DB is shut down
					if (connPool[i].isClosed()) {
						throw new SQLException();
					}
					// Connection has a problem, restart it
				} catch (SQLException e) {
					try {
						System.out.println(new Date().toString()
								+ " ***** Recycling connection "
								+ String.valueOf(i) + ":");

						connPool[i].close();
						createConn(i);
					} catch (SQLException e1) {
						System.out.println("Failed: " + e1);
						connStatus[i] = 0; // Can't open, try again next time
					}
				} finally {
					try {
						if (stmt != null) {
							stmt.close();
						}
					} catch (SQLException e1) {
					}
					;
				}
			}

			try {
				Thread.sleep(20000); // Wait 20 seconds for next cycle
			} catch (InterruptedException e) {
				// Returning from the run method sets the internal
				// flag referenced by Thread.isAlive() to false.
				// This is required because we don't use stop() to
				// shutdown this thread.
				return;
			}

		} // for(;;)
	} // End run

	/**
	 * This method hands out the connections in round-robin order. This prevents
	 * a faulty connection from locking up an application entirely. A browser
	 * 'refresh' will get the next connection while the faulty connection is
	 * cleaned up by the housekeeping thread. If the min number of threads are
	 * ever exhausted, new threads are added up the the max thread count.
	 * Finally, if all threads are in use, this method waits 2 seconds and tries
	 * again, up to ten times. After that, it returns a null.
	 */
	public Connection getConnection() {

		Connection conn = null;

		if (available) {
			boolean gotOne = false;

			for (int outerloop = 1; outerloop <= 10; outerloop++) { // try ten
				// times to
				// find one.

				try {
					int loop = 0;
					int roundRobin = connLast + 1;
					if (roundRobin >= currConnections) {
						roundRobin = 0;

					}
					do {
						synchronized (connStatus) {
							if ((connStatus[roundRobin] < 1)
									&& (!connPool[roundRobin].isClosed())) {
								conn = connPool[roundRobin];
								connStatus[roundRobin] = 1;
								connLockTime[roundRobin] = System
										.currentTimeMillis();
								connLast = roundRobin;
								gotOne = true;
								break;
							} else {
								loop++;
								roundRobin++;
								if (roundRobin >= currConnections) {
									roundRobin = 0;
								}
							}
						}
					} while ((gotOne == false) && (loop < currConnections));
				} catch (SQLException e1) {
				}

				if (gotOne) {
					break; // break the outer loop
				} else {
					synchronized (this) { // Add new connections to the pool
						if (currConnections < maxconns) {
							try {
								createConn(currConnections);
								currConnections++;
							} catch (SQLException e) {
								System.out
										.println("Unable to create new connection: "
												+ String
														.valueOf(currConnections - 1));
								System.out.println("Exception : " + e);
							}
						} // if
					} // synchronized

					try {
						Thread.sleep(2000);
					} catch (InterruptedException e) {
					}

					System.out
							.println("-----> Connections Exhausted!  Will wait and try "
									+ "again in outer loop "
									+ String.valueOf(outerloop));
				}
			} // End of try 10 times loop

		} else { // not available
			System.out
					.println("Unsuccessful getConnection() request during destroy()");
		} // End if(available)
		return conn;

	}

	/**
	 * Returns the local JDBC ID for a connection. if there's not the connection
	 * int the pool return -1
	 */
	public int idOfConnection(Connection conn) {
		int match;
		String tag;

		try {
			tag = conn.toString();
		} catch (NullPointerException e1) {
			tag = "none";
		}

		match = -1;

		for (int i = 0; i < currConnections; i++) {
			if (connID[i].equals(tag)) {
				match = i;
				break;
			}
		}
		return match;
	}

	/**
	 * Frees a connection. Replaces connection back into the main pool for
	 * reuse.
	 */
	public String freeConnection(Connection conn) {
		String res = "";

		int thisconn = idOfConnection(conn);
		if (thisconn >= 0) {
			connStatus[thisconn] = 0;
			res = "freed " + conn.toString();
			// log("Freed connection " + String.valueOf(thisconn) +
			// " normal exit: ");
		} else {
			System.out.println("----> Could not free connection!!!"
					+ "Try to close it directly");
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}
		return res;
	}

	/**
	 * Returns the age of a connection -- the time since it was handed out to an
	 * application.
	 */
	public long getAge(Connection conn) { // Returns the age of the connection
		// in millisec.
		int thisconn = idOfConnection(conn);
		return System.currentTimeMillis() - connLockTime[thisconn];
	}

	/**
	 * Shuts down the housekeeping thread and closes all connections in the
	 * pool. Call this method from the destroy() method of the servlet.
	 * Multi-phase shutdown. having following sequence:
	 * <OL>
	 * <LI><code>getConnection()</code> will refuse to return connections.
	 * <LI>The housekeeping thread is shut down.<br>
	 * Up to the time of <code>millis</code> milliseconds after shutdown of
	 * the housekeeping thread, <code>freeConnection()</code> can still be
	 * called to return used connections.
	 * <LI>After <code>millis</code> milliseconds after the shutdown of the
	 * housekeeping thread, all connections in the pool are closed.
	 * <LI>If any connections were in use while being closed then a
	 * <code>SQLException</code> is thrown.
	 * <LI>The log is closed.
	 * </OL>
	 * <br>
	 * Call this method from a servlet destroy() method.
	 * 
	 * @param millis
	 *            the time to wait in milliseconds.
	 * @exception SQLException
	 *                if connections were in use after <code>millis</code>.
	 */
	public void destroy(int millis) throws SQLException {

		// Checking for invalid negative arguments is not necessary,
		// Thread.join() does this already in runner.join().

		// Stop issuing connections
		available = false;

		// Shut down the background housekeeping thread
		runner.interrupt();

		// Wait until the housekeeping thread has died.
		try {
			runner.join(millis);
		} catch (InterruptedException e) {
		} // ignore

		// The housekeeping thread could still be running
		// (e.g. if millis is too small). This case is ignored.
		// At worst, this method will throw an exception with the
		// clear indication that the timeout was too short.

		long startTime = System.currentTimeMillis();

		// Wait for freeConnection() to return any connections
		// that are still used at this time.
		int useCount;
		while ((useCount = getUseCount()) > 0
				&& System.currentTimeMillis() - startTime <= millis) {
			try {
				Thread.sleep(500);
			} catch (InterruptedException e) {
			} // ignore
		}

		// Close all connections, whether safe or not
		for (int i = 0; i < currConnections; i++) {
			try {
				connPool[i].close();
			} catch (SQLException e1) {
				System.out.println("Cannot close connections on Destroy");
			}
		}

		if (useCount > 0) {
			// bt-test successful
			String msg = "Unsafe shutdown: Had to close " + useCount
					+ " active DB connections after " + millis + "ms";
			System.out.println(msg);
			// Throwing following Exception is essential because servlet authors
			// are likely to have their own error logging requirements.
			throw new SQLException(msg);
		}

	} // End destroy()

	/**
	 * Less safe shutdown. Uses default timeout value. This method simply calls
	 * the <code>destroy()</code> method with a <code>millis</code> value of
	 * 10000 (10 seconds) and ignores <code>SQLException</code> thrown by that
	 * method.
	 * 
	 * @see #destroy(int)
	 */
	public void destroy() {
		try {
			destroy(10000);
		} catch (SQLException e) {
		}
	}

	/**
	 * Returns the number of connections in use.
	 */
	// This method could be reduced to return a counter that is
	// maintained by all methods that update connStatus.
	// However, it is more efficient to do it this way because:
	// Updating the counter would put an additional burden on the most
	// frequently used methods; in comparison, this method is
	// rarely used (although essential).
	public int getUseCount() {
		int useCount = 0;
		synchronized (connStatus) {
			for (int i = 0; i < currConnections; i++) {
				if (connStatus[i] > 0) { // In use
					useCount++;
				}
			}
		}
		return useCount;
	} // End getUseCount()

	/**
	 * Returns the number of connections in the dynamic pool.
	 */
	public int getSize() {
		return currConnections;
	} // End getSize()

	public static void main(String[] args) {
		String driver = "org.gjt.mm.mysql.Driver";
		String server = "jdbc:mysql://127.0.0.1/elearning?autoReconnect=true";
		String dbuser = "root";
		String dbpassword = "";
		int minconn = 5;
		int maxconn = 20;
		double maxconntime = 0.1D;
		try {
			ConnectionPool p = new ConnectionPool(driver, server, dbuser,
					dbpassword, minconn, maxconn, maxconntime);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

} // end class
