Connection Pools with MariaDB Connector/J

Overview

Java developers can use MariaDB Connector/J to establish connection pools with MariaDB database products.

Connection Pools

Connection pools enable the reuse of database connections to minimize the performance overhead of connecting to the database and the churn of opening and closing connections.

Connection pools hold connections open in a pool. When a process is done with the connection, the connection is returned to the pool rather than being closed, allowing MariaDB Connector/J to acquire a connection as needed.

Several connection pool implementations are available for Java.

Apache Commons Classes

Although several connection pool implementations are available for Java, this documentation uses the commons-dbcp2 implementation from Apache Commons.

The commons-dbcp2 implementation from Apache Commons uses several classes from the org.apache.commons.dbcp2 and org.apache.commons.pool2 packages:

Class

Description

org.apache.commons.dbcp2.ConnectionFactory

Abstract factory interface for creating java.sql.Connection instances.

org.apache.commons.dbcp2.DriverManagerConnectionFactory

A java.sql.DriverManager-based implementation of org.apache.commons.dbcp2.ConnectionFactory.

org.apache.commons.pool2.ObjectPool

A pooling simple interface.

org.apache.commons.pool2.impl.GenericObjectPool

A configurable org.apache.commons.pool2.ObjectPool implementation.

org.apache.commons.pool2.impl.GenericObjectPoolConfig

A simple "struct" encapsulating the configuration for a GenericObjectPool.

org.apache.commons.dbcp2.PoolableConnection

A delegating connection that, rather than closing the underlying connection, returns itself to an ObjectPool when closed. It implements the java.sql.Connection interface.

org.apache.commons.dbcp2.PoolableConnectionFactory

A PooledObjectFactory that creates PoolableConnection instances.

org.apache.commons.dbcp2.PoolingDataSource

A simple java.sql.DataSource implementation that obtains java.sql.Connection instances from the specified ObjectPool.

Code Example: Connection Pooling

The following example program creates a connection pool to a server at the IP address 192.0.2.1 using the user account created in the example setup. A connection pool allows the application to use multiple database connections. If your application requires a single database connection instead, see Connect for a more appropriate example.

import java.sql.*;
import org.apache.commons.dbcp2.*;
import org.apache.commons.pool2.impl.*;
import javax.sql.DataSource;

public class App {

    private static final String URL = "jdbc:mariadb://192.0.2.1:3306?user=db_user&password=db_user_password";

    public static void main(String[] args) {

        GenericObjectPool<PoolableConnection> connectionPool = App.createObjectPool();
        DataSource dataSource = new PoolingDataSource(connectionPool);

        try (Connection conn = dataSource.getConnection()) {

            try (Statement stmt = conn.createStatement()) {

                try (ResultSet rset = stmt.executeQuery("SELECT first_name from test.contacts")) {

                    int numcols = rset.getMetaData().getColumnCount();

                    while (rset.next()) {

                       connectionPoolStatus(connectionPool);

                       for (int i = 1; i <= numcols; i++) {
                           System.out.print("\t" + rset.getString(i));
                       }

                       System.out.println("");
                    }
                }
            }
        } catch (Exception e) {

            e.printStackTrace();
        }

        connectionPoolStatus(connectionPool);
    }

    public static GenericObjectPool<PoolableConnection> createObjectPool() {

        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(URL);

        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
        poolableConnectionFactory.setValidationQuery("SELECT 1");

        GenericObjectPoolConfig<PoolableConnection> config = new GenericObjectPoolConfig<>();
        config.setTestOnBorrow(true);
        config.setMaxTotal(10);

        return new GenericObjectPool<>(poolableConnectionFactory, config);
    }

    private static void connectionPoolStatus(GenericObjectPool<PoolableConnection> connectionPool) {

        System.out.println(String.format("Active: %s; Idle  : %s", connectionPool.getNumActive(), connectionPool.getNumIdle()));
    }
}

The application output lists the number of active and idle connections in the connection pool while the query result set data is being output, and after the Connection is not in use.

Example output:

Active: 1; Idle  : 0
   John
Active: 1; Idle  : 0
   Jon
Active: 1; Idle  : 0
   Johnny
Active: 0; Idle  : 1