connectionpool.java

《JSP通用模块及典型系统开发实例导航》源代码

package com.wxpn.tutorial.db;

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...");
					e.printStackTrace();
					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;