JDBC Connection Pooling Framework

Guys,

I would like to know which framework is the ideal one for doing a JDBC connection pooling. I would like to have a standalone application and need a connection pooling mechanism for my JDBC database calls.

A standalone application which gets installed on each user PC ??
If that will be the case, then IMO, you don't need any DBCP. Still if you need one, I'll prefer Apache Commons DBCP

I found this to be good enough....http://www.source-code.biz/snippets/java/8.htm

I tried using the DBCP BasicDataSourceExample....as below:

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 
 * @author user
 *
 */
public class BasicDataSourceExample {	
	 
	/**
	 * 
	 * @param args
	 */
	 public static void main(String[] args) {
	  	// First we set up the BasicDataSource.
	 	// Normally this would be handled auto-magically by
	  	// an external configuration, but in this example we'll
	  	// do it manually.
	  	//
	  	System.out.println("Setting up data source.");
	  	DataSource dataSource = setupDataSource("jdbc:oracle:thin:wcms/wcms@localhost:20000:C2t1");
	  	System.out.println("Done.");
	  	
	  	//
	  	// Now, we can use JDBC DataSource as we normally would.
	  	//
	  	Connection conn = null;
	  	Statement stmt = null;
	  	ResultSet rset = null;
	  	
	  	try {
		  	System.out.println("Creating connection.");
		  	conn = dataSource.getConnection();
		  	System.out.println("Creating statement.");
		  	stmt = conn.createStatement();
		  	System.out.println("Executing statement.");
		  	rset = stmt.executeQuery("select name from table_items where item_id = 3758");
		  	System.out.println("Results:");
		  	int numcols = rset.getMetaData().getColumnCount();
		  	while(rset.next()) {
		  		for(int i=1;i<=numcols;i++) {
		  			System.out.print("\t" + rset.getString(i));
		  		}
		  		System.out.println("");
		 	}
		  	printDataSourceStats(dataSource);
		} 
	  	catch(SQLException e) {
	  		e.printStackTrace();
	  	} finally {
	 	try { rset.close(); } catch(Exception e) { }
	 	try { stmt.close(); } catch(Exception e) { }
	 	try { conn.close(); } catch(Exception e) { }
	 	}
	 }
	
	 /**
	  * 
	  * @param connectURI
	  * @return
	  */
	 public static DataSource setupDataSource(String connectURI) {
	 	BasicDataSource ds = new BasicDataSource();
	 	ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
	 	ds.setUsername("wcms");
	 	ds.setPassword("wcms");
	 	ds.setUrl(connectURI);
	 	ds.setInitialSize(10);
	 	return ds;
	 }
	 
	 /**
	  * 
	  * @param ds
	  * @throws SQLException
	  */
	 public static void printDataSourceStats(DataSource ds) throws SQLException {
		BasicDataSource bds = (BasicDataSource) ds;
	 	System.out.println("NumActive: " + bds.getNumActive());
	  	System.out.println("NumIdle: " + bds.getNumIdle());
	 }
	 
	 /**
	  * 
	  * @param ds
	  * @throws SQLException
	  */
	 public static void shutdownDataSource(DataSource ds) throws SQLException {
		 BasicDataSource bds = (BasicDataSource) ds;
		 bds.close();
	 }
}

Where did 7 come from?? I was the only one who ran this program. I created a connection pool size of 10...

I mean in the above example, where is the Connection object getting pooled?

What is the difference between the above sample and the one here in this link...http://commons.apache.org/dbcp/apidocs/org/apache/commons/dbcp/datasources/package-summary.html (Without JNDI.....given at the bottom of the page)

Jothi Shankar Kumar wrote:I mean in the above example, where is the Connection object getting pooled?

If you want pooling, why are you using a BasicDataSource instead of a PoolingDataSource?

Yes, what are the differences between the two?

After having read their respective javadocs, what do *you* think the differences are?

What can I infer from just there two lines of Javadoc...

Basic implementation of javax.sql.DataSource that is configured via JavaBeans properties. This is not the only way to combine the commons-dbcp and commons-pool packages, but provides a "one stop shopping" solution for basic requirements.

Do you mean to say, I have to try something like this?

public static DataSource setupDataSource(String connectURI) {
	  	//
	  	// 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.
	  	//
	  	ObjectPool connectionPool = new GenericObjectPool(null);
	  	
	  	//
	  	// Next, we'll create a ConnectionFactory that the
	  	// pool will use to create Connections.
	  	// We'll use the DriverManagerConnectionFactory,
	  	// using the connect string passed in the command line
	  	// arguments.
	  	//
	  	ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectURI,null);
	  	//
	  	// 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);
	  	
	  	//
	  	// Finally, we create the PoolingDriver itself,
	  	// passing in the object pool we created.
	  	//
	  	PoolingDataSource dataSource = new PoolingDataSource(connectionPool);
	  	
	  	return dataSource;
	 }

I'd start by looking through the example code that's available on the DBCP web site. The README file that goes along with those examples specifically mentions reading the package javadocs of org.apache.commons.dbcp to get an overview of how it all works.

Normally, it is the Connection object that we pool? am I right? But the examples shown in the DBCP actually pools the DataSource.