Connection pool

When JDBC executes an SQL statement, most of the time is wasted on creating and closing connections, and the user experience is very poor

Connection pooling can be used to manage connections, so connection pooling is a buffering technology used to manage connections.

Working process of connection pool

The role of connection pool is to manage connections: create, destroy, allocate, and work status (free or busy)

Because a certain number of connections will be created after the connection pool is initialized, when an application obtains a connection from the connection pool, it can obtain the established connection, so the connection operation is faster

1. When creating a connection pool, create a connection with a certain amount of data in the connection pool. When an application obtains a connection from the connection pool, the status of the connection is busy;
2. When the application releases the connection, the connection becomes idle, but the connection is not really closed;
3. When the number of free connections in the connection pool is less than the minimum number of free connections, the connection pool shall create a new connection; If the number of free connections in the connection pool is greater than the maximum number of free connections, destroy the excess connections and free up space;
4. If all connections are busy when the application obtains a connection, the application will block waiting. You can set the maximum waiting time. If the waiting time exceeds the maximum waiting time, an exception will be thrown;

Some concepts in connection pool

1. Necessary settings and information required to connect to the database
Driven information
Resource code (url)
Account required to connect to the database
Password required to connect to database
2. Configuration information of connection pool
Number of initial connections
Maximum connections
Minimum number of free connections
Maximum number of free connections
Maximum waiting time

Use connection pool:

Generally, the connection pool is not written by yourself. In most cases, the mature connection pool developed by a special company is used

Mature connection pools include:

• dbcp connection pool: it is a traditional connection pool of apache

• c3p0 connection pool: it is more stable than the dbcp connection pool (there will be no connection leakage: after the application releases the connection, the connection is still busy)

• Hikari connection pool: fast, and the spring boot framework uses the default connection pool

• Drain connection pool: provides SQL monitoring function

dbcp connection pool programming:

Initial

1. Download the jar package of dbcp

2. Create a BasicDataSource object, which is a connection pool object;

3. Configure connection pool

4. Get the connection and call the getConnection() method of the connection pool

After using the connection pool, to obtain the connection is to obtain the connection for the connection pool, not for the database

Example:

package com.oracle.dao; 

import java.sql.Connection;
import java.sql.SQLException; 
import org.apache.commons.dbcp.BasicDataSource;

public class Test { 
    public static void main(String[] args) { 
/* 		//ResourceBundle Is a class that specifically reads the properties configuration file 
        //getBundle("db"); The location + name of the parameter properties file 
        //Read DB directly from the classpath Properties on COM In Oracle package, write:
        com.oracle.db ResourceBundle bundle = ResourceBundle.getBundle("db"); 
        System.out.println(bundle.getString("password"));*/ 
        
        //Create DBCP connection pool object 
        BasicDataSource dataSource = new BasicDataSource(); 
        //Necessary settings: basic information for connecting to the database 
        dataSource.setDriverClassName("com.mysql.jdbc.Driver"); 
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test2108?characterEncoding=utf8"); 
        dataSource.setUsername("root"); 
        dataSource.setPassword("root"); 
        //Number of initial connections 
        dataSource.setInitialSize(5);
        //maximum connection 
        dataSource.setMaxActive(20); 
        //Maximum number of free connections
        dataSource.setMaxIdle(10);
        //Minimum number of free connections 
        dataSource.setMinIdle(5);
        //Maximum waiting time 
        dataSource.setMaxWait(3000); 
        //Get connection get connection from connection pool 
        try {
            Connection conn = dataSource.getConnection();
            System.out.println(conn.isClosed()); 
            conn = dataSource.getConnection(); 
            System.out.println(conn.isClosed()); 
            conn = dataSource.getConnection(); 
            System.out.println(conn.isClosed()); 
            conn = dataSource.getConnection(); 
            System.out.println(conn.isClosed()); 
        } catch (SQLException e) { 
            // TODO Auto-generated catch block 
            e.printStackTrace(); 
        }
    }
}