What is connection pool
Under the traditional development mode, the Servlet handles the user's request and looks for DAO to query the data. DAO will create a link with the database, and close the link of the database after completing the data query.
Such a way will lead to the user to establish a link to the database every time he requests, and it usually takes a relatively large amount of resources and a long time to create a connection to the database. Assuming that the website has 100,000 visits a day, the database server needs to create 100,000 connections, which greatly wastes the resources of the database, and easily causes the memory overflow and downtime of the database server.

The solution is the database connection pool
Connection pool is a buffer pool for database connection objects.
We can create 10 database connection caches in the connection pool first. When the user requests it, DAO does not need to create a database connection. Instead, DAO takes one from the database connection pool and does not need to close the connection when it runs out. Instead, it changes the connection back into the pool to continue caching.

Using database connection pool can greatly improve system performance.

Implementing database connection pool
jdbc also defines the interface java.sql.DataSource for database connection pool, which all database connection pool implementations need to implement.
Two overload methods are defined in this interface
Connection getConnection()
Connection getConnection(String username, String password)

Thoughts on Implementing Database Connection Pool
1) Define a class to implement the java.sql.DataSource interface
2) Define a collection for saving Connection objects. LinkedList is preferable because of frequent addition and deletion operations.
3) Implement the getConnection method, in which a connection object in the LinkedList collection is retrieved and returned

-
Be careful:

When the user runs out of Connection, he calls the close method to release the resource. At this time, he wants to make sure that the connection is replaced by the connection pool instead of closing the connection. Therefore, his close method needs to be enhanced.

Connection pool code example:

package com.my.pool;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

public class MyPoolJdbcUtil {
    //  1. There is a pool (collection) 
    private static LinkedList<Connection> pool = new LinkedList<Connection>();  //Frequent addition and deletion
    //1.1 Store several links -- 3
    static {
        try {
            for(int i = 0 ; i< 3 ; i++){
                // Get a link
                Class.forName("com.mysql.jdbc.Driver");
                Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=UTF-8","root","1234");
                // Add to pool
                //pool.add(conn);
                pool.add(new MyConnection(conn, pool));
            }
        }  catch (Exception e) {
            e.printStackTrace();
        }
    }

    //  2. When acquired, remove from the pool
    public static Connection getConnection(){
        // 2.1 Processing pool without data
        if(pool.isEmpty()){
            // wait for
            try {
                Thread.sleep(100);
            } catch (InterruptedException e) {
            }
            // If it's empty, get it from pool
            return getConnection();  //recursion
        } else {
            return pool.removeFirst();
        }
    }

    //  3. When released, return the link back to the pool
    public static void closeResource(Connection conn ,Statement st , ResultSet rs){
        try {
            if(rs != null){
                rs.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            try {
                if(st != null){
                    st.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e.getMessage(),e);
            } finally {
                try {
                    if(conn != null){
                        //conn.close();
                        pool.add(conn); //Return links back to the pool
                    }
                } catch (Exception e) {
                    throw new RuntimeException(e.getMessage(),e);
                }
            }
        }

    }

}

Enhanced implementation of close method:

package com.my.pool;

import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;

/**
 * Objectives: Enhance mysql's com.mysql.jdbc.Connection class and implement the java.sql.Connection interface in MySQL
 * 1,Custom class A needs to implement interface B, which needs to enhance the interface of the class.
 * 2,Custom Class A requires an implementation class that can accept Interface B.
 * 3,Custom class A needs to cache the concrete implementation class of interface B
 * 4,Implementing the method close that needs to be enhanced
 * 5,Other methods that do not require enhancement call directly the specific implementation class of interface B
 */

public class MyConnection implements Connection {
    private Connection conn;
    private List<Connection> pool;

    public MyConnection(Connection conn){
        this.conn = conn;
    }

    /* Because it's related to custom connection pools, you need to add another constructor */
    public MyConnection(Connection conn,List<Connection> pool){
        this.conn = conn;
        this.pool = pool;
    }

    /* Here's how to enhance */

    @Override
    public void close() throws SQLException {
        //Add the link Connection object that calls the current close to the link pool
        System.out.println("Connection pool returning..." + this);
        this.pool.add(this);
    }

    /* Here's how you don't need enhancement  */

    @Override
    public void commit() throws SQLException {
        this.conn.commit();
    }

    @Override
    public void clearWarnings() throws SQLException {
        this.conn.clearWarnings();
    }

    @Override
    public Array createArrayOf(String typeName, Object[] elements)
            throws SQLException {
        return this.conn.createArrayOf(typeName, elements);
    }

    @Override
    public Blob createBlob() throws SQLException {
        return this.conn.createBlob();
    }

    @Override
    public Clob createClob() throws SQLException {
        return this.conn.createClob();
    }

    @Override
    public NClob createNClob() throws SQLException {
        return this.conn.createNClob();
    }

    @Override
    public SQLXML createSQLXML() throws SQLException {
        return this.conn.createSQLXML();
    }

    @Override
    public Statement createStatement() throws SQLException {
        return this.conn.createStatement();
    }

