Monday, December 21, 2020

Connection Pooling Using Apache DBCP in Java

In this post we’ll see how to configure connection pooling in your Java application using Apache DBCP datasource. The DB we are connecting to is MySQL.

Jars needed

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

<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-dbcp2</artifactId>
  <version>2.8.0</version>
</dependency>

Alternatively you can download the JARs and put them in the project’s classpath, check the versions as per your Java and DB versions. You will need the following JARs

commons-dbcp2-2.8.0.jar
commons-pool2-2.8.0.jar
commons-logging-1.2.jar

Connection pooling using Apache DBCP - Java Example

Properties file that is used to read DB configuration.

resources/db.properties

DRIVER_CLASS=com.mysql.jdbc.Driver
DB_CONNECTION_URL=jdbc:mysql://localhost:3306/netjs
DB_USER=root
DB_PWD=admin

In the Java example code for connection pooling using Apache DBCP there are two Java classes. We have a PooledDataSource class with a static block to create an instance of DBCP's BasicDataSource.

There is another class DSConnection where we get the instance of dbcp2 BasicDataSource and use it to get the Connection object.

PooledDataSource.java

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;

public class PooledDataSource {
  private static BasicDataSource basicDS;
  static {
    try {
    	basicDS = new BasicDataSource();
      Properties properties = new Properties();
      // Loading properties file
      InputStream inputStream = new FileInputStream("resources/db.properties");   
      properties.load(inputStream);	
      basicDS.setDriverClassName(properties.getProperty("DRIVER_CLASS")); //loads the jdbc driver            
      basicDS.setUrl(properties.getProperty("DB_CONNECTION_URL"));
      basicDS.setUsername(properties.getProperty("DB_USER"));                                  
      basicDS.setPassword(properties.getProperty("DB_PWD"));  
      // Parameters for connection pooling
      basicDS.setInitialSize(10);
      basicDS.setMaxTotal(10);	  
      
    }catch(IOException e) {
      e.printStackTrace();
    }
  }
	
  public static DataSource getDataSource() {
	  return basicDS;
  } 
}

In this class, apart from setting the DB properties, we have set some of the parameters for the connection pool like setInitialSize() that sets the initial size of the connection pool. These many connection will immediately be created and put to connection pool.

There are more configurations like setting the maximum and minimum number of idle connections that should be there in the connection pool (setMaxIdle() and setMinIdle()), maximum size of the connection pool (setMaxTotal()), maximum number of milliseconds that the pool will wait for a connection to be returned before throwing an exception (getMaxWaitMillis()).

DSConnection.java

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

public class DSConnection {
  public static void main(String[] args) {
    DSConnection dsCon = new DSConnection();
    try {
      dsCon.displayEmployee(37);
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
	
  private void displayEmployee(int id) throws SQLException{		
    Connection connection = null; 
    String selectSQL = "Select * from employee where id = ?";
    PreparedStatement prepStmt = null;
    try {
      DataSource ds = PooledDataSource.getDataSource();
      // getting connection
      connection = ds.getConnection();
      prepStmt = connection.prepareStatement(selectSQL);
      prepStmt.setInt(1, id);
      ResultSet rs = prepStmt.executeQuery();
      while(rs.next()){
        System.out.println("id: " + rs.getInt("id") + " Name: " 
            + rs.getString("name") + " Age: " + rs.getInt("age")); 
      }
    }finally{
      if(prepStmt != null){
        prepStmt.close();
      }
      if(connection != null){
        connection.close();
      }
    }
  }
}

That's all for this topic Connection Pooling Using Apache DBCP in Java. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Programs Page


Related Topics

  1. Connection Pooling Using C3P0 in Java
  2. Java Program to Get All DB Schemas
  3. Java Program to Get All The Tables in a DB Schema
  4. DatabaseMetaData Interface in Java-JDBC
  5. Batch Processing in Java JDBC - Insert, Update Queries as a Batch

You may also like-

  1. Convert String to int in Java
  2. How to Create Deadlock in Java
  3. Reading file in Java using BufferedReader
  4. Invoking Getters And Setters Using Reflection in Java
  5. How ArrayList Works Internally in Java
  6. Transient Keyword in Java With Examples
  7. Heap Memory Allocation in Java
  8. Java Concurrency Interview Questions And Answers