Connection Pooling

Connection Pooling

In this tutorial, we are going to discuss about Connection Pooling. Connection pooling is a technique used in software applications to manage and reuse a pool of database connections, rather than creating and closing connections for each database operation. This approach improves application performance, scalability, and resource utilization by reducing the overhead associated with establishing and tearing down database connections.

If we required to communicate with database multiple times then it is not recommended to create separate Connection object every time, because creating and destroying Connection object every time creates performance problems. To overcome this problem, we should go for Connection Pool.

Connection Pooling

Connection Pool is a pool of already created Connection objects which are ready to use. If we want to communicate with database then we request Connection pool to provide Connection. Once we got the Connection, by using that we can communicates with database. After completing our work, we can return Connection to the pool instead of destroying.

Hence the main advantage of Connection Pool is we can reuse same Connection object multiple times, so that overall performance of application will be improved.

Process to implement Connection Pooling

1. Creation of DataSource object 

  • DataSource is responsible to manage connections in Connection Pool.
  • DataSource is an interface present in javax.sql package.
  • Driver Software vendor is responsible to provide implementation.
  • Oracle people provided implementation class name is : OracleConnectionPoolDataSource.
  • This class present inside oracle.jdbc.pool package and it is the part of ojdbc6.jar.
OracleConnectionPoolDataSource ds = new OracleConnectionPoolDataSource();

2. Set required JDBC Properties to the DataSource object

ds.setURL("jdbc:oracle:thin:@localhost:1521:XE");
ds.setUser("scott");
ds.setPassword("tiger");

3. Get Connection from DataSource object

Connection con = ds.getConnection();

Once we got Connection object then remaining process is as usual.

E.g Oracle DB

import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class SelectAllRowsDemo { 
   public static void main(String[] args) throws Exception {
      String sqlQuery = "select * from emp"; 
      Class.forName("oracle.jdbc.OracleDriver"); 
      OracleConnectionPoolDataSource ds = new OracleConnectionPoolDataSource();
      ds.setURL("jdbc:oracle:thin:@localhost:1521:XE");
      ds.setUser("scott"); 
      ds.setPassword("tiger"); 
      Connection con = ds.getConnection();
      Statement st = con.createStatement();
      boolean flag = false;
      ResultSet rs = st.executeQuery(sqlQuery);
      System.out.println("Eno\tEName\tESalary\tEAddress");
      while(rs.next()) {
         System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getDouble(3)+"\t"+rs.getString(4));
      }
      if(flag==false) {
         System.out.println("No Records found");
      }
      con.close();
   }
}

E.g MySQL DB

import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class SelectAllRowsDemo { 
   public static void main(String[] args) throws Exception {
      String sqlQuery = "select * from emp"; 
      Class.forName("oracle.jdbc.OracleDriver"); 
      MysqlConnectionPoolDataSource ds = new MysqlConnectionPoolDataSource();
      ds.setURL("jdbc:mysql://localhost:3306/ashokdb");
      ds.setUser("ashok"); 
      ds.setPassword("ashok"); 
      Connection con = ds.getConnection();
      Statement st = con.createStatement();
      boolean flag = false;
      ResultSet rs = st.executeQuery(sqlQuery);
      System.out.println("Eno\tEName\tESalary\tEAddress");
      while(rs.next()) {
         System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getDouble(3)+"\t"+rs.getString(4));
      }
      if(flag==false) {
         System.out.println("No Records found");
      }
      con.close();
   }
}

Note

  • This way of implementing Connection Pool is useful for Standalone applications. In the case of web and enterprise applications, we have to use server level connection pooling. Every web and application server can provide support for Connection Pooling.
  • In the case of DriverManager.getConnection(), always a new Connection object will be created and returned.
  • But in the case of DataSourceObject.getConnection(), a new Connection object won’t be created and existing Connection object will be returned from Connection Pool.

That’s all about the Connection Pooling in database. If you have any queries or feedback, please write us email at contact@waytoeasylearn.com. Enjoy learning, Enjoy Java.!!

Connection Pooling
Scroll to top