Java JDBC connection pool using Tomcat JDBC Connection Pool

Java JDBC library does not ship with any connection pooling mechanism. The lack of it impends the application performance. One has either implement a connection pooling manually or use a third-party library to achieve that. One of the best options out there is the Tomcat JDBC Connection Pool. In this tutorial, we discuss how to utilize the Java JDBC connection pool using Tomcat JDBC Connection Pool.

Introduction

Creating a database connection is a resource-intensive task. Hence, it is crucial to have a pool of connections at hand to reduce system overload, especially when aiming to perform many database operations. Frameworks such as EJB, Spring Boot, and Hibernate automatically provide a connection pool for developers effortlessly and by default. At most, one only has to fine-tune settings such as pool size, TTL, etc.

JDBC does not provide any pool mechanism. The only remaining option is utilizing a third-party library, and the best option is Tomcat JDBC Connection Pool.

It is important to note that we do not recommend using the standalone JDBC without any frameworks for a production environment. The plain JDBC is more suitable for school and college projects. However, for the sake of the experiment, in this article, we demonstrate how to manually configure a JDBC connection pool, without any third-party framework. Use it at your own risk.

Adding Tomcat JDBC Connection Pool library

Adding the Tomcat JDBC Pool library to a Maven project is straightforward. We only need to open the pom.xml file and add the below dependency.

<dependency>
    <groupId>org.apache.tomcat</groupId>
    <artifactId>tomcat-jdbc</artifactId>
    <version>11.0.0-M7</version>
</dependency>

The next step is to create a configuration class to initialize the database connection and use the Tomcat JDBC pool library. Let’s create a class and call it DataSourceConfig.java as follows,

import java.sql.Connection;
import java.sql.SQLException;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;

public class DataSourceConfig {
    public static final String DATA_SOURCE_CLASS_NAME = "org.postgresql.Driver";
    public static final String DATA_SOURCE_URL = "jdbc:postgresql://%s:%s/%s";
    public static final String JDBC_INTERCEPTORS = "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;" + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer";

    private String dataSourceClassName = DATA_SOURCE_CLASS_NAME;
    private String hostName;
    private String portNumber;
    private String userName;
    private String password;
    private String dbName;
    private PoolProperties properties = new PoolProperties();
    private DataSource datasource = new DataSource();

    public String getHostName() {
        return hostName;
    }

    public String getPortNumber() {
        return portNumber;
    }

    public String getUserName() {
        return userName;
    }

    public String getPassword() {
        return password;
    }

    public String getDbName() {
        return this.dbName;
    }

    public void setHostName(String hostName) {
        this.hostName = hostName;
    }

    public void setPortNumber(String portNumber) {
        this.portNumber = portNumber;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setDbName(String dbName) {
        this.dbName = dbName;
    }

    public void setupPool() {
        String url = String.format(DATA_SOURCE_URL, hostName, portNumber, dbName);
        properties.setUrl(url);
        properties.setDriverClassName(DATA_SOURCE_CLASS_NAME);
        properties.setUsername(userName);
        properties.setPassword(password);
        properties.setJmxEnabled(true);
        properties.setTestWhileIdle(false);
        properties.setTestOnBorrow(true);
        properties.setValidationQuery("SELECT 1");
        properties.setTestOnReturn(false);
        properties.setValidationInterval(30000);
        properties.setTimeBetweenEvictionRunsMillis(30000);
        properties.setMaxActive(100);
        properties.setInitialSize(10);
        properties.setMaxWait(10000);
        properties.setRemoveAbandonedTimeout(60000);
        properties.setMinEvictableIdleTimeMillis(30000);
        properties.setMinIdle(10);
        properties.setLogAbandoned(true);
        properties.setRemoveAbandoned(true);
        properties.setJdbcInterceptors(JDBC_INTERCEPTORS);
        datasource.setPoolProperties(properties);
    }

    public Connection getConnection() throws SQLException {
        return datasource.getConnection();
    }
}

Much of the above code is borrowed from the official Tomcat JDBC Connection Pool website, here. However, there are a couple of things to note about it.

Firstly, the DATA_SOURCE_CLASS_NAME value depends on the database type. In this example, we use the PostgreSQL database. If you use a different data store, adjust the driver name accordingly.

Secondly, the HostUrl, Username, Password, and PortNumber values should be set immediately after the class is initialized. Otherwise, the code will throw an exception.

Thirdly and most importantly, after setting the database properties, the client code must call the setupPool method. This method establishes the database connection and configures the connection pool. It is similar to the classic Hibernate XML configuration and serves the same purpose.

Lastly, whenever the client code wants to perform a database operation, it must call the getConnection method. The client must refrain from closing the connection after it finishes its task. Otherwise, the connection pool would be without effect.

Now let’s write a sample program that uses our DataSourceConfig code.

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

public class SampleProgram {
    public static void main(String[] args) throws SQLException {
        DataSourceConfig ds = configureConnection();
        String statement = "SELECT id FROM test_tbl";
        try (Connection con = ds.getConnection()) {
            try (PreparedStatement stmt = con.prepareStatement(statement)) {
                ResultSet rs = stmt.executeQuery();
                while (rs.next()) {
                    int id = rs.getInt("id");
                    System.out.println("Id is : " + id);
                }
                rs.close();
            }
        }
    }

    private static DataSourceConfig configureConnection() {
        DataSourceConfig ds = new DataSourceConfig();
        ds.setHostName("localhost");
        ds.setPortNumber("2222");
        ds.setUserName("test");
        ds.setPassword("test");
        ds.setDbName("testDB");
        ds.setupPool();
        return ds;
    }
}

As you can see in the above code, the configureConnection method is called in the first line of the main method. That method initializes the DataSourceConfig class and set the appropriate values such as username, password, and host name. Those values can come from anywhere, such as a configuration server or a simple file.

It is vital to avoid as much value hard coding as possible, especially database credentials.

Afterward, the main method gets a connection from the DataSourceConfig class, runs a query against the database, and prints the retrieved objects’ ids.

Conclusion

In this article, we discussed how to create the Java JDBC connection pool using the Tomcat JDBC Connection Pool library. A connection pool is a must in any production-grade application. However, the recommendation is to use a framework that provides it out of the box. What is discussed above is for educational purposes only. We recommend against using the above code in production.