81 Advanced Java Tutorial | JDBC | JDBC Connection Pool and Its Utilization | adv java

Advanced Java Tutorial :adv java: JDBC Connection Pool and Its Utilization | java Database connectivity :JDBC Connection Pooling:
JDBC Connection pool is a factory that contains set of readily available JDBC connection objects.

Using JDBC Connection pool we can make more clients interacting with Database by using minimum JDBC connection objects.

JDBC DataSource object represent JDBC Connection pool that means to access connection objects from connection pool we need to use DataSource object.

DataSource Object means its the object of a class that implements javax.sql.DataSource interface

There are two types of JDBC connection objects:
-----------------------------------------------

1) Direct Connection object:
Created by programmer (manually)
Ex.
Class.forName("oracle.jdbc.driver.OralcleDriver");
Connection con=DriverManager.getConnection("url","user","pass");


2)Pooled Connection object:

It is the object that is collected from JDBC connection pool using JDBC DataSource object.

Two types of JDBC connection pools:
-----------------------------------
1) DriverManaged JDBC Connection Pool: (Driver itself maintains connections)

-Suitbale for standalone application.
-Uses Type 1/2/4/5 drivers directly.
-Only few JDBC drivers support this kind of JDBC connection pools
ex. Oracle OCI, thin driver

2) Server managed JDBC connection pool:

-Suitable for web applicatoin which runs on web/proxy server.
-Uses type 3 with type 1/2/4/5 JDBC drivers.
-Suitable for 3-tier/n-tier applicatoin which runs on server environment.

Example JDBC application on Oracle thin driver to manage the JDBC connection pool for Oracle:
=========================================================================

import java.sql.*;
import javax.sql.*;
import oracle.jdbc.pool.*;

public class ConnectionPoolTest
{
public static void main(String[] args) throws Exception
{
//create the Empty jdbc datasource object representing driver managed Connection pool for oracle

OracleConnectionPoolDataSource ds=new OracleConnectionPoolDataSource();

//give the details to create JDBC connection objects in the above empty datasource connection pool i.e 'ds'

ds.setDriverType("thin");
ds.setServerName("localhost");
ds.setPortNumber(1521);
ds.setServiceName("orcl");
ds.setUser("scott");
ds.setPassword("boss"); //Upto here JDBC connection pool ready with JDBC connection object

//get one JDBC connection object from connnection pool
Connection con=ds.getConnection();

// Write the JDBC persistant logic
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("select * from student");

while(rs.next()){
System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+ rs.getString(3));
}
rs.close();
st.close();
con.close(); // --- returns/release the conection object back to JDBC connection pool
}
}