Sunday, April 15, 2018

Connection Pooling Using Apache DBCP in Java

In this post we’ll see how to create a connection pool using Apache DBCP datasource. The DB we are connecting to is MySQL.

Jars needed

You need the following jars in your project’s classpath, check the versions as per your Java and DB versions.

commons-dbcp2-2.1.1.jar
commons-pool2-2.5.0.jar
commons-logging-1.2.jar

Connection pooling using Apache DBCP - Java Example

In the Java example code for connection pooling using Apache DBCP there are two Java classes. There is a DataSource class that is a singleton class creating and returning the instance of dbcp2 BasicDataSource.

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

DataSource.java

import org.apache.commons.dbcp2.BasicDataSource;

public class DataSource {
 private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
 private static final String DB_CONNECTION_URL = "jdbc:mysql://localhost:3306/netjs";
 private static final String DB_USER = "root";
 private static final String DB_PWD = "admin";
 
 private static DataSource ds;
 private BasicDataSource basicDS = new BasicDataSource();

 //private constructor
 private DataSource(){
  //BasicDataSource basicDS = new BasicDataSource();
  basicDS.setDriverClassName(DRIVER_CLASS);
  basicDS.setUrl(DB_CONNECTION_URL);
  basicDS.setUsername(DB_USER);
  basicDS.setPassword(DB_PWD);
  
  // Parameters for connection pooling
  basicDS.setInitialSize(10);
  basicDS.setMaxTotal(10); 
 }
 
 /**
   *static method for getting instance.
 */
 public static DataSource getInstance(){
    if(ds == null){
        ds = new DataSource();
    }
    return ds;
 }

 public BasicDataSource getBasicDS() {
  return basicDS;
 }

 public void setBasicDS(BasicDataSource basicDS) {
  this.basicDS = 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 org.apache.commons.dbcp2.BasicDataSource;

public class DSConnection {
 public static void main(String[] args) {
  DSConnection dsCon = new DSConnection();
  try {
   dsCon.displayEmployee(6);
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 
 /**
  * 
  * @param connection
  * @param id
  * @throws SQLException
  */
 private void displayEmployee(int id) throws SQLException{
  
  Connection connection = null; 
  String selectSQL = "Select * from employee where id = ?";
  PreparedStatement prepStmt = null;
  try {
   BasicDataSource basicDS = DataSource.getInstance().getBasicDS();
   connection = basicDS.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. Getting All The Schemas in a DB - Java Program
  3. Getting All The Tables in a Schema in a DB - Java Program
  4. DatabaseMetaData Interface in Java-JDBC
  5. Batch Processing in Java-JDBC

You may also like-

  1. Converting String to int - Java Program
  2. How to create deadlock in Java multi-threading - Java Program
  3. Reading file in Java using BufferedReader
  4. Invoking Getters And Setters Using Reflection - Java Program
  5. How ArrayList works internally in Java
  6. Transient in Java
  7. Heap Memory Allocation in Java
  8. Java Concurrency interview questions