JDBC Connection Pooling

JDBC connection pool is divided into two parts:

1. JDBC Driver Managed/Stand Alone JDBC Connection Pool (Resides Outside The Server)

2. Web Server Managed JDBC Connection Pool

While developing small-scale applications like standalone desktop applications we can use driver-managed JDBC connection pool. While developing large-scale applications like web sites and enterprise application, we can use server managed JDBC connection pool. JDBC connection pool for Oracle means all JDBC connection pool represents connectivity with Oracle database software. Oracle thin driver/OCI driver gives one built-in JDBC connection pool for oracle. JDBC data source object represents JDBC connection pool so our application should always get JDBC connection object from connection pool through data source object.

JDBC data source object is the object of a Java class that implements javax.sql. DataSource (I). The JDBC connection object that is created by the programmer manually is called the Direct JDBC Connection Object. The JDBC connection object that is collected from JDBC connection pool is called Pooled JDBC Connection Object.

Example application on Oracle thin Driver-Managed JDBC Connection Pool

ConnectionPoolTest.java

import java.sql.*;

import javax.sql.*;//for pooledConnection (I)

import oracle.jdbc.pool.*;// (for oracle Connection pool DataSource (c)) 

public class ConnPoolTest

{

      public void main (String args []) throws Exception

        {

                /*create JDBC DataSource object representing empty driver managed

                connection pool of oracle*/

                OracleConnectionPoolDataSource ds = new OracleConnectionPoolDataSource

                ();

                //gives details to create JDBC connection objects in the Connection pool

                ds.setDriverType (“thin”);

               ds.setPortNumber (1521) ;

               ds.setServiceName (“Orcl”) ;

               ds.setUser (“Scott”) ;

               ds.setPassword (“tiger”);

               /*get access to JDBC connection pool for oracle (this connection pool will be

               created based on the details)*/

               PooledConnection con = pcon.getConnection ();

               //Write JDBC based on the above details

               Statement st = con.createStatement ();

               ResultSet rs = st.executeQuery (“select * from student”);

               while (rs.next())

                {

                      System.out.println (“rs.getInt (1) + ” ” + rs.getInt (2) + ” ” + rs.getInt (3)”);

                }

                rs.close ();

                st.close (); //releases Connection object back to JDBC connection pool

         }

}