    @Override
    public Statement createStatement(int resultSetType,
            int resultSetConcurrency, int resultSetHoldability)
            throws SQLException {
        return this.conn.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability);
    }

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency)
            throws SQLException {
        return this.conn.createStatement(resultSetType, resultSetConcurrency);
    }

    @Override
    public Struct createStruct(String typeName, Object[] attributes)
            throws SQLException {
        return this.conn.createStruct(typeName, attributes);
    }

    @Override
    public boolean getAutoCommit() throws SQLException {
        return this.conn.getAutoCommit();
    }

    @Override
    public String getCatalog() throws SQLException {
        return this.conn.getCatalog();
    }

    @Override
    public Properties getClientInfo() throws SQLException {
        return this.conn.getClientInfo();
    }

    @Override
    public String getClientInfo(String name) throws SQLException {
        return this.conn.getClientInfo(name);
    }

    @Override
    public int getHoldability() throws SQLException {
        return this.conn.getHoldability();
    }

    @Override
    public DatabaseMetaData getMetaData() throws SQLException {
        return this.conn.getMetaData();
    }

    @Override
    public int getTransactionIsolation() throws SQLException {
        return this.conn.getTransactionIsolation();
    }

    @Override
    public Map<String, Class<?>> getTypeMap() throws SQLException {
        return this.conn.getTypeMap();
    }

    @Override
    public SQLWarning getWarnings() throws SQLException {
        return this.conn.getWarnings();
    }

    @Override
    public boolean isClosed() throws SQLException {
        return this.conn.isClosed();
    }

    @Override
    public boolean isReadOnly() throws SQLException {
        return this.conn.isReadOnly();
    }

    @Override
    public boolean isValid(int timeout) throws SQLException {
        return this.conn.isValid(timeout);
    }

    @Override
    public String nativeSQL(String sql) throws SQLException {
        return this.conn.nativeSQL(sql);
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType,
            int resultSetConcurrency, int resultSetHoldability)
            throws SQLException {
        return this.conn.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType,
            int resultSetConcurrency) throws SQLException {
        return this.conn.prepareCall(sql, resultSetType, resultSetConcurrency);
    }

    @Override
    public CallableStatement prepareCall(String sql) throws SQLException {
        return this.conn.prepareCall(sql);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType,
            int resultSetConcurrency, int resultSetHoldability)
            throws SQLException {
        return this.conn.prepareStatement(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType,
            int resultSetConcurrency) throws SQLException {
        return this.conn.prepareStatement(sql, resultSetType, resultSetConcurrency);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
            throws SQLException {
        return this.conn.prepareStatement(sql, autoGeneratedKeys);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int[] columnIndexes)
            throws SQLException {
        return this.conn.prepareStatement(sql, columnIndexes);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, String[] columnNames)
            throws SQLException {
        return this.conn.prepareStatement(sql, columnNames);
    }

    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        return this.conn.prepareStatement(sql);
    }

    @Override
    public void releaseSavepoint(Savepoint savepoint) throws SQLException {
        this.conn.releaseSavepoint(savepoint);
    }

    @Override
    public void rollback() throws SQLException {
        this.conn.rollback();
    }

    @Override
    public void rollback(Savepoint savepoint) throws SQLException {
        this.conn.rollback(savepoint);
    }

    @Override
    public void setAutoCommit(boolean autoCommit) throws SQLException {
        this.conn.setAutoCommit(autoCommit);
    }

    @Override
    public void setCatalog(String catalog) throws SQLException {
        this.conn.setCatalog(catalog);
    }

    @Override
    public void setClientInfo(Properties properties)
            throws SQLClientInfoException {
        this.conn.setClientInfo(properties);
    }

    @Override
    public void setClientInfo(String name, String value)
            throws SQLClientInfoException {
        this.conn.setClientInfo(name, value);
    }

    @Override
    public void setHoldability(int holdability) throws SQLException {
        this.conn.setHoldability(holdability);
    }

    @Override
    public void setReadOnly(boolean readOnly) throws SQLException {
        this.conn.setReadOnly(readOnly);
    }

    @Override
    public Savepoint setSavepoint() throws SQLException {
        return this.conn.setSavepoint();
    }

    @Override
    public Savepoint setSavepoint(String name) throws SQLException {
        return this.conn.setSavepoint(name);
    }

    @Override
    public void setTransactionIsolation(int level) throws SQLException {
        this.conn.setTransactionIsolation(level);
    }

    @Override
    public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
        this.conn.setTypeMap(map);
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return this.conn.isWrapperFor(iface);
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return this.conn.unwrap(iface);
    }

    @Override
    public void setSchema(String schema) throws SQLException {
        this.conn.setSchema(schema);
    }

    @Override
    public String getSchema() throws SQLException {
        return this.conn.getSchema();
    }

    @Override
    public void abort(Executor executor) throws SQLException {
        this.conn.abort(executor);
    }

    @Override
    public void setNetworkTimeout(Executor executor, int milliseconds)
            throws SQLException {
        this.conn.setNetworkTimeout(executor, milliseconds);
    }

    @Override
    public int getNetworkTimeout() throws SQLException {
        return this.conn.getNetworkTimeout();
    }

}

Test code:

package  com.my.pool;

import java.sql.Connection;
import java.sql.SQLException;

public class TestMyPool {

    public static void main(String[] args) {
        for(int i = 0 ; i < 5 ; i ++){
            new MyThread().start();
        }
    }

}

class MyThread extends Thread{
    @Override
    public void run() {
        try {
            // Get links
            Connection conn = MyPoolJdbcUtil.getConnection();
            System.out.println(conn);
            // return
            //MyPoolJdbcUtil.closeResource(conn, null, null);
            conn.close(); //Intention: Really close. Expectation: Return the current link to the custom connection pool (method enhancement)
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Test results: