Jakarta Commons DBCP Tutorial

A quick code preview showing working example of using one of the best-performing JDBC connection pools in Java.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

-- snippet --


        Connection conn = null; PreparedStatement p_stmt = null;

        try {
            conn = ConnectionManager.ds.getConnection();

            p_stmt =  conn.prepareStatement(
            	"INSERT INTO articles (text,url,content) VALUES (?,?,?)");
            p_stmt.setBytes(1, (new String("jsdhgkdjshgkjdshkg")).
            	getBytes("UTF8") );
            p_stmt.setString(2, "http://dsfsfsfdsfdsfda");
            p_stmt.setBytes(3, (new String("aadada")).getBytes("UTF8") );

            p_stmt.executeUpdate();
        }  catch(Exception e) {
                LOG.debug("Failed to execute a JDBC task: ", e );
            } finally {
                try {
                    p_stmt.close();
                    conn.close();
                }  catch ( Exception ex) {
                    LOG.error ( "Failed to finalize JDBC task: ", ex );
                }
            }

-- snippet --

config.xml:

<config>
    <dbDriverName>com.mysql.jdbc.Driver</dbDriverName>
    <dbUser>root</dbUser>
    <dbPassword>whatever</dbPassword>
 <dbURI><![CDATA[jdbc:mysql://localhost/testdb?
 useUnicode=true&characterEncoding=UTF-8&autoReconnect=true]]></dbURI>
    <dbPoolMinSize>30</dbPoolMinSize>
    <dbPoolMaxSize>70</dbPoolMaxSize>
</config>

ConnectionManager.java

package ge.ika.tutorial.dbcp;

import ge.ika.tutorial.dbcp.util.Configuration;

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.*;

/**
 * @ inadareishvili
 */
public class ConnectionManager {

    private static final Log LOG = LogFactory
         .getLog(ConnectionManager.class);

    public static DataSource ds = null;
    private static GenericObjectPool _pool = null;

    /**
    *  @param config configuration from an XML file.
    */
    public ConnectionManager(Configuration config)
    {
        try
        {
            connectToDB( config );
        }
        catch(Exception e)
        {
            LOG.error( "Failed to construct 
            ConnectionManager", e );
        }
    }

    /**
    *  destructor
    */
    protected void finalize()
    {
        LOG.debug("Finalizing ConnectionManager");
        try
        {
            super.finalize();
        }
        catch(Throwable ex)
        {
            LOG.error( "ConnectionManager finalize failed 
            to disconnect from mysql: ", ex );
        }
    }


    /**
    *  connectToDB - Connect to the MySql DB!
    */
    private void connectToDB( Configuration 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
        {
            ConnectionManager.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 {
        //
        // First, we'll need a ObjectPool that serves as the
        // actual pool of connections.
        //
        // We'll use a GenericObjectPool instance, although
        // any ObjectPool implementation will suffice.
        //
        GenericObjectPool connectionPool = new GenericObjectPool(null);

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

        ConnectionManager._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 = ConnectionManager._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 = ConnectionManager.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;
    }

}

Configuration.java

package ge.ika.tutorial.dbcp.util;

import org.jdom.*;
import org.jdom.input.*;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.commons.lang.builder.ReflectionToStringBuilder;

import java.io.*;

/**
 * @ inadareishvili
 */
public class Configuration {

    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( Configuration.class );

    public Configuration() {
        SAXBuilder builder = new SAXBuilder();

        try {

            InputStream is =
            this.getClass().getClassLoader().getResourceAsStream( CONFIG_FILENAME );

            Document doc = builder.build ( is );
            Element root = doc.getRootElement();

            dbDriverName = root.getChild("dbDriverName").getTextTrim();
            dbUser = root.getChild("dbUser").getTextTrim();
            dbPassword = root.getChild("dbPassword").getTextTrim();
            dbURI = root.getChild("dbURI").getTextTrim();
            dbPoolMinSize = 
            	Integer.parseInt( root.getChild("dbPoolMinSize").getTextTrim() );
            dbPoolMaxSize = 
            	Integer.parseInt( root.getChild("dbPoolMaxSize").getTextTrim() );

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

    }


    public String getDbDriverName() {
        return dbDriverName;
    }

    public String getDbUser() {
        return dbUser;
    }

    public String getDbPassword() {
        return dbPassword;
    }

    public String getDbURI() {
        return dbURI;
    }

    public int getDbPoolMinSize() {
        return dbPoolMinSize;
    }

    public int getDbPoolMaxSize() {
        return dbPoolMaxSize;
    }

    public String toString() {
        MultilineToStringStyle style = new MultilineToStringStyle();
        ReflectionToStringBuilder tsb = 
        	new ReflectionToStringBuilder(this, style);
        return tsb.toString();
    }
}