Database connection pool

Previous problems

In fact, the previous code has no problem, enough to operate the database.

But getting the connection object (driverManager.getConnection(String driverClassName, String url, String password);) is a resource-intensive step. Creating the connection object every time you use it, closing it when you use it, and creating it the next time you need it, consumes performance.

Designing a scenario where we create objects every time we need to use them, save them when we use them (instead of closing them immediately) and take them out the next time we use them instead of creating them, is very helpful for performance improvement.

A component that holds connection objects is called a database connection pool.

To put it bluntly, the database Connection pool is used to get the Connection objects. Later, the Connection pool is used to get the Connection objects and improve the performance.

• The difference between using connection pools and not using connection pools
  • No connection pooling
    • Connection objects are retrieved using driverManager

    	Connection conn = driverManager.getConnection(String driverClassName, String url, String password);
    

    • Close the connection after use and release resources

    	conn.close();
    

  • Use connection pools
    • Connecting objects are retrieved with DataSource objects

    	Connection conn = datasource.getConnection();
    

    • Return the connection object to the connection pool after use

    	conn.close();
    

Use of connection pool

	//Create a connection pool object
	public DataSource getDataSource(){
		//Create connection pool objects
		BasicDataSource ds = new BasicDataSource();
		//Four Elements of Setting up Connecting Database
		ds.setDriverClassName("com.mysql.jdbc.Driver");
		ds.setUrl("jdbc:mysql://localhost:3306/jdbcdemo");
		ds.setUsername("root");
		ds.setPassword("1092568516");
		return ds;
	}
    
    @Test
	public void test01() throws Exception{
		String sql = "SELECT * FROM `user`";
        //Create connection pool objects
		DataSource ds = this.getDataSource();
        //Getting connections through connection pool objects
		Connection conn = ds.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			System.out.println(rs.getLong("id"));
		}
	}

• DataSource is the database connection pool interface, and Basic DataSource is its implementation class.
• After creating a DataSource object, use setXxx() to set the four elements of connection: driver name, url, account number, password (how do you know which database you want to connect if you don't set it up?)
• Get the connection using the created connection pool object

The difference between using connection pool and not using connection pool is obvious: when using connection pool, the connection four elements are passed by parameters; when using connection pool, the connection four elements are set by setXxx() method (connection four elements indicate which database you want to connect to, which database, account and password to connect to which computer, so It's necessary, whether it's a connection pool or not.

Extraction Tool Class

Before writing JDBC code, because many steps are repetitive, such as loading drivers, getting connections, etc., we extracted these repetitive steps from the jdbcutil tool class. This can also be done when using connection pools.

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

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSourceFactory;

public class DBCPUtil {
    
	static{
		BasicDataSource basicDs = new BasicDataSource();
        
		basicDs.setDriverClassName("com.mysql.jdbc.Driver");
		basicDs.setUrl("jdbc:mysql://localhost:3306/jdbcdemo");
		basicDs.setUsername("root");
		basicDs.setPassword("1092568516");
	}
	
	public static Connection getConn(){
		try {
			//Getting Connection Objects from Connection Pool
			return basicDs.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static void close(Connection conn, Statement st, ResultSet rs){
		try{
			if(rs != null){
				rs.close();
			}
		}catch(Exception e){
		}finally{
			try{
				if(st != null){
					st.close();
				}
			}catch(Exception e){
			}finally{
				try{
					if(conn != null){
						conn.close();
					}
				}catch(Exception e){
				}
			}
		}
	}
	
}

Like JDBC, the four elements of database connection are written in the properties file to reduce coupling. Here, a database connection pool called DBCP is used to create a data source using a factory instead of a new one (encapsulating the new Basic Data Source and setXxx() into the Basic Data Source Factory, and the bottom layer is still a new Basic Data Source (). The code for Xxx ();) is as follows:

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

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSourceFactory;

public class DBCPUtil {
	
	private static DataSource ds = null;
	static{
		//BasicDataSource basicDs = new BasicDataSource();
		//basicDs.setDriverClassName("com.mysql.jdbc.Driver");
		//basicDs.setUrl("jdbc:mysql://localhost:3306/jdbcdemo");
		//basicDs.setUsername("root");
		//basicDs.setPassword("1092568516");
		try {
			Properties p = new Properties();
			//Loading configuration files
			p.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("dbcp.properties"));
			//Creating DataSource Objects through BasicDataSourceFactory
			ds = BasicDataSourceFactory.createDataSource(p);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConn(){
		try {
			//Getting Connection Objects from Connection Pool
			return ds.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

The properties configuration file code is as follows:

#key can't be scribbled, it must be an attribute in Basic Data Source (see the source code, actually the bottom is the setter method)
DriverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcdemo
username=root
password=1092568516

druid connection pool

Ali's database connection pool, known as the best database connection pool in the world

The method of use is similar to DBCP, just change Basic Data Source Factory to Druid Data Source Factory.

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

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class DruidUtil {
	
	private static DataSource ds = null;
	static{
		try {
			Properties p = new Properties();
			p.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("druid.properties"));
			//Creating DataSource Objects
//			ds = BasicDataSourceFactory.createDataSource(p);
			ds = DruidDataSourceFactory.createDataSource(p);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConn(){
		try {
			//Getting Connection Objects from Connection Pool
			return ds.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static void close(Connection conn, Statement st, ResultSet rs){
		try{
			if(rs != null){
				rs.close();
			}
		}catch(Exception e){
		}finally{
			try{
				if(st != null){
					st.close();
				}
			}catch(Exception e){
			}finally{
				try{
					if(conn != null){
						conn.close();
					}
				}catch(Exception e){
				}
			}
		}
	}
	
}