Analysis of JDBC core technology

Database connection pool

1. Necessity of JDBC database connection pool

• When developing web programs based on database, the traditional mode basically follows the following steps:

  • Establish database connection in the main program (such as servlet and beans)
  • Perform sql operations
  • Disconnect database

• Problems in the development of this model:

  • Ordinary JDBC database connections are obtained using DriverManager. Each time a Connection is established to the database, the Connection must be loaded into memory, and then the user name and password must be verified (it takes 0.05s ~ 1s). When you need a database Connection, ask for one from the database and disconnect it after execution. This way will consume a lot of resources and time** The Connection resources of the database have not been well reused** If hundreds or even thousands of people are online at the same time, frequent database Connection operation will occupy a lot of system resources, and even cause server crash.
  • **For each database connection, it must be disconnected after use** Otherwise, if the program fails to close due to exceptions, it will lead to memory leakage in the database system and eventually restart the database. (recall: what is Java's memory leak?)
  • This development cannot control the number of connection objects created, and system resources will be allocated without consideration. If there are too many connections, it may also lead to memory leakage and server crash.

2. Database connection pool technology

• In order to solve the problem of database connection in traditional development, database connection pool technology can be used.

• The basic idea of database connection pool is to establish 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.

• Database connection pool is responsible for allocating, managing and releasing database connections. It allows applications to reuse an existing database connection instead of re establishing one.

• During initialization, the database connection pool will create a certain number of database connections into the connection pool. The number of these database connections is set by the minimum number of database connections. No matter whether these database connections are used or not, the connection pool will always ensure that there are at least so many connections. The maximum number of database connections in the connection pool limits the maximum number of connections that the connection pool can occupy. When the number of connections requested by the application from the connection pool exceeds the maximum number of connections, these requests will be added to the waiting queue.

• working principle:

• Advantages of database connection pool technology

  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

3. Multiple open source database connection pools

• The database connection pool of JDBC uses javax sql. DataSource means that DataSource is just an interface, which is usually implemented by servers (Weblogic, WebSphere, Tomcat) and some open source organizations:
  • dbcp is a database connection pool provided by Apache. The tomcat server has its own dbcp database connection pool. The speed is relatively c3p0 fast, but hibernate 3 no longer provides support due to its own BUG.
  • C3P0 is a database connection pool provided by an open source organization. The * * speed is relatively slow and the stability is OK** hibernate is officially recommended
  • Proxool is an open source project database connection pool under sourceforge. It has the function of monitoring the status of the connection pool, and its stability is c3p0 poor
  • BoneCP is a database connection pool provided by an open source organization with high speed
  • Druid 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. However, it is uncertain whether it is faster than BoneCP
• DataSource is usually called data source, which includes connection pool and connection pool management. Traditionally, DataSource is often called connection pool
• DataSource is used to replace DriverManager to obtain Connection, which is fast and can greatly improve database access speed.
• Special attention:
  • The data source is different from the database connection. There is no need to create multiple data sources. It is the factory that generates the database connection. Therefore, the whole application only needs one data source.
  • After the database access is completed, the program closes the database connection as before: conn.close(); However, Conn. Close () does not close the physical connection to the database. It only releases the database connection and returns it to the database connection pool.

3.1 C3P0 database connection pool

• Get connection method 1

//Use C3P0 database connection pool to obtain database connection: not recommended
public static Connection getConnection1() throws Exception{
	ComboPooledDataSource cpds = new ComboPooledDataSource();
	cpds.setDriverClass("com.mysql.jdbc.Driver"); 
	cpds.setJdbcUrl("jdbc:mysql://localhost:3306/test");
	cpds.setUser("root");
	cpds.setPassword("abc123");
		
//	cpds.setMaxPoolSize(100);
	
	Connection conn = cpds.getConnection();
	return conn;
}

mysql8.0 times

public static Connection getConnection1() throws Exception{
	ComboPooledDataSource cpds = new ComboPooledDataSource();
	cpds.setDriverClass( "com.mysql.cj.jdbc.Driver" );             	  
    cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT" );
	cpds.setUser("root");                                  
	cpds.setPassword("abc123");
		
//	cpds.setMaxPoolSize(100);
	
	Connection conn = cpds.getConnection();
	return conn;
}

• Get connection mode 2

//Use the configuration file method of C3P0 database connection pool to obtain database connection: Recommended
private static DataSource cpds = new ComboPooledDataSource("helloc3p0");
public static Connection getConnection2() throws SQLException{
	Connection conn = cpds.getConnection();
	return conn;
}

The configuration file under src is: [c3p0 config. XML]

<?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">abc123</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>

mysql8. Configuration file for 0 (note the change of JDBC URL)

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>

  <named-config name="helloc3p0"> 
  	<!-- Provides 4 basic information for obtaining connections -->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?useSSL=false&amp;serverTimezone=UTC&amp;allowPublicKeyRetrieval=true</property>
    <property name="user">root</property>
    <property name="password">abc123</property>
    
    <!-- Basic information for database connection pool management -->
    <!-- When there are not enough connections in the database connection pool, c3p0 Number of connections requested from the database server at one time -->
    <property name="acquireIncrement">5</property>
    <!-- c3p0 The number of connections initialized in the database connection pool -->
    <property name="initialPoolSize">10</property>
    <!-- c3p0 The minimum number of connections maintained by the database connection pool -->
    <property name="minPoolSize">10</property>
    <!-- c3p0 The maximum number of connections maintained by the database connection pool -->
    <property name="maxPoolSize">100</property>
	<!-- c3p0 Maximum number of database connection pools maintained Statement Number of -->
    <property name="maxStatements">50</property>
    <!-- Maximum number of users per connection Statement Number of --> 
    <property name="maxStatementsPerConnection">2</property>

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

3.2 DBCP database connection pool

• DBCP is an open source connection pool implementation under the Apache Software Foundation, which relies on another open source system under the organization: common pool. If you need to use this connection pool, you should add the following two jar files to the system:
  • Commons-dbcp.jar: implementation of connection pool
  • Commons-pool.jar: dependency library for connection pool implementation
• **Tomcat's connection pool is implemented by this connection pool** The database connection pool can be integrated with the application server or used independently by the application.
• The data source is different from the database connection. There is no need to create multiple data sources. It is the factory that generates the database connection. Therefore, the whole application only needs one data source.
• After the database access is completed, the program closes the database connection as before: conn.close(); However, the above code does not close the physical connection to the database. It only releases the database connection and returns it to the database connection pool.
• Configuration attribute description

attribute	Default value	explain
initialSize	0	The number of initialization connections created when the connection pool started
maxActive	8	The maximum number of simultaneous connections in the connection pool
maxIdle	8	The maximum number of free connections in the connection pool. More free connections will be released. If set to a negative number, it means no limit
minIdle	0	The minimum number of free connections in the connection pool. Below this number, new connections will be created. The closer the parameter is to the maxIdle, the better the performance, because the creation and destruction of connections consume resources; But not too big.
maxWait	unlimited	Maximum waiting time. When there is no available connection, the connection pool waits for the maximum time for the connection to be released. If the time limit is exceeded, an exception will be thrown. If - 1 is set, it means unlimited waiting
poolPreparedStatements	false	Whether the Statement of the open pool is prepared
maxOpenPreparedStatements	unlimited	The maximum number of simultaneous connections after the prepared of the pool is turned on
minEvictableIdleTimeMillis		The time when a connection in the connection pool is idle and expelled from the connection pool
removeAbandonedTimeout	300	Recycling unused (discarded) connections beyond the time limit
removeAbandoned	false	After the removeAbandonedTimeout time is exceeded, whether to recycle unused connections (discarded)

• Get connection method 1:

public static Connection getConnection3() throws Exception {
	BasicDataSource source = new BasicDataSource();
		
	source.setDriverClassName("com.mysql.jdbc.Driver");
	source.setUrl("jdbc:mysql:///test");
	source.setUsername("root");
	source.setPassword("abc123");
		
	//
	source.setInitialSize(10);
		
	Connection conn = source.getConnection();
	return conn;
}

• Get connection mode 2:

//Use the configuration file method of dbcp database connection pool to obtain database connection: Recommended
private static DataSource source = null;
static{
	try {
		Properties pros = new Properties();
		
		InputStream is = DBCPTest.class.getClassLoader().getResourceAsStream("dbcp.properties");
			
		pros.load(is);
		//Create the corresponding DataSource object according to the provided BasicDataSourceFactory
		source = BasicDataSourceFactory.createDataSource(pros);
	} catch (Exception e) {
		e.printStackTrace();
	}
		
}
public static Connection getConnection4() throws Exception {
		
	Connection conn = source.getConnection();
	
	return conn;
}

The configuration file under src is: [dbcp.properties]

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&useServerPrepStmts=false
username=root
password=abc123

initialSize=10
#...

mysql8.0's src configuration file

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT
username=root
password=abc123

initialSize=10

3.3 Druid database connection pool

Druid is a database connection pool implementation on Alibaba's open source platform. It combines the advantages of C3P0, DBCP, Proxool and other DB pools, and adds log monitoring. It can well monitor the connection of DB pool and the execution of SQL. It can be said that Druid is a DB connection pool for monitoring, which can be said to be one of the best connection pools at present.

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 {
		Properties pro = new Properties();		 pro.load(TestDruid.class.getClassLoader().getResourceAsStream("druid.properties"));
		DataSource ds = DruidDataSourceFactory.createDataSource(pro);
		Connection conn = ds.getConnection();
		System.out.println(conn);
	}
}

