Implement Java multithreaded database connection pool from scratch (with a mysterious problem) __c#

This example uses MySQL database, so please download Mysql-connection.jar first

In our actual development, can not be separated from the database to deal with. and database communication, inseparable from the database connection.
Typically, when you connect to a database with JDBC, you need to load the data driver and then return the database connection through the interface.
Generally divided into two steps:
1. Load Drive to memory
Class.forName ("Com.mysql.jdbc.Driver");

2. Create and get a connection, return the connection in JDBC
Drivermanager.getconnection (URL, user, password)

Example:

//The URL of the database to be connected
String url = "jdbc:mysql://localhost:3306/tangwmdb";
//User name used when connecting to the database
String username = "root";
//The password used when connecting to the database
String password = "root";

//1. Load the driver
//DriverManager.registerDriver(new com.mysql.jdbc.Driver()); It is not recommended to use this method to load the driver
Class.forName("com.mysql.jdbc.Driver");//It is recommended to use this method to load the driver

//2. Get the link to the database
Connection conn = DriverManager.getConnection(url, username, password);

//3. Get the statement used to send SQL statements to the database
Statement st = conn.createStatement();

String sql = "select id,name,password from members";
//4. Send sql to the database and get the resultset representing the result set
ResultSet rs = st.executeQuery(sql);

//5. Take out the data of the result set
while(rs.next()){
    System.out.println("id=" + rs.getObject("id"));
    System.out.println("name=" + rs.getObject("name"));
    System.out.println("password=" + rs.getObject("password"));
}

//6. Close the link and release resources
rs.close();
st.close();
conn.close();

As we all know, creating a database connection requires more resources and has a longer creation time. If the site 1 million PV per day (assuming that each page has DB read or modify operation), the program will need to create 1 million connections, a huge waste of resources.
In fact, the number of requests to create a database connection at the same time is not much, and generally hundreds of is sufficient. Then we can create a connection pool, as needed, that allocates, manages, and frees the database connection, which allows the application to reuse the same existing database connection, rather than re-establish one. This uses a pattern in the design pattern: the =Flyweight pattern.
For example, there are 1000 connections in our connection pool, when the request comes, the connection pool allocates a request from the pool, and then reclaims it, instead of destroying it, until the next time a request comes, it can be used again.

When the database connection pool is used, there is no need to write code to connect the database in the actual development of the project, and the database connection is obtained directly from the data source. Like what:

DBCP database Connection Pool
DataSource ds = Basicdatasourcefactory.createdatasource (prop);
Connection conn = Ds.getconnection ();

You can see that creating a connection is simple, because the complex allocation and recycling functions are given to the connection pool to handle.

Currently there are some open source data connection pool implementation: DBCP database connection pool C3P0 database connection pool

In addition Ali Open source project Druid (the whole project consists of database connection pool, plug-in framework and SQL Parser) in the database connection pool is used by many internet companies in the production environment.

Write your own database connection pool

The following basic points are required to write the connection pool:
1. Can configure and manage connection pool for multiple connection nodes

2. Create a specified number of connections based on the initial number of connections in the configuration at the beginning of the process
3, if the connection pool does not reach the maximum number of connections, if there are free connections available to use the idle connection directly, if not, create a new connection.
4. When the number of active connections in the connection pool reaches the maximum number of connections, the new request enters the standby state until a connection is released.
5, because the database connection is idle for a long time will be closed, so the need for connection pooling mechanism to ensure that each requested connection is effectively available.
6. Thread Safety
7, the connection pool inside to ensure that the minimum number of connections to the free connection.
For the minimum number of connections in practical applications and the difference between the number of initial connections, in fact, not very understanding. The way I use it in the program is if the active connection number + idle connection number < minimum connection number, the corresponding quantity (minimum connection number-active connection number-idle connection number) 's Idle connection is padded

Excerpt paragraph:

The minimum number of connections and maximum number of connections for a database connection pool is set to take into account the following factors:

minimum number of connections: The connection pool has been maintained by the database connection, so if the application of the database connection is not used, there will be a large number of database connection resources wasted.
Maximum number of connections: is the maximum number of connections that can be requested by the connection pool, and if the database connection request exceeds the number of times, subsequent database connection requests will be added to the wait queue, which can affect subsequent database operations.
If the minimum number of connections is significantly different from the maximum number of connections, then the first connection request will be profitable, and then the connection request exceeding the minimum number of connections is equivalent to establishing a new database connection. However, these database connections that are larger than the minimum number of connections are not immediately released when they are used, and are placed in the connection pool for reuse or after the timeout is released. 


