SpringBoot integrated Druid connection pool

1. Data source

Data source is a JDBC specification interface defined by SUN company for obtaining database connection
It is located in the javax.sql package and is used to obtain the connection instead of the driver manager

package javax.sql;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Wrapper;
// Omit comments
public interface DataSource  extends CommonDataSource, Wrapper {

  Connection getConnection() throws SQLException;

  Connection getConnection(String username, String password)
    throws SQLException;
}

Using the data source, you can directly obtain the connection object of the database without writing the database code.

2. Database connection pool

The data source can create multiple database connections, which are saved in the database connection pool.
When you need to establish a database connection, you only need to take an idle connection from the connection pool and put it back after it is used up.

In the traditional JDBC access technology, each time you access the database, you need to establish a database connection through the database Driver, database name, password and other resources, which will increase the overhead of system resources and CPU, and the connection pool can solve this problem well.

In addition, we can monitor the number and usage of database connections through the management mechanism of connection pool, so as to provide basis for system development, testing and performance adjustment.

3. Integrated Druid connection pool

Druid connection pool is the best database connection pool at present. It is far superior to DBCP and C3P0 in function, performance and scalability.

Spring boot integrates Druid connection pool in two main ways.

(1) Using the Druid launcher

Directly introduce Druid spring boot starter in pom.xml, and then configure it in application.yaml:

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.22</version>
</dependency>
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    username: root
    password: root

Write a simple test case and print the configuration of the data source:

@SpringBootTest
@RunWith(SpringRunner.class)
public class DruidStarterConfigTest {

    @Resource
    private DataSource dataSource;

    @Test
    public void test() throws SQLException {
        DruidDataSource druidDataSource = (DruidDataSource) dataSource;
        System.out.println(druidDataSource.getDriverClassName());
        System.out.println(druidDataSource.getUrl());
        System.out.println(druidDataSource.getUsername());
        System.out.println(druidDataSource.getPassword());
    }
}

Operation results:

(2) Custom Druid connection pool

Introduce the data source in pom.xml and configure it in application.yaml:

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.5</version>
</dependency>

Without Druid initiator, we need to inject a druid data source manually. Here @ ConfigurationProperties is used to inject the properties configured in application.yaml.

@Configuration
public class DruidStarterConfig {
    @Bean
    @ConfigurationProperties("spring.datasource")
    public DataSource druidDataSource() {
        return new DruidDataSource();
    }
}

Or use the test case above to see the results:

@SpringBootTest
@RunWith(SpringRunner.class)
public class DruidStarterConfigTest {

    @Resource
    private DataSource dataSource;

    @Test
    public void test() throws SQLException {
        DruidDataSource druidDataSource = (DruidDataSource) dataSource;
        System.out.println(druidDataSource.getDriverClassName());
        System.out.println(druidDataSource.getUrl());
        System.out.println(druidDataSource.getUsername());
        System.out.println(druidDataSource.getPassword());
    }
}

4. Druid connection pool properties

In addition to the properties configured above, Druid also provides many connection pool properties to better manage and monitor the connection pool:
https://github.com/alibaba/dr...

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    username: root
    password: root
    #druid connection pool configuration
    druid:
      #Number of physical connections established during initialization
      initial-size: 5
      #Minimum number of connection pools
      min-idle: 5
      #The maximum number of connection pools maxIdle is no longer used
      max-active: 20
      #Maximum wait time to get a connection, in milliseconds
      max-wait: 60000
      #Check when applying for a connection. If the idle time is greater than timebetween evictionrunsmillis, run validationQuery to check whether the connection is valid.
      test-while-idle: true
      #It is used not only as the detection interval, but also as the basis for the execution of testwhiteidel
      time-between-eviction-runs-millis: 60000
      #When destroying a thread, detect that the difference between the last activity time and the current time of the current connection is greater than this value, and close the current connection
      min-evictable-idle-time-millis: 30000
      #The sql used to check whether the connection is valid must be a query statement
      #select 'x' in mysql
      #select 1 from dual in oracle
      validation-query: select 'x'
      #When applying for a connection, validationQuery will be executed to check whether the connection is valid. Opening it will reduce performance. The default value is true
      test-on-borrow: false
      #When returning a connection, validationQuery will be executed to check whether the connection is valid. Opening it will reduce performance. The default value is true
      test-on-return: false
      #When the database throws an unrecoverable exception, the connection is discarded
      exception-sorter: true
      #Whether to cache Preparedstatement. Mysql5.5 + is recommended to be enabled
      pool-prepared-statements: true
      #When the value is greater than 0, poolPreparedStatements is automatically modified to true
      max-pool-prepared-statement-per-connection-size: 20

Due to space constraints, other functions in Druid will not be covered in this paper, and will be introduced later.

Reference acknowledgment

Data source: https://blog.csdn.net/dragon9...
Data source and connection pool: https://www.cnblogs.com/chens...
Use of Druid data sources: https://juejin.cn/post/699320...
Use of Druid launcher: https://juejin.cn/post/699355...

Tags: Druid Spring Boot

Posted on Tue, 30 Nov 2021 17:05:41 -0500 by ray-solomon