JDBC Connection Pooling

For JDC database connection pooling you can use this cool datasource called the BasicDataSource which comes out of the commons-dbcp library (and also requires commons-collections and commons-pool). You can set parameters like the initial pool size, the maximum pool size, whether abandoned connections are removed etc.

They key thing to remember while using the connection pooling is that the datasource object is the scope of the connection pool. ie. if you create a new datasource object then you create a new connection pool. So you need to create one datasource and then either pass it to all your child classes (which something like SPring does for you pretty well), or you refer to a static instance of it from your child classes.

This may sound pretty obvious but I've been working on some code today that had this flaw and and was pretty hard to tell it was there. If you have other users/apps hitting the same DB then it is worth creating a new users for this test. The key is to set the maximum pool size to a low number eg. 10), set the initial pool size to 5, and then run up a 20 or 30 threads. You should not see more than 10 connections to the database (use 'show processlist' in MySQL). If you do, then the pooling isn't working and your code is probably creating datasources as it goes. Nasty!