Database Connection Pooling

When applications interact with databases, they need to establish a connection to send queries and receive results.
Creating and closing a new database connection every time can be slow and resource-intensive.
This is where connection pooling comes into play. It helps applications reuse existing connections instead of creating new ones repeatedly, making the process much faster and more efficient.

What is Connection Pooling?

Connection pooling is a technique that allows applications to maintain a set of open database connections.
Instead of creating a new connection for every request, the application borrows an existing connection from the pool,
uses it, and then returns it back to the pool. This reduces the overhead of creating and closing connections multiple times.

How Connection Pooling Works?

The process is simple:

• At the start, a pool of database connections is created and kept ready.
• When an application needs to query the database, it requests a connection from the pool.
• The pool provides an available connection, which the application uses.
• Once the query is complete, the connection is returned to the pool for future use.
• If all connections are busy, new requests wait until a connection becomes available.

Advantages of Connection Pooling

Some of the advantages of connection pooling are as follows:

• Improved Performance: Reduces the time spent on creating and closing connections.
• Efficient Resource Usage: Manages a limited number of connections instead of opening unlimited connections.
• Scalability: Helps handle multiple requests smoothly in high-traffic applications.
• Stability: Prevents the database server from being overloaded with too many connection requests.

Example: Connection Pooling in MySQL

Let’s look at a simple example of using connection pooling in a Java application with MySQL.
Here, we use HikariCP, a popular connection pool library.

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

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

public class MySQLConnectionPoolingExample {
 public static void main(String[] args) {
 HikariConfig config = new HikariConfig();
 config.setJdbcUrl("jdbc:mysql://localhost:3306/mydatabase");
 config.setUsername("root");
 config.setPassword("password");
 config.setMaximumPoolSize(10);

 HikariDataSource dataSource = new HikariDataSource(config);

 try (Connection connection = dataSource.getConnection();
 Statement stmt = connection.createStatement();
 ResultSet rs = stmt.executeQuery("SELECT * FROM users")) {

 while (rs.next()) {
 System.out.println("User ID: " + rs.getInt("id"));
 System.out.println("User Name: " + rs.getString("name"));
 }

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

 

In this example, the HikariCP library manages a pool of MySQL connections.
Instead of creating a new connection each time, the application retrieves one from the pool, uses it, and then returns it for reuse.