catalogue

VIII. Database connection pool

8.1 necessity of JDBC database connection pool

8.2 database connection pool technology

8.3 multiple open source database connection pools

8.3.1 C3P0 database connection pool

8.3.2 DBCP database connection pool

8.3.3 Druid database connection pool

VIII. Database connection pool

8.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.

8.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

8.3 multiple open source database connection pools

• The database connection pool of JDBC is represented by javax.sql.DataSource. DataSource is only an interface, which is usually implemented by the server (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, which is relatively slow and stable. 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.

8.3.1 C3P0 database connection pool

//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>

8.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

  Acquisition method

//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
#...

8.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: