sql

JDBC Connection Pool Example

Connection pooling is a mechanism to create and maintain a collection of JDBC connection objects. The primary objective of maintaining the pool of connection object is to leverage re-usability and improve the overall performance of the application.

In this article, we will try to show how connection pooling mechanism can be applied to a Java application.

1. Introduction

JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.
 

 
The JDBC library includes API for each of the tasks commonly associated with the database usage:

  • Making a connection to the database.
  • Creating SQL statements.
  • Executing SQL queries in the database.
  • Viewing and Modifying the resulting records.

1.1 JDBC Connection Pooling

Connection pooling is a process where we maintain a cache of database connections and has become the standard for middleware database drivers. The process of creating a connection, always an expensive, time-consuming operation, is multiplied in these environments where a large number of users are accessing the database in short, unconnected operations. Creating connections over and over in these environments is simply too expensive.

A connection pool operates by performing the work of creating connections ahead of time. In the case of a JDBC connection pool, a pool of Connection objects are created at the time the application server starts. These objects are then managed by a pool manager that disperses connections as they are requested by clients and returns them to the pool when it determines the client is finished with the Connection object. A great deal of housekeeping is involved in managing these connections.

When the connection pool server starts, it creates a predetermined number of Connection objects. A client application would then perform a lookup to retrieve a reference to a DataSource object that implements the ConnectionPoolDataSource interface. The client application would not need to make any special provisions to use the pooled data source; the code would be no different from code written for a non-pooled DataSource.

When the client application requests a connection from the ConnectionPoolDataSource, the data source implementation would retrieve a physical connection to the client application. The ConnectionPoolDataSource would return a Connection object that implemented the PooledConnection interface.

The PooledConnection interface dictates the use of event listeners. These event listeners allow the connection pool manager to capture important connection events, such as attempts by the client application to close the connection. When the driver traps a close-connection event, it intercedes and performs a pseudo-close operation that merely takes the Connection object, returns it to the pool of available connection, and performs any housekeeping that is necessary.

The operation of the connection pool should be completely transparent to the client application. The triggering of connection events, the manipulation of the object pool, and the creation and destruction of physical connections are all managed by the pool manager. The activities of the connection pool are, however, configurable by the application developer.

1.2 Download and Install MySQL

You can watch this video in order to download and install the MySQL database on your windows operations system.

Now, open up the Eclipse IDE and let’s start building the application!

2. JDBC Connection Pool Example

2.1 Tools Used

We are using Eclipse Kepler SR2, JDK 8, MySQL database and Maven (to download the MySQL connector jar and Connection Pool libraries). Having said that, we have tested the code against JDK 1.7 and it works well.

2.2 Project Structure

Firstly, let’s review the final project structure, in case you are confused about where you should create the corresponding files or folder later!

Fig. 1: JDBC Connection Pool Application Project Structure
Fig. 1: JDBC Connection Pool Application Project Structure

2.3 Project Creation

This section will demonstrate on how to create a Dynamic Web Java Maven project with Eclipse. In Eclipse IDE, go to File -> New -> Maven Project

Fig. 2: Create Maven Project
Fig. 2: Create Maven Project

In the New Maven Project window, it will ask you to select project location. By default, ‘Use default workspace location‘ will be selected. Select the ‘Create a simple project (skip archetype selection)‘ checkbox and just click on next button to proceed.

Fig. 3: Project Details
Fig. 3: Project Details

It will ask you to ‘Enter a group id for the artifact.’ We will input the details as shown in the below image. The version number will be by default 0.0.1-SNAPSHOT.

Fig. 4: Archetype Parameters
Fig. 4: Archetype Parameters

Click on Finish and now creating a maven project is completed. If you observe, it has downloaded the maven dependencies and a pom.xml file will be created. It will have the following code:

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>JdbcPool</groupId>
	<artifactId>JdbcPool</artifactId>
	<version>0.0.1-SNAPSHOT</version>
</project>

We can start adding the dependencies that developers want like MySQL, Connection Pooling jars etc. Let’s start building the application!

3. Application Building

Below are the steps involved in developing this application:

3.1 Database & Table Creation

This tutorial uses a database called tutorialDb . The database is not included when you create the project in Eclipse so you first need to create the database to follow this tutorial:

  • Create a new database tutorialDb as:
CREATE DATABASE tutorialDb;
  • Use the created database tutorialDb to create table as:
USE tutorialDb;
  • Create the table technical_editors as shown below:
CREATE TABLE technical_editors (tech_id int(11) NOT NULL AUTO_INCREMENT, tech_username varchar(20) DEFAULT NULL, PRIMARY KEY (tech_id));
  • Now we shall insert some values into the technical_editors table as:
INSERT INTO technical_editors (tech_id, tech_username) VALUES (1, 'Java Code Geek');

INSERT INTO technical_editors (tech_id, tech_username) VALUES (2, 'Harry Potter');

If everything goes well, the table will be shown as below in the MySQL workbench,

Fig. 5: Database and Table Creation
Fig. 5: Database and Table Creation

3.2 Maven Dependencies

