2 ways to create Connection Pool in java with examples

2 ways to create Connection Pool in java with examples

This tutorial provides an overview of the basics of Connection Pools and demonstrates how to create them in Java with examples.

What is a Connection Pool in Java?

Typically, applications hosted on an application server communicate with databases through database connections. These connections, established through a simple HTTP socket connection from Java code to the database machine, are initiated and closed for each request. However, the process of creating and closing connections is resource-intensive, affecting the application’s performance.

A Connection Pool is a mechanism used by applications to enhance connection performance. It involves pre-creating a group of connections that can be reused whenever needed. After completing a request, these connections are placed in a pool for reuse.

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

Advantages of Connection Pool:

  • Reduces network calls, improving network latency
  • Enhances application performance by reusing connections
  • Eliminates the need to open or close connections individually

Disadvantages of Connection Pool:

Requires careful configuration to avoid database rejection/closure of connections Potential resource memory leakage issues as connections are treated as resources

How to create Connection Pooling in java

Below are examples illustrating various ways to create a connection pool. Connection pools can be implemented using custom Java code with LIFO stacks, FIFO Queues, or different frameworks provided by Apache and others.

These pools can be implemented either in standalone environments or within web containers/application servers such as JBoss, Tomcat, WebLogic, and WebSphere.

JDBC Connection Pooling example

A JDBC connection pool can be created using JDBC with the JNDI name or Datasource. The JNDI name is configured and mapped to connection pools using WebSphere or WebLogic, while Datasource is an interface implemented 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

Apache DBCP is a database connection pooling concept used to create/manage a group of connections. It is employed to create a Java database connection pool, enhancing the performance of Java applications. The example below demonstrates how to create a connection pool using Apache DBCP.

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

C3PO connection pool is a powerful and stable open-source connection pool, known for its good performance compared to Apache DBCP.

The example below illustrates how to create a connection pool using C3PO.

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);
 }
}