throw away a brick in order to get a gem

When and when are we talking about the "pool"? I remember the last time it was an IO stream, I created a buffer pool to speed up the read / write speed (actually reducing the number of IO operations)

So when will the connection pool be used???

Connection pooling is a technique for creating and managing a buffer pool of connections that are ready to be used by any thread that needs them.

Connection pool

In terms of java tool classes, java function tool classes provide convenience for our development. Similarly, connection pools are formally designed based on this concept;

For most client requests, only one thread that can access the JDBC connection is required for database related transactions. When the client does not need to process transactions, the connection will be idle.
In this way, if there are many clients to request, can the idle connection be used again?
The answer is yes, which reduces the consumption of resources (when the client requests, it can use the idle connection), and speeds up the speed of the client request;

The use of connection pool technology can reduce resource consumption and make the cpu more used for other transactions;

Let's simulate a connection buffer pool

Create connection pool

General description of connection pool------

Suppose such a scenario, for a school's course selection system, the concurrency of the course selection system is very small at ordinary times. Only when the course selection starts in the semester, the concurrency goes up;
I remember that the restriction of our school (don't ask which school, it's not your school anyway) was that 500 people could choose courses at the same time; When one person releases the link, the next person can connect to the course selection system;

Let's start working-------

public class MyConnection {
    //Prepare for connection
    final static String URL = "jdbc:mysql://127.0.0.1:3306/gavin?";
    final static String USER = "gavin";
    final static String PASSWORD = "root";
    //Prepare a pool for the connection
    private static LinkedList<Connection> pool;
    //Initialization size
    private static int initSize = 5;
    //Limit the maximum size of the pool
    private static int maxSize = 10;

    //When the connection pool class information is loaded, the pool is initialized, and five connection instances are stored in the pool
    static {
        //Load driver
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //Create space to initialize the connection pool
       pool = new LinkedList<Connection>();
       //Add 5 connection instances to the pool
       for (int i = 0; i < initSize; i++) {
       Connection connection = initConnection();
        pool.addLast(connection);
            }
        }
        //Initialize a connection;
public static Connection initConnection(){
Connection connection = null;
    try {
        connection=DriverManager.getConnection(URL, USER, PASSWORD);
    } catch (SQLException e) {
        e.printStackTrace();
        System.out.println("abnormal");
    }
return connection;

}
    //Method for obtaining connection
    public static Connection getConnection() {
        //How to get it?
        //When there is a connection in the connection pool, it is directly fetched from it
        Connection connection;
        if (pool.size() != 0) {
            connection = pool.removeFirst();
            System.out.println("In connection pool--"+connection.hashCode()+"It was taken away");

        } else {//Create a connection pool when there is no connection pool
             connection = initConnection();
            System.out.println("Connection pool is empty--"+connection.hashCode()+"Was created");

        }
        return connection;
    }

    //How to return the user
    public static void returnConnection(Connection connection){

//When will I return it? I won't accept it until there are less than five in the pool
        //testing
        if(null!=connection) {//Not empty

                try {//testing
                    if (!connection.isClosed()) {//If the connection is not closed
                        //testing
                        //If there are less than 10 pools, you can add them -- always maximize the buffer pool
                        if (pool.size() <= maxSize) {
                            //Add the connection to the pool
                            connection.setAutoCommit(true);
                            pool.addLast(connection);//
                            System.out.println("Returned connection"+connection.hashCode()+"Meet the requirements,Return successful");
                        }else{//If the number of connections in the pool after returning is greater than 10, the link will be closed
                            System.out.println("The pool is full"+connection.hashCode()+"--Will be closed");
                            connection.close();

                        }
                    }else{//Connection closed
                        System.out.println("The returned connection cannot be used,Return failed");
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }

        }else{
            System.out.println("Return failed");
        }

    }

    public static void main(String[] args) {
        Connection connection = getConnection();
        Connection connection1 = getConnection();
        try {
            connection1.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        getConnection();
        getConnection();
        getConnection();
        getConnection();
        returnConnection(connection);
        returnConnection(null);
        returnConnection(connection1);
    }
}

The test results are as follows-----

Consider the robustness of the connection pool and the pressure of the server, and flexibly adjust the number of connections in the connection pool;