How to integrate hikaricp connection pool under 04 springboot project?

Time:2020-11-29

An analysis of pooling thought

The idea of pooling is a very important idea in the process of our project development. For example, the integer pool, string pool, object pool, connection pool and thread pool created by the bottom layer for us are all an application of pooling idea. They all reuse objects to reduce the resource consumption caused by creating and releasing objects, so as to improve the system performance. For example, the internal pool application of integer object is as follows:

package com.cy.java.pool;
public class TestInteger01 {
    public static void main(String[] args) {
        Integer n1=100;// Integer.valueOf (100) compile time optimization
        Integer n2=100;
        Integer n3=200;
        Integer N4 = 200; // if there is no integer in the pool, then new integer (200)
        System.out.println(n1==n2);//true
        System.out.println(n3==n4);//false 
    }
    
}

Brief analysis: why is N1 = N2 true and N3 = N4 false? It mainly depends on whether the value is in the range (- 128 ~ 127) of the integer pool. If it is in the range, it will be used. If it is not, it will be renewed.

Introduction to database connection pool

Background analysis

At present, when the application program interacts with the database in the development process, “get connection” or “release connection” are two processes that consume system resources. Frequent establishment and closing of database connection will greatly affect the performance of the system. If the multithreading concurrency is large, such time-consuming database connection may cause the system to become stuck. Because the cost of creating TCP connection is very expensive, and the number of concurrent TCP connections that the database can carry is limited, database connection pool emerges as the times require. As shown in the figure below:

How to integrate hikaricp connection pool under 04 springboot project?

Thinking: if you design a connection pool, from what perspective?
First: physical storage structure (based on what structure to store data)
Second: what algorithm is used to get connections from the pool?
Third: what algorithm is used to remove connections from the pool?
Fourth: when there is no connection in the pool, what method is used to process connection requests?
Fifthly: pools can be shared. Do you need to consider concurrent security when accessing pools?

Principle analysis of connection pool

When the system is initialized, a space is opened up in the memory, a certain number of database connections are stored in the object pool as objects, and the methods of obtaining and returning database connections are provided. When users access the database, they do not create a new connection, but take an existing idle connection object from the database connection pool. The connection returned after use will not be closed immediately, but will be managed and recycled by the database connection pool to prepare for the next borrowing. If the connection in the database connection pool is borrowed due to high concurrency requests, other threads will wait until a connection is returned. Throughout the process, the connection will not be closed, but will continue to be recycled. The database connection pool can also set its parameters to control the initial number of connections, the upper and lower limits of the connection pool, as well as the maximum usage times and maximum idle time of each connection. It can also monitor the number and usage of database connections through its own management mechanism.

Connection pool in Java

Java officially defines a set of data source specifications, such as javax.sql.DataSource Many interfaces are created based on this individual or team interface. Then, through coupling and datasource interface in our application, we can easily switch the connection pool of different manufacturers. A basic process of getting connections through connection pool in Java project, as shown in the following figure:

How to integrate hikaricp connection pool under 04 springboot project?

In the figure above, the user gets a connection through the getconnection () method of the datasource object. If there is a connection in the pool, the connection is returned directly to the user. If there is no connection in the pool, the connect method of the dirver (driver, implemented by the database manufacturer) object will be called to obtain the connection from the database. After getting the connection, you can put a copy of the connection in the pool, and then return the connection to the caller. When the connection demander needs to connect again, it can be obtained from the pool and returned to the pool object after it is used up.

Hikaricp integration test under springboot project

Data initialization

Open the MySQL console and follow the steps below goods.sql Documents.
Step 1: log in to MySQL.

mysql –uroot –proot

Step 2: set the console encoding mode.

set names utf8;

Step 3: Execution goods.sql File (remember not to open the file and copy it to the MySQL client).

source d:/goods.sql

among goods.sql The contents of the document are as follows:

drop database if exists dbgoods;
create database dbgoods default character set utf8;
use dbgoods;
create table tb_goods(
     id bigint primary key auto_increment,
     name varchar(100) not null,
     remark text,
     createdTime datetime not null
)engine=InnoDB;
insert into tb_goods values (null,'java','very good',now());
insert into tb_goods values (null,'mysql','RDBMS',now());
insert into tb_goods values (null,'Oracle','RDBMS',now());
insert into tb_goods values (null,'java','very good',now());
insert into tb_goods values (null,'mysql','RDBMS',now());
insert into tb_goods values (null,'Oracle','RDBMS',now());
insert into tb_goods values (null,'java','very good',now());

Create the project module and add related dependencies

Step 1: create the project module based on idea, as shown in the figure:

How to integrate hikaricp connection pool under 04 springboot project?

Step 2: add dependencies
1) Mysql database driver dependency.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

2) JDBC support for spring objects (in this case, the hikaricp connection pool will be downloaded for us by default).

<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

Hikaricp connection configuration

open application.properties Configuration file, add the following content (required).

spring.datasource.url=jdbc:mysql:///dbgoods?serverTimezone=GMT%2B8&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=root

Other additional configuration of hikaricp (optional), the code is as follows (if the specific configuration is not clear, you can do it by Baidu)

spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=15
spring.datasource.hikari.auto-commit=true
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.pool-name=DatebookHikariCP
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.connection-test-query=SELECT 1 

Hikaricp connection pool test

Unit test API design and application analysis are shown in the figure

How to integrate hikaricp connection pool under 04 springboot project?

Add unit test class and test method to the project. The code is as follows:

package com.cy.pj.common.datasource;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
public class DataSourceTests {
/**
 *Who defines the datasource? Java official
 *What is defined? Defines a specification for getting connections from a database or connection pool
 *Why should we couple to it? When there are dependencies (coupling) between classes, try to rely on abstract specifications, and then get connections based on specifications
 *Who is the object type that this variable points to at runtime? Hikari data source, how do you know (breakpoints, logs)
 */
    @Autowired
    private DataSource dataSource;
    @Test
    public void testConnection() throws Exception{
     //What is the specific type of the datasoure object
System.out.println(dataSource.getClass().getName());//com.zaxxer.hikari.HikariDataSource
//What is the process for datasource to get the connection
System.out.println(dataSource.getConnection());
    }
}

In the current test class, we need to:

  • Master unit test class and test method writing specification.
  • Understand the design specification and implementation of datasource.
  • Analyze who the datasource property points to in the test class?
  • Analyze who creates and manages the implementation class objects of datasource in the test class?
  • What is the basic process of getting a connection based on the datasource interface?

Test bug analysis

  • The database does not exist, as shown in the figure:

How to integrate hikaricp connection pool under 04 springboot project?
Solution: the name of the database is wrongly written, for example, dbgood is written as dbgood.

  • Class compilation error, datasource is javax.sql Types in the package, as shown in the figure:

How to integrate hikaricp connection pool under 04 springboot project?
Solution: 1. Check whether the datasource class references javax.sql This package, 2. Whether the dependency is added successfully.

  • Connection error: the database cannot be connected, as shown in the figure:

How to integrate hikaricp connection pool under 04 springboot project?
Solution: check the spring.datasource.username=root and spring.datasource.password=root Whether the user name of is correct.

Implementation of JDBC operation based on hikaricp

Business analysis

Based on hikaricp, the data in commodity database is accessed by JDBC technology.

API architecture design

Based on business, API design is carried out, as shown in the figure

How to integrate hikaricp connection pool under 04 springboot project?

Business sequence diagram analysis

Based on the business requirements, the sequence diagram of commodity query process is designed, as shown in the figure

How to integrate hikaricp connection pool under 04 springboot project?

Business code design and Implementation

Step 1: define the goodsdao interface, for example:

package com.cy.pj.goods.dao;
import java.util.List;
import java.util.Map;
/**
 *Commodity module data access layer interface
 */
public interface GoodsDao {
    /**
 *Query all commodity information, store each line of record into a map object, and then store multiple records in the list collection
 */ List<Map<String,Object>> findGoods();
}

Step 2: create the goodsdao interface implementation class. The code is as follows:
Method 1

