How do I create a BasicDataSource object?

This example demonstrates how to use the BasicDataSource class of Apache Commons DBCP to create a basic requirements for database connection. The configuration of the data source can be defined using some properties method provided by this class. The basic properties is the driver classname, connection url, username and password.

After the datasource ready we can obtain a connection by calling the getConnection() method of the datasource. This method might throw an SQLException when errors occurs.

package org.kodejava.example.commons.dbcp;

import org.apache.commons.dbcp2.BasicDataSource;

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

public class BasicDataSourceExample {
    public static void main(String[] args) throws Exception {
        // Creates a BasicDataSource and defines its properties
        // including the driver class name, jdbc url, username
        // and password.
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setUrl("jdbc:mysql://localhost/kodejava");
        dataSource.setUsername("root");
        dataSource.setPassword("");

        // Get a connection from the data source and do some
        // database query with the obtained connection.
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement("SELECT * FROM authors")) {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println("Name: " + rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

We can simplify the code above so that we don’t have to close the PreparedStatement and Connection manually like we did in the finally block in the code snippet. We can use try-with-resources to automatically close resources. An example can be seen in the following example: How to automatically close resources in JDBC?.

Maven Dependencies

<!-- https://search.maven.org/remotecontent?filepath=org/apache/commons/commons-dbcp2/2.6.0/commons-dbcp2-2.6.0.jar -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.6.0</version>
</dependency>
<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central
Maven Central

How do I create a database connection pool?

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

package org.kodejava.example.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 = "root";
    private static final String PASSWORD = "";

    private GenericObjectPool<PoolableConnection> connectionPool = null;

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

        // Creates a PoolableConnectionFactory that will wraps 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 connections object.
        GenericObjectPoolConfig config = new GenericObjectPoolConfig();
        config.setMaxTotal(10);
        connectionPool = new GenericObjectPool<>(pcf);
        pcf.setPool(connectionPool);

        return new PoolingDataSource<>(connectionPool);
    }

    private GenericObjectPool getConnectionPool() {
        return connectionPool;
    }

    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 authors")) {
            demo.printStatus();

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

        demo.printStatus();
    }

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

The code show the following status as output example:

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

Maven Dependencies

<!-- https://search.maven.org/remotecontent?filepath=org/apache/commons/commons-dbcp2/2.6.0/commons-dbcp2-2.6.0.jar -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.6.0</version>
</dependency>
<!-- https://search.maven.org/remotecontent?filepath=org/apache/commons/commons-pool2/2.6.2/commons-pool2-2.6.2.jar -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-pool2</artifactId>
    <version>2.6.2</version>
</dependency>
<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central
Maven Central
Maven Central