There is a problem in our daily operation of the database. We need to establish a database connection for each data operation request. Every time a connection is established, it takes a lot of overhead, such as loading the driver class, registering the driver, and getting the connection. If the connection is made several times in a short time, then it will cost a lot of money.
It takes extra time (load driver + registration driver) * n times.

Then there is a database connection pool solution:

This saves a lot of time. Closing the data connection is the same as above, so it's no longer drawing. The following is the implementation of database connection pool in java and the analysis of time consumption in two ways:

First, DBconnectPool.java, which is equivalent to abstracting business

package Pool;
import java.util.*;
import java.sql.*;

/**
 * Project name: CSDN package name: Pool file name: DBconnectPool.java creation time: April 14, 2019
 * 
 * @author: xiatom Description: Establishing a data connection pool
 * 
 *
 **/
public class DBconnectPool {
	// Available database connections, or data connection pools, use Vector for thread safety
	private Vector<Connection> freeConnection = new Vector<>();
	
	private int maxConn;// maximum connection
	private int normalConn;// Keep Connection Number
	private String pass;
	private String user;
	private String url;
	private int numActive = 0;// Current number of active connections
	private static int num = 0;// Number of currently idle connections

	public DBconnectPool(String url, String user, String pass, int maxConn, int normalConn) {
		this.user = user;
		this.url = url;
		this.pass = pass;
		this.maxConn = maxConn;
		this.normalConn = normalConn;
		for (int i = 0; i < normalConn; i++) {
			Connection con = newConnection();
			if (con != null) {
				freeConnection.addElement(con);
				num++;
			}
		}

	}

	//Create a new connection, which is the small grid of the second picture. Save the registration driver time per load
	private Connection newConnection() {
		Connection con = null;
		try {
			if (user == null)
				con = DriverManager.getConnection(url);
			else
				con = DriverManager.getConnection(url, user, pass);
			System.out.println("Create a new database link");
		} catch (SQLException e) {
			System.out.println("New database link failed,Error:" + e);
			return null;
		}
		return con;
	}

	//Get the current idle connection
	public int getNum() {
		return num;
	}
	
	//Get the currently used connection
	public int getNumActive() {
		return numActive;
	}

	public synchronized Connection getConnection() {
		Connection con = null;
		System.out.println(Thread.currentThread().getName() + "Start getting database links");
		if (freeConnection.size() > 0) {
			num--;
			con = freeConnection.elementAt(0);
			freeConnection.removeElementAt(0);
			// Do not consider the connection that has been closed in the data pool, if you consider the need to add your own
		} else if (maxConn == 0 || normalConn < maxConn) {
			con = newConnection();
		}
		if (con != null)
			System.out.println(Thread.currentThread().getName() + "Get a database link");
		else
			System.out.println("Get empty database connections");
		numActive++;
		return con;
	}

	public synchronized void freeConnection(Connection con) {
		freeConnection.addElement(con);
		num++;
		numActive--;
		notifyAll();
	}

	//Close all connections
	public synchronized void release() {
		for (Connection con : freeConnection) {
			try {
				con.close();
				num--;
				System.out.println("Close a database link");
			} catch (SQLException e) {
				System.out.println("Failed to release data link pool");
			}
		}
		if (num == 0)
			System.out.println("Release all links");
		freeConnection.removeAllElements();
		numActive = 0;
	}
}

The following is Pool.java, which implements the database connection pool:

package Pool;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * Project name: CSDN package name: Pool file name: Pool.java creation time: April 15, 2019
 * 
 * @author: xiatom Description:
 * 
 *
 **/

public class Pool {
	private static Pool instance = null;
	private int maxConn = 100;
	private int normalConn = 10;
	private String password = "1023";
	private String user = "root";
	private String url = "jdbc:mysql://localhost:3306/test";
	private String driverName = "com.mysql.jdbc.Driver";
	DBconnectPool dbpool = null;
	Driver dbDriver = null;

	private Pool() {
		loadDriver(driverName);
		createPool();
	}

	private void createPool() {
		dbpool = new DBconnectPool(url, user, password, maxConn, normalConn);
	}

	private void loadDriver(String driver) {
		try {
			dbDriver = (Driver) Class.forName(driver).newInstance();
			// DriverManager.registerDriver(dbDriver);
			System.out.println("Register Driver Class" + driver + "Success");

		} catch (Exception e) {
			System.out.println("Unable to register driver class" + driver + " Error:" + e);
		}
	}

	public void freeCon(Connection con) {
		if (con != null) {
			dbpool.freeConnection(con);
			System.out.println("Release success");
		} else
			System.out.println("Pass an empty connection.");
	}

	public static Pool getInstance() {
		if (instance == null)
			instance = new Pool();
		return instance;
	}

	public Connection getCon() {
		return dbpool.getConnection();
	}

	public int getNum() {
		return dbpool.getNum();
	}

	public int getNumActive() {
		return dbpool.getNumActive();
	}

	public synchronized void release() {
		dbpool.release();
		try {
			DriverManager.registerDriver(dbDriver);
			System.out.println("Undo Driver Success");
		} catch (SQLException e) {
			System.out.println("Undo Driver Failure");
			e.printStackTrace();
		}
	}
}

The synchronized keyword above is for thread-safe locks
Without locks: For example, the getConnection method of the DBconnectPool class

			num--;
			con = freeConnection.elementAt(0);
If there is no lock, two threads will execute the above step when they get the connection.
So they get the same connection, because the free Connection hasn't changed.
The first element is the same. Then they all execute the following sentence
			freeConnection.removeElementAt(0);
This results in the same connection between two threads, and then the connection pool loses a link.

Here's how long it takes to establish 10 data connections in accordance with this approach

package Pool;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
* Project name: CSDN
* Packet name: ____________ Pool
* File name: MutiThreadTest.java
* Creation date: 15 April 2019
* 
* @author:	xiatom
* Description:		
* 
*
**/
public class ThreadTest implements Runnable{

	static Pool pool = null;
	
	public void test() {
		
	}
	
	@Override
	public void run() {
		Connection con = pool.getCon();
		System.out.println("Surplus"+pool.getNum()+"Available Connections");
	}
	
	public static void main(String[] args) {
		pool = Pool.getInstance();
		ThreadTest tt = new ThreadTest();
		Thread t[] = new Thread[10];
		long start = System.currentTimeMillis();
		for(int i=0;i<10;i++) {
			new Thread(tt,"Thread-"+(i+1)).start();
		}
		while(pool.getNum()!=0) {
		}	
		try {
			Thread.sleep(1);
		} catch (InterruptedException e) {
			e.printStackTrace();
		}
		System.out.println("Use time:"+(System.currentTimeMillis()-start));
	}
}

Since I spent an extra millisecond waiting for all threads to end, the actual usage time was about 3 milliseconds.

Following is a general approach to establishing data connections:

import java.sql.*;
public class Connect {
	private Connection connection;
	Connect() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/test";
			this.connection = DriverManager.getConnection(url,"root","1023");
		} 
		catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}
	public Connection getCon() {
		return this.connection;
	}
}

Test class

import java.sql.*;
public class Main {
	public static void main(String[] args) throws SQLException {
		long start = System.currentTimeMillis();
		for(int i=0;i<10;i++) {
			new Connect().getCon();
		}
		long end = System.currentTimeMillis()-start;
		System.out.println("Consumption time:"+end);
	}
}

The gap is obvious.