connectionpoolpostgresqlimpl.java.svn-base

this is example use EJB with jboss.

/*
 * Copyright(C) 2008, NTT AT Co., Ltd.
 * Project: AWGStar
 *
 * Notes:
 *  N/A
 *
 * Record of change:
 * Date         Version      Name       Content
 * 2008/12/15   1.0          TriNT      First create       
 */
package jp.co.ntt.awgview.server.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Vector;
import org.postgresql.jdbc3.Jdbc3PoolingDataSource;
import jp.co.ntt.awgview.server.common.LogWriter;
import jp.co.ntt.awgview.server.common.Setting;
import jp.co.ntt.awgview.server.common.Utils;
import jp.co.ntt.awgview.server.constant.DBConstants;

/**
 * Class name : ConnectionPool <BR>
 * 
 * Package : jp.co.nttat.awgstar.server.dao <BR>
 * 
 * Description: Implements ConnectionPool interface <BR>
 * 
 * 
 * @author : AI&T
 * @version : 1.0
 */

public class ConnectionPoolPostgreSQLImpl implements ConnectionPool {

	/** The minimum number of open database connections to allow */
	private static int minConnections = 2;

	/** The maximum number of open database connections to allow */
	private static int maxConnections = 20;

	/** Connection pool of postgreSQL database */
	private static Jdbc3PoolingDataSource pgConnPoolDS = null;

	/** The number of connections that you want to open to database */
	private int initialConnections = 1;

	/** Set wait for all thread to allow only one update process running */
	private boolean waitIfBusy = false;

	/** Reatry connections */
	private Vector<Connection> readyConnections = null;

	/** Working connection */
	private Vector<Connection> workingConnections = null;

	/** Connection pending */
	private boolean connectionPending = false;
	
	private boolean isConnected = false;
	
	public static int numberConnection = 0;
	
	private static int MAX_RETRY_GET_CONNECTION = 3;
	/**
	 * empty constructor
	 */

	public ConnectionPoolPostgreSQLImpl() {
	}
	
	/***
	 * initialize an instance of PostgreSQL database.
	 */
	public void initInstance(String dbName, String dbHostName,
			int dbPortNumber, String dbUser, String dbPass,
			int initialConnections, boolean waitIfBusy) throws Exception {

		if ((dbName == null) || (dbName == "") || (dbHostName == null)
				|| (dbHostName == "") || (dbUser == null) || (dbUser == "")
				|| (dbPass == null)) {
			LogWriter.getDBLogger().fatal("Cannot initInstance connection pool. Setting Parameters is incorrect");
			throw (new Exception());
		}
		try {
			
			pgConnPoolDS = new Jdbc3PoolingDataSource();
			Class.forName(DBConstants.POSTGRES_DRIVER_STRING);
			
			pgConnPoolDS.setDatabaseName(dbName); 
			pgConnPoolDS.setServerName(dbHostName);
			pgConnPoolDS.setPortNumber(dbPortNumber);
			pgConnPoolDS.setUser(dbUser);
			pgConnPoolDS.setPassword(dbPass);			

			/*
			 * The number of database connections to be created when the pool is
			 * initialized
			 */
			pgConnPoolDS.setInitialConnections(minConnections);
			
			LogWriter.getDBLogger().info("The number of database connections to be created: " + minConnections);
			
			/*
			 * The maximum number of open database connections to allow
			 */
			maxConnections = Setting.getDBMaxConnect();
			
			pgConnPoolDS.setMaxConnections(maxConnections);
			
			LogWriter.getDBLogger().info("The maximum number of open database connections to allow: " + maxConnections);			
						
			this.initialConnections = initialConnections;
			this.waitIfBusy = waitIfBusy;

			if (initialConnections <= 0) {
				initialConnections = 1;
			}
			
			// the number of connections that you want to open are larger than
			if (initialConnections > maxConnections) {
				initialConnections = maxConnections / 4;
			}

			this.initialConnections = initialConnections;
			readyConnections = new Vector<Connection>(this.initialConnections);
			
			workingConnections = new Vector<Connection>();
			
			
			for (int i = 0; i < initialConnections; i++) {				
				// LogWriter.getInstance(LogWriter.DATABASE_LOG).info("makeNewConnection:" + i);
				readyConnections.addElement(makeNewConnection());
			}
			
			LogWriter.getDBLogger().info("ready Connections created " + readyConnections.size());
			LogWriter.getDBLogger().info("Pool initInstance is success");
			
			isConnected = true;
			
		} catch (ClassNotFoundException cnfe) {
			LogWriter.getDBLogger().error(cnfe.toString());
			isConnected = false;
			throw new Exception(cnfe.toString());
		} catch (Exception ce) {
			LogWriter.getDBLogger().error(ce.toString());
			isConnected = false;
			throw new Exception(ce.toString());
		}
	}

