Data connection pool

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.

Introduction to database connection pool

1. Problems of traditional connection:

• The connection resources of the database have not been well reused.
• For each database connection, it must be disconnected after use.
• This development cannot control the number of connection objects created

2. How to solve the database connection problem in traditional development: using database connection pool

working principle

3. Benefits of using database connection pool:

• 1. Improve the response speed of the program (reduce the corresponding time for creating connections)
• 2. Reduce resource consumption (can reuse already provided connections)
• 3. Easy connection management

4. Implementation method:

• 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

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;
}

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

DBCP data connection pool

DBCP(DataBase connection pool) database connection pool is a Java connection pool project on apache. DBCP establishes some connections with the database in advance through the connection pool and puts them in memory (i.e. in the connection pool). When the application needs to establish a database connection, it directly applies for a connection from the connection pool. After it is used up, the connection is recycled by the connection pool, so as to achieve the purpose of connection reuse and reduce resource consumption.

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

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