System Structure:

1. Connection Pool Interface Iconnectionpool: It defines some basic ways to get connections.
2. Connection pool Interface Implementation ConnectionPool
3. Connection Pool Management Dbconnectionmanager: Manage different connection pools, all connections are obtained from here.
4. Other tool classes, such as attribute read Class Propertiesmanager, property save Class Dbpropertybean.
Engineering Structure:


Engineering Code:

Dbpropertybean.java

package com.twm.TDBConnectionPool;
public class DBPropertyBean {

    private String nodeName;
    //Data connection driver
    private String driverName;
    //Data connection url
    private String url;
    //Data connection username
    private String username;
    //Data connection password
    private String password;
    //Maximum number of connections in the connection pool
    private int maxConnections;
    //Minimum number of connections in the connection pool
    private int minConnections;
    //The initial number of connections in the connection pool
    private int initConnections;
    //Reconnection interval time, in milliseconds
    private int conninterval;
    //Get the connection timeout time, in milliseconds, 0 will never time out
    private int timeout;

    //Construction method
    public DBPropertyBean(){
        super();
    }

    //Below is getter and setter

    /**
     * Get the name of the database connection node
     * @return
     */
    public String getNodeName() {
        return nodeName;
    }

    /**
     * Set the name of the database connection node
     * @param nodeName
     */
    public void setNodeName(String nodeName) {
        this.nodeName = nodeName;
    }

    /**
     * Get the database driver
     * @return
     */
    public String getDriverName() {
        return driverName;
    }

    /**
     * Set up the database driver
     * @param driverName
     */
    public void setDriverName(String driverName) {
        this.driverName = driverName;
    }

    /**
     * Get database url
     * @return
     */
    public String getUrl() {
        return url;
    }

    /**
     * Set the database url
     * @param url
     */
    public void setUrl(String url) {
        this.url = url;
    }

    /**
     * Get username
     * @return
     */
    public String getUsername() {
        return username;
    }

    /**
     * Set user name
     * @param username
     */
    public void setUsername(String username) {
        this.username = username;
    }

    /**
     * Get the database connection password
     * @return
     */
    public String getPassword(){
        return password;
    }

    /**
     * Set the database connection password
     * @param password
     */
    public void setPassword(String password) {
        this.password = password;
    }

    /**
     * Get the maximum number of connections
     * @return
     */
    public int getMaxConnections() {
        return maxConnections;
    }

    /**
     * Set the maximum number of connections
     * @param maxConnections
     */
    public void setMaxConnections(int maxConnections) {
        this.maxConnections = maxConnections;
    }

    /**
     * Get the minimum number of connections (also the initial number of connections to the data pool)
     * @return
     */
    public int getMinConnections() {
        return minConnections;
    }

    /**
     * Set the minimum number of connections (also the initial number of connections to the data pool)
     * @param minConnections
     */
    public void setMinConnections(int minConnections) {
        this.minConnections = minConnections;
    }

    /**
     * Get the initial number of connections
     * @return
     */
    public int getInitConnections() {
        return initConnections;
    }

    /**
     * Set the initial number of connections
     * @param initConnections
     */
    public void setInitConnections(int initConnections) {
        this.initConnections = initConnections;
    }

    /**
     * Get the reconnection interval time, in milliseconds
     * @return
     */
    public int getConninterval() {
        return conninterval;
    }

    /**
     * Set the reconnection interval time, in milliseconds
     * @param conninterval
     */
    public void setConninterval(int conninterval) {
        this.conninterval = conninterval;
    }

    /**
     * Get the connection timeout time in milliseconds
     * @return
     */
    public int getTimeout() {
        return timeout;
    }

    /**
     * Set the connection timeout time, in milliseconds, 0-unlimited reconnection
     * @param timeout
     */
    public void setTimeout(int timeout) {
        this.timeout = timeout;
    }

}

Iconnectionpool.java

Package com.twm.TDBConnectionPool;

Import java.sql.Connection;
Import java.sql.SQLException;