	/**
	 * Attemp the connection by the PostgreSQL database.
	 * 
	 * @throws DatabaseException
	 *             is error
	 * @return Connection is Connection
	 */
	private Connection attempConnection() throws Exception {
		Connection conn = null;		
		int count = 1;
		try {
			for (int i = 1; i <= MAX_RETRY_GET_CONNECTION; i++) {
				count = i;
				if (pgConnPoolDS != null) {
					conn = pgConnPoolDS.getConnection();
					break;
				}
			}
		} catch (SQLException se) {
			if(LogWriter.getDBLogger().isTraceEnabled()){
				LogWriter.getDBLogger().trace(Utils.parseException(se));
			}
			if (count == MAX_RETRY_GET_CONNECTION && conn != null) {
				LogWriter.getDBLogger().error(se.getMessage());
					
				throw new Exception(se.getMessage());
			} else {
				LogWriter.getDBLogger().error("Failed to get connection from Pool." +
														" Exception: " + se.getMessage());
			}
		}

		if (conn == null) {
			throw new Exception("Failed to get connection from Pool. Maybe too many clients already.");			
		} else {
			return conn;
		}
	}

	/**
	 * When an object implementing interface <code>Runnable</code> is used to
	 * create a thread, starting the thread causes the object's <code>run</code>
	 * method to be called in that separately executing thread.
	 * <p>
	 * The general contract of the method <code>run</code> is that it may take
	 * any action whatsoever.
	 * 
	 * @see java.lang.Thread#run()
	 */
	public void run() {
		try {
			Connection connection = makeNewConnection();
			synchronized (this) {
				readyConnections.addElement(connection);
				connectionPending = false;
				notifyAll();
			}
		} catch (Exception e) {
			// SQLException or OutOfMemory
			// Give up on new connection and wait for existing one to free up.
			LogWriter.getDBLogger().error("SQLException or OutOfMemory");
			LogWriter.getDBLogger().error(e.toString());
			LogWriter.getDBLogger().error("Give up on new connection and wait for existing one to free up.");
			
		}
	}

	/**
	 * Free connection
	 * 
	 * @param connection
	 *            Connection
	 */
	public synchronized void free(Connection connection) {
		try {
			if (connection != null && !connection.isClosed()) {
				LogWriter.getDBLogger().info("Free connection ...");
				workingConnections.removeElement(connection);
				readyConnections.addElement(connection);
		
				LogWriter.getDBLogger().info("workingConnections: " + workingConnections.size());
				LogWriter.getDBLogger().info("readyConnections: " + readyConnections.size());
				// Wake up threads that are waiting for a connection
			} else {
				LogWriter.getDBLogger().info(" Free connection when connection null: " + workingConnections.size());
				workingConnections.removeElement(connection);			
				// make new connection when connection is null
				LogWriter.getDBLogger().info("Make new connection after free connection null");
				makeBackgroundConnection();
			}
		} catch (SQLException ex) {
			LogWriter.getDBLogger().error(ex.toString());
		}	
		notifyAll();
	}

	/**
	 * Close all the connections. Use with caution: be sure no connections are
	 * in use before calling. Note that you are not <I>required</I> to call this
	 * when done with a ConnectionPool, since connections are guaranteed to be
	 * closed when garbage collected. But this method gives more control
	 * regarding when the connections are closed.
	 */
	public synchronized void closeAllConnections() {
		try{
			closeConnections(readyConnections);
			closeConnections(workingConnections);
			readyConnections.clear();
			workingConnections.clear();
			if (pgConnPoolDS != null){
				pgConnPoolDS.close();
				isConnected = false;
			}
		} catch (Exception e){}//Close without error
	}

