Connection Pooling with C3P0 DataSources

JDBC for SQL Server - 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 SQL Server 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 url = "jdbc:sqlserver://localhost\\SQLEXPRESS;"
      + "user=herong;password=T0pSecret";

    // 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 both SQL Server JDBC and C3P0 JAR files as shown in the previous tutorial.

Let's run it:

herong> java -cp .:\
  c3p0-0.9.5.5.jar:\
  mssql-jdbc-9.4.1.jre16.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 PoolBackedDataSource Info:
   Total Connections: 3
   Busy Connections: 1
   Idle Connections: 2

Database Connection Info:
  Server name: Microsoft SQL Server
  Server version: 15.00.2000
  Driver name: Microsoft JDBC Driver 9.4 for SQL Server
  Driver version: 9.4.1.0
  JDBC major version: 4
  JDBC minor version: 3

Go and count connections on the database server ...

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

1> SELECT loginame, COUNT(dbid) FROM sys.sysprocesses
2> GROUP BY dbid, loginame;
3> GO

loginame
------------------------------------ ------
DESKTOP\Admin                             1
Herong                                    3
NT SERVICE\SQLTELEMETRY$SQLEXPRESS        1
sa                                       13
sa                                       48

(5 rows affected)

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 MySQL or Oracle.