How to establish a connection pool in JDBC?

Question

If you need a standalone connection pool, my preference goes to C3P0 over DBCP (that I've mentioned in this previous answer), I just had too much problems with DBCP under heavy load. Using C3P0 is dead simple. From the documentation:

ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "org.postgresql.Driver" ); //loads the jdbc driver
cpds.setJdbcUrl( "jdbc:postgresql://localhost/testdb" );
cpds.setUser("swaldman");
cpds.setPassword("test-password");

// the settings below are optional -- c3p0 can work with defaults
cpds.setMinPoolSize(5);
cpds.setAcquireIncrement(5);
cpds.setMaxPoolSize(20);

// The DataSource cpds is now a fully configured and usable pooled DataSource 

But if you are running inside an application server, I would recommend to use the built-in connection pool it provides. In that case, you'll need to configure it (refer to the documentation of your application server) and to retrieve a DataSource via JNDI:

DataSource ds = (DataSource) new InitialContext().lookup("jdbc/myDS");

HikariCP

It's modern, it's fast, it's simple. I use it for every new project. I prefer it a lot over C3P0, don't know the other pools too well.

Usually if you need a connection pool you are writing an application that runs in some managed environment, that is you are running inside an application server. If this is the case be sure to check what connection pooling facilities your application server providesbefore trying any other options.

The out-of-the box solution will be the best integrated with the rest of the application servers facilities. If however you are not running inside an application server I would recommend the Apache Commons DBCP Component. It is widely used and provides all the basic pooling functionality most applications require.

Don't reinvent the wheel.

Try one of the readily available 3rd party components:

• Apache DBCP - This one is used internally by Tomcat, and by yours truly.
• c3p0

Apache DBCP comes with different example on how to setup a pooling javax.sql.DataSource. Here is one sample that can help you get started.

I would recommend using the commons-dbcp library. There are numerous examples listed on how to use it, here is the link to the move simple one. The usage is very simple:

 BasicDataSource ds = new BasicDataSource();
 ds.setDriverClassName("oracle.jdbc.driver.OracleDriver")
 ds.setUsername("scott");
 ds.setPassword("tiger");
 ds.setUrl(connectURI);
 ...
 Connection conn = ds.getConnection();

You only need to create the data source once, so make sure you read the documentation if you do not know how to do that. If you are not aware of how to properly write JDBC statements so you do not leak resources, you also might want to read this Wikipedia page.

In the app server we use where I work (Oracle Application Server 10g, as I recall), pooling is handled by the app server. We retrieve a javax.sql.DataSource using a JNDI lookup with a javax.sql.InitialContext.

it's done something like this

try {     
   context = new InitialContext();
   jdbcURL = (DataSource) context.lookup("jdbc/CachedDS");
   System.out.println("Obtained Cached Data Source ");
}
catch(NamingException e)   
{  
    System.err.println("Error looking up Data Source from Factory: "+e.getMessage());
}

(We didn't write this code, it's copied from this documentation.)