Recent Posts

Thursday, 15 November 2018

Connection Pooling

     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 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.
Example - 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();
   }
}
Example - 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.

Next Tutorial : About Properties Class 

Previous Tutorial : Working with Large Objects - CLOB 
 

No comments:

Post a Comment