package com.cy.pj.goods.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Repository
public class GoodsGoodsImpl implements GoodsDao {
    @Autowired
 private DataSource dataSource;
    @Override
 public List<Map<String, Object>> findGoods() {
        Connection conn=null;
        Statement sta=null;
        ResultSet rs=null;
    //1. Get connection (from connection pool)
 try {
      conn=dataSource.getConnection();
    //2. Create statement object
 sta=conn.createStatement();
    //3. Send SQL
 String sql="select * from tb_goods";
      rs=sta.executeQuery(sql);
    //4. Treatment results
 List<Map<String,Object>> list=new ArrayList<>();
      while ( rs.next ()) {// loop takes one line at a time, and one line of record is mapped to a map object
 Map<String,Object> map=new HashMap<>();
          map.put("id", rs.getInt("id"));
          map.put("name", rs.getString("name"));
          map.put("remark", rs.getString("remark"));
          map.put("createdTime", rs.getTimestamp("createdTime"));
          list.add(map);
      }
      return list;
        }catch (SQLException e){ e.printStackTrace();
        return null;
        }finally {
    //5. Release resources
 if (rs!=null)try{rs.close();}catch(Exception e){e.printStackTrace();}
      if (sta!=null)try{sta.close() ;}catch(Exception e){e.printStackTrace();}
      if (conn!=null)try{conn.close();}catch (Exception e){e.printStackTrace();}
  }
    }
}

Method 2: optimization

package com.cy.pj.goods.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Repository
public class GoodsDaoImpl implements GoodsDao {
    @Autowired
 private DataSource dataSource;
    @Override
 public List<Map<String, Object>> findGoods() {
        Connection con = null;
        Statement sta = null;
        ResultSet rs = null;
        //1. Get the connection
 try {
            con = dataSource.getConnection();
            //2. Create statement
 sta = con.createStatement();
            //3. Send SQL
 String sql = "select id,name,remark,createdTime from tb_goods";
            rs = sta.executeQuery(sql);
            //4. Treatment results
 List<Map<String, Object>> list = new ArrayList<>();
            //Get the metadata in the result set (data describing the data - such as table name, field name,...)
 ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                list.add(rowMap(rsmd, rs));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            //5. Release memory
 close(rs, sta, con);
        }
    }
//Encapsulate result set
private Map<String,Object> rowMap(ResultSetMetaData rsmd,ResultSet rs)throws SQLException{
    Map<String,Object> map=new HashMap<>();
    int count=rsmd.getColumnCount();
    for(int i=1;i<=count;i++){
        map.put(rsmd.getColumnLabel(i), rs.getObject(i));
    }
    return map;
}
//Encapsulation frees memory
private void close(ResultSet rs,Statement sta,Connection conn){
    if (rs!=null)try { rs.close();}catch (Exception e){e.printStackTrace();}
    if(sta!=null)try{sta.close();}catch(Exception e){e.printStackTrace();}
    //The connection here is returned to the pool
 if(conn!=null)try{conn.close();}catch(Exception e){e.printStackTrace();}
    }

Writing and running of test code

Define the unit test class and conduct unit test on its query process, for example:

package com.cy.pj.goods.dao;

@SpringBootTest
public class GoodsDaoTests {

      @Autowired
      private GoodsDao goodsDao;

      @Test
      void testFindGoods(){
          List<Map<String,Object>> list= goodsDao.findGoods();
          for(Map<String,Object> map:list){
              System.out.println(map);
          }
      }

}

Bug analysis in test running process

Common is the bug introduced above.

Summary

In short, the database connection pool has brought many advantages to our project development and operation. The details are as follows:

  • Better resource reuse.

Because the database connection is reused, the overhead of creating and closing the connection is reduced, and the memory fragment and the number of database temporary processes and threads are greatly reduced, which makes the whole system run more smoothly.

  • System tuning is easier.

After the use of database connection pool, due to resource reuse, it greatly reduces the cost of frequently closing connections and greatly reduces the time_ The frequency of wait.

  • The system responds faster.

In the process of application initialization, database connection pool usually prepares some database connections in advance. Business requests can directly use the connection that has been created without waiting for the overhead of connection creation. The database connection pool can greatly shorten the overall response time of the system by initializing database connection and reusing resources.

  • Connection management is more flexible.

As a middleware, database connection pool can configure the minimum number, maximum number, maximum idle time, connection timeout, heartbeat detection, etc. In addition, users can also add a series of practical functions such as dynamic configuration, monitoring and fault drill of database connection pool according to the new technology trend.