In this example, we are using latest MySQL version i.e. mysql-connector-java-5.1.41 and connection pooling dependencies. The updated file will have the following code:

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>JdbcPool</groupId>
	<artifactId>JdbcPool</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	<dependencies>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.41</version>
		</dependency>
		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>
	</dependencies>
</project>

3.3 Java Class Creation

Let’s create the required java files. Right click on src/main/java folder, New -> Package

Fig. 6: Java Package Creation
Fig. 6: Java Package Creation

A new pop window will open where we will enter the package name as com.jcg.jdbc.connection.pooling

Fig. 7: Java Package Name (com.jcg.jdbc.connection.pooling)
Fig. 7: Java Package Name (com.jcg.jdbc.connection.pooling)

Once the package is created in the application, we will need to create the required implementation class. Right click on the newly created package, New -> Class

Fig. 8: Java Class Creation
Fig. 8: Java Class Creation

A new pop window will open and enter the file name as ConnectionPool. The implementation class will be created inside the package: com.jcg.jdbc.connection.pooling

Fig. 9: Java Class (ConnectionPool.java)
Fig. 9: Java Class (ConnectionPool.java)

3.3.1 Implementation of Main Class

In this class, we are using the GenericObjectPool that provides robust pooling functionality for arbitrary objects and will have the main() method to execute the application. Add the following code to it:

ConnectionPool.java

package com.jcg.jdbc.connection.pooling;

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

import javax.sql.DataSource;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

public class ConnectionPool {

	// JDBC Driver Name & Database URL
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	static final String JDBC_DB_URL = "jdbc:mysql://localhost:3306/tutorialDb";

	// JDBC Database Credentials
	static final String JDBC_USER = "root";
	static final String JDBC_PASS = "admin@123";

	private static GenericObjectPool gPool = null;

	@SuppressWarnings("unused")
	public DataSource setUpPool() throws Exception {
		Class.forName(JDBC_DRIVER);

		// Creates an Instance of GenericObjectPool That Holds Our Pool of Connections Object!
		gPool = new GenericObjectPool();
		gPool.setMaxActive(5);

		// Creates a ConnectionFactory Object Which Will Be Use by the Pool to Create the Connection Object!
		ConnectionFactory cf = new DriverManagerConnectionFactory(JDBC_DB_URL, JDBC_USER, JDBC_PASS);

		// Creates a PoolableConnectionFactory That Will Wraps the Connection Object Created by the ConnectionFactory to Add Object Pooling Functionality!
		PoolableConnectionFactory pcf = new PoolableConnectionFactory(cf, gPool, null, null, false, true);
		return new PoolingDataSource(gPool);
	}

	public GenericObjectPool getConnectionPool() {
		return gPool;
	}

	// This Method Is Used To Print The Connection Pool Status
	private void printDbStatus() {
		System.out.println("Max.: " + getConnectionPool().getMaxActive() + "; Active: " + getConnectionPool().getNumActive() + "; Idle: " + getConnectionPool().getNumIdle());
	}

	public static void main(String[] args) {
		ResultSet rsObj = null;
		Connection connObj = null;
		PreparedStatement pstmtObj = null;
		ConnectionPool jdbcObj = new ConnectionPool();
		try {	
			DataSource dataSource = jdbcObj.setUpPool();
			jdbcObj.printDbStatus();

			// Performing Database Operation!
			System.out.println("\n=====Making A New Connection Object For Db Transaction=====\n");
			connObj = dataSource.getConnection();
			jdbcObj.printDbStatus(); 

			pstmtObj = connObj.prepareStatement("SELECT * FROM technical_editors");
			rsObj = pstmtObj.executeQuery();
			while (rsObj.next()) {
				System.out.println("Username: " + rsObj.getString("tech_username"));
			}
			System.out.println("\n=====Releasing Connection Object To Pool=====\n");			
		} catch(Exception sqlException) {
			sqlException.printStackTrace();
		} finally {
			try {
				// Closing ResultSet Object
				if(rsObj != null) {
					rsObj.close();
				}
				// Closing PreparedStatement Object
				if(pstmtObj != null) {
					pstmtObj.close();
				}
				// Closing Connection Object
				if(connObj != null) {
					connObj.close();
				}
			} catch(Exception sqlException) {
				sqlException.printStackTrace();
			}
		}
		jdbcObj.printDbStatus();
	}
}

4. Run the Application

To run the application, Right click on the ConnectionPool class, Run As -> Java Application.

Fig. 10: Run Application
Fig. 10: Run Application

5. Project Demo

The code shows the following status as output:

Fig. 11: Project Output
Fig. 11: Project Output

That’s all for this post. Happy Learning!!

6. Conclusion

Here, we understood what is connection pooling and how we can implement the same using the DBCP library.

7. Download the Eclipse Project

This was an example of JDBC Connection Pooling.

Download
You can download the full source code of this example here: JDBC Connection Pooling

Yatin

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Anoo
Anoo
5 years ago

Plz reply to my email

Krishna
Krishna
4 years ago

This is very helpful for beginners. Keep it up.

Back to top button