Java method to implement database connection pooling
- 2020-04-01 03:59:57
- OfStack
This article illustrates a Java approach to database connection pooling. Share with you for your reference. The details are as follows:
package com.kyo.connection;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Vector;
public class ConnectionPool {
private ConnectionParam param;
private String testTable = "";
//The name of the test table to test whether the connection is available. There is no test table by default
private Vector connections = null;
//The vector that holds the database connections in the connection pool
//Null, in which the object is of type PooledConnection
public void setParam(ConnectionParam param) {
this.param = param;
}
public ConnectionParam getParam() {
return param;
}
public ConnectionPool(ConnectionParam param) {
this.param = param;
}
public String getTestTable() {
return this.testTable;
}
public void setTestTable(String testTable) {
this.testTable = testTable;
}
public synchronized void createPool() throws Exception {
//Make sure the connection pool is not created
//If the connection pool has been created, the vector connections to save the connection will not be empty
if (connections != null) {
return; //If created, returns
}
//Instantiate the Driver class instance specified in the JDBC Driver
Driver driver = (Driver) (Class.forName(this.param.getDriver())
.newInstance());
DriverManager.registerDriver(driver); //Register the JDBC driver
//Create the vector that holds the connection, starting with 0 elements
connections = new Vector();
//Create a connection based on the value set in initialConnections.
createConnections(this.param.getMinConnection());
System.out.println("Database connection Pool created successfully! ");
}
private void createConnections(int numConnections) throws SQLException {
//Loops to create a specified number of database connections
for (int x = 0; x < numConnections; x++) {
//Has the number of database connections in the connection pool reached the maximum? The maximum value is indicated by the class member maxConnections, if maxConnections
//Zero or negative means there is no limit to the number of connections.
//If the number of connections has reached the maximum, it exits.
if (this.param.getMaxConnection() > 0
&& this.connections.size() >= this.param.getMaxConnection()) {
break;
}
// add a new PooledConnection object to connections vector
//Add a connection to the connection pool (vector connections)
try {
connections.addElement(new PooledConnection(newConnection()));
} catch (SQLException e) {
System.out.println(" Failed to create database connection! " + e.getMessage());
throw new SQLException();
}
System.out.println("Database connection Has created ......");
}
}
private Connection newConnection() throws SQLException {
//Create a database connection
Connection conn = DriverManager.getConnection(this.param.getUrl(),
this.param.getUser(), this.param.getPassword());
//If this is the first time a database connection is created, check the database to get what this database is allowed to support
//Maximum number of customer connections
//Connexs.size ()==0 means that no connections have been created
if (connections.size() == 0) {
DatabaseMetaData metaData = conn.getMetaData();
int driverMaxConnections = metaData.getMaxConnections();
//A 0 value for driverMaxConnections returned by the database indicates that the database does not have a maximum
//The connection limit, or the maximum connection limit for the database, is not known
//DriverMaxConnections is an integer returned that represents the number of entries that this database allows customers to connect to
//Set the maximum number of connections in the connection pool if the maximum number of connections set in the connection pool is greater than the number of connections allowed by the database
//The number of connections is the maximum number allowed by the database
if (driverMaxConnections > 0
&& this.param.getMaxConnection() > driverMaxConnections) {
this.param.setMaxConnection(driverMaxConnections);
}
}
return conn; //Returns the new database connection created
}
public synchronized Connection getConnection() throws SQLException {
//Make sure the connection pool is created
if (connections == null) {
return null; //Null is returned if the connection pool has not been created
}
Connection conn = getFreeConnection(); //Get an available database connection
//If no connections are currently available, all connections are in use
while (conn == null) {
//Try again later
wait(250);
conn = getFreeConnection(); //Try again until the connection is available if
//GetFreeConnection () returns null
//Indicates that the available connections are not available after a batch of connections are created
}
return conn;//Returns the available connections obtained
}
private Connection getFreeConnection() throws SQLException {
//Get an available database connection from the connection pool
Connection conn = findFreeConnection();
if (conn == null) {
//If no connection is currently available in the connection pool
//Create some connections
createConnections(this.param.getIncrementalConnections());
//Recheck the pool to see if any connections are available
conn = findFreeConnection();
if (conn == null) {
//If no connection is available after the connection is created, null is returned
return null;
}
}
return conn;
}
private Connection findFreeConnection() throws SQLException {
Connection conn = null;
PooledConnection pConn = null;
//Gets all the objects in the connection pool vector
Enumeration enumerate = connections.elements();
//Walk through all the objects to see if any connections are available
while (enumerate.hasMoreElements()) {
pConn = (PooledConnection) enumerate.nextElement();
if (!pConn.isBusy()) {
//If the object is not busy, get its database connection and make it busy
conn = pConn.getConnection();
pConn.setBusy(true);
//Test whether this connection is available
if (!testConnection(conn)) {
//If the connection is no longer available, a new connection is created,
//And replaces this unavailable connection object, returning null if the creation fails
try {
conn = newConnection();
} catch (SQLException e) {
System.out.println(" Failed to create database connection! " + e.getMessage());
return null;
}
pConn.setConnection(conn);
}
break; //A connection has been found available, exit
}
}
return conn;//Returns the available connection found to
}
private boolean testConnection(Connection conn) {
try {
//Determine if the test sheet exists
if (testTable.equals("")) {
//If the test table is empty, try using the setAutoCommit() method for this connection
//To determine if the connection is available (this method is only available in some databases, if not,
//Throws an exception. Note: the method of using test sheets is more reliable
conn.setAutoCommit(true);
} else {
//Use test sheets when you have them
// check if this connection is valid
Statement stmt = conn.createStatement();
stmt.execute("select count(*) from " + testTable);
}
} catch (SQLException e) {
//An exception is thrown above, the connection is no longer available, close it, and return false;
closeConnection(conn);
return false;
}
//The connection is available and returns true
return true;
}
public void returnConnection(Connection conn) {
//Make sure the connection pool exists, and return if the connection is not created (it does not exist)
if (connections == null) {
System.out.println(" Connection pool does not exist, this connection cannot be returned to the connection pool !");
return;
}
PooledConnection pConn = null;
Enumeration enumerate = connections.elements();
//Walk through all connections in the connection pool to find the connection object to return
while (enumerate.hasMoreElements()) {
pConn = (PooledConnection) enumerate.nextElement();
//Find the connection object to return from the connection pool
if (conn == pConn.getConnection()) {
//Found. Set this connection to idle
pConn.setBusy(false);
break;
}
}
}
public synchronized void refreshConnections() throws SQLException {
//Ensure that the connected pool has its own innovations
if (connections == null) {
System.out.println(" Connection pool does not exist and cannot be refreshed !");
return;
}
PooledConnection pConn = null;
Enumeration enumerate = connections.elements();
while (enumerate.hasMoreElements()) {
//Gets a connection object
pConn = (PooledConnection) enumerate.nextElement();
//If the object is busy, wait 5 seconds, then refresh directly after 5 seconds
if (pConn.isBusy()) {
wait(5000); //Such as 5 seconds
}
//Close the connection and replace it with a new one.
closeConnection(pConn.getConnection());
pConn.setConnection(newConnection());
pConn.setBusy(false);
}
}
public synchronized void closeConnectionPool() throws SQLException {
//Make sure the connection pool exists, and if it does not, return
if (connections == null) {
System.out.println(" Connection pool does not exist and cannot be closed !");
return;
}
PooledConnection pConn = null;
Enumeration enumerate = connections.elements();
while (enumerate.hasMoreElements()) {
pConn = (PooledConnection) enumerate.nextElement();
//If busy, wait 5 seconds
if (pConn.isBusy()) {
wait(5000); //Such as 5 seconds
}
//Close it after 5 seconds
closeConnection(pConn.getConnection());
//Remove it from the connection pool vector
connections.removeElement(pConn);
}
//Leave the connection pool empty
connections = null;
}
private void closeConnection(Connection conn) {
try {
conn.close();
} catch (SQLException e) {
System.out.println(" Error closing database connection: " + e.getMessage());
}
}
private void wait(int mSeconds) {
try {
Thread.sleep(mSeconds);
} catch (InterruptedException e) {
}
}
class PooledConnection {
Connection connection = null;//Database connection
boolean busy = false; //The flag for whether this connection is in use is not in use by default
//Constructor that instructs a PooledConnection object based on a Connection
public PooledConnection(Connection connection) {
this.connection = connection;
}
//Returns a connection in this object
public Connection getConnection() {
return connection;
}
//Set the connection for this object
public void setConnection(Connection connection) {
this.connection = connection;
}
//Gets whether the object connection is busy
public boolean isBusy() {
return busy;
}
//The connection to the setup object is busy
public void setBusy(boolean busy) {
this.busy = busy;
}
}
}
I hope this article has been helpful to your Java programming.