	/**
	 * Close Connection
	 * 
	 * @param connections
	 *            Vector
	 */
	private void closeConnections(Vector<Connection> connections) {
		try {
			int intSize = connections.size();
			for (int i = 0; i < intSize; i++) {
				Connection connection = (Connection) connections.elementAt(i);
				if ((connection != null) && (!connection.isClosed())) {
					connection.close();
				}
			}
		} catch (SQLException sqle) {
			LogWriter.getDBLogger().error("Cannot close connections. Error: " + sqle.toString());
		}
	}

	/**
	 * This explicitly makes a new connection. Called in the foreground when
	 * initializing the ConnectionPool, and called in the background when
	 * running.
	 * 
	 * @throws SQLException
	 * @return Connection
	 */
	private Connection makeNewConnection() throws Exception {
		try {
			numberConnection ++;
			LogWriter.getDBLogger().info("numberConnection:" + numberConnection);
			Connection connection = null;
			connection = attempConnection();
			if (connection == null) {
				throw (new Exception("Failed to get connection from Pool"));				
			} else {
				LogWriter.getDBLogger().info("Get connection from Pool success");
				return connection;
			}
		} catch (Exception de) {
			throw de;
		}
	}

	/**
	 * Make background connection
	 * 
	 * @param none
	 * @return none
	 */
	private void makeBackgroundConnection() {
		connectionPending = true;
		try {
			Thread connectThread = new Thread(this);
			connectThread.start();
		} catch (OutOfMemoryError oome) {
			// Give up on new connection
			LogWriter.getDBLogger().error(oome.toString());
		}
	}

	/**
	 * Get one connection from existing connections in this system
	 * 
	 * @throws Exception
	 *             is error
	 * @return DBConnection is DBConnection
	 */
	public synchronized DBConnection getConnection() throws Exception {
		
		// LogWriter.getInstance(LogWriter.DATABASE_LOG).debug("Start get Connection");
		
		if ((readyConnections != null) && (!readyConnections.isEmpty())) {
			
			LogWriter.getDBLogger().info("readyConnections number: " + readyConnections.size());
			
			Connection existingConnection = (Connection) readyConnections.lastElement();
			
			int lastIndex = readyConnections.size() - 1;
			
			readyConnections.removeElementAt(lastIndex);
			
			//LogWriter.getDBLogger().debug("Get existing Connection  from readyConnections");
			//LogWriter.getDBLogger().debug("readyConnections remove 1 connection object");
			//LogWriter.getDBLogger().debug("readyConnections number: " + readyConnections.size());
			
			try {
				if (existingConnection == null || existingConnection.isClosed()) {
					
					LogWriter.getDBLogger().debug("Get existing Connection  from readyConnections is null or closed");
					
					notifyAll(); // Freed up a spot for anybody waiting
					
					LogWriter.getDBLogger().debug("Again get connection from readyConnections");
					
					return (getConnection());
					
				} else {
					LogWriter.getDBLogger().info("Add connection into workingConnections");
					
					if (workingConnections == null){
						workingConnections = new Vector<Connection>(); 
					}
					
					workingConnections.addElement(existingConnection);
					
					LogWriter.getDBLogger().info("workingConnections number: " + workingConnections.size());
					
					DBConnection conn = new DBConnection(existingConnection);
					
					LogWriter.getDBLogger().info("getConnection is success");
					
					return (conn);
				}
			} catch (SQLException se) {
				LogWriter.getDBLogger().error(se.toString());
				throw new Exception("Closed connection " + se.toString());
			}
			
		} else {
			
			if ((readyConnections != null) 
					&& ((readyConnections.size() + workingConnections.size()) < maxConnections)
					&& (!connectionPending)) {
				
				makeBackgroundConnection();
				
			} else if (!waitIfBusy) {
				LogWriter.getDBLogger().error("No more connection available");
				throw new Exception("No more connection available");
			} 
			// Wait for either a new connection to be established or for an
			// existing connection to be freed up.
			LogWriter.getDBLogger().debug("Wait for either a new connection to be established or for an existing connection to be freed up.");
			try {
				wait();
			} catch (InterruptedException ie) {
				LogWriter.getDBLogger().error("Error while execute command wait() for either a new connection to be established");
				throw new Exception("Error execute wait " + ie.toString());
			}
			// Someone freed up a connection, so try again.
			return (getConnection());
		}
	}
	
	public boolean isConnected(){
		return isConnected;
	}
}