Connection pooling options with JDBC: DBCP vs C3P0 [closed]

Question

What is the best connection pooling library available for Java/JDBC?

I'm considering the 2 main candidates (free / open-source):

• Apache DBCP - http://commons.apache.org/dbcp/
• C3P0 - http://sourceforge.net/projects/c3p0

I've read a lot about them in blogs and other forums but could not reach a decision.

Are there any relevant alternatives to these two?

Margaretamargarete

Answer 1

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.

UPDATE: It turns out that after many years of sitting on a shelf, the Apache Commons folk have taken DBCP out of dormancy and it is now, once again, an actively developed project. Thus my original post may be out of date.

That being said, I haven't yet experienced this new upgraded library's performance, nor heard of it being de-facto in any recent app framework, yet.

Vadavaden

Thanks! How about the suggested Proxool alternative? The current version of Hibernate comes with both c3p0 and Proxool. – Margaretamargarete

We haven't tried Proxool but I'll be sure to check it out now :) – Vadavaden

c3p0 has some drawbacks. it sometimes fails to handle connection peaks. – Solicitous

things have changed a lot since 4 years when you first posted this answer, could you add an update sharing the current scenario, if possible ? – Chaos

DBCP does not have regalular maintenance releases... e.g. memory leak fixed 2 years ago (issues.apache.org/jira/browse/DBCP-352) is still not in any stable release. This is very sad story which is common for other Apache projects (e.g. log4j). – Cordwain

I highly recommend HikariCP, but then I helped write it. – Kosse

Is this still true? Which one is best for JDBC connection pooling? – Alessandro

@lining There's a guy who continued/updated in 2013 the discussion on his blog starting from this exact answer. Keep also in mind now that there's version 2 of DBCP, and apache suggests to make it run with Java 7 only while Java 8 has been released awhile ago. – Bulbil

is the current answer still relevant? – Feverroot

@Kosse - We are currently using dbcp 1.4 and running into a strange issue. We're using DBCP for high volume batch processing and not sure after 2 hours in test, our batch goes in hang mode. Do you think HikariCP can help here? – Lakesha

Answer 2

I invite you to try out BoneCP -- it's free, open source, and faster than the available alternatives (see benchmark section).

Disclaimer: I'm the author so you could say I'm biased :-)

UPDATE: As of March 2010, still around 35% faster than the new rewritten Apache DBCP ("tomcat jdbc") pool. See dynamic benchmark link in benchmark section.

Update #2: (Dec '13) After 4 years at the top, there's now a much faster competitor : https://github.com/brettwooldridge/HikariCP

Update #3: (Sep '14) Please consider BoneCP to be deprecated at this point, recommend switching to HikariCP.

Update #4: (April '15) -- I no longer own the domain jolbox.com

Lipski

Would really love get a troubleshoot using BoneCP as a Tomcat Datasource. The main problem I had with this was that it required BoneCP Classes in tomcat's lib dir, as well as the log4j and google classes. Doing this made the connection pools work - (it hadn't worked while in WAR) - however it conflicted with the log4j setting of Tomcat and prevented any log output at all from the application, which was a dealbreaker... – Vadavaden

This sounds like a log4j issue more than anything else. Drop me a line on forum.jolbox.com and I'll help you track it down ASAP. – Lipski

1up, BoneCP is brilliant. Switched from C3P0. It even allowed me to remove my dependency on log4jdbc-remix, because it allows statement logging out of the box! – Jeer

We faced serious issue while trying to use BoneCP in our spring+hibernate webapp. We were previously using DBCP, however, just wanted to give BoneCP a try (for performance reason). We have paginated grids (pagination done using jsp framework on full resultset in-memory). On refreshing the same page, it seemed to (as per the view grid) fetch inconsistent number of records. With the same code and just the connection provider changed back to DBCP, it worked as expected! Since I was rushing on a deadline, couldn't dig deep on this, however this was the first hand experience unfortunately :( – Natal

