Java Database connection pool (BoneCP vs DBPool vs c3p0)

Question

For a Java app outside of a J2EE container, which connection pool library is the best?

• I heard c3p0 is getting outdated.
• Jakarta's common pool library is no longer under development

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?

Di

For those who are curious, I ended up going with BoneCP. – Di

Do you have any link for detailed configuration doc of BoneCP ? Or can u pls share how to use BoneCP in JBoss ? – Cobra

Answer 1

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.

Egocentrism

The latest release of BoneCP is still 0.7.1, does this mean it's still in a "beta" stage or is it mature enough? – Ephemerid

Keep in mind that versioning is very subjective, contextual. So exact value really does not matter that much. Some authors are hesitant to call their library 1.0. JDOM took years to go there, even after being stable. So my guess is that it is stable, fine to use. – Egocentrism

The benchmarks from the BoneCP page [link] (jolbox.com/index.html?page=http://jolbox.com/benchmarks.html) look great. – Lengel

At this point I cannot recommend the current release of BoneCP. I've been running it in production for a couple of days now and it is unacceptably buggy. I'd be using HikariCP if Java 7 was an option for me. – Gollin

@MattBall When you say buggy, in which way? What are some of the issues you experienced? – Di

@StephaneGrenier see the two bugs I linked in my previous comment: bugs.launchpad.net/bonecp/+bug/1243551 and bugs.launchpad.net/bonecp/+bug/1259257 – Gollin

Also note that HikariCP now supports Java 6. – Gollin

Answer 2

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:

1. support and activity (as you've noted)
2. speed
3. monitoring, logging, and production control

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.

Ecphonesis

Regarding logging specific sql statements, can you not enable debug log for java.sql package? – Gyroscope

The SpringSource pool allows realtime inspection via JMX is my understanding. – Ecphonesis

You can enable SQL logging with BoneCP as well: jolbox.com/bonecp/downloads/site/apidocs/index.html?com/jolbox/…, under the method of "setLogStatementsEnabled". – Jordon

Most if not all of them can log. The question is, what SQL statements are running right now, on what threads, in which pools? You can obtain that from logs with some work but JMX is easier from an operations standpoint. – Ecphonesis

@ScottA sorry the post is quite old but can you give a pointer to the SpringSource pool that you mentioned – Nierman

@Nierman The SpringSource high concurrency pool comes with tcServer. You can find configuration instructions here: static.springsource.com/projects/tc-server/6.0/admin/… You're looking for the "tc Server Datasource". – Ecphonesis

Answer 3

Take a look at HikariCP which replaces BoneCP https://brettwooldridge.github.io/HikariCP/ This is the one I'm using now in my project.

Rainger

Answer 4

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.

Hattiehatton

Answer 5

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.

Dilapidation

Answer 6

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

Bonnice