数据库连接池

感谢网络提供资源的朋友，自己做了个版本，正在测试，看看是否有问题。

2009-09-08 更新成单例模式

/*
 * $DatabaseInfo.java 2009-01-20 By Bill Yao $
 */

package shopping.servlet.db;

import java.util.PropertyResourceBundle;
import java.util.ResourceBundle;

/**
 * DatabaseInfo class provided the basic information for DB connection.
 * 
 * @author yaoh
 *
 */
public abstract class DatabaseInfo {
	
	private static ResourceBundle bundle = PropertyResourceBundle.getBundle("shopping");

    protected static final String url = bundle.getString("DB_URL");

    protected static final String username = bundle.getString("DB_USERNAME");

    protected static final String password = bundle.getString("DB_PASSWORD");

    protected static final String driverClass = bundle.getString("DB_DRIVER");
    
    protected static final int INIT_CONNECTION = Integer.valueOf(bundle.getString("INIT_CONNECTION"));
    
    protected static final int ADD_CONNECTION = Integer.valueOf(bundle.getString("ADD_CONNECTION"));

    protected static final int MAX_CONNECTION = Integer.valueOf(bundle.getString("MAX_CONNECTION"));
}

package shopping.servlet.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Vector;

import org.apache.log4j.Logger;

/**
 * ConnectionPool provide database connections by pool.
 * 
 * @author yaoh
 *
 */
public final class ConnectionPool extends DatabaseInfo{
    static Logger logger = Logger.getLogger(ConnectionPool.class);
    
    // The connection pool.
    private Vector<PooledConnection> connections = null;
    
    // Table used to test connection.
    private String testTable = "TEST";
    
    //Singleton pattern.
    private static final ConnectionPool pool = new ConnectionPool();
    
