Java method to implement database connection pooling

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.