blogger templates blogger widgets
This is part of a list of blog posts.
To browse the contents go to

JDBC connection pooling - own implementation (Traditional)

Before frameworks and libraries, we used to write our own pooling mechanisms.

Let's try first a standalone java application.

Since we are using connection pooling, our application will ask the pool for new or free connections instead of DriverManager.

The Pool will take care of creating new connections or utilizing idle connections or even closing long-time idle connections.

The protocol of the connection url (jdbc:subprotocol:subname) we used to fetch connections from DriverManager is
jdbc:oracle:thin.

Since we are not asking the DriverManager directly, let's use a sub-protocol of our choice - jdbc:jdc:jdcpool.

And then we call,
Connection conn = DriverManager.getConnection("jdbc:jdc:jdcpool");

But before that we need to register the actual driver with the DriverManager.
We do this as follows,
public class JDCConnectionDriver implements Driver {
private JDCConnectionPool pool;
public static final String URL_PREFIX = "jdbc:jdc:";

public JDCConnectionDriver(String driver, String url, String user,
String password) throws ClassNotFoundException,
InstantiationException, IllegalAccessException, SQLException {

//Registers the given driver with the DriverManager. 
DriverManager.registerDriver(this);
System.out.println("Registered JDCConnectionDriver as Driver for DriverManager");

//Creating a connection pool
pool = new JDCConnectionPool(url, user, password);
}

public Connection connect(String url) throws SQLException  {
if(!url.startsWith(URL_PREFIX)) {
return null;
}
return pool.getConnection();
}

The DriverManager internally calls connect on the available registered Driver, whenever we do DriverManager.getConnection().

public class JDCConnectionPool {

private List connections; //pool of connections
private String url, user, password;
final private long timeout=5000;
private ConnectionReaper reaper; //A thread that removes stale connections
final private int poolsize=10;   //initial pool size

public JDCConnectionPool(String url, String user, String password) {
this.url = url;
this.user = user;
this.password = password;
connections = (List) Collections.synchronizedList(new ArrayList(poolsize));
System.out.println("Initalized list of JDCConnections of size "+poolsize);
reaper = new ConnectionReaper(this);
reaper.start(); //start the thread that calls reapConnections after every 100 millisec
}

public void reapConnections() {
//check if connections are in use or been idle for long
//if yes, remove them from pool
}

public synchronized Connection getConnection()
throws SQLException {
Connection conn = DriverManager.getConnection(url, user, password);
c = new JDCConnection(conn, this); 
//wrapping the original connection with our custom conn class
connections.add(c);
return c;
}
}

public class JDCConnection implements Connection {....}

Download the project here.

A common disadvantage of connection pooling is that we can never be sure of when the connections are actually closed. Default behavior of conn.close() is to close all cursors(ResultSets) and also statements associated with the connection. But most of the connection pooling frameworks and libraries override this behavior and do not close connections when we call conn.close() instead the connection is returned back to the pool.

Connections are later closed based on various criteria like when the thread calling conn.close()
actually terminates or when the application is stopped.

Because of this limitation it is always advisable to close all Resultsets and Statements in finally clause.

Keeping connections or statements open leads to a very infamous exception

java.sql.SQLException: - ORA-01000: maximum open cursors exceeded


There are 2 primary causes for this exception


  1. You have more threads in your application querying the database than cursors on the DB. (More about cursors later in this section) This is a configuration mistake and can be rectified by either increasing the number of cursors on the DB or by decreasing the number of threads in the application.
  2. The second most common cause is not closing ResultSets/Statements (in JDBC code) or cursors (in stored procedures on the database). This can be avoided by following good programming practices and static code analysis.

Continue reading: JDBC connections using DataSouce (framework implementation)

No comments:

Post a Comment