Home Tutorial Java Jdbc A JDBC Connection Pooling Concept

 
 

Share on Google+Share on Google+
A JDBC Connection Pooling Concept
Posted on: October 20, 2010 at 12:00 AM
Advertisement
In this tutorial you will learn JDBC connection pooling concept and how to make pool of connection object.

JDBC Connection Pooling

JDBC Connection pooling is similar to any other object pooling. Connection pooling is very useful for any application which uses database database as backend. Database connection is very expensive to create over network, for this we need to create a network connection, then initializing a database, creating a session, doing transaction and then after closing the connection, this take more time, therefore application becomes slower. The valuable database resources such as memory, cursors, locks , temporary tables all tends to increase on numbers of concurrent connections.

In connection pooling, we creates limited numbers of connection objects pools at a time, such as 10 connections, 50 connections, 100 connections etc. This depends upon the capacity of the database that how much connections it can handle at a time. If any request comes we allocate a connection object to it. When it completed their work then it releases the connection object and this object is added into the pool. 

An example of JDBC connection pooling is given below. to run this example you need to create a database in MySql named 'student'.

 

MainClaz.java

package roseindia.net;

import java.sql.*;

public class MainClaz {
	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception E) {
			System.out.println("Unable to load a driver " + E.toString());
		}
		try {
			JDBCConnectionPooling connectionPooling = new JDBCConnectionPooling(
					"jdbc:mysql://localhost:3306/student", "root", "root");

			Connection[] connArr = new Connection[7];

			for (int i = 0; i < connArr.length; i++) {
				connArr[i] = connectionPooling.connectionCheck();
				System.out.println("Checking Connections..." + connArr[i]);
				System.out.println("Connections Available... "
						+ connectionPooling.availableCount());
			}
		} catch (SQLException sqle) {
			System.out.println(sqle.toString());
		} catch (Exception e) {
			System.out.println(e.toString());
		}
	}
}

JDBCConnectionPooling.java

package roseindia.net;

import java.util.*;
import java.sql.*;

public class JDBCConnectionPooling implements Runnable {
	int initialConnections = 5;
	Vector connectionsAvailable = new Vector();
	Vector connectionsUsed = new Vector();

	String connectionUrl ;
	String userName;
	String userPassword ;
	public JDBCConnectionPooling(String url,String userName, String userPass) throws SQLException {
		try {
			this.connectionUrl = url;
			this.userName = userName;
			this.userPassword = userPass;
			Class.forName("com.mysql.jdbc.Driver");
			for (int count = 0; count < initialConnections; count++) {
				connectionsAvailable.addElement(getConnection());
			}
		} catch (ClassNotFoundException e) {
			System.out.println(e.toString());
		}
	}
	private Connection getConnection() throws SQLException {
		return DriverManager.getConnection(connectionUrl, userName,
				userPassword);
	}
	public synchronized Connection connectionCheck() throws SQLException {
		Connection newConnection = null;
		if (connectionsAvailable.size() == 0) {
			// creating a new Connection
			newConnection = getConnection();
			// adding Connection to used list
			connectionsUsed.addElement(newConnection);
		} else {
			newConnection = (Connection) connectionsAvailable.lastElement();

			connectionsAvailable.removeElement(newConnection);

			connectionsUsed.addElement(newConnection);
		}
		return newConnection;
	}

	public int availableCount() {
		return connectionsAvailable.size();
	}
	public void run() {
		try {
			while (true) {
				synchronized (this) {
					while (connectionsAvailable.size() > initialConnections) {
						Connection connection = (Connection) connectionsAvailable
								.lastElement();
						connectionsAvailable.removeElement(connection);

						connection.close();
					}

				}
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

When you run this application it will display message as shown below:


Checking Connections...com.mysql.jdbc.JDBC4Connection@94948a
Connections Available... 4
Checking Connections...com.mysql.jdbc.JDBC4Connection@a401c2
Connections Available... 3
Checking Connections...com.mysql.jdbc.JDBC4Connection@16f8cd0
Connections Available... 2
Checking Connections...com.mysql.jdbc.JDBC4Connection@85af80
Connections Available... 1
Checking Connections...com.mysql.jdbc.JDBC4Connection@c51355
Connections Available... 0
Checking Connections...com.mysql.jdbc.JDBC4Connection@f3d6a5
Connections Available... 0
Checking Connections...com.mysql.jdbc.JDBC4Connection@a56a7c
Connections Available... 0

Download this example code

Advertisement

Related Tags for A JDBC Connection Pooling Concept:


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: October 20, 2010

Recommend the tutorial

Advertisements Advertisements
 

 

 

DMCA.com