Java/Apache Common/Connection Pool

Basic DataSource Example

import java.sql.Connection;
import org.apache.rumons.dbcp.BasicDataSource;
public class BasicDataSourceExample {
  public static void main(String args[]) throws Exception {
    BasicDataSource bds = new BasicDataSource();
    bds.setDriverClassName("com.mysql.jdbc.Driver");
    bds.setUrl("jdbc:mysql://localhost/commons");
    bds.setUsername("root");
    bds.setPassword("");
//    bds.setInitialSize(5);
    Connection connection = bds.getConnection();
    System.err.println(connection);
    connection.close();
  }
}

Connection Pool Basics

import java.sql.Connection;
import java.util.Properties;
import java.sql.PreparedStatement;
import org.apache.rumons.dbcp.ConnectionFactory;
import org.apache.rumons.dbcp.PoolingDriver;
import org.apache.rumons.dbcp.PoolingDataSource;
import org.apache.rumons.dbcp.DriverConnectionFactory;
import org.apache.rumons.dbcp.PoolableConnectionFactory;
import org.apache.rumons.dbcp.DriverManagerConnectionFactory;
import org.apache.rumons.pool.impl.GenericObjectPool;
import org.apache.rumons.pool.KeyedObjectPoolFactory;
import org.apache.rumons.pool.impl.GenericKeyedObjectPoolFactory;
public class ConnectionPoolBasics {
  public static void main(String args[]) throws Exception {
    GenericObjectPool gPool = new GenericObjectPool();
    /*Class.forName("com.mysql.jdbc.Driver");
    DriverManagerConnectionFactory cf =
      new DriverManagerConnectionFactory(
        "jdbc:mysql://localhost/commons", "root", "");*/
    Properties props = new Properties();
    props.setProperty("Username", "root");
    props.setProperty("Password", "");
    ConnectionFactory cf =
      new DriverConnectionFactory(new com.mysql.jdbc.Driver(),
                                  "jdbc:mysql://localhost/commons",
                    props);

    KeyedObjectPoolFactory kopf =new GenericKeyedObjectPoolFactory(null, 8);
    PoolableConnectionFactory pcf =  new PoolableConnectionFactory(cf,
                                    gPool,
                                    kopf,
                                    null,
                                    false,
                                    true);

    for(int i = 0; i < 5; i++) {
      gPool.addObject();
    }
    // PoolingDataSource pds = new PoolingDataSource(gPool);
    PoolingDriver pd = new PoolingDriver();
    pd.registerPool("example", gPool);
    for(int i = 0; i < 5; i++) {
      gPool.addObject();
    }
    Connection conn = java.sql.DriverManager.getConnection("jdbc:apache:commons:dbcp:example");
    System.err.println("Connection: " + conn ); //": Delegate: " + ((org.apache.rumons.dbcp.PoolingConnection)conn).getDelegate());
    // do some work with the connection
    PreparedStatement ps = conn.prepareStatement("Select * from customer where id = ?");
    System.err.println("Active: " + gPool.getNumActive() + ", Idle: " + gPool.getNumIdle());
    conn.close();
    System.err.println("Active: " + gPool.getNumActive() + ", Idle: " + gPool.getNumIdle());
  }
}

Database connection pool demo

import org.apache.rumons.pool.impl.GenericObjectPool;
import org.apache.rumons.dbcp.*;
import java.sql.*;

public class DBCPDemo{
  public static void main(String args[]) throws Exception{
    // create a generic pool
    GenericObjectPool pool = new GenericObjectPool(null);
    // use the connection factory which will wraped by
    // the PoolableConnectionFactory
    DriverManagerConnectionFactory cf =  new DriverManagerConnectionFactory(
                                            "jdbc:jtds:sqlserver://myserver:1433/tandem", 
                                            "user", 
                                            "pass");
    PoolableConnectionFactory pcf =  new PoolableConnectionFactory(cf, pool, null, "SELECT * FROM mysql.db", false, true);
    // register our pool and give it a name
    new PoolingDriver().registerPool("myPool", pool);
    // get a connection and test it
        Connection conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:myPool");
        // now we can use this pool the way we want.
        System.err.println("Are we connected? " + !conn.isClosed());
        System.err.println("Idle Connections: " + pool.getNumIdle() + ", out of " + pool.getNumActive());
  }
}