A JDBC Connection Pooling Concept
In this tutorial you will learn JDBC connection pooling concept and how to make pool of connection object.
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 |
