JDBC Connection Pool Technology and Druid

Because database connection is a kind of resource, it must be created before use, and this process has time and space overhead. If the connection is created every time when database access is executed, and the connection is closed after use, this process is inefficient. Therefore, before database operation can be considered in actual development. A batch of database connection objects are created and maintained in advance. When they need to be used, a connection is obtained from the batch of objects and returned after use, thus avoiding unnecessary time overhead and improving the running efficiency of the program. This technology is called Connection Pool in JDBC.

The most commonly used connection pool technologies at present include:

• apache-DBCP
• C3P0 (Spring/Hibernate framework in use)
• Proxool
• Druid (known as the fastest connection pool in the world, developed by alibaba)

Here we use Druid and DBCP connection pools respectively:

Druid connection pool usage

Dependency of importing Druid before use: druid-1.1.9.jar

public class DruidPoolDemo {

	//Declare data sources for druid connection pools
	public static DruidDataSource dataSource;
	
	static{
		init();
	}
	
	public static void init(){
		//Create connection pool objects
		dataSource = new DruidDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/mydb"); 
		dataSource.setUsername("root");
		dataSource.setPassword("123456"); 
		
		//Set maximum connection number
		dataSource.setMaxActive(10);//CUP*2+1
		//Set the minimum number of idle connections
		dataSource.setMinIdle(1);
		//Set the initial number of connections
		dataSource.setInitialSize(2); 
		//Maximum Waiting Connection Time (MS)
		dataSource.setMaxWait(10000);
	}
	
	public static synchronized Connection getConn(){
		//Ensure that connection pools are singletons
		if(dataSource == null || dataSource.isClosed()){
			//Reinitialize connection pool
			init();
		}
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	public static void main(String[] args) throws InterruptedException, SQLException {
		
		Connection conn1 = getConn();
		Connection conn2 = getConn();
		Connection conn3 = getConn();
		Connection conn4 = getConn();
		Connection conn5 = getConn();
		System.out.println(conn1);
		System.out.println(conn2);
		System.out.println(conn3);
		System.out.println(conn4);
		System.out.println(conn5);
		Thread.sleep(5000);
		conn3.close();
		Connection conn6 = getConn();
		System.out.println("Connection acquisition-->"+conn6);
	}
}

DBCP connection pool usage (DBCP2)

Relevant dependencies need to be imported before use:

public class DBCPPoolDemo {
    
	public static BasicDataSource dataSource;
	
	static{
		init();
	}
	
	public static void init(){
		dataSource = new BasicDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/mydb");
		dataSource.setUsername("root");
		dataSource.setPassword("123456");
		
		//Set the number of connections in the initial connection pool
		dataSource.setInitialSize(2);
		//Set maximum connection number
		dataSource.setMaxTotal(5);
		//Setting the smallest idle connection
		dataSource.setMinIdle(1);
		//Maximum Waiting Connection Time
		dataSource.setMaxWaitMillis(10000);
	}
	
	public static Connection getConn(){
		if(dataSource == null || dataSource.isClosed()){
			init();
		}
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static void main(String[] args) throws InterruptedException, SQLException {
		Connection conn1 = getConn();
		Connection conn2 = getConn();
		Connection conn3 = getConn();
		Connection conn4 = getConn();
		Connection conn5 = getConn();
		System.out.println(conn1);
		System.out.println(conn2);
		System.out.println(conn3);
		System.out.println(conn4);
		System.out.println(conn5);
		Thread.sleep(5000);
		//Recovery connection
		conn3.close();
		Connection conn6 = getConn();
		System.out.println(conn6);
	}
}