Big thanks for your last update. I've been using BoneCP for 2 years now, but now I discovered HikariCP and it's plain awesomeness! :) – Hepsibah

thanks for the heads up on HikariCP, it's performance seems insane! – Reenter

I am getting a lot of dead threads on the newest bonecp release in fedora using postgresql 9.3, as in the pool was returning closed threads. Any chance this will be fixed? It would be a great connection pool without the bug. – Owenowena

@AndrewScottEvans Probably best to revert to v0.7.1 – Lipski

@wwadgeSite introduce your library lost some content as examples, api doc ... if maybe you should update your article,I was basically programmer,so I wish you can guide our example video library.Ask me how to be able to use your library for Swing. – Graves

It's 2016 - Is HikariCP still the best choice? – Remission

@Lipski the content of jolbox.com has been completely altered... (new owner or new new owner...?) – Bloomer

what a ride. Thanks for the updates. – Slaveholder

Answer 3

I was having trouble with DBCP when the connections times out so I trialled c3p0. I was going to release this to production but then started performance testing. I found that c3p0 performed terribly. I couldn't configure it to perform well at all. I found it twice as slow as DBCP.

I then tried the Tomcat connection pooling.

This was twice as fast as c3p0 and fixed other issues I was having with DBCP. I spent a lot of time investigating and testing the 3 pools. My advice if you are deploying to Tomcat is to use the new Tomcat JDBC pool.

Maim

Answer 4

For the auto-reconnect issue with DBCP, has any tried using the following 2 configuration parameters?

validationQuery="Some Query"

testOnBorrow=true

Luff

As to documentation, testOnBorrow has default value true, so if validationQuery is defined DBCP will test every connection before it is passed to application. – Erek

Answer 5

Another alternative is HikariCP.

Here is the comparison benchmark

Aldarcy

Answer 6

Have been using DBCP for a couple of years now in production. It is stable, survives DB server reboot. Just configure it properly. It only requires a handful of parameters to be specified so don't be lazy. Here is a snippet from our system production code which lists parameters that we explicitly set to make it work:

DriverAdapterCPDS driverAdapterCPDS = new DriverAdapterCPDS();
driverAdapterCPDS.setUrl(dataSourceProperties.getProperty("url"));
driverAdapterCPDS.setUser(dataSourceProperties.getProperty("username"));
driverAdapterCPDS.setPassword(dataSourceProperties.getProperty("password"));
driverAdapterCPDS.setDriver(dataSourceProperties.getProperty("driverClass"));

driverAdapterCPDS.setMaxActive(Integer.valueOf(dataSourceProperties.getProperty("maxActive")));
driverAdapterCPDS.setMaxIdle(Integer.valueOf(dataSourceProperties.getProperty("maxIdle")));
driverAdapterCPDS.setPoolPreparedStatements(Boolean.valueOf(dataSourceProperties.getProperty("poolPreparedStatements")));

SharedPoolDataSource poolDataSource = new SharedPoolDataSource();
poolDataSource.setConnectionPoolDataSource(driverAdapterCPDS);
poolDataSource.setMaxWait(Integer.valueOf(dataSourceProperties.getProperty("maxWait")));
poolDataSource.setDefaultTransactionIsolation(Integer.valueOf(dataSourceProperties.getProperty("defaultTransactionIsolation")));
poolDataSource.setDefaultReadOnly(Boolean.valueOf(dataSourceProperties.getProperty("defaultReadOnly")));
poolDataSource.setTestOnBorrow(Boolean.valueOf(dataSourceProperties.getProperty("testOnBorrow")));
poolDataSource.setValidationQuery("SELECT 0");

Sero

Answer 7

Here are some articles that show that DBCP has significantly higher performance than C3P0 or Proxool. Also in my own experience c3p0 does have some nice features, like prepared statement pooling and is more configurable than DBCP, but DBCP is plainly faster in any environment I have used it in.

Difference between dbcp and c3p0? Absolutely nothing! (A Sakai developers blog) http://blogs.nyu.edu/blogs/nrm216/sakaidelic/2007/12/difference_between_dbcp_and_c3.html

See also the like to the JavaTech article "Connection Pool Showdown" in the comments on the blog post.

Limber

faster in single threaded environments, maybe, buggy and un-stable and just plain broken anywhere else. – Hogtie

Answer 8

Another alternative, Proxool, is mentioned in this article.

You might be able to find out why Hibernate bundles c3p0 for its default connection pool implementation?

Cristicristian

Answer 9

Unfortunately they are all out of date. DBCP has been updated a bit recently, the other two are 2-3 years old, with many outstanding bugs.

Petty

That is true - the last release of C3PO (a 0.9 pre-release) is from May 2007. The latest release of Proxool (a 0.9 pre-release) is from August 2008. The last release of DBCP is also from Apr 2007, but at least its a stable 1.2 release. Is there anything actually maintained out there? – Hesketh

To be fair these are not big projects so you should expect fewer and fewer updates in C3P0/DBCP and time goes by. – Lipski

Answer 10

Dbcp is production ready if configured properly.

It is for example used on a commerce Website of 350000 visitors/ day and with pools of 200 connections.

It handles very well timeouts provided you configure it correctly.

Version 2 is on progress and it has a background which makes it reliable since Many Production problems have been tackled.

We use it for our batch server solution and it has been running hundreds of batches That work on millions of lines in database.

Performance tests run by tomcat jdbc pool show it has better performance than cp30.

Adventurous

UBIK LOAD PACK - We're using DBCP 1.4 and running into constant hangs of our single batch with 10000 records. We are using Spring Batch + JSR 352 and thinking of switching to HikariCP. When you say, 100's of batches running smooth, do you mean its running with DBCP 2.x or any other version? Also, would you mind sharing the configurations? Our configuration is maxActive=150, minIdle=15, maxIdle=75, initialSize=15 but haven't seen hangs go away. We're not using any validationQuery or testOnBorrow / testOnReturn. Do you recommend using it? – Lakesha

Answer 11

Just got done wasting a day and a half with DBCP. Even though I'm using the latest DBCP release, I ran into exactly the same problems as j pimmel did. I would not recommend DBCP at all, especially it's knack of throwing connections out of the pool when the DB goes away, its inability to reconnect when the DB comes back and its inability to dynamically add connection objects back into the pool (it hangs forever on a post JDBCconnect I/O socket read)

I'm switching over to C3P0 now. I've used that in previous projects and it worked and performed like a charm.

Orman

Answer 12

c3p0 is good when we are using mutithreading projects. In our projects we used simultaneously multiple thread executions by using DBCP, then we got connection timeout if we used more thread executions. So we went with c3p0 configuration.

Midinette

Answer 13

my recommendation is

hikari > druid > UCP > c3p0 > DBCP

It's based on what I have tested - 20190202, in my local test environment(4GB mac/mysql in docker/pool minSize=1, maxSize=8), hikari can serve 1024 threads x 1024 times to get connections, average time for each thread to finish is 1 or 2 million seconds, while c3p0 can only serve 256 threads x 1024 times and average time for each thread is already 21 million seconds. (512 threads failed).

Reconcile

Answer 14

A good alternative which is easy to use is DBPool.

"A Java-based database connection pooling utility, supporting time-based expiry, statement caching, connection validation, and easy configuration using a pool manager."

http://www.snaq.net/java/DBPool/

Halfway

I benchmarked DBPool vs BoneCP. DBPool makes getConnection() synchronized amongst other things and is far far slower than BoneCP (see: jolbox.com/forum/viewtopic.php?f=3&t=175). – Lipski

Answer 15

We came across a situation where we needed to introduce connection pool and we had 4 options in front of us.

• DBCP2
• C3P0
• Tomcat JDBC
• HikariCP

We carried out some tests and comparison based on our criteria and decided to go for HikariCP. Read this article which explains why we chose HikariCP.

Gaylagayle