Pool DBCP!

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package persistence;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.dbcp.*;

import javax.sql.DataSource;
import java.util.Date;
import java.sql.*;

/**
 *
 * @author 
 * Classe responsável por gerenciar as conexões
 */
public class ALConnectionManager {

    private static final Log LOG = LogFactory.getLog(ALConnectionManager.class);
    public static DataSource ds = null;
    private static GenericObjectPool _pool = null;    
    /**
     *  @param config configuração das propriedades do pool
     */
    public ALConnectionManager(ALConfigurationConnection config) {
        try {
            connectToDB(config);
        } catch (Exception e) {
            LOG.error( "Failed to construct ALConnectionManager", e );
        }
    }
    
    /**
     *  destructor
     */
    @Override
    protected void finalize() {
        LOG.debug("Finalizing ALConnectionManager");
        try {
            super.finalize();
        } catch (Throwable ex) {
            LOG.error( "ALConnectionManager finalize failed to disconnect from mysql: ", ex );
        }
    }


    /**
    *  connectToDB - Conecta o banco MySQL
    */
    private void connectToDB( ALConfigurationConnection config ) {
        try {
            java.lang.Class.forName( config.getDbDriverName() ).newInstance();
        }
        catch(Exception e)
        {
            LOG.error("Error when attempting to obtain DB Driver: " + config.getDbDriverName() + " on " + new Date().toString(), e);
        }

        LOG.debug("Trying to connect to database...");
        try {
            ALConnectionManager.ds = setupDataSource(
                    config.getDbURI(),
                    config.getDbUser(),
                    config.getDbPassword(),
                    config.getDbPoolMinSize(),
                    config.getDbPoolMaxSize());

            LOG.debug("Connection attempt to database succeeded.");
        } catch (Exception e) {
            LOG.error("Error when attempting to connect to DB ", e);
        }
    }

    /**
     *
     * @param connectURI - JDBC Connection URI
     * @param username - JDBC Connection username
     * @param password - JDBC Connection password
     * @param minIdle - Minimum number of idel connection in the connection pool
     * @param maxActive - Connection Pool Maximum Capacity (Size)
     * @throws Exception
     */
    public static DataSource setupDataSource(String connectURI,
            String username,
            String password,
            int minIdle, int maxActive) throws Exception {
        
        /* Em primeiro lugar, precisamos de ObjectPool que serve de 
            pool de conexões. 
            Iremos utilizar um GenericObjectPool , embora 
            Qualquer ObjectPool é o suficiente.
        */
        GenericObjectPool connectionPool = new GenericObjectPool(null);

        connectionPool.setMinIdle(minIdle);
        connectionPool.setMaxActive(maxActive);

        ALConnectionManager._pool = connectionPool;
        // we keep it for two reasons
        // #1 We need it for statistics/debugging
        // #2 PoolingDataSource does not have getPool()
        // method, for some obscure, weird reason.

        //
        // Next, we'll create a ConnectionFactory that the
        // pool will use to create Connections.
        // We'll use the DriverManagerConnectionFactory,
        // using the connect string from configuration
        //
        ConnectionFactory connectionFactory =
                new DriverManagerConnectionFactory(connectURI, username, password);

        //
        // Now we'll create the PoolableConnectionFactory, which wraps
        // the "real" Connections created by the ConnectionFactory with
        // the classes that implement the pooling functionality.
        //
        PoolableConnectionFactory poolableConnectionFactory =
                new PoolableConnectionFactory(
                connectionFactory, connectionPool, null, null, false, true);

        PoolingDataSource dataSource =
                new PoolingDataSource(connectionPool);

        return dataSource;
    }

    public static void printDriverStats() throws Exception {
        ObjectPool connectionPool = ALConnectionManager._pool;
        LOG.info("NumActive: " + connectionPool.getNumActive());
        LOG.info("NumIdle: " + connectionPool.getNumIdle());
    }

    /**
     *  getNumLockedProcesses - gets the 
     *  number of currently locked processes on the MySQL db
     *
     *  @return Number of locked processes
     */
    public int getNumLockedProcesses() {
        int num_locked_connections = 0;
        Connection con = null;
        PreparedStatement p_stmt = null;
        ResultSet rs = null;
        try {
            con = ALConnectionManager.ds.getConnection();
            p_stmt = con.prepareStatement("SHOW PROCESSLIST");
            rs = p_stmt.executeQuery();
            while (rs.next()) {
                if (rs.getString("State") !=
                        null && rs.getString("State").equals("Locked")) {
                    num_locked_connections++;
                }
            }
        } catch (Exception e) {
            LOG.debug("Failed to get get Locked Connections - Exception: " + e.toString());
        } finally {
            try {
                rs.close();
                p_stmt.close();
                con.close();
            }  catch ( java.sql.SQLException ex) {
                LOG.error ( ex.toString() );
            }
        }
        return num_locked_connections;
    }

}

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package persistence;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 *
 * @author 
 */
public class ALConfigurationConnection {
    //private static final String CONFIG_FILENAME = "config.xml";

    private String dbDriverName = null;
    private String dbUser = null;
    private String dbPassword = null;
    private String dbURI = null;

    private int dbPoolMinSize = 0;
    private int dbPoolMaxSize = 0;

    private static final Log LOG = 
    	LogFactory.getLog( ALConfigurationConnection.class );
    
    /* Deve pegar os dados da conexão para cada usuário */
    public ALConfigurationConnection() {        

        try {
            // Se conecta à base do usuário e pega a configuração
            this.setDbDriverName(dbDriverName);
            this.setDbUser(dbUser);
            this.setDbPassword(dbPassword);            
            this.setDbPoolMinSize(dbPoolMinSize);
            this.setDbPoolMaxSize(dbPoolMaxSize);

        }   catch ( Exception ex ) {
            LOG.error( "Could not read configuration file: ", ex );
        }

    }    

    public String getDbDriverName() {
        return dbDriverName;
    }

    public void setDbDriverName(String dbDriverName) {
        this.dbDriverName = dbDriverName;
    }

    public String getDbUser() {
        return dbUser;
    }

    public void setDbUser(String dbUser) {
        this.dbUser = dbUser;
    }

    public String getDbPassword() {
        return dbPassword;
    }

    public void setDbPassword(String dbPassword) {
        this.dbPassword = dbPassword;
    }

    public String getDbURI() {
        return dbURI;
    }

    public void setDbURI(String dbURI) {
        this.dbURI = dbURI;
    }

    public int getDbPoolMinSize() {
        return dbPoolMinSize;
    }

    public void setDbPoolMinSize(int dbPoolMinSize) {
        this.dbPoolMinSize = dbPoolMinSize;
    }

    public int getDbPoolMaxSize() {
        return dbPoolMaxSize;
    }

    public void setDbPoolMaxSize(int dbPoolMaxSize) {
        this.dbPoolMaxSize = dbPoolMaxSize;
    }
}