In the previous section, we introduced HikariCP, the default data source used by Spring Boot for automated configuration in the JDBC module. In the next section, we’ll look at another widely used open source data source: Druid.

Druid is an open source project produced by Alibaba’s Database division. It is not only a high-performance database connection pool, but also a database connection pool with its own monitoring. While HikariCP is already great, Druid may be more familiar to domestic users. So, how to use Druid in Spring Boot is a basic skill that back-end developers must master.

Configure the Druid data source

This section will be based on code from the Spring Boot 2.x Basics tutorial: Accessing the MySQL database using JdbcTemplate. Therefore, the reader can check out the Chapter3-1 directory from the code repository at the end of this article for practical learning.

Let’s start by configuring the Druid data source for our Spring Boot project:

Step 1: Introduce the Druid’s official Spring Boot Starter package in POM.xml.

< the dependency > < groupId > com. Alibaba < / groupId > < artifactId > druid - spring - the boot - starter < / artifactId > < version > 1.1.21 < / version >  </dependency>Copy the code

Step 2: Configure the database connection information in application.properties.

Druid = spring.datasource. Druid = spring.datasource. Druid = spring.datasource. Druid = spring.datasource. Druid = spring.datasource.

spring.datasource.druid.url=jdbc:mysql://localhost:3306/test
spring.datasource.druid.username=root
spring.datasource.druid.password=
spring.datasource.druid.driver-class-name=com.mysql.cj.jdbc.DriverCopy the code

Step 3: Configure the Druid connection pool.

As with Hikari, to make good use of a data source, you need to configure its connection pool as follows:

spring.datasource.druid.initialSize=10
spring.datasource.druid.maxActive=20
spring.datasource.druid.maxWait=60000
spring.datasource.druid.minIdle=1
spring.datasource.druid.timeBetweenEvictionRunsMillis=60000
spring.datasource.druid.minEvictableIdleTimeMillis=300000
spring.datasource.druid.testWhileIdle=true
spring.datasource.druid.testOnBorrow=true
spring.datasource.druid.testOnReturn=false
spring.datasource.druid.poolPreparedStatements=true
spring.datasource.druid.maxOpenPreparedStatements=20
spring.datasource.druid.validationQuery=SELECT 1
spring.datasource.druid.validation-query-timeout=500
spring.datasource.druid.filters=statCopy the code

See the table below for a description of the various connection pool configurations in Druid.

configuration The default value instructions
name The significance of this attribute is that if multiple data sources exist, they can be distinguished by name when monitoring. If not, a name is generated in the format: “DataSource-” + system.identityHashCode (this). In addition, setting this property does not work at least in version 1.0.5, forcing name will cause an error.For details – click here.
url The URL used to connect to a database varies from database to database. For example: mysql: JDBC: mysql: / / 10.20.153.104:3306 / druid2 oracle: JDBC: oracle: thin: @ 10.20.149.85:1521: ocnauto
username User name for connecting to the database
password Password for connecting to the database. If you don’t want your password written directly to the configuration file, use ConfigFilter.Look at this in detail
driverClassName Automatic identification by URL Druid automatically identifies dbType by URL and selects the appropriate driverClassName
initialSize 0 Number of physical connections established during initialization. Initialization occurs when the display calls the init method, or the first getConnection
maxActive 8 Maximum number of connection pools
maxIdle 8 It’s no longer in use. It doesn’t work when configured
minIdle Minimum number of connection pools
maxWait Gets the maximum wait time for a connection, in milliseconds. After maxWait is configured, fair lock is enabled by default, reducing the concurrency efficiency. If necessary, you can set useUnfairLock to true to use an unfair lock.
poolPreparedStatements false Whether to cache preparedStatement, which is PSCache. PSCache provides a huge performance boost for databases that support cursors, such as Oracle. You are advised to disable this function in mysql.
maxPoolPreparedStatementPerConnectionSize – 1 To enable PSCache, the configuration must be greater than 0, when greater than 0, poolPreparedStatements automatically triggers a change to true. In Druid, there is no problem with Oracle PSCache taking up too much memory. You can set this value to a higher value, such as 100
validationQuery SQL to check whether the connection is valid, the requirement is a query statement, usually select ‘x’. If validationQuery is null, testOnBorrow, testOnReturn, and testWhileIdle will not take effect.
validationQueryTimeout Unit: second: timeout period for checking whether the connection is valid. The underlying void setQueryTimeout(int seconds) method of the JDBC Statement object is called
testOnBorrow true When applying for a connection, execute validationQuery to check whether the connection is valid. This configuration degrades performance.
testOnReturn false ValidationQuery is executed when the connection is returned to verify that the connection is valid. This configuration degrades performance.
testWhileIdle false You are advised to set this parameter to true to ensure performance and security. Apply for connection, if free time is more than timeBetweenEvictionRunsMillis, performing validationQuery test connection is valid.
keepAlive False (1.0.28) Links within minIdle number of connection pool, free time more than minEvictableIdleTimeMillis, will perform the keepAlive operations.
timeBetweenEvictionRunsMillis 1 minute (1.0.14) Has two meanings: 1) the Destroy thread will detect the connection time interval, if the connection is idle time is greater than or equal to minEvictableIdleTimeMillis close physical connection. 2) testWhileIdle determines the value of the whileIDLE attribute
numTestsPerEvictionRun 30 mins (1.0.14) No longer used, a DruidDataSource supports only one EvictionRun
minEvictableIdleTimeMillis Minimum time for a connection to remain idle without being expelled
connectionInitSqls SQL executed when the physical connection is initialized
exceptionSorter Automatic identification by dbType When the database throws some unrecoverable exception, the connection is discarded
filters The attribute type is a string. Extensions are configured using aliases. Common plug-ins include: Filter :stat for monitoring statistics Filter :log4j for defending against SQL injection Filter: Wall
proxyFilters Type is the List < com. Alibaba. Druid. Filter. The filter >, if the configuration of the filters and proxyFilters at the same time, is the combination relations, is not to replace

