2 ways to create Connection Pool in java with examples

What is a Connection Pool in java

Usually, applications are hosted on the application server and talk to the database using a database connection.

The database connection is a simple HTTP socket connection from the java code to the database machine.

Whenever the application receives a request, it establishes a simple socket connection to the database; once the request is completed, the connection is timed out/closed; in this way, the system is connected for each request. Creating and closing a connection is an expensive process, hence it is an expensive operation for each request, thus, reducing the application’s performance. Applications are responsible for creating and closing connections.

The connection pool is used to increase the performance of the connections process.

Connection pool is a mechanism/concept used by software applications to connect to backend databases with pre-created group connections and reuse the connections whenever required,
Once a request is completed, connections are placed in the connection pool.

Connection pool is a pool of a group of pre-created connections managed by an application server or webserver,

Advantages of Connection Pool

Reduce network calls, thus improving network latency
Improve the application performance by reusing the connections
No need of opening or closing connections as a group of connections are already in the pool and reused and placed in or out of the pool.

Disadvantages of Connection Pool

Need to configure connection pooling carefully, otherwise, the database rejects/closes connections.
Connections are like a resource and there might be resource memory leakage issues.

How to create Connection Pooling in java

Below are examples to create various ways to create a connection pool.
Connection Pool can be implemented in various ways by using Custom java code using LIFO stacks or FIFO Queues or different frameworks provided by apache and others.

A connection pool creates in either Standalone or web container/application Servers like Jboss, tomcat, WebLogic, and WebSphere.

JDBC Connection Pooling example

A connection pool was also created using JDBC using the JNDI name or Datasource.
JNDI name is a name configured mapped to connection pools using Websphere, WebLogic
Datasource is the interface on which implementation is done by different vendors like apache DBCP and c3po

Connection Pool settings:-

For any connection pool, the Below number of parameters are required. For better performance in Production systems, settings need to be tuned.

**a Total number of connections in Connection Pool:-**This parameter specifies the total number of connections to be created at startup. Basically in web applications, these connections are created at application startup and destroyed when the application stops.

Maximum Connections:- tells maximum connections to be created in the connection pool

Minimum connections: - specifies the minimum connection to create a pool

Connection Increment: - if the total request is more than the maximum number of connections, the container increment the connection count

Connection pool Idle timeout:- if the connection is established and idle for some time, then the container return that connection to the pool, and later another request can be reused. It is basically to free the database connection resource.

Apache DBCP Connection pool tutorial:-

Apache DBCP is a database connection pooling concept to create/manage a group of connections. This can be used to create a java database connection pool to improve the performance of Java applications. Apache Tomcat is internally using the DBCP connection pool framework.

How to create a connection pool using Apache DBCP with an example

The data source object is required for getting the Connection object.
To get the Datasource object, we need to create the following code.

import java.sql.*;  
import org.apache.commons.dbcp.*;  
  
public class C3POPConnectionExample {  
 public static void main(String args[]) throws Exception {  
  BasicDataSource dataSource = new BasicDataSource();  
  dataSource.setDriverClassName("com.mysql.jdbc.Driver");  
  dataSource.setUrl("jdbc:mysql://localhost:3306/mydatabase");  
  dataSource.setUsername("root");  
  dataSource.setPassword("");  
  dataSource.setInitialSize(1);  
  Connection con = dataSource.getConnection();  
  System.out.println("Connection Object information : " + con);  
  
 }  
}  

To execute/compile the above dbcp connection pool example, the dbcp jar should be in the classpath.

Java C3PO connection pool example

A C3PO connection pool is a powerful and stable open-source connection pool, performance is also good compared to apache dbcp.

Below is the example for the C4PO code. Before that c3po jar should be in the classpath.

import java.sql.*;  
import com.mchange.v2.c3p0.ComboPooledDataSource;  
  
public class DBCPConnectionExample {  
 public static void main(String args[]) throws Exception {  
  ComboPooledDataSource connectionPoolDatasource = new ComboPooledDataSource();  
  connectionPoolDatasource.setDriverClass("com.mysql.jdbc.Driver");  
  connectionPoolDatasource.setJdbcUrl("jdbc:mysql://localhost:
  3306/mydatabase");  
  connectionPoolDatasource.setUser("root");  
  connectionPoolDatasource.setPassword("");  
  connectionPoolDatasource.setMinPoolSize(1);  
  connectionPoolDatasource.setAcquireIncrement(5);  
  connectionPoolDatasource.setMaxPoolSize(20);  
  Connection con = connectionPoolDatasource.getConnection();  
  System.out.println("Connection Object information : " + con);  
 }  
}  

that’s it my learnings/understanding of connection pooling in java.