jdbc connection pooling

Question

Can anybody provide examples or links on how to establish a JDBC connection pool?

From searching google I see many different ways of doing this and it is rather confusing.

Ultimately I need the code to return a java.sql.Connection object, but I am having trouble getting started..any suggestions welcome.

Update: Doesn't javax.sql or java.sql have pooled connection implementations? Why wouldn't it be best to use these?

Update 2: I ended up using Commons DBCP to accomplish the pooling. I used something similar to the code sample suggested by Eric Hauser (see his response below). Can somebody confirm that this actually creates and uses a pool behind the scenes?

Answer 1

If you read the tutorial at http://java.sun.com/developer/onlineTraining/Programming/JDCBook/conpool.html, it will provide you with everything you need including code samples.

Note that it is the getConnection() method of the provided JDCConnectionPool class that returns a Connection object.

Answer 2

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.

Answer 3

Apache Commons has a library for that purpose: DBCP. Unless you have strange requirements around your pools, I'd use a library as it's bound to be trickier and more subtle than you would hope.

Answer 4

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.

Answer 5

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.

Answer 6

As answered by others, you will probably be happy with Apache Dbcp or 3cp0. Both are popular, and work fine.

Regarding your doubt

Doesn't javax.sql or java.sql have pooled connection implementations? Why wouldn't it be best to use these?

They actually dont provide implementations, rather interfaces and some support classes, only revelant to the programmers that implement third party libraries (pools or drivers). Normally you don't even look at that. Your code should deal with the connections from your pool just as they were "plain" connections, totally transparent to you.

Answer 7

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.)

Answer 8

If you need a standalone connection pool, my preference goest 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 using 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");