1. Java development process

• Load driver (only need to load once)
• Establish a database Connection (database Connection pool plays a role in this step)
• Execute sql Statement
• ResultSet receive result set (query)
• Disconnect and free up resources

Question:
The database connection object is obtained through DriverManager. Every time you obtain it, you need to apply to the database to obtain the connection, verify the user name and password, and disconnect after executing the sql statement. This way will cause a waste of resources, and the data connection resources are not well reused.

resolvent:
Database connection pool

Design idea:
The basic idea of data connection pool is to establish a buffer pool for the database, and put a certain number of connection objects into the buffer pool in advance. When you need to obtain the database connection, you only need to take an object out of the buffer pool, and then put it back into the buffer pool for the next request, so as to achieve the reuse of resources, Allows programs to reuse an existing database connection object without creating it repeatedly.
When there is no free connection in the database connection pool, new requests will enter the waiting queue and wait for other threads to release the connection.

2. Implementation of data connection pool

The database connection pool of JDBC uses javax sql. DataSource interface. DataSource is an interface officially provided by Java. When using it, developers do not need to implement the interface themselves. They can use third-party tools. C3P0 is a common third-party implementation. In actual development, C3P0 can be directly used to complete the operation of database connection pool.

2.1 import jar package

Step 1: download the jar package Download address
Step 2: copy the jar to the lib folder
step3: right click "build path" - > "Configure Build Path..." - > "Java Path" - > "Libraries" - > "Add Jars" - > find the path where the jar is copied under "lib" and click Jar - > apply and close

2.2 java code implementation

package servlet;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DataSourceTest {

	public static void main(String[] args) {
		//Create C3P0
		
		try {
			ComboPooledDataSource dataSource = new ComboPooledDataSource();
			dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
			dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test?userUnicode=true&characterEncoding=UTF-8");
			dataSource.setUser("root");
			dataSource.setPassword("123456");
			Connection conn = dataSource.getConnection();
			System.out.println(conn);
		} catch (PropertyVetoException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}	
	}
}

Modify the code according to the user name and password you set, and the background output result is that the connection is successful, similar to the following

Connection from C3P0:

com.mchange.v2.c3p0.impl.NewProxyConnection@27ddd392

Traditional Connection:

com.mysql.jdbc.JDBC4Connection@1ed6993a

Reasons for the difference between the two:

C3P0:

//Return to the database connection pool
conn.close();

Traditional way:

//Release resources
conn.close();

2.3 c3p0-config.xml implementation

java file configuration is replaced by xml file
java file

dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test?userUnicode=true&characterEncoding=UTF-8");
dataSource.setUser("root");
dataSource.setPassword("123456");

//Set the number of initialization connections
dataSource.setInitialPoolSize(20);
//Set the maximum number of connections
dataSource.setMaxPoolSize(40);
//When there are not enough connection objects, the number of connection objects applied for again
dataSource.setAcquireIncrement(10);
//Set the minimum number of connections
dataSource.setMinPoolSize(2);

xml file
Must be named c3p0 config xml
It's best to copy it and then change it. It's easy to make mistakes by typing one by one

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

	<named-config name="testc3p0">
	
	<!-- Specifies the basic properties of the connected data source -->
	<property name="user">root</property>
	<property name="password">123456</property>
	<property name="driverClass">com.mysql.jdbc.Driver</property>
	<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
	
	<!-- If the number of connections to the database is less than one, how many connections to the database -->
	<property name="acquireIncrement">5</property>
	<!-- Number of connections when initializing the database connection pool -->
	<property name="initialPoolSize">20</property>
	<!-- Minimum number of database connections in the database connection pool -->
	<property name="minPoolSize">2</property>
	<!-- The maximum number of database connections in the database connection pool -->
	<property name="maxPoolSize">40</property>
	
	</named-config>

</c3p0-config>

They have the same meaning
The output results are shown in the figure

In the actual development, the configuration information of C3P0 is defined in the xml file, and the java program only needs to load the configuration file to complete the initialization of the data connection pool.
Note:
1. The name of the configuration file must be c3p0 config xml
2. When initializing ComboPooledDataSource, the parameter passed in must be c3p0 config Attribute value of named config tag in XML
3. xml is placed under the src root directory