At this point, you’re done switching HikariCP, the default Spring Boot data source, to Druid.

Configure Druid monitoring

If you use Druid, how can you not use the monitoring function of Druid? Now let’s do some more configuration to enable monitoring for Druid.

Step 1: Import the Spring-boot-starter -actuator module in POM. XML

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-actuator</artifactId>
</dependency>Copy the code

Step 2: Add monitoring configuration for Druid in application.properties.

spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.reset-enable=true
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=adminCopy the code

The preceding configuration is used to enable the STAT monitoring statistics page and configure monitoring content. The meanings are as follows:

  • spring.datasource.druid.stat-view-servlet.url-pattern: Access address rule
  • spring.datasource.druid.stat-view-servlet.reset-enable: Indicates whether statistics can be cleared
  • spring.datasource.druid.stat-view-servlet.login-username: Indicates the account used to log in to the monitoring page
  • spring.datasource.druid.stat-view-servlet.login-password: Password for logging in to the monitoring page

Step 3: For the UserService content we implemented earlier, we create a Controller to invoke the data access operation through the interface:

@Data @AllArgsConstructor @RestController public class UserController { private UserService userService; @PostMapping("/user") public int create(@RequestBody User user) { return userService.create(user.getName(), user.getAge()); } @GetMapping("/user/{name}") public List<User> getByName(@PathVariable String name) { return userService.getByName(name); } @DeleteMapping("/user/{name}") public int deleteByName(@PathVariable String name) { return userService.deleteByName(name); } @GetMapping("/user/count") public int getAllUsers() { return userService.getAllUsers(); } @DeleteMapping("/user/all") public int deleteAllUsers() { return userService.deleteAllUsers(); }}Copy the code

Step 4: complete all of the above configuration, start the application, access to the Druid monitoring page, http://localhost:8080/druid/, you can see the following login page:

Input on the spring. The datasource. The druid. Stat – view – servlet. The login username and spring. The datasource. The druid. Stat – view – servlet. Login password configuration. – After logging in to your account and password, you can see the following monitoring page:

When you enter this side, you can see all kinds of monitoring data for the application side. Here are some of the most commonly used monitoring pages:

Data source: Here you can see the database connection pool information we configured earlier and various indicators of current usage.

SQL monitoring: SQL statements and statistics executed in this data source. On this page, we can easily see the current Spring Boot has executed the SQL, the execution frequency and efficiency of these SQL can be clearly seen. If you don’t see any data here? Remember that we created a Controller earlier and used these interfaces to trigger UserService operations on the database. So, here we can invoke the interface to trigger some operations, so that the SQL monitor page will produce some data:

In terms of monitoring items in the figure, execution time, number of rows read and number of rows updated are all expressed in the form of interval distribution, and the time is distributed into 8 intervals:

  • 0-1 Indicates the number of times that the elapsed time ranges from 0 to 1 ms
  • 1-10 Times between 1 and 10 milliseconds
  • 10-100 Times between 10 and 100 milliseconds
  • 100-1,000 Times between 100 and 1000 milliseconds
  • 1,000-10,000 Times of taking 1 to 10 seconds
  • Times that take 10 to 100 seconds
  • 100,000-1,000,000 times between 100 and 1000 seconds
  • 1,000,000 – The number of times it took more than 1000 seconds

Log the number of times the elapsed interval has occurred, and by differentiating the distribution, it is easy to see the distribution of excellent, mediocre, and extreme SQL running. Time-sensitive distribution provides the “Distribution when executing +RS”, which combines the execution time and the ResultSet holding time for monitoring. This facilitates the diagnosis of queries that return too many rows.

SQL firewall: This page records the monitoring data of different dimensions from SQL monitoring. It is more used for the statistics of table access dimension and SQL defense dimension.

The function of data records statistics need in spring. The datasource. The druid. Would like to add wall filters attribute record statistics, such as this:

spring.datasource.druid.filters=stat,wallCopy the code

Note: all monitoring information here is for the data source of the application instance, not for the global database level. It can be regarded as application layer monitoring, not middleware layer monitoring.

Code sample

For an example of this article, see the chapter3-3 directory in the repository below:

  • Github:github.com/dyc87112/Sp…
  • Gitee:gitee.com/didispace/S…

If you found this article good, welcomeStarSupport, your attention is my motivation!

Welcome to pay attention to my official account: Program ape DD, for exclusive learning resources and daily dry goods push.


If you are interested in my topics, you can also follow my blog:
didispace.com