Connection pooling options with JDBC: DBCP vs C3P0

JavaJdbcConnection PoolingC3p0Apache Commons-Dbcp

Java Problem Overview

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

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

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?

Java Solutions

Solution 1 - Java

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.

Solution 2 - Java

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

Solution 3 - Java

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.

Solution 4 - Java

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

validationQuery="Some Query"

testOnBorrow=true

Solution 5 - Java

Another alternative is HikariCP.

Here is the comparison benchmark

Solution 6 - Java

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");

Solution 7 - Java

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</strike>

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

Solution 8 - Java

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?

Solution 9 - Java

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.

Solution 10 - Java

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.

Solution 11 - Java

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.

Solution 12 - Java

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.

Solution 13 - Java

A good alternative which is easy to use is http://www.snaq.net/java/DBPool/">DBPool</a>;.

"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/

Solution 14 - Java

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.

Solution 15 - Java

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).

Solution 16 - Java

To Implement the C3P0 in best way then check this answer

C3P0:

For enterprise application, C3P0 is best approach. C3P0 is an easy-to-use library for augmenting traditional (DriverManager-based) JDBC drivers with JNDI-bindable DataSources, including DataSources that implement Connection and Statement Pooling, as described by the jdbc3 spec and jdbc2 std extension. 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.

So this is why c3p0 and other connection pools also have prepared statement caches- it allows application code to avoid dealing with all this. The statements are usually kept in some limited LRU pool, so common statements reuse a PreparedStatement instance.

Worse still DBCP was returning Connection objects to the application for which the underlying transport had broken. A common use case for c3p0 is to replace the standard DBCP connection pooling included with Apache Tomcat. Often times, a programmer will run into a situation where connections are not correctly recycled in the DBCP connection pool and c3p0 is a valuable replacement in this case.

In current updates C3P0 has some brilliant features. those are given bellow:

ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setMinPoolSize();
dataSource.setMaxPoolSize();
dataSource.setMaxIdleTime();
dataSource.setMaxStatements();
dataSource.setMaxStatementsPerConnection();
dataSource.setMaxIdleTimeExcessConnections();

Here, max and min poolsize define bounds of connection that means how minimum and maximum connection this application will take. MaxIdleTime() define when it will release the idle connection.

DBCP:

This approach is also good but have some drawbacks like connection timeout and connection realeasing. 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. 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)

Thanks :)

Categories
Recommended Java Solutions
Recommended Jdbc Solutions
Recommended Connection Pooling Solutions
Recommended C3p0 Solutions
Recommended Apache Commons-Dbcp Solutions

Previous Solution:

Access to Modified Closure

C#ResharperClosures

Next Solution:

What is Shelving in TFS?

TfsShelve

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionDemaView Question on Stackoverflow
Solution 1 - Javaj pimmelView Answer on Stackoverflow
Solution 2 - JavawwadgeView Answer on Stackoverflow
Solution 3 - Javauser542651View Answer on Stackoverflow
Solution 4 - JavaBrandon TeoView Answer on Stackoverflow
Solution 5 - JavaKunalView Answer on Stackoverflow
Solution 6 - JavaoᴉɹǝɥɔView Answer on Stackoverflow
Solution 7 - Javauser187702View Answer on Stackoverflow
Solution 8 - JavatoolkitView Answer on Stackoverflow
Solution 9 - JavamjbView Answer on Stackoverflow
Solution 10 - JavaUBIK LOAD PACKView Answer on Stackoverflow
Solution 11 - JavaLarry HView Answer on Stackoverflow
Solution 12 - JavannsView Answer on Stackoverflow
Solution 13 - JavaSoundlinkView Answer on Stackoverflow
Solution 14 - JavaJeevan PatilView Answer on Stackoverflow
Solution 15 - JavaKyle ZhangView Answer on Stackoverflow
Solution 16 - JavaMd. Sajedul KarimView Answer on Stackoverflow