August 20, 2022

Apache DBCP Connection Pooling Java Example

In this post we’ll see how to configure connection pooling in your application using Apache DBCP.

Why connection pool is needed

If a DB connection is created every time user connects to DB and closed later it results in a lot of time getting wasted in creating connection object itself to perform a database transaction that might take few milliseconds. Rather than opening a connection per user it is much more efficient to create a pool of connections that is shared among the users.

User can retrieve an idle connection object from the connection pool whenever user has to connect to DB and once done connection is returned to the pool to be used by another user. You can also impose an upper limit to the number of connections in the pool, in that case if the maximum open connection limit for the pool has been reached any further requests for the connection are queued to get the connection when it is released by other user.

Maven dependency for Apache DBCP

If you are using Maven then you can add the following dependency in pom.xml

You can also download the jars directly from the following location and put them in application’s classpath.

Apache DBCP Connection pooling Java example

In the example DB used is MySQL it connects to knpcode schema and table is EMPLOYEE with columns as id, FIRST_NAME, LAST_NAME and DEPARTMENT.

Apache DBCP provides a BasicDataSource class which creates a PoolingDataSource. Steps for creating a DataSource instance using BasicDataSource and configuring connection pool are as follows-

  1. Create an object of BasicDataSource class.
  2. Set the DB related configurations like Driver class, URL, user name and password.
  3. Specify the required connection pool related configuration out of the following-
    • initialSize- The initial number of connections that are created when the pool is started. Default is 0.
    • maxTotal- The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit. Default is 8.
    • maxIdle- The maximum number of connections that can remain idle in the pool. Default is 8.
    • minIdle- The minimum number of connections that can remain idle in the pool. Default is 0.
    • maxWaitMillis- The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception. Default waiting time is indefinitely.

Following class is used to create a BasicDataSource.

import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;

public class DSCreator {
  private static BasicDataSource basicDS;
  static {
    try {
      basicDS = new BasicDataSource();
      Properties properties = new Properties();
      // Loading properties file from classpath
      InputStream inputStream = DSCreator.class
      if(inputStream == null){
        throw new IOException("File not found");
      //The initial number of connections that are created when the pool is started.
      //The maximum number of active connections that can be allocated from this pool at the same time
    }catch(IOException e) {
  public static DataSource getDataSource() {
    return basicDS;

Properties file used by the program to read DB properties ( make sure that this properties file is in the classpath.


Test class that creates a connection using the returned DataSource object and queries the DB.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

public class DSDemo {
  public static void main(String[] args) {
    DSDemo dsDemo = new DSDemo();
  private void displayEmployeeById(int id){
    Connection connection = null; 
    String selectSQL = "SELECT * FROM EMPLOYEE WHERE id = ?";
    PreparedStatement prepStmt = null;
    try {
      DataSource ds = DSCreator.getDataSource();
      connection = ds.getConnection();
      prepStmt = connection.prepareStatement(selectSQL);
      prepStmt.setInt(1, id);
      ResultSet rs = prepStmt.executeQuery();
        System.out.println("id: " + rs.getInt("id"));
        System.out.println("First Name: " + rs.getString("FIRST_NAME"));
        System.out.println("Last Name: " + rs.getString("LAST_NAME"));
        System.out.println("Department: " + rs.getString("DEPARTMENT"));
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      if(connection != null){
        try {
        } catch (SQLException e) {
          // TODO Auto-generated catch block

That's all for the topic Apache DBCP Connection Pooling Java Example. If something is missing or you have something to share about the topic please write a comment.

You may also like

No comments:

Post a Comment