1, Introduction

As we all know, database connection usually uses JDBC to connect to the database, but what does pool mean?

There are many kinds of pools, such as database connection pool, object pool, memory pool, thread pool, etc.

For example, the [object pool] object pool can be regarded as a container for saving objects, and a certain number of objects are created during process initialization. When necessary, a free object is directly taken from the pool. After it is used up, the object is not directly released, but put it into the object pool so that the next object request can be reused directly.

Similarly, a database connection pool is a "buffer pool" for database connections. Put a certain number of connections in the buffer pool in advance. When you need to establish a database connection, just take one from the buffer pool and put it back after use. These connections returned to the connection pool are not closed (instead of closing the physical connection to the database, the database connection is released and returned to the database connection pool), Instead, it is ready to be allocated to the next caller. We can prevent the system from connecting to the database endlessly by setting the maximum number of connections in the connection pool. More importantly, we can monitor the number and usage of database connections through the connection pool management mechanism, so as to provide a basis for system development, testing and performance adjustment.

Database connection pool involves three concepts:

• Minimum connections: the number of connections opened immediately after the application is started and the minimum number of subsequent connections maintained.
• Maximum connections: the maximum number of connections that an application can use.
• Connection growth: the number of newly opened connections for each application.

Database connection pools commonly used in Java include DBCP, C3P0, BoneCP, Proxool, DDConnectionBroker, DBPool, XAPool, Primrose, SmartPool, MiniConnectionPoolManager and Druid.

The following describes database connection pool C3P0 and Druid:

2, C3P0 database connection pool

It is a database connection pool provided by an open source organization, which is relatively slow and stable.

2.1 Guide Package

Jar package: c3p0-0.9.1.2 jar

2.2. Get connection

//Use the information in the configuration file to create a C3P0 database connection pool and obtain the connection to the database
private static DataSource cpds = new ComboPooledDataSource("helloc3p0");
public static Connection getConnection2() throws SQLException{
    //Assign call when connection is required
	Connection conn = cpds.getConnection();
	return conn;
}

The code in the configuration file [c3p0-config.xml] is as follows:

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<named-config name="helloc3p0">
		<!-- Get 4 basic information of connection -->
		<property name="user">root</property>
		<property name="password">root</property>
		<property name="jdbcUrl">jdbc:mysql:///test</property>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		
		<!-- Settings of related properties related to the management of database connection pool -->
		<!-- If the number of connections in the database is insufficient, How many connections are requested from the database server at a time -->
		<property name="acquireIncrement">5</property>
		<!-- The number of connections when initializing the database connection pool -->
		<property name="initialPoolSize">5</property>
		<!-- The minimum number of database connections in the database connection pool -->
		<property name="minPoolSize">5</property>
		<!-- The maximum number of database connections in the database connection pool -->
		<property name="maxPoolSize">10</property>
		<!-- C3P0 The database connection pool can be maintained Statement Number of -->
		<property name="maxStatements">20</property>
		<!-- Each connection can be used at the same time Statement Number of objects -->
		<property name="maxStatementsPerConnection">5</property>

	</named-config>
</c3p0-config>

3, Druid database connection pool

It is a database connection pool provided by Alibaba. It is said to be a database connection pool integrating the advantages of DBCP, C3P0 and Proxool.

3.1 Guide Package

Jar package: druid-1.1.10 jar

3.2. Get connection

package com.atguigu.druid;

import java.sql.Connection;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class TestDruid {
	public static void main(String[] args) throws Exception{
        //Create Properties object
        Properties pro = new Properties();
        //Get configuration information
        pro.load(TestDruid.class.getClassLoader().getResourceAsStream("druid.properties"));
		//Use the Druid database connection pool to connect to the database according to the information in the configuration file
        DataSource ds = DruidDataSourceFactory.createDataSource(pro);
		//Get connection
        Connection conn = ds.getConnection();
		//Print connection
        System.out.println(conn);
	}
}

The code in the configuration file [druid.properties] is as follows:

url=jdbc:mysql://localhost:3306/test? Rewritebackedstatements = true # url to connect to the database
username=root #User name to connect to the database
password=root #Password to connect to the database
driverClassName=com.mysql.jdbc.Driver #dbType, which can be automatically identified according to the url

initialSize=10 #Number of physical connections established during initialization
maxActive=20 #Maximum number of connection pools
maxWait=1000 # Maximum wait time to get a connection, in milliseconds
filters=wall # Defensive sql injection

4, There are four advantages of database connection pool

1. resource reuse

   Because the database connection can be reused, it avoids frequent creation and releases a lot of performance overhead caused by the connection. On the basis of reducing system consumption, on the other hand, it also increases the stability of system operation environment.

2. Faster system response

   During the initialization of the database connection pool, several database connections have often been created and placed in the connection pool for standby. At this time, the initialization of the connection has been completed. For business request processing, the existing available connections are directly used to avoid the time overhead of database connection initialization and release process, so as to reduce the response time of the system.

3. New means of resource allocation

   For systems where multiple applications share the same database, the maximum number of available database connections of an application can be limited through the configuration of database connection pool at the application layer to avoid an application monopolizing all database resources.

4. Unified connection management to avoid database connection leakage

   In the more perfect implementation of database connection pool, the occupied connections can be forcibly recovered according to the pre occupation timeout setting, so as to avoid the possible resource leakage in the conventional database connection operation.