What are the advantages of using connection pools over our usual JDBC code?

1. Resource reuse
Because of the reuse of database connections, frequent creation is avoided and a large amount of performance overhead caused by connections is released. On the other hand, it also increases the stability of system operation environment on the basis of reducing system consumption.
2. Faster system response speed
In the initialization process of database connection pool, several database connections have been created and placed in the connection pool for standby. At this point, the initialization of the connection has been completed. For business request processing, using available connections directly avoids the time overhead of initialization and release of database connections, thus reducing the response time of the system.
3. New means of resource allocation
For systems with multiple applications sharing the same database, the maximum number of database connections available for an application can be limited through the configuration of the database connection pool at the application level, thus avoiding the exclusive use of all database resources by an application.
4. Unified connection management to avoid database connection leakage
In a more perfect database connection pool implementation, the occupied connection can be forcibly reclaimed according to the pre-occupied timeout settings, thus avoiding the possible resource leakage in the conventional database connection operation.

First, let's introduce DBCP, one of the open source data sources.

DBCP(DataBase connection pool), database connection pool. It is a java connection pool project on apache and is also a connection pool component used by tomcat. Using DBCP alone requires two packages: commons-dbcp.jar and commons-pool.jar. Because establishing database connection is a very time-consuming and resource-consuming behavior, some connections with the database are established in advance through connection pool and stored in memory. When an application needs to establish a database connection, it can apply directly to the connection pool and put it back after it is used up.

DBCP development steps (2 steps):

Step 1: Write dbcpconfig.properties

#connections setting up
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day15
username=root
password=123456

#<! - Initialize the connection - >
initialSize=10

#Maximum number of connections
maxActive=50

#<! - Maximum idle connection
maxIdle=20

#<! - Minimum idle connection - >
minIdle=5

#<! - The time-out waiting time is 60,000 milliseconds per 1000 in milliseconds - >
maxWait=60000

#The format of the join attribute attributes attached to the JDBC driver when establishing a connection must be as follows: [attribute name = property;] 
#Note that the attributes "user" and "password" will be passed explicitly, so they need not be included here.
connectionProperties=useUnicode=true;characterEncoding=utf8

#Specifies the auto-commit state of the connection created by the connection pool.
defaultAutoCommit=true

#driver default specifies the read-only state of the connection created by the connection pool.
#If this value is not set, the "setReadOnly" method will not be invoked. (Some drivers do not support read-only mode, such as Informix)
defaultReadOnly=

#driver default specifies the transaction level (Transaction Isolation) of the connection created by the connection pool.
#Available values are one of the following: (Details can be found in javadoc.) NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=REPEATABLE_READ

Step 2: Writing the DBCPUtil Tool Class

import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;
/**
 * Open Source Data Source - Writing Step of DBCP Tool Class:
 * 	1. Define a connection pool object
 * 	2. Load the configuration file jdbcconfig.properties
 * 	3. Call Basic DataSourceFactory. createDataSource (properties)
 * 			Get a connection pool object
 * 	4. getConnection()Method to get a database connection
 * @author Administrator
 *
 */
public class DBCPUtil {
	//Define connection pool objects
	private static DataSource dataSource;
	static {
		try {
			InputStream inputStream = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
			Properties properties = new Properties();
			properties.load(inputStream);
			/**
			 * Key Statement: Get Connection Pool Objects
			 */
			dataSource = BasicDataSourceFactory.createDataSource(properties);
		} catch (Exception e) {
			throw new ExceptionInInitializerError("Initialization connection error, please check the configuration file!");
		}
	}
	
	public static DataSource getDataSource() {
		return dataSource;
	}
	
	public static Connection getConnection() {
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}

Step 3: Write test classes

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

import com.yangguang.c3p0.util.C3P0Util;
import com.yangguang.dbcp.util.DBCPUtil;

public class Test {

	public static void main(String[] args) {
		Connection connection = DBCPUtil.getConnection();
		System.out.println("The Class is:"+connection.getClass().getName());
		try {
			connection.close();//Play back the connection pool
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}