Spring Boot monitors SQL runs through Druid data sources

Today I want to talk to you about the monitoring function in Druid.

Druid database connection pool believe that many people have used, I feel that Druid is a more successful open source project Ali, unlike Fastjson has so many problems. druid has been better in all aspects, full-featured, easy to use, the basic usage will not say, today we look at the monitoring function in Druid.

1. Preparation

First let’s create a Spring Boot project. Add dependencies such as MyBatis as follows.

Choose MyBatis and MySQL driver to make a simple test case.

First, let’s connect to the database.

1
2
3

spring.datasource.username=root
spring.datasource.password=123
spring.datasource.url=jdbc:mysql:///test05?serverTimezone=Asia/Shanghai

Create a User entity class and make a simple query case as follows.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

public class User {
    private Integer id;
    private String username;
    private String address;
    private String password;
    private String email;
    //省略 getter/setter
}
@Mapper
public interface UserMapper {
    List<User> getUserByUsername(String username);
}
@Service
public class UserService {
    @Autowired
    UserMapper userMapper;
    public List<User> getUserByUsername(String username){
        return userMapper.getUserByUsername(username);
    }
}
@RestController
public class UserController {
    @Autowired
    UserService userService;

    @GetMapping("/user")
    public List<User> getUser(String username) {
        return userService.getUserByUsername(username);
    }
}

UserMapper.xml is as follows.

1
2
3
4
5
6
7
8

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.javaboy.druid_monitor.mapper.UserMapper">
    <select id="getUserByUsername" resultType="org.javaboy.druid_monitor.model.User">
        select * from user where username=#{username}
    </select>
</mapper>

A very simple test, not much to say.

Now the default database connection pool used by this project is HikariDataSource, which is the default database connection pool in Spring Boot, and it’s actually not bad.

2. Using Druid

Next we import Druid’s dependencies:

1
2
3
4
5
6

<!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.8</version>
</dependency>

It’s easier to use the official starter directly.

Next, we configure WebStatFilter in application.properties, which is used to collect data from web-jdbc association monitoring.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

# 启用 WebStatFilter
spring.datasource.druid.web-stat-filter.enabled=true
# 配置拦截规则
spring.datasource.druid.web-stat-filter.url-pattern=/*
# 排除一些不必要的 url，这些 URL 不会涉及到 SQL 查询
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
# 开启 session 统计功能
spring.datasource.druid.web-stat-filter.session-stat-enable=true
# 缺省 sessionStatMaxCount 是 1000 个，我们可以按需要进行配置
spring.datasource.druid.web-stat-filter.session-stat-max-count=1000
# 配置 principalSessionName，使得 druid 能够知道当前的 session 的用户是谁
# 根据需要，这个参数的值是 user 信息保存在 session 中的 sessionName
#spring.datasource.druid.web-stat-filter.principal-session-name=
# 下面这个配置的作用和上面配置的作用类似，这个是通过 Cookie 来识别用户
#spring.datasource.druid.web-stat-filter.principal-cookie-name=
# 开启 profile 后就能够监控单个 URL 地址调用列表
#spring.datasource.druid.web-stat-filter.profile-enable=

We configure the first five on it, and the last three can be configured without. The meaning of each configuration is already listed in the code.

Next, open the StatViewServlet configuration, as follows.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 启用内置的监控页面
spring.datasource.druid.stat-view-servlet.enabled=true
# 内置监控页面的地址
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
# 开启 Reset All 功能
spring.datasource.druid.stat-view-servlet.reset-enable=true
# 设置登录用户名
spring.datasource.druid.stat-view-servlet.login-username=javaboy
# 设置登录密码
spring.datasource.druid.stat-view-servlet.login-password=123
# 白名单（如果allow没有配置或者为空，则允许所有访问）
spring.datasource.druid.stat-view-servlet.allow=127.0.0.1
# 黑名单（deny 优先于 allow，如果在 deny 列表中，就算在 allow 列表中，也会被拒绝）
spring.datasource.druid.stat-view-servlet.deny=

Just configure the page address and configure the black and white list.

Note that the reset-enable property will be displayed even if it is set to false, the reset button will just not be reset by clicking it.

Okay, that’s it.

3. Testing

Next, we start the Spring Boot project for testing.

After the Spring Boot project starts successfully, first visit the following link.

• http://localhost:8080/druid/login.html

At this point we will see the login authentication page as follows.

Enter the username/password we configured earlier (javaboy/123) to log in, and after successful login, you can see the following page.

As you can see from the title bar, data source, SQL monitoring, SQL firewall and other functions are all available.

Next, we visit http://localhost:8080/user?username=aaa address and execute a SQL, after the execution is finished, let’s check the SQL monitoring.

As you can see from the title bar, data source, SQL monitoring, SQL firewall and other functions are all available.

Next, we request http://localhost:8080/user?username=aaa, which executes a SQL. After the execution, let’s check the SQL monitor.

As you can see, there is a monitoring record of SQL execution at this time.

Other monitoring data can also be seen, so I won’t list them all. If you think the data shown here is not intuitive and you want to draw your own HTML page, that is also possible. You can see the JSON rest Api for each monitoring item by clicking the JSON API at the end, and you can customize the view with these rest api.

4. remove ads

If you want to use this monitoring page directly, this one has Ali’s ads on it, as shown below. If this is the company’s monitoring backend, the ads will look awkward.

We might want to remove the ad, which is also easy.

First, after analyzing it, we found that the ad is built from a file called common.js, which is located in druid-x.x.x.jar!/support/http/resources/js/common.js Here, the common.js file has the following lines.

1
2
3
4
5
6
7
8

init : function() {
 this.buildFooter();
 druid.lang.init();
},
buildFooter : function() {
 var html ='';
 $(document.body).append(html);
},

The buildFooter method is responsible for building the ads at the end of the page, and the buildFooter method is called in the init method.

So if you want to remove the ads, just don’t call the buildFooter method.

So our idea of removing ads is simple: write a filter to intercept requests to common.js, and then make a little modification, as follows.

1
2
3
4
5
6
7
8
9

@WebFilter(urlPatterns = "/druid/js/common.js")
public class RemoveAdFilter implements Filter {
    @Override
    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
        String text = Utils.readFromResource("support/http/resources/js/common.js");
        text = text.replace("this.buildFooter();", "");
        servletResponse.getWriter().write(text);
    }
}

As you can see, this filter is to intercept /druid/js/common.js requests, after intercepting, go to the file to read the common.js file, and then manually replace the this.buildFooter(); sentence on the line, and finally write the file out on the line.

Of course, remember to scan Filter in the startup class, as follows.

1
2
3
4
5
6
7
8
9

@SpringBootApplication
@ServletComponentScan("org.javaboy.druid_monitor.filter")
public class DruidMonitorApplication {

    public static void main(String[] args) {
        SpringApplication.run(DruidMonitorApplication.class, args);
    }

}

Reference https://mp.weixin.qq.com/s/5rsknrXgizcaUEs7yufUBQ