// place this in a package
//package  com.webdoyen.database;

import  java.sql.*;
import  java.util.*;
import  java.util.Timer;
import  java.util.TimerTask;


/*
 JAVADOC ME AND READ THE JAVADOCS - IT WILL BE VERY EASY TO UNDERSTAND THEN

 Here's how:
   javadoc -d docs ConnectionPool.java

 where "docs" is a directory to place the documentation into
*/


/**
 * <h2>Creates and handles a pool of database connections.</h2>
 *                                                                         <P>
 * Methods allow for getting a connection from the pool                    <BR>
 * and returning a connection to the pool.
 *                                                                         <P>
 * The pool will dynamically grow as needed, and over time                 <BR>
 * reduce itself back to its original size if some of the                  <BR>
 * connections aren't being used.                                          <P>
 *
 * Jeff Patterson (jeff@webdoyen.com)  - <a target=_blank href="http://www.webdoyen.com">www.webdoyen.com</a> - (c) 2001  <P>
 * 
 * <small><B>This code is distributed under the GNU GENERAL PUBLIC LICENSE - you MAY USE OR MODIFY IT as you like,    
 * but CANNOT charge money for this single piece of code.  You MAY USE this code WITHIN a piece of software
 * that you charge money for.</B></small>
 * <P><code>
 * <a href="#COMPLEX">See example using complex constructor</a><BR>
 * <a href="#SIMPLE">See example using simple constructor</a>
 * </code><P>
 * <table bgcolor="#DDDDDD" border="1" cellpadding="4" cellspacing="0"><tr><td>
 * <a name="COMPLEX"><B>example using complex constructor:</B></a>
 * <pre><code>
 * ConnectionPool pool = null;
 * String dbURL = "jdbcurl_here";
 * String dbUserName = "someusername";
 * String dbPassword = "somepassword";
 * String dbDriver = "jdbc.driver";
 * try 
 * {
 *      // open a pool of 10 connections - when I run low, open 5 more
 *      // check my pool's health every 2 minutes, and keep "extra" idle
 *      // connections alive for 3 minutes before closing them down
 *      pool = new ConnectionPool( dbURL , dbUserName , dbPassword, dbDriver, 10, 5, 2, 3 );
 * } 
 * catch(SQLException e)  // thrown if problems building connections to database
 * {
 *     System.out.println( e.getMessage() );
 * }
 * catch(ClassNotFoundException cnf)  // thrown if db class driver not found in classpath
 * {
 *     System.out.println( cnf.getMessage() );
 * }
 *
 * // now that the pool is running, we can use it's connections...
 *
 * Connection con = null;
 * try {
 *     con = pool.getConnection();    // get a connection from the pool
 *     // use the connection...
 *     // ...
 *     // done using the connection
 *     pool.returnConnection( con );  // return the connection to the pool
 * } 
 * catch(SQLException e)  // thrown if problems building connections to database
 * {
 *     System.out.println( e.getMessage() );
 * }
 * </code></pre>
 * </td></tr></table>
 * <P>
 * <table bgcolor="#DDDDDD" border="1" cellpadding="4" cellspacing="0"><tr><td>
 * <a name="#SIMPLE"><B>example using default/simple constructor:</B></a>
 * <code><pre>
 * ConnectionPool pool = new ConnectionPool();                     // construct an empty pool
 * try
 * {
 *      pool.setURL("jdbc:mysql://localhost:3306/store_product");  // MySQL database on local machine
 *      pool.setDriverClassName("org.gjt.mm.mysql.Driver");        // DB driver - must be in classpath
 *      pool.start();                                              // Start the pool
 *      Connection con = pool.getConnection("Test Program");       // get a connection and identify ourselves
 *      // here is where we use the pool
 *      pool.returnConnection( con );                              // return a connection
 *      System.out.println( pool.getReport() );                    // let's see a report of the pool
 *  }
 *  catch(SQLException e)  // might be thrown by start() or getConnection()
 *  {
 *      System.out.println( e.getMessage() );
 *  }
 *  catch(ClassNotFoundException cnf)  // might be thrown by setDriverClassName()
 *  {
 *      System.out.println( cnf.getMessage() );
 *  }
 * </pre></code>
 * </td></tr></table>
 *
 * @author Jeff Patterson (<a href="mailto:jeff@webdoyen.com">jeff@webdoyen.com</a>)  - www.webdoyen.com - (c) 2001
 */
public class ConnectionPool 
{
    // CONSTANTS
    private static final String NEWLINE = System.getProperty( "line.separator" ); // platform dependant newline for our report

    // switch this to true if you want to use it in production environment
    // it is used by the exerciseConnection() method to determine how much
    // of a workout to give a connection
    private static final boolean PRODUCTION = false;

    private static final boolean DEBUG = false;        // I used this when programming this originally
    
    // MEMBERS
    private Hashtable connections = null;    // this will hold our connections
    private int increment = 1;               // how many new cons to open if the pool is all used up
    private int iterationTime = 10;          // number of minutes to wait before checking integrity
    private String dbURL, user, password;    // JDBC settings
    private int initialConnections = 1;      // initial connections to open
    private int minutesIdle = 5;             // any connection over initialConnections that has been idle for this long or longer is closed
    private Timer timer = null;              // the Timer that will keep track of connection integrity, etc.
    private boolean classDriverSet = false;  // has the class driver been set for this pool?


    /**
     * Default constructor - there is some setup required
     * <P>See the <code>start()</code> method to understand how to set up and start the pool<BR>
     * You may also want to look at the <a href="#SIMPLE">default constructor example</a> to see a VERY simple example<P>
     * @see #start
     */
    public ConnectionPool()
    {
    }

    /**
     * Make a connection pool based on the parameters<P>
     * By using this complex constructor, you will skip having to go through the
	 * individual steps associated with the default/empty constructor (<code>ConnectionPool()</code>)
	 * <P>
     * This connection pool will automatically start - you do not need to issue the <code>start()</code> command
     * <P>
     * @param  dbURL JDBC url of the DB
     * @param  user database username
     * @param  password database password
     * @param  driverClassName JDBC Driver Class Name
     * @param  initialConnections number of connections to open initially.  
	 *         The pool will never have fewer than this many connections established to the DB.
     * @param  increment number of connections to open if you try and get a connection from the pool but
     *         there are none available.  These "extra" connections will be closed over time if they remain idle.
     * @param  iterationTime time (in munites) between connection integrity checks.
     * @param  timeInMinutes time (in munites) to keep an idle connection alive before closing it
     * @see    #ConnectionPool()
     */
    public ConnectionPool (String dbURL, String user, String password, String driverClassName, int initialConnections, int increment, int iterationTime,  int minutesIdle) throws SQLException, ClassNotFoundException
    {
        // Load the specified driver class
        setDriverClassName(driverClassName);
        setURL(dbURL);
        setUser(user);
        setPassword(password);
        setIncrementCount(increment);
        setInitialConnections(initialConnections);
        setHealthCheckIterationTime(iterationTime);
        setMinutesIdle( minutesIdle );
        start();
    }

    /**
     * Gets a connection from the pool - you MUST return it
     * <P>You must return it via <code>returnConnection()</code> when you're done with it.  Failure to do so
     * will cause this connection to be permanently "checked out" of the pool, and never again usable.
     * <P>
     * @throws SQLException if a connection is unhealthy and we have to make a new one, getConnection throws
     *  an error if the new connection isn't made successfully
     * @see #returnConnection
     * @see #getConnection(String)
     */
    public Connection getConnection () throws SQLException
    {
        return  (getConnection(null));        // polymorphic getConnection
    }