    /**
     * Constructor
     */
	private ConnectionPool() {
		try {
			createPool();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static ConnectionPool getInstance() {
		return pool;
	}
	
	/**
	 * Create the connection pool.
	 * 
	 */
	private synchronized void createPool() throws SQLException, ClassNotFoundException {
		if (connections != null) return;

		Class.forName(driverClass);

		connections = new Vector<PooledConnection>();
		createConnections(INIT_CONNECTION);
		logger.info("Create connection pool successfully.");
	}

	/**
	 * Create number of connections, add into connection pool.
	 * @param num, number of connections.
	 */
	private void createConnections(int num) throws SQLException {
		for (int x = 0; x < num; x++) {
			if (MAX_CONNECTION > 0 && connections.size() >= MAX_CONNECTION) break;

			//add a new PooledConnection object to connections vector
			try {
				connections.addElement(new PooledConnection(newConnection()));
			} catch (SQLException e) {
				logger.error(" Create database connection failed. " + e.getMessage());
				throw new SQLException();
			}
			logger.info(" Create database connection successfully. ");
		}
	}
	
	/**
	 * Create and return a new database connection.
	 *
	 * @return a new database connection.
	 */
	private Connection newConnection() throws SQLException {

		Connection conn = DriverManager.getConnection(url, username, password);

		if (connections.size() == 0) {
			DatabaseMetaData metaData = conn.getMetaData();
			int driverMaxConnections = metaData.getMaxConnections();
			if (driverMaxConnections > 0 && MAX_CONNECTION > driverMaxConnections) {
				//TODO; update the max connection.
			}
		}
		return conn;
	}
	
	/**
	 * Return a avaliable connection.
	 * Wait a while and retrive again if not free connection and the connection
	 * number is max.
	 * 
	 * @return a avaliable connection.
	 */
	public synchronized Connection getConnection() throws SQLException {
		if (connections == null) return null; 

		Connection conn = getFreeConnection();
		
		while (conn == null) {
			wait(250);
			conn = getFreeConnection();
		}
		return conn;
	}

	/**
	 * Retrive an avaliable connection, create some if no avaliable.
	 * If all unavaliable after creation, return null
	 * @return a free connection.
	 */
	private Connection getFreeConnection() throws SQLException {
		Connection conn = findFreeConnection();
		if (conn == null) {
			createConnections(ADD_CONNECTION);
			conn = findFreeConnection();
			if (conn == null) return null;
		}
		
		return conn;
	}
	
	/**
	 * Find a free connection，
	 * if no free connection, return null.
	 *
	 * @return a free connection
	 */
	private Connection findFreeConnection() throws SQLException {
		Connection conn = null;
		PooledConnection pConn = null;

		Enumeration<PooledConnection> enumerate = connections.elements();
		while (enumerate.hasMoreElements()) {
			pConn = enumerate.nextElement();
			
			if (!pConn.isBusy()) {
				conn = pConn.getConnection();
				pConn.setBusy(true);
				if (!testConnection(conn)) {
					try {
						conn = newConnection();
					} catch (SQLException e) {
						logger.error("Create database connection failed." + e.getMessage());
						return null;
					}
					pConn.setConnection(conn);
				}
				break;
			}
		}

		return conn;
	}

	/**
	 * Testing if a connection works, if not, close it and return false.
	 * else return true.
	 *
	 * @param conn connection need to test.
	 * @return true if connection works.
	 */
	private boolean testConnection(Connection conn) {
		try {
			if (testTable.equals("")) {
				conn.setAutoCommit(true);

			} else {
				Statement stmt = conn.createStatement();
				stmt.execute("select count(*) from " + testTable);
				
				stmt.close();
			}
		} catch (SQLException e) {
			closeConnection(conn);
			return false;
		}

		return true;

	}
	
	/**
	 * Return a connection to pool, set it free。
	 *
	 * @param free connection.
	 */
	public void returnConnection(Connection conn) {
		if (connections == null) {
			logger.warn("Connection pool does not exist, could not return connection.");
			return;
		}

		PooledConnection pConn = null;

		Enumeration<PooledConnection> enumerate = connections.elements();
		while (enumerate.hasMoreElements()) {
			pConn = (PooledConnection) enumerate.nextElement();
			if (conn == pConn.getConnection()) {
				pConn.setBusy(false);
				conn = null;
				break;
			}
		}
	}
	
	/**
	 * Refresh all connections in the pool.
	 *
	 */
	public synchronized void refreshConnections() throws SQLException {
		if (connections == null) {
			logger.warn("Connection pool does not exist, could not refresh connection.");
			return;
		}

		PooledConnection pConn = null;

		Enumeration<PooledConnection> enumerate = connections.elements();
		while (enumerate.hasMoreElements()) {
			pConn = (PooledConnection) enumerate.nextElement();
			if (pConn.isBusy()) {
				wait(5000);
			}
			
			closeConnection(pConn.getConnection());
			pConn.setConnection(newConnection());
			pConn.setBusy(false);
		}
	}
	
	/**
	 * Close all connection and clear pool.
	 */

	public synchronized void closeConnectionPool() throws SQLException {
		if (connections == null) {
			logger.warn("Connection pool does not exist, cound not close.");
			return;
		}

		PooledConnection pConn = null;

		Enumeration<PooledConnection> enumerate = connections.elements();
		while (enumerate.hasMoreElements()) {
			pConn = (PooledConnection) enumerate.nextElement();
			if (pConn.isBusy()) {
				wait(5000);
			}
			closeConnection(pConn.getConnection());
			connections.removeElement(pConn);
		}
		connections = null;
	}

	/**
	 * Close a connection.
	 *
	 * @param the connection need to be closed.
	 */

	private void closeConnection(Connection conn) {
		try {
			conn.close();
		} catch (SQLException e) {
			logger.error("Close connection failed:" + e.getMessage());
		}
	}

	/**
	 * Let proccess wait.
	 *
	 * @param ms time.
	 */
	private void wait(int mSeconds) {
		try {
			Thread.sleep(mSeconds);
		} catch (InterruptedException e) {
		}
	}

	/**
	 * Inner class for saving database connection.
	 * Field connection
	 * Filed busy
	 */
	class PooledConnection {

		Connection connection = null;// database connection.

		boolean busy = false; // if this connection is in using, default is used.

		public PooledConnection(Connection connection) {
			this.connection = connection;
		}

		// Return the connection.
		public Connection getConnection() {
			return connection;
		}

		// Set the connection.
		public void setConnection(Connection connection) {
			this.connection = connection;
		}

		// Return if connection is busy.
		public boolean isBusy() {
			return busy;
		}

		// Set the connection is busy.
		public void setBusy(boolean busy) {
			this.busy = busy;
		}
	}
}