Wednesday, April 3, 2013

Java: Replace Apache Commons DBCP

Until recently I used to use the Apache Commons Database Connection Pool in my JDBC Java applications. Then there was an incident, and I had to replace it with c3p0. Here are my reasons.

I never gave it much thought. A db connection pool just manages the connections... handing them out and taking them back. How hard can that be? It's not something I want to spend time on. There's that project from Apache, and I like their HTTP server. So it's probably a safe bet. I thought.

What's the quality of commons-dbcp?

The hottest question about commons-dbcp on stackoverflow is this one: Someone asking whether to use Apache DBCP or C3P0.

The top answer with many agreements says:
DBCP is out of date and not production grade. Some time back we conducted an in-house analysis of the two, creating a test fixture which generated load and concurrency against the two to assess their suitability under real life conditions.
DBCP consistently generated exceptions into our test application and struggled to reach levels of performance which C3P0 was more than capable of handling without any exceptions.
C3P0 also robustly handled DB disconnects and transparent reconnects on resume whereas DBCP never recovered connections if the link was taken out from beneath it. Worse still DBCP was returning Connection objects to the application for which the underlying transport had broken.
Since then we have used C3P0 in 4 major heavy-load consumer web apps and have never looked back.
The post (from 2009) then goes on and says that recently there was some work on  commons-dbcp. Now, 4  years later, what happened in the meantime?

What's the status of  commons-dbcp?

The website was last updated 2.5 years ago.

The last release 1.4 is 3 years old, 1.4.1 is a snapshot.

The website is broken:
  • It has dead links (for example to the official Javadoc). 
  • The logo image on the left doesn't load.
  • And it has character set issues to display the text (those ? signs). 
Maybe these errors are in place when the website url changed from http://commons.apache.org/dbcp/ to the one it redirects to. Anyway, they don't seem to be important to be fixed.

Commons-dbcp is a subversion repo at svn.apache.org (C3P0 is on GitHub).

My issue

The reason why I had to kick commons-dbcp out was that it broke my application. After a couple hours it stopped handing out connections, and all threads were in WAITING state.
org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1104)
After some reading I tried c3p0, run long running high concurrency tests where commons-dbcp choked, and it all worked fine. And after some more reading I was convinced that the problem was solved.

But it works for me...

Maybe it does. Maybe you just don't hit it hard enough yet. And maybe you don't get to see all errors. As reported by others there are issues with commons-dbcp, and it looks like a dead project. Also, Hibernate comes with c3p0, not commons-dbcp.

But dbcp is faster ...

I've seen statistics where one or the other product stands out. Any connection pool is fast enough for me... as long as it keeps doing its main job.

Other alternatives

I did not look into other, newer products because I'm happy with one that has been in use for long time.

Now there's the Apache Tomcat JDBC Connection Pool and this blog post talks about feeling uncomfortable with commons-dbcp and replacing it with tomcat-jdbc.

And there's BoneCP which says how much faster it is than the others. It started in 2009, had no release in 2 years, and is at version v0.7.1.

I want to replace commons-dbcp with c3p0!

Here's what you have to do if you're using Maven and Spring, connecting to MySQL:

1. Replace the commons-jdbc dependency with this:
<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.2.1</version>
</dependency>
2. Replace some words in the data source bean:
a) change the class to com.mchange.v2.c3p0.ComboPooledDataSource
b) change the word "driverClassName" to "driverClass"
c) change the word "url" to "jdbcUrl"
d) change the word "username" to "user"

Example:
<bean id="geoDataSource" destroy-method="close" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="com.mysql.jdbc.Driver"/>
    <property name="jdbcUrl"     value="jdbc:mysql://localhost/db"/>
    <property name="user"        value="myUser"/>
    <property name="password"    value="myPassword"/>
</bean>

Conclusions

  1. You might want to consider replacing commons-dbcp also. Maybe with c3p0, it works for me.
  2. Maybe commons-dbcp should be moved to their attic.

Update 2014-03-11
C3P0 is under active development on GitHub, and the author nicely replied to my questions on StackOverflow and on the mailing list within hours. Thumbs up.

6 comments:

  1. I know many websites in Apache have been suffering with broken links, but it is due to some internal migration. Maybe it could be the reason for DBCP broken links in their site... thanks for sharing this.

    ReplyDelete
  2. There are newer versions of BoneCP in the maven central repository (http://search.maven.org/)
    Latest one is 0.8.0-rc1 from December 2012. But you are right the latest release is two years old and the homepage does not mention 0.8 anywhere. Not sure what's going on there.

    ReplyDelete
  3. c3p0 0.9.1.2 (that you used) was released in Aug 2007. That makes it nearly 6 years old.

    http://search.maven.org/#search%7Cgav%7C1%7Cg%3A%22c3p0%22%20AND%20a%3A%22c3p0%22

    The new c3p0 is actually under com.mchange:c3p0 :

    http://search.maven.org/#search%7Cgav%7C1%7Cg%3A%22com.mchange%22%20AND%20a%3A%22c3p0%22

    0.9.5-pre3 is May 2013.

    ReplyDelete
  4. Thanks for the info! I'll go with the stable 0.9.2.1 from 20 March 2013.

    old:
    <dependency>
    <groupId>c3p0</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.1.2</version>
    </dependency>

    new:
    <dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.2.1</version>
    </dependency>

    ReplyDelete
  5. Recently rewriting database connection pool, figure out that apache dbcp2 was released. however, never making it work. connection always not returning to the pool. apache dbcp still a fail. now considering to use c3p0.

    ReplyDelete
  6. Hi Roy,
    How are ensuring that connections are not returned to pool ?

    ReplyDelete