    /**
     * Gets a connection from the pool - you MUST return it
     * <P>You must return it via <code>returnConnection()</code> when you're done with it.  Failure to do so
     * will cause this connection to be permanently "checked out" of the pool, and never again usable.
     * <P>
     * @param appId identifer of application.  Used in the report created by <code>getReport()</code>
     * @throws SQLException if a connection is unhealthy and we have to make a new one, getConnection throws an error if the new connection isn't made successfully
     * @see #returnConnection
     * @see #getConnection()
     * @see #getReport
     */
    public Connection getConnection (String appId) throws SQLException
    {
        // polymorphic getConnection
        Connection con = null;
        Enumeration cons = connections.keys();
        synchronized (connections) {
            while (cons.hasMoreElements()) 
            {
                con = (Connection)cons.nextElement();
                //Boolean b = new Boolean( ( (ConData)connections.get(con) ).inUse );
                //if (b == Boolean.FALSE) {
                if (((ConData)connections.get(con)).inUse == false) 
                {
                    // So we found an unused connection.
                    // Test its integrity with a quick setAutoCommit(true) call.
                    // For production use, more testing could be performed, such as a simple query.
                    try 
                    {
                        con.setAutoCommit(true);
                    } 
                    catch (SQLException e) 
                    {
                        // Problem with the connection, replace it.
                        con = DriverManager.getConnection(dbURL, user, password);
                    }
                    // Update the Hashtable to show this one's taken
                    connections.put(con, new ConData(true, appId));
                    // Return the connection
                    return  con;
                }
            }
        }
        // If we get here, there were no free connections - we've got to make more.
        for (int i = 0; i < increment; i++) // make however many we've decided on
            connections.put(DriverManager.getConnection(dbURL, user, password), new ConData());

        // Recurse to get one of the new connections.
        return  getConnection(appId);
    }

    /** This will return a connection to the pool and free it up for something else 
     * @param connection the connection from the pool that you're done using and ready to put back in the pool
     * @return <code>true</code> if the connection is returned without problems, else <code>false</code> (should never happen)
     * @see #getConnection
     */
    public boolean returnConnection (Connection connection)
    {
        boolean returnVal = false;
        Connection con;
        Enumeration cons = connections.keys();
        while (cons.hasMoreElements()) 
        {
            con = (Connection)cons.nextElement();
            if (con == connection) 
            {
                connections.put(con, new ConData());
                returnVal = true;
                break;
            }
        }
        return  (returnVal);
    }

    /** Starts the pool<P>
     *  You are only required to issue the <code>start()</code> method if you have
     *  used the default/empty constructor (<code>ConnectionPool()</code>) to build your pool.
     *  The complex constructor automatically starts the pool.
     *  <P>
     *  Before starting the pool, you <B>must call</B> the following (in no particular order):
     *  <code>
     *  <ul>
     *  <li>setDriverClassName()
     *  <li>setURL()
     *  </ul>
     *  </code>
     *  <P>You will <B>almost certainly have to call</B> the following:
     *  <code>
     *  <ul>
     *  <li>setUser()
     *  <li>setPassword()
     *  </ul>
     *  </code>
     *  <P>In addition, you <B>may call</B> any or all of these:
     *  <ul>
     *  <li><code>setIncrementCount()</code>            <small><I>   (default is 1)</I></small>
     *  <li><code>setInitialConnections()</code>   <small><I>        (default is 1)</I></small>
     *  <li><code>setHealthCheckIterationTime()</code>   <small><I>  (default is 10)</I></small>
     *  <li><code>setMinutesIdle()</code>   <small><I>               (default is 5)</I></small>
     *  </ul>
     *  <P>
     *  @see #ConnectionPool()
     *  @see #setDriverClassName
     *  @see #setURL
     *  @see #setUser
     *  @see #setPassword
     *  @see #setIncrementCount
     *  @see #setInitialConnections
     *  @see #setHealthCheckIterationTime
     *  @see #setMinutesIdle
     */
    public void start() throws SQLException
    {
        if (this.connections != null) return; // we have already started the pool, so let's get out of here!