How do I configure C3P0 connection pool in JDBC?

To configure c3p0 as a JDBC connection pool, you typically:

  1. create a DataSource (usually ComboPooledDataSource)
  2. tune pool parameters (min/max pool size, idle time, timeouts, statement cache)
  3. use the DataSource everywhere instead of DriverManager.getConnection(...)
  4. close Connection/Statement/ResultSet normally (c3p0 returns connections to the pool on close())

Below are the common ways to do it.


1) Programmatic configuration (most common for plain JDBC)

package org.kodejava.jdbc;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public final class C3p0DataSourceFactory {
    private static final ComboPooledDataSource ds = new ComboPooledDataSource();

    static {
        try {
            ds.setDriverClass("org.postgresql.Driver");
        } catch (PropertyVetoException e) {
            throw new ExceptionInInitializerError(e);
        }

        ds.setJdbcUrl("jdbc:postgresql://localhost:5432/<db>");
        ds.setUser("<db_user>");
        ds.setPassword("<db_password>");

        // Pool sizing
        ds.setMinPoolSize(5);
        ds.setInitialPoolSize(5);
        ds.setMaxPoolSize(20);
        ds.setAcquireIncrement(2);

        // Timeouts / lifecycles
        ds.setCheckoutTimeout(10_000);          // ms to wait for a connection
        ds.setMaxIdleTime(300);                 // seconds before idle conns are culled
        ds.setMaxConnectionAge(0);              // 0 = no limit (consider setting in prod)
        ds.setIdleConnectionTestPeriod(60);     // seconds between idle tests

        // Validation (pick ONE strategy; this is the most reliable)
        ds.setPreferredTestQuery("SELECT 1");
        ds.setTestConnectionOnCheckout(false);
        ds.setTestConnectionOnCheckin(true);

        // Statement caching (optional)
        ds.setMaxStatements(200);
        ds.setMaxStatementsPerConnection(20);
    }

    private C3p0DataSourceFactory() {}

    public static DataSource dataSource() {
        return ds;
    }

    // Optional: call on app shutdown (desktop apps / simple main())
    public static void shutdown() {
        ds.close();
    }

    // Example usage
    public static void main(String[] args) throws Exception {
        try (Connection c = dataSource().getConnection();
             PreparedStatement ps = c.prepareStatement("SELECT now()");
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                System.out.println(rs.getObject(1));
            }
        } finally {
            shutdown();
        }
    }
}

Notes on the key settings

  • minPoolSize / maxPoolSize: your main capacity knobs.
  • checkoutTimeout: prevents threads from waiting forever when the pool is exhausted.
  • Connection testing:
    • Prefer preferredTestQuery + testConnectionOnCheckin=true (or periodic tests) to avoid “broken connection” surprises.
    • testConnectionOnCheckout=true is safer but adds latency to every borrow.
  • Statement cache helps if you repeatedly run the same SQL.

2) Configure via c3p0.properties (cleaner config)

Create src/main/resources/c3p0.properties:

# Basic
c3p0.jdbcUrl=jdbc:postgresql://localhost:5432/<db>
c3p0.user=<db_user>
c3p0.password=<db_password>
c3p0.driverClass=org.postgresql.Driver

# Pool sizing
c3p0.minPoolSize=5
c3p0.initialPoolSize=5
c3p0.maxPoolSize=20
c3p0.acquireIncrement=2

# Timeouts / health checks
c3p0.checkoutTimeout=10000
c3p0.maxIdleTime=300
c3p0.idleConnectionTestPeriod=60
c3p0.preferredTestQuery=SELECT 1
c3p0.testConnectionOnCheckin=true

# Statement cache (optional)
c3p0.maxStatements=200
c3p0.maxStatementsPerConnection=20

Then your Java becomes minimal:

package org.kodejava.jdbc;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;

public final class DataSources {
    private static final ComboPooledDataSource ds = new ComboPooledDataSource(); // reads c3p0.properties

    public static DataSource c3p0() {
        return ds;
    }

    public static void shutdown() {
        ds.close();
    }
}

c3p0 will auto-load configuration from:

  • c3p0.properties on the classpath, or
  • c3p0-config.xml (more advanced, supports named configs)

3) Important usage rule: always close connections

With pooling, connection.close() does not close the physical DB connection; it returns it to the pool. So keep using try-with-resources:

try (Connection con = dataSource.getConnection();
     PreparedStatement ps = con.prepareStatement("SELECT * FROM t WHERE id = ?");
) {
  ps.setLong(1, 123L);
  // ...
}

4) Common pitfalls (quick checklist)

  • Don’t create a new ComboPooledDataSource per query. Create one and reuse it.
  • Set checkoutTimeout so you fail fast under load.
  • Use a validation query if your DB/network closes idle connections.
  • Tune pool sizes to match DB limits (max connections on the server) and your concurrency.

