For a Java app outside of a J2EE container, which connection pool library is the best?
Therefore I'm left with BoneCP and DBPool. From what I can tell both have limited activity. The main difference I can see is performance, which BoneCP seems to win out with. However the documentation is pretty weak.
Which database pool library have you used in the real world and why? What was the good and bad?
At work we have used BoneCP (as the replacement for c3p0) and as far as I know haven't had any issues (I did not do the upgrade myself). From what I have seen and read it seems like a well-designed solid library, and I would personally use it over alternatives: it appears to be one of those "just works" libraries that are nice to have around.
Nothing negative to say about DBPool, I am just not familiar enough with it; although looking at its site documentation certainly seems like a plus.
We use C3P0 both in and outside of Tomcat. However, the monitoring and logging isn't the greatest, so we're going to start using the SpringSource connection pool. One of the best features I'm looking forward to is showing exactly what SQL statements are running at any particular time.
One thing we had to add to C3P0 was a means of timing how long a particular connection request waits for a connection when the pool is full and all the connections are busy:
public Connection getConnection() throws SQLException
{
long t = System.currentTimeMillis();
ComboPooledDataSource ds = (ComboPooledDataSource) getDelegate();
Connection conn = null;
if (ds.getNumBusyConnections() == ds.getMaxPoolSize())
{
logger.info("Pool (" + ds.getUser() + ") full, waiting for connection");
conn = ds.getConnection();
t = System.currentTimeMillis() - t;
logger.info("Connection busy wait time (" + ds.getUser() + "): " + t + "ms");
}
else
{
conn = ds.getConnection();
}
return conn;
}
So the things you have to consider:
BoneCP looks fast (I haven't heard of it before) but honestly C3P0 has been more than fast for us as well. Back when we tested a few 4 or 5 years ago DBCP was horrendously slow (they appear to have fixed that), Oracle's pool was fairly slow, and C3P0 was very fast. Our test looked very much like the one on BoneCP's site.
I don't know anything about BoneCP's manageability. #3 has turned out to be the most important functionality in a production environment for us.
Take a look at HikariCP which replaces BoneCP https://brettwooldridge.github.io/HikariCP/ This is the one I'm using now in my project.
When we were making our choice a couple years ago, it was just between c3p0 and dbcp. At that time c3p0 was the one that could rebuild its connections after oracle restart. With DBCP we had to restart the app server to get it running again.
Also I find c3p0 debug hanging connections feature extremely useful for tracking connection leaks which can be otherwise extremely hard to find.
What I was missing from c3p0 is useful logging for executed statements with information about how long they took.
I am currently trialing BoneCP in a large enterprise intranet environment. I had consistent threading issues with c3p0 (pretty common ones if you dig around), so I did my research and it seemed like the best stock library. The configuration is a bit of an exercise, but once you get it down, it seems great.
I was using c3p0 along with DataNucleus/JPA and it was easy to switch to BoneCP. Practically all I had to do is change the DataSource configuration in the Spring context file.
As far as I saw the BoneCP benchmarks are really good: http://www.databaseskill.com/2282333/, http://jolbox.com/benchmarks.html
