Database Connection Pooling

Database Connection Pooling

 

·        Opening a connection to a database in a time consuming process . For smaller queries execution, it takes more time to open connection than executing queries.

·        Suppose if system takes 3seconds to establish a database connection, Since, for each query it tries to open the connection, to execute the 10 queries, it spends 300 seconds to open the connection.

·        Every time client sends the request, the connection has been opened which is more time consuming process. It will affect the application performance. So, Instead of opening a database connection each time to execute the queries, we could pre-allocating database connections and reuse it.

 

 Solution :

 

   We could create pool of connections in advance, So we could avoid the overhead of when the client made requests on database.

 

Create the pool of connection in advance :

When jsp/servlet request for connection , get the unused connection from the pool.

Connection is used by new jsp/servlet :

When finished jsp/servlete returns the connection back to the pool, now the connection is available for use :

       The connection pool class can able to perform the following tasks .

                1. Preallocate the connections.

                2. Manage available connections

                3. Allocate new connections

                4. Wait for a connection to become available

                5. Close the connections

 

 

 

• I have used two collection vectors to store available idle connection(availableConnection) and unavailable busy connections(busyConnection).
• The constructor of the class gets the url, username and password etc as input and create the connection pool. The connection pool objects are store in vector collection named availableConnection.
• When the user request for connection, the function getConnection() initially checks whether the available connection is free or not. If it is free ,it will remove the connection from availableConnection vector and add it to the busyConnection vector. Now the connection is in use.
• If the connection is not free and the totalConnection has not reached maxConnection limit then it will make the new connection for the user.
• When the connection is required but there is no free connections and the totalConnection reached the maxConnection limit then we should wait(call wait() method) until the connection has been released and notify or notifyAll is called.
• Connections are closed when they are garbage collected but sometimes we need to  close it explicitly.

 

ConnectionPool.java

 

import java.sql.*;

import java.util.*;

 

public class ConnectionPool implements Runnable {

 

private String driver, url, username, password;

private int maxConnections;

private boolean waitIfBusy;

private Vector availableConnections, busyConnections;

private boolean connectionPending = false;

 

public ConnectionPool(String driver, String url,String username, String password,int initialConnections,

int maxConnections,boolean waitIfBusy)throws SQLException {

 

this.driver = driver;

this.url = url;

this.username = username;

this.password = password;

this.maxConnections = maxConnections;

this.waitIfBusy = waitIfBusy;

if (initialConnections > maxConnections) {

initialConnections = maxConnections;

}

 

//pre-allocate the connections

availableConnections = new Vector(initialConnections);

busyConnections = new Vector();

for(int i=0; i<initialConnections; i++) {

availableConnections.addElement(makeNewConnection());

}

}

 

//manage the available connections

public synchronized Connection getConnection() throws SQLException {

 

if (!availableConnections.isEmpty()) {

Connection existingConnection =(Connection)availableConnections.lastElement();

int lastIndex = availableConnections.size() - 1;

availableConnections.removeElementAt(lastIndex);

if (existingConnection.isClosed()) {

notifyAll();

return(getConnection());

} else {

busyConnections.addElement(existingConnection);

return(existingConnection);

}

} else {

//allocate new connections

if ((totalConnections() < maxConnections) &&!connectionPending) {

makeBackgroundConnection();

} else if (!waitIfBusy) {

throw new SQLException("Connection limit reached");

}

//wait for a connection to become available

try {

wait();

} catch(InterruptedException ie) {}

return(getConnection());

}

}

 

 

private void makeBackgroundConnection() {

 

connectionPending = true;

try {

Thread connectThread = new Thread(this);

connectThread.start();

} catch(OutOfMemoryError oome) {

System.out.println(“Exception :”+oome);

 

}

}

 

public void run() {

 

try {

Connection connection = makeNewConnection();

synchronized(this) {

availableConnections.addElement(connection);

connectionPending = false;

notifyAll();

}

} catch(Exception e) {

}

}

 

private Connection makeNewConnection() throws SQLException {

 

try {

Class.forName(driver);

Connection connection =

DriverManager.getConnection(url, username, password);

return(connection);

} catch(ClassNotFoundException cnfe) {

throw new SQLException("Can’t find class for driver: " +driver);

}

}

 

 

public synchronized void free(Connection connection) {

 

busyConnections.removeElement(connection);

availableConnections.addElement(connection);

 

// Wake up threads that are waiting for a connection

 

notifyAll();

}

 

public synchronized int totalConnections() {

 

return(availableConnections.size() +

busyConnections.size());

}

 

 

//close the connections

public synchronized void closeAllConnections() {

 

closeConnections(availableConnections);

availableConnections = new Vector();

closeConnections(busyConnections);

busyConnections = new Vector();

}

 

private void closeConnections(Vector connections) {

 

try {

for(int connectionCount=0; connectionCount <connections.size();connectionCount ++) {

Connection connection =

(Connection)connections.elementAt(connectionCount);

if (!connection.isClosed()) {

connection.close();

}

}

} catch(SQLException sqle) {

System.out.println(“Exception :”+sqle);

}

}

 

public synchronized String toString() {

String info =

"ConnectionPool(" + url + "," + username + ")" +

", available=" + availableConnections.size() +

", busy=" + busyConnections.size() +

", max=" + maxConnections;

return(info);

}

}