Maven dependencies

<dependencies>
  <dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.11.1</version>
  </dependency>

  <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.7</version>
  </dependency>
</dependencies>

Maven Central Maven Central

How to Configure Apache DBCP Connection Pool in JDBC?

To configure an Apache DBCP connection pool for “plain” JDBC, you typically create a pooled DataSource once at startup, then get connections from it (and always close them to return to the pool).

Below are the two most common approaches.

1) Recommended (DBCP2): BasicDataSource (simplest)

Create and configure the pool

package org.kodejava.jdbc;

import org.apache.commons.dbcp2.BasicDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public final class Dbcp2Example {
    public static DataSource dataSource() {
        BasicDataSource ds = new BasicDataSource();
        ds.setUrl("jdbc:postgresql://localhost:5432/app");
        ds.setUsername("<db_user>");
        ds.setPassword("<db_password>");

        ds.setMaxTotal(20);
        ds.setMaxIdle(10);
        ds.setMinIdle(2);

        ds.setValidationQuery("SELECT 1");
        ds.setTestOnBorrow(true);

        return ds;
    }

    public static void main(String[] args) throws Exception {
        DataSource ds = dataSource();
        try (Connection c = ds.getConnection();
             PreparedStatement ps = c.prepareStatement("SELECT 1");
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) System.out.println(rs.getInt(1));
        }
    }
}

Key idea: with pooling, conn.close() does not close the physical DB connection; it returns it to the pool.


2) Lower-level (more flexible): PoolingDataSource + Commons Pool

This approach wires DBCP to Commons Pool manually (useful when you want full control over the pool object/config).

package org.kodejava.jdbc;

import org.apache.commons.dbcp2.ConnectionFactory;
import org.apache.commons.dbcp2.DriverManagerConnectionFactory;
import org.apache.commons.dbcp2.PoolableConnection;
import org.apache.commons.dbcp2.PoolableConnectionFactory;
import org.apache.commons.dbcp2.PoolingDataSource;
import org.apache.commons.pool2.impl.GenericObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPoolConfig;

import javax.sql.DataSource;

public final class ManualDbcpPoolFactory {

    public static DataSource createDataSource() {
        ConnectionFactory cf =
                new DriverManagerConnectionFactory("jdbc:postgresql://localhost:5432/app",
                        "<db_user>", "<db_password>");

        PoolableConnectionFactory pcf = new PoolableConnectionFactory(cf, null);
        pcf.setValidationQuery("SELECT 1");

        GenericObjectPoolConfig<PoolableConnection> config = new GenericObjectPoolConfig<>();
        config.setMaxTotal(20);
        config.setMaxIdle(10);
        config.setMinIdle(2);
        config.setTestOnBorrow(true);

        GenericObjectPool<PoolableConnection> pool = new GenericObjectPool<>(pcf, config);
        pcf.setPool(pool);

        return new PoolingDataSource<>(pool);
    }
}

Practical tuning checklist (what to set and why)

  • Sizing
    • maxTotal: hard cap of concurrent borrowed connections.
    • maxIdle / minIdle: how many connections to keep around to absorb spikes.
  • Validation
    • Use validationQuery (or validationQueryTimeout) and pick one strategy:
      • testOnBorrow=true (safer, slightly more overhead), or
      • testWhileIdle=true + eviction run (common for reducing borrow-time latency).
  • Timeouts
    • maxWaitMillis: how long callers wait for a free connection before failing.
  • Always close
    • Ensure try-with-resources everywhere; leaks will exhaust the pool.

Maven dependencies

<dependencies>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-dbcp2</artifactId>
        <version>2.14.0</version>
    </dependency>

    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-pool2</artifactId>
        <version>2.13.1</version>
    </dependency>

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.7.7</version>
    </dependency>
</dependencies>

Maven Central Maven Central Maven Central

How do I use connection pooling in JDBC with HikariCP?

To use connection pooling in plain JDBC with HikariCP, the main shift is:

  • stop using DriverManager.getConnection(...) everywhere
  • create one DataSource (the pool) at startup
  • whenever you need a DB connection, call dataSource.getConnection()
  • always close resources with try-with-resources (closing returns the connection to the pool, it does not kill the physical connection)

1) Create a pooled DataSource once

A simple “factory” that builds a singleton pool:

package org.kodejava.jdbc;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import javax.sql.DataSource;
import java.time.Duration;

public final class DataSourceFactory {
    private static final HikariDataSource dataSource = create();

    private DataSourceFactory() {}

    private static HikariDataSource create() {
        HikariConfig config = new HikariConfig();

        config.setJdbcUrl("jdbc:postgresql://localhost:5432/app_db");
        config.setUsername("db_user");
        config.setPassword("db_password"); // use env vars/secret store in real apps

        // Pool sizing (tune per app + DB limits)
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(2);

        // Timeouts
        config.setConnectionTimeout(Duration.ofSeconds(5).toMillis()); // wait for a connection from pool
        config.setIdleTimeout(Duration.ofMinutes(5).toMillis());
        config.setMaxLifetime(Duration.ofMinutes(30).toMillis());

        // Optional: validation / observability
        config.setPoolName("AppHikariPool");

        return new HikariDataSource(config);
    }

    public static DataSource getDataSource() {
        return dataSource;
    }

    /** Call this on application shutdown */
    public static void shutdown() {
        dataSource.close();
    }
}

Notes:

  • maximumPoolSize is usually the most important setting.
  • Prefer one pool per database, not one per DAO/class.

2) Use it in JDBC code (and always close)

Example query using the pooled DataSource:

package org.kodejava.jdbc;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserRepository {
    private final DataSource dataSource;

    public UserRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public String findEmailById(long id) throws SQLException {
        String sql = "select email from users where id = ?";

        try (Connection con = dataSource.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {

            ps.setLong(1, id);

            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? rs.getString("email") : null;
            }
        }
    }
}

Key point: con.close() (done by try-with-resources) returns the connection to the pool.


3) Shutdown cleanly

If you’re writing a CLI app / desktop app / simple server, ensure the pool is closed on exit:

package org.kodejava.jdbc;

public class App {
    public static void main(String[] args) throws Exception {
        var ds = DataSourceFactory.getDataSource();
        var repo = new UserRepository(ds);

        System.out.println(repo.findEmailById(1L));

        DataSourceFactory.shutdown();
    }
}

For long-running apps, register a shutdown hook:

Runtime.getRuntime().addShutdownHook(new Thread(DataSourceFactory::shutdown));

4) Common configuration tips (practical)

  • Pool size: start with maximumPoolSize=10 for typical web apps, then tune using metrics and DB limits.
  • Don’t set minimumIdle too high unless you truly need warm connections.
  • Transactions: still work the same (use con.setAutoCommit(false) and commit/rollback), but make sure you always return the connection to the pool.
  • If you see “connection leak” warnings, it usually means some path didn’t close the connection (missing try-with-resources).

Maven dependencies

<dependencies>
  <dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>6.3.0</version>
  </dependency>

  <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.7</version>
  </dependency>
</dependencies>

Maven Central Maven Central

How do I create a reusable SSH connection pool with JSch in a multithreaded application?

Creating a reusable SSH connection pool using JSch in a multithreaded application involves managing connections efficiently and ensuring thread safety. JSch (Java Secure Channel) does not natively provide a connection pooling feature, so you have to implement it manually using a pooling library or write your own pooling logic.

Below is the step-by-step guide to implementing a reusable SSH connection pool with JSch.

1. Define an SSH Connection Pool

You can use a thread-safe pool, such as Java’s BlockingQueue, to manage SSH connections. Here’s how:

Define a Connection Pool Manager

package org.kodejava.jsch;

import com.jcraft.jsch.*;
import java.util.concurrent.*;

public class SSHConnectionPool {
    private final BlockingQueue<Session> pool;
    private final JSch jsch;
    private final String username;
    private final String host;
    private final int port;
    private final String password; // or private key if using key-based authentication

    public SSHConnectionPool(int poolSize, String username, String password, 
                             String host, int port) throws JSchException {
        this.pool = new LinkedBlockingQueue<>(poolSize); // Thread-safe pool
        this.jsch = new JSch();
        this.username = username;
        this.host = host;
        this.port = port;
        this.password = password;

        for (int i = 0; i < poolSize; i++) {
            pool.offer(createSession()); // Initialize the pool with SSH sessions
        }
    }

    private Session createSession() throws JSchException {
        Session session = jsch.getSession(username, host, port);
        session.setPassword(password);

        // Configuration - Disable strict host checking for simplicity
        java.util.Properties config = new java.util.Properties();
        config.put("StrictHostKeyChecking", "no");
        session.setConfig(config);

        session.connect();
        return session;
    }

    public Session borrowSession() throws InterruptedException {
        return pool.take(); // Borrow a session from the pool
    }

    public void returnSession(Session session) {
        if (session != null) {
            pool.offer(session); // Return session to the pool
        }
    }

    public void close() {
        // Close all sessions and clear the pool
        for (Session session : pool) {
            session.disconnect();
        }
        pool.clear();
    }
}

2. Usage in a Multi-Threaded Application

You can now use SSHConnectionPool in a multithreaded environment. For every task, borrow a session, perform the necessary operations, and return the session to the pool.

Example

package org.kodejava.jsch;

import com.jcraft.jsch.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class SSHPoolDemo {
    public static void main(String[] args) {
        try {
            // Create a pool with 5 connections
            SSHConnectionPool pool = new SSHConnectionPool(5, "username", 
                    "password", "example.com", 22);

            // Thread pool for executing tasks
            ExecutorService executorService = Executors.newFixedThreadPool(10);

            for (int i = 0; i < 10; i++) {
                executorService.submit(() -> {
                    Session session = null;
                    try {
                        // Borrow a session
                        session = pool.borrowSession();

                        // Execute commands via ChannelExec
                        ChannelExec channel = (ChannelExec) session.openChannel("exec");
                        channel.setCommand("echo Hello, World!");
                        channel.setInputStream(null);
                        channel.setErrStream(System.err);

                        channel.connect();

                        // Read the output
                        try (var input = channel.getInputStream()) {
                            int data;
                            while ((data = input.read()) != -1) {
                                System.out.print((char) data);
                            }
                        }

                        channel.disconnect();
                    } catch (Exception e) {
                        e.printStackTrace();
                    } finally {
                        // Return the session to the pool
                        pool.returnSession(session);
                    }
                });
            }

            // Shutdown thread pool after tasks are complete
            executorService.shutdown();

            // Clean up the connection pool
            pool.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

3. Notes

  • Thread Safety: LinkedBlockingQueue ensures thread-safe access to the pool.
  • Session Validity: Before returning a session to the pool, consider checking if it is still alive. JSch does not reconnect automatically if a session is disconnected.
  • Connection Configuration: You can use private key authentication by adding:
jsch.addIdentity("/path/to/private_key");
  • Resource Cleanup: Always close the pool properly to avoid resource leaks.

By following this setup, you can create a reusable and thread-safe SSH connection pool in a multithreaded application.


Maven Dependencies

<dependency>
    <groupId>com.jcraft</groupId>
    <artifactId>jsch</artifactId>
    <version>0.1.55</version>
</dependency>

Maven Central

How do I create a database connection pool?

This example shows you how to create a connection pool implementation using the Apache Commons DBCP library.

package org.kodejava.commons.dbcp;

import org.apache.commons.dbcp2.*;
import org.apache.commons.pool2.impl.GenericObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPoolConfig;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class ConnectionPoolExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    private GenericObjectPool<PoolableConnection> connectionPool = null;

    public static void main(String[] args) throws Exception {
        ConnectionPoolExample demo = new ConnectionPoolExample();
        DataSource dataSource = demo.setUp();
        demo.printStatus();

        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement("SELECT * FROM author")) {
            demo.printStatus();

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println("Author name: " + rs.getString("author_name"));
            }
        }

        demo.printStatus();
    }

    public DataSource setUp() {
        // Creates a connection factory object, which will be used by
        // the pool to create the connection object. We pass the
        // JDBC url info, username and password.
        ConnectionFactory cf = new DriverManagerConnectionFactory(
                ConnectionPoolExample.URL,
                ConnectionPoolExample.USERNAME,
                ConnectionPoolExample.PASSWORD);

        // Creates a PoolableConnectionFactory that will wrap the
        // connection object created by the ConnectionFactory to add
        // object pooling functionality.
        PoolableConnectionFactory pcf = new PoolableConnectionFactory(cf, null);
        pcf.setValidationQuery("SELECT 1");

        // Creates an instance of GenericObjectPool that holds our
        // pool of connection objects.
        GenericObjectPoolConfig<PoolableConnection> config = new GenericObjectPoolConfig<>();
        config.setTestOnBorrow(true);
        config.setMaxTotal(10);
        connectionPool = new GenericObjectPool<>(pcf, config);
        pcf.setPool(connectionPool);

        return new PoolingDataSource<>(connectionPool);
    }

    private GenericObjectPool<PoolableConnection> getConnectionPool() {
        return connectionPool;
    }

    /**
     * Prints connection pool status.
     */
    private void printStatus() {
        System.out.println("Max   : " + getConnectionPool().getNumActive() + "; " +
                "Active: " + getConnectionPool().getNumActive() + "; " +
                "Idle  : " + getConnectionPool().getNumIdle());
    }
}

The code shows the following status as output example:

Max   : 0; Active: 0; Idle  : 0
Max   : 1; Active: 1; Idle  : 0
Author name: Raoul-Gabriel Urma
Author name: Mario Fusco
Author name: Alan Mycroft
Max   : 0; Active: 0; Idle  : 1

Maven Dependencies

<dependencies>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-dbcp2</artifactId>
        <version>2.10.0</version>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.1.0</version>
    </dependency>
</dependencies>

Maven Central Maven Central