Connection Pooling with C3P0 DataSources

JDBC for MySQL - Herong's Tutorial Examples

∟Connection Pooling with C3P0 DataSources

This section provides a tutorial example that shows you how to use the C3P0 DataSources class to create and convert a unpooled DataSource to a pooled DataSource using PoolBackedDataSource with default configurations.

The another easy way to use C3P0 package for connection pooling is to use the com.mchange.v2.c3p0.DataSources class. It offers 2 statics methods to create and convert a unpooled DataSource to a pooled DataSource using PoolBackedDataSource with default configurations.

Here is my sample Java program, C3p0DataSourcesExample.java against my local MySQL server.

/* C3p0DataSourcesExample.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.DataSources;
import com.mchange.v2.c3p0.PoolBackedDataSource;

public class C3p0DataSourcesExample {
  static java.io.PrintStream out = System.out;

  public static void main(String[] args) throws Exception { 
    String tzid = java.util.TimeZone.getDefault().getID();
    String url = "jdbc:mysql://localhost:3306/HerongDB?"
      + "user=Herong&password=TopSecret&serverTimezone="+tzid;

    // Get a DataSource with pooled connections
    DataSource ds = getDataSource(url);

    // Get a pooled connection
    Connection con = ds.getConnection();
    checkDataSource(ds);

    // Verify the pooled connection
    DatabaseMetaData meta = con.getMetaData();
    out.println();
    out.println("Database Connection Info: ");
    out.println("  Server name: "+meta.getDatabaseProductName());
    out.println("  Server version: "+meta.getDatabaseProductVersion());
    out.println("  Driver name: "+ meta.getDriverName());
    out.println("  Driver version: "+ meta.getDriverVersion());
    out.println("  JDBC major version: "+ meta.getJDBCMajorVersion());
    out.println("  JDBC minor version: "+ meta.getJDBCMinorVersion());

    // Close the pooled connection 
    con.close();

    // Wait for 3 minutes to check the server side
    out.println();
    out.println("Go and count connections on the database server ...");
    Thread.sleep(3*60*1000);

    // Close the DataSource
    closeDataSource(ds);
  }

  public static DataSource getDataSource(String url) throws Exception {
    DataSource unpooled = DataSources.unpooledDataSource(url);
    DataSource pooled = DataSources.pooledDataSource(unpooled);
    return pooled;
  }

  public static void closeDataSource(DataSource ds) throws Exception {
    DataSources.destroy(ds);
  }

  public static void checkDataSource(DataSource ds) throws Exception {
    PoolBackedDataSource pbds = (PoolBackedDataSource) ds;
    out.println();
    out.println("C3P0 PoolBackedDataSource Info: ");
    out.println("   Total Connections: "+pbds.getNumConnections());
    out.println("   Busy Connections: "+pbds.getNumBusyConnections());
    out.println("   Idle Connections: "+pbds.getNumIdleConnections());
  }
}

To run this test program, I need the . See other tutorials to download them.

c3p0-0.9.5.5.jar - To provide JDBC connection pooling.
mysql-connector-java-8.0.27.jar - To provide JDBC driver for MySQL server.

Let's run it:

herong> java -cp .:\
  c3p0-0.9.5.5.jar:\
  mysql-connector-java-8.0.27.jar \
  C3p0DataSourcesExample.java 

9:46:40 PM com.mchange.v2.log.MLog 
INFO: MLog clients using java 1.4+ standard logging.
9:46:45 PM com.mchange.v2.c3p0.C3P0Registry 
INFO: Initializing c3p0-0.9.5.5 [built 11-December-2019 22:07:46 ...]
9:46:45 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource 
  [ acquireIncrement -> 3, 
    acquireRetryAttempts -> 30, 
    ...
  ]

C3P0 ComboPooledDataSource Info: 
   Total Connections: 3
   Busy Connections: 1
   Idle Connections: 2
   Connection URL: jdbc:mysql://localhost:3306/HerongDB?user=Herong...
   User Name: null
   Password: null

Database Connection Info: 
   Server name: MySQL
   Server version: 8.0.17
   Driver name: MySQL Connector/J
   Driver version: mysql-connector-java-8.0.19 (Revision: ...)
   JDBC major version: 4
   JDBC minor version: 2

Go and count connections on the database server ...

On the MySQL server, log in as "root". I see 3 connections:

mysql> show processlist;
+-----+--------+-----------------+----------+---------+--------+
| Id  | User   | Host            | db       | Command | Time   |
+-----+--------+-----------------+----------+---------+--------+
| 121 | Herong | localhost:57469 | herongdb | Sleep   |      9 |
| 122 | Herong | localhost:57468 | herongdb | Sleep   |      9 |
| 123 | Herong | localhost:57470 | herongdb | Sleep   |      9 |
+-----+--------+-----------------+----------+---------+--------+

So, what we can see from this tutorial:

C3P0 DataSources class wraps the creation of the connection pool in 2 static methods calls: unpooledDataSource(url) and pooledDataSource(unpooled). Once created, it can be used in your application to perform normal JDBC operations.
It seems to create 3 connections initially.
You can easily change the connection URL to test C3P0 for connection pooling with Oracle or SQL Server.