Public interface Iconnectionpool {
    /**
     * Gets a database connection that returns NULL
     * @return The database Connection object
    If the wait exceeds the time-out period Public Connection getconnection ();

    /**
     * Get the current thread connection library connection
     * @return Database Connection object
    /Public Connection Getcurrentconnecton ();

    /**
     * Frees the current thread database connection
     * @param conn Database Connection object
     * @throws SQLException
     /public
    void Releaseconn ( Connection conn) throws SQLException;

    /**
     * Destroy empty current connection pool
    /public void Destroy ();

    /**
     * Connection pool Available Status
     * @return Connection pool available *
    /public boolean isactive ();

    /**
     * timer, check connection pool
     *
    /public void Checkpool ();

    /**
     * Get thread pool Active connection number
     * @return thread pool active connections
     /public
    int getactivenum ();

    /**
     * Get thread pool idle connection number
     * @return thread pool idle connections
     /public
    int getfreenum ();
}

Connectionpool.java

package com.twm.TDBConnectionPool;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.TimerTask;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;

import org.apache.log4j.Logger;


/**
 * Class description: friend class, visible in the package, not directly accessible to client programs.
 */
class ConnectionPool implements IConnectionPool {

    private static final Logger log = Logger.getLogger(ConnectionPool.class);

    private DBPropertyBean propertyBean=null;

    //Availability status of connection pool
    private Boolean isActive = true;

    // Idle connection pool. Because List is frequently read and written, it is more appropriate to use LinkedList storage
    private LinkedList<Connection> freeConnections = new LinkedList<Connection>();
    // Active connection pool. Number of active connections <= maximum number of connections allowed (maxConnections)
    private LinkedList<Connection> activeConnections = new LinkedList<Connection>();

    //The connection obtained by the current thread
    private ThreadLocal<Connection> currentConnection = new ThreadLocal<Connection>();

    //The constructor cannot return null, so cancel it. Add the CreateConnectionPool static method below.
    private ConnectionPool(){
        super();
    }

    public static ConnectionPool CreateConnectionPool(DBPropertyBean propertyBean) {
        ConnectionPool connpool=new ConnectionPool();
        connpool.propertyBean = propertyBean;

        //Load driver

        //In a multi-node environment configuration, because it is impossible to determine whether the driver has been loaded here, it may cause the same driver to be loaded repeatedly.
        //So the action of loading the driver is moved to the connectionManager management class to implement it.
        /*try {
            Class.forName(connpool.propertyBean.getDriverName());
            log.info("Load JDBC driver"+connpool.propertyBean.getDriverName()+"success");
        } catch (ClassNotFoundException e) {
            log.info("JDBC driver not found" + connpool.propertyBean.getDriverName() + "Please import related packages");
            return null;
        }*/

        //Basic point 2. Create a specified number of connections according to the initial number of connections in the configuration when starting up
        for (int i = 0; i <connpool.propertyBean.getInitConnections(); i++) {
            try {
                Connection conn = connpool.NewConnection();
                connpool.freeConnections.add(conn);
            } catch (SQLException | ClassNotFoundException e) {
                log.error(connpool.propertyBean.getNodeName()+"Node connection pool initialization failed");
                return null;
            }
        }

        connpool.isActive = true;
        return connpool;
    }



    /**
     * Check if the connection is valid
     * @param database connection object
     * @return Boolean
     */
    private Boolean isValidConnection(Connection conn) throws SQLException{
        try {
            if(conn==null || conn.isClosed()){
                return false;
            }
        } catch (SQLException e) {
            throw new SQLException(e);
        }
        return true;
    }

    /**
     * Create a new connection
     * @return database connection object
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private Connection NewConnection() throws ClassNotFoundException,
            SQLException {

        Connection conn = null;
        try {
            if (this.propertyBean != null) {
                //Class.forName(this.propertyBean.getDriverName());
                conn = DriverManager.getConnection(this.propertyBean.getUrl(),
                        this.propertyBean.getUsername(),
                        this.propertyBean.getPassword());
            }
        } catch (SQLException e) {
            throw new SQLException(e);
        }



        return conn;
    }


    @Override
    public synchronized Connection getConnection() {
        Connection conn = null;
        if (this.getActiveNum() <this.propertyBean.getMaxConnections()) {
            // Branch 1: The currently used connection does not reach the maximum number of connections
            // Basic point 3. Before the connection pool does not reach the maximum number of connections, if there are free connections available, use the free connection directly, if not, create a new connection.
            if (this.getFreeNum()> 0) {
                // Branch 1.1: If there is a connection in the free pool, get it directly from the free pool
                log.info("Branch 1.1: If there is a connection in the free pool, get it directly from the free pool");
                conn = this.freeConnections.pollFirst();

                //If the connection is idle for a long time, it will time out. Therefore, there will be fewer and fewer valid connections in the free pool. Another process is required to scan and monitor to keep a certain number of available connections.
                //The TimerTask class of checkFreepools is defined below, which is called in the checkPool() method.

                //Basic point 5. Since the database connection will be closed over time if it is idle for a long time, the connection pool is required to adopt a mechanism to ensure that each requested connection is valid and available.
                try {
                    if(this.isValidConnection(conn)){
                        this.activeConnections.add(conn);
                        currentConnection.set(conn);
                    }else{
                        conn = getConnection();//The synchronization method is reentrant lock
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } else {
                // Branch 1.2: If there is no available connection in the free pool, create a new connection
                log.info("Branch 1.2: If there is no available connection in the free pool, create a new connection");
                try {
                    conn = this.NewConnection();
                    this.activeConnections.add(conn);
                } catch (ClassNotFoundException | SQLException e) {
                    e.printStackTrace();
                }
            }
        } else {
            // Branch 2: The current maximum number of connections has been reached
// Basic point 4. When the number of active connections in the connection pool reaches the maximum number of connections, new requests enter the waiting state until a connection is released.
            log.info("Branch 2: The current maximum number of connections has been reached");
            long startTime = System.currentTimeMillis();

            //Enter the waiting state. Wait to be awakened by notify(), notifyALL() or automatically wake up after timeout
            try{
                this.wait(this.propertyBean.getConninterval());
            }catch(InterruptedException e) {
                log.error("Thread waiting was interrupted");
            }

            //If the thread is awakened before the timeout and successfully obtains the connection, it will not go to return null.
            //If the thread does not obtain a connection before the timeout, null is returned.
            //If timeout is set to 0, it will reconnect indefinitely.
            if(this.propertyBean.getTimeout()!=0){
                if(System.currentTimeMillis()-startTime> this.propertyBean.getTimeout())
                    return null;
            }
            conn = this.getConnection();

        }
        return conn;
    }


    @Override
    public Connection getCurrentConnecton() {
        Connection conn=currentConnection.get();
        try {
            if(! isValidConnection(conn)){
                conn=this.getConnection();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }


    @Override
    public synchronized void releaseConn(Connection conn) throws SQLException {

        log.info(Thread.currentThread().getName()+"Close connection: activeConnections.remove:"+conn);
        this.activeConnections.remove(conn);
        this.currentConnection.remove();
        //The connection deleted from the active connection pool is added to the free connection pool accordingly
        try {
            if(isValidConnection(conn)){
                freeConnections.add(conn);
            }else{
                freeConnections.add(this.NewConnection());
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        //Wake up the thread waiting in getConnection()
        this.notifyAll();
    }

    @Override
    public synchronized void destroy() {
        for (Connection conn: this.freeConnections) {
            try {
                if (this.isValidConnection(conn)) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        for (Connection conn: this.activeConnections) {
            try {
                if (this.isValidConnection(conn)) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        this.isActive = false;
        this.freeConnections.clear();
        this.activeConnections.clear();
    }

    @Override
    public boolean isActive() {
        return this.isActive;
    }


    @Override
    public void checkPool() {

        final String nodename=this.propertyBean.getNodeName();

        ScheduledExecutorService ses=Executors.newScheduledThreadPool(2);

        //Function 1: Start a timer thread output state
        ses.scheduleAtFixedRate(new TimerTask() {
            @Override
            public void run() {
                System.out.println(nodename + "Number of free connections:" +getFreeNum());
                System.out.println(nodename + "Number of active connections:" +getActiveNum());

            }
        }, 1, 1, TimeUnit.SECONDS);

        //Function 2: Start a timer thread to monitor and maintain the minimum number of connections in the free pool
        ses.scheduleAtFixedRate(new checkFreepools(this), 1, 5, TimeUnit.SECONDS);
    }

    @Overr