1. Database Connection Pooling In Java ( Best Solutions ) | How2do

Database Connection Pooling DBCP:

DBCP (Database Connection Pooling) is a technique used in software development to manage a pool of database connections. It helps improve the performance and scalability of applications that interact with databases by reusing existing connections instead of creating new ones for each request.

Apache DBCP (Database Connection Pool) is a popular Java library that provides an implementation of connection pooling. It is commonly used in Java-based applications to manage database connections efficiently.

Here’s a brief overview of how DBCP works:

1. Configuration: To use DBCP ( Database Connection Pooling ), you need to configure it with the necessary parameters such as the database URL, username, password, and pool size. These configurations are typically stored in a properties file or provided programmatically.
2. Connection Pool Creation: Once the configuration is set, DBCP ( Database Connection Pooling ) creates a pool of initial database connections based on the specified pool size. These connections are established with the database server and are ready to be used.
3. Request for Connection: When your application needs to interact with the database, it requests a connection from the pool by calling a method provided by Database Connection Pooling.
4. Connection Reuse: DBCP returns an available connection from the pool to your application. If there are no idle connections available, DBCP ( Database Connection Pooling ) can create new connections up to the maximum pool size (if not exceeded) or wait until a connection becomes available.
5. Connection Release: After your application completes its database operation, it releases the connection back to DBCP by calling a corresponding method. The released connection becomes available for reuse.
6. Connection Validation: DBCP ( Database Connection Pooling ) can optionally validate connections before returning them to the application. It performs a quick validation check to ensure that the connection is still active and usable. If a connection fails the validation, DBCP removes it from the pool and creates a new one to replace it.

DBCP ( Database Connection Pooling ) provides various configuration options to customize the behavior of connection pooling, such as maximum pool size, minimum idle connections, connection timeout, and more. These options allow you to optimize the pool based on your application’s specific requirements.

By utilizing connection pooling with DBCP, you can reduce the overhead of establishing new database connections for each request, thereby improving the performance and scalability of your application when dealing with database interactions.

Database Connection Pooling is achieved with connection thread pooling. The goal of connection thread pooling is to allow serving many requests.

In DBCP it works the following way. Depending on how you configured your DBCP either on Tomcat start-up or on the web application deployment a specific number of connection threads is generated. Whenever a new connection request comes, it is queued to the requests queue. If there is an available free (idle) connection thread, the request from the queue is allocated to that connection thread immediately, otherwise the request needs to wait in the queue until a connection thread becomes available.

When the connection thread is allocated it becomes (active), until it is deallocated by the web application explicitly or by the Database Connection Pooling implicitly (after abandoned wait time expires). This schema allows reusing connection threads and avoiding creating a connection thread for each new connection request. In addition, it allows developers to assume that each connection request will have a dedicated JDBC connection. We will discuss later in more detail how to configure the connection pool.

Requirements :

• Eclipse IDE
• Tomcat Server
• MySQL database
• MySQL Java Driver

Steps : Database Connection Pooling DBCP

mysql> create database connpool;

Now we create and populate the table from which we will fetch the data:

mysql> use connpool; 
mysql> create table data(id int(5) not null unique auto_i
ncrement, name varchar(255) not null);
mysql> insert into data(name) values("How2Do."),("co.in");

• In Eclipse Create Dynamic Web Project :

Click  File 🡪 New 🡪 Dynamic Web Project

•  Add jar ( Create a jar folder inside the project and copy all given jars)

/Connection Pooling/jar/commons-beanutils-1.8.2.jar

/Connection Pooling/jar/commons-collections-3.2.1.jar

/Connection Pooling/jar/commons-dbcp2-2.0.jar

/Connection Pooling/jar/commons-digester-2.1.jar

/Connection Pooling/jar/commons-logging-1.2.jar

/Connection Pooling/jar/commons-pool2-2.7.0 (1).jar

/Connection Pooling/jar/mysql-connector-java-5.1.35.jar

• Create package under src 🡪 new 🡪 package (com.how2do.dbutil)

• Create DBUtil.java ( Datasource Configuration)

package com.how2do.dbutil;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
public class DButil {
private static BasicDataSource dataSource;
	static{
		try {
//minimum  configuration
			dataSource = new BasicDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/connpool");
			dataSource.setUsername("your_username");
			dataSource.setPassword("your_password");
			dataSource.setMinIdle(-1);
			dataSource.setMaxIdle(-1);
} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static DataSource getDataSource(){
		return dataSource;
	}
}

Explanation:

• The code is defining a class named DButil within the com.how2do.dbutil package.
• It imports the necessary classes from the javax.sql and org.apache.commons.dbcp2 packages.
• The class contains a static variable named dataSource of type BasicDataSource from the DBCP library.
• In the static block, the dataSource object is configured with the required properties:
  • setDriverClassName sets the JDBC driver class name for the database you are connecting to (in this case, MySQL).
  • setUrl sets the URL of the database server.
  • setUsername and setPassword provide the credentials for authentication.
  • setMinIdle and setMaxIdle set the minimum and maximum number of idle connections in the pool (-1 means no limit).
• If any exception occurs during the configuration process, it is printed to the console.
• Finally, the class provides a static method getDataSource() that returns the configured dataSource object.

To use this DButil class in your application, you can call DButil.getDataSource() to retrieve the DataSource object, which can be used to obtain connections from the connection pool for performing database operations.

• Create package under src 🡪 new 🡪 package (com.how2do.test)

• Create Test.java ( For Connection Testing)

package com.how2do.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.how2do.dbutil.DButil;

public class Test {
	public static void main(String[] args) {
		try {
			Connection con = DButil.getDataSource().getConnection();
			PreparedStatement ps = con.prepareStatement("select * from data");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				System.out.print(rs.getInt(1) + "\t");
				System.out.print(rs.getString(2) + "\t");
				System.out.println();

			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

Explanation:

• The code is defining a class named Test within the com.how2do.test package.
• It imports the necessary classes for database connectivity and the DButil class from your custom package.
• In the main method, the code obtains a connection from the connection pool by calling DButil.getDataSource().getConnection()
• A PreparedStatement is created with a SQL query: "select * from data".
• The query is executed using executeQuery(), and the resulting ResultSet is retrieved.
• Inside the while loop, the code retrieves the values from each row of the ResultSet and prints them.
• Any exceptions that occur during the process are caught and printed to the console.

To execute this code, make sure you have the necessary database driver library in your project’s classpath. Also, ensure that your database table data exists and contains appropriate columns that match the data types being retrieved in the code (an integer and a string).

When you run the Test class, it will retrieve the data from the data table and print it to the console.

Here :

maxTotal: Maximum number of database connections in pool. Make sure you configure your mysqld max_connections large enough to handle all of your db connections. Set to -1 for no limit.

maxIdle: Maximum number of idle database connections to retain in pool.
Set to -1 for no limit. See also the DBCP documentation on this and the minEvictableIdleTimeMillis configuration parameter.

maxWaitMillis: Maximum time to wait for a database connection to become available in ms, in this example 10 seconds. An Exception is thrown if this timeout is exceeded. Set to -1 to wait indefinitely.

username and password: MySQL username and password for database connections

driverClassName: Class name for the old mm.mysql JDBC driver is
org.gjt.mm.mysql.Driver – we recommend using Connector/J though.
Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.

url: The JDBC connection url for connecting to your MySQL database. “jdbc:mysql://localhost:3306/connpool”

Resource name=”jdbc/TestDB” auth=”Container” type=”javax.sql.DataSource”
maxTotal=”100″ maxIdle=”30″ maxWaitMillis=”10000″
username=”your_username” password=”your_password”
driverClassName=”com.mysql.jdbc.Driver”

The Tomcat JDBC Connection Pool :

The Tomcat JDBC Connection Pool is a popular connection pooling library specifically designed for use with the Apache Tomcat server. It provides a high-performance, scalable, and reliable connection pool implementation for managing database connections in Tomcat-based applications.

The Tomcat JDBC Connection Pool offers several advantages:

1. Performance: It provides efficient connection pooling mechanisms that help reduce the overhead of establishing and tearing down database connections. Reusing existing connections improves application performance.
2. Concurrency: The connection pool supports concurrent access by multiple threads, allowing multiple requests to execute database operations concurrently. This enhances the scalability and responsiveness of the application.
3. Configuration flexibility: The connection pool offers a wide range of configuration options, allowing you to fine-tune its behavior based on your application’s requirements. You can adjust parameters such as maximum pool size, idle connection timeout, validation queries, and more.
4. Connection validation: The connection pool includes built-in connection validation capabilities. It can automatically validate connections before returning them to the application, ensuring that the connection is still active and usable. This helps prevent issues caused by stale or broken connections.
5. Advanced features: The Tomcat JDBC Connection Pool supports advanced features such as statement caching, transaction isolation level configuration, connection leak detection, and interception of connection creation and closing events.

To use the Tomcat JDBC Connection Pool in your Tomcat-based application, you typically need to perform the following steps:

1. Add the library: Include the Tomcat JDBC Connection Pool library (typically a JAR file) in your project’s dependencies. You can download the library from the official Apache Tomcat website or use a build tool like Maven or Gradle to manage dependencies.
2. Configure the connection pool: Create a configuration file, usually named context.xml, to define the connection pool settings. This file is typically placed in the META-INF or WEB-INF directory of your web application. The configuration includes details such as the database URL, username, password, and other properties specific to the connection pool implementation.
3. Configure the data source: In the context.xml file, define a data source configuration using the <Resource> element. This configuration specifies the connection pool class (org.apache.tomcat.jdbc.pool.DataSource), as well as the connection pool settings such as the maximum pool size, validation queries, etc.
4. Reference the data source: In your application code, you can obtain a connection from the Tomcat JDBC Connection Pool by looking up the configured data source. This can be done using JNDI (Java Naming and Directory Interface) lookup or by using a utility class provided by the connection pool library.

By utilizing the Tomcat JDBC Connection Pool, you can benefit from efficient and scalable database connection management within your Tomcat-based applications.

The JDBC Connection Pool org.apache.tomcat.jdbc.pool is a replacement or an alternative to the commons-dbcp connection pool.

Here are a few of the reasons:

1. commons-dbcp is single threaded, in order to be thread safe commons-dbcp locks the entire pool, even during query validation.
2. commons-dbcp is slow – as the number of logical CPUs grow, the performance suffers, the above point shows that there is not support for high concurrency Even with the enormous optimizations of the synchronized statement in Java 6, commons-dbcp still suffers in speed and concurrency.
3. commons-dbcp is complex, over 60 classes. tomcat-jdbc-pool, core is 8 classes, hence modifications for future requirement will require much less changes. This is all you need to run the connection pool itself, the rest is gravy.
4. commons-dbcp uses static interfaces. This means you can’t compile it with JDK 1.6, or if you run on JDK 1.6/1.7 you will get NoSuchMethodException for all the methods not implemented, even if the driver supports it.
5. The commons-dbcp has become fairly stagnant. Sparse updates, releases, and new feature support.
6. It’s not worth rewriting over 60 classes, when something as a connection pool can be accomplished with as a much simpler implementation.
7. Tomcat jdbc pool implements a fairness option not available in commons-dbcp and still performs faster than commons-dbcp
8. Tomcat jdbc pool implements the ability retrieve a connection asynchronously, without adding additional threads to the library itself
9. Tomcat jdbc pool is a Tomcat module, it depends on Tomcat JULI, a simplified logging framework used in Tomcat.
10. Retrieve the underlying connection using the javax.sql.PooledConnection interface.
11. Starvation proof. If a pool is empty, and threads are waiting for a connection, when a connection is returned, the pool will awake the correct thread waiting. Most pools will simply starve.

Steps : Tomcat JDBC Connection Pool

mysql> create database connpool;

Now we create and populate the table from which we will fetch the data:

mysql> use connpool; 
mysql> create table data(id int(5) not null unique auto_i
ncrement, name varchar(255) not null);
mysql> insert into data(name) values("how2do."),("co.in");

Create Simple Maven Project :

Click  File 🡪New🡪Maven Project (Tomcat JDBC Connection Pool)

Add the Following Dependencies :

<dependency>
	<groupId>org.apache.tomcat</groupId>
	<artifactId>tomcat-jdbc</artifactId>
	<version>9.0.30</version>
</dependency>
	
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.47</version>
</dependency>

• Create package under src 🡪 new 🡪 package (com.how2do.dbutil)
• Create DBUtil.java ( Datasource Configuration)

import java.sql.SQLException;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;

public class DButil {

	public static DataSource dataSource() throws SQLException {

	PoolProperties p = new PoolProperties();
	p.setUrl("jdbc:mysql://localhost:3306/connpool");
        p.setDriverClassName("com.mysql.jdbc.Driver");
		p.setUsername("root");
		p.setPassword("admin");

		// additional properties
		p.setJmxEnabled(true);
		p.setTestWhileIdle(false);
		p.setTestOnBorrow(true);
		p.setValidationQuery("SELECT 1");
		p.setTestOnReturn(false);
		p.setValidationInterval(30000);
		p.setTimeBetweenEvictionRunsMillis(30000);
		p.setMaxActive(100);
		p.setInitialSize(10);
		p.setMaxWait(10000);
		p.setRemoveAbandonedTimeout(60);
		p.setMinEvictableIdleTimeMillis(30000);
		p.setMinIdle(10);
		p.setLogAbandoned(true);
		p.setRemoveAbandoned(true);
		p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
				+ "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
		DataSource datasource = new DataSource();
		datasource.setPoolProperties(p);

		return datasource;
	}

}

Explanation:

• The code defines a class named DButil.
• It imports the necessary classes from the org.apache.tomcat.jdbc.pool package.
• The class provides a static method named dataSource() that returns a DataSource object.
• Inside the method, a PoolProperties object is created and configured with the necessary properties for the connection pool.
• The properties include the database URL, driver class name, username, and password.
• Additional properties are set to customize the behavior of the connection pool, such as connection validation, eviction policies, pool size, timeouts, and more.
• A DataSource object is created and configured with the PoolProperties.
• Finally, the DataSource object is returned.

To use this DButil class, you can call DButil.dataSource() to obtain the configured DataSource object, which can be used to obtain connections from the Tomcat JDBC Connection Pool for database operations in your application.

• Create package under src 🡪 new 🡪 package (com.how2do.test)
• Create Test.java ( For Connection Testing)

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

public class Test {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        ResultSet rs = null;
        Statement st = null;
        try {
            con = DButil.dataSource().getConnection();
            st = con.createStatement();
            rs = st.executeQuery("select * from data");

            while (rs.next()) {
                System.out.print(rs.getInt(1) + "\t");
                System.out.print(rs.getString(2) + "\t");
                System.out.println();
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Explanation:

• The code remains similar to the previous version, with a few modifications in the exception handling and closing of resources.
• Inside the main method, a Connection, ResultSet, and Statement objects are declared and initialized to null.
• In the try block, the code obtains a connection from the Tomcat JDBC Connection Pool using the DButil.dataSource().getConnection() method.
• A statement is created using the createStatement() method.
• The SQL query is executed using executeQuery() and the resulting ResultSet is retrieved.
• Inside the while loop, the code retrieves the values from each row of the ResultSet and prints them.
• In the finally block, the resources (ResultSet, Statement, and Connection) are closed using the close() method to release any acquired database resources.

Note: The resources should be closed in a finally block to ensure they are released even if an exception occurs. Additionally, each resource is checked for null before calling close() to avoid NullPointerException.