The configuration file under src is: [druid.properties]

url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver

initialSize=10
maxActive=20
maxWait=1000
filters=wall

• Detailed configuration parameters:

to configure	default	explain
name		The significance of configuring this attribute is that if there are multiple data sources, they can be distinguished by name during monitoring. If there is no configuration, a name will be generated in the format: "DataSource -" + system identityHashCode(this)
url		The url to connect to the database is different from database to database. For example: MySQL: JDBC: mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20. 149.85:1521:ocnauto
username		User name to connect to the database
password		Password to connect to the database. If you don't want the password written directly in the configuration file, you can use ConfigFilter. See here for details: https://github.com/alibaba/druid/wiki/ Using ConfigFilter
driverClassName		Automatic identification according to url is optional. If druid is not configured, dbType will be automatically identified according to url, and then corresponding driverclassname will be selected (under recommended configuration)
initialSize	0	The number of physical connections established during initialization. Initialization occurs when the display calls the init method or the first getConnection
maxActive	8	Maximum number of connection pools
maxIdle	8	It is no longer used, and the configuration has no effect
minIdle		Minimum number of connection pools
maxWait		Maximum wait time to get a connection, in milliseconds. After maxWait is configured, the fair lock is enabled by default, and the concurrency efficiency will be reduced. If necessary, you can use a non fair lock by configuring the useUnfairLock attribute to true.
poolPreparedStatements	false	Whether to cache preparedStatement, that is, PSCache. PSCache greatly improves the performance of databases that support cursors, such as oracle. It is recommended to close under mysql.
maxOpenPreparedStatements	-1	To enable PSCache, it must be configured to be greater than 0. When greater than 0, poolPreparedStatements is automatically triggered and modified to true. In Druid, there will be no problem that PSCache in Oracle occupies too much memory. You can configure this value to be larger, such as 100
validationQuery		The sql used to check whether the connection is valid requires a query statement. If validationQuery is null, testonmirror, testOnReturn, and testwhiteidle will not work.
testOnBorrow	true	When applying for a connection, execute validationQuery to check whether the connection is valid. This configuration will reduce performance.
testOnReturn	false	When returning the connection, execute validationQuery to check whether the connection is valid. This configuration will reduce the performance
testWhileIdle	false	It is recommended to configure to true, which will not affect performance and ensure security. Check when applying for a connection. If the idle time is greater than timebetween evictionrunsmillis, run validationQuery to check whether the connection is valid.
timeBetweenEvictionRunsMillis		It has two meanings: 1) the destroy thread will detect the connection interval; 2) the judgment basis of testwhiteidle. See the description of testwhiteidle property for details
numTestsPerEvictionRun		No longer used, a DruidDataSource only supports one EvictionRun
minEvictableIdleTimeMillis
connectionInitSqls		sql executed during physical connection initialization
exceptionSorter		According to dbType, it is automatically recognized that when the database throws some unrecoverable exceptions, the connection is discarded
filters		The attribute type is string. The extension plug-ins are configured by alias. The commonly used plug-ins are: filter for monitoring statistics: stat, filter for log: log4j, filter for defending sql injection: wall
proxyFilters		The type is List. If both filters and proxyFilters are configured, it is a combination relationship, not a replacement relationship