Connection pooling options with JDBC DBCP vs C3P0

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

For the auto-reconnect issue with DBCP  has any tried using the following 2 configuration parameters   validationQuery  Some Query   testOnBorrow true

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Another alternative is HikariCP   Here is the comparison benchmark

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to java

Examples related to jdbc

Examples related to connection-pooling

Examples related to c3p0

Examples related to apache-commons-dbcp