Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "Spring Boot monitoring SQL operation case analysis". The editor shows you the operation process through the actual case. The operation method is simple, fast and practical. I hope this "Spring Boot monitoring SQL operation case analysis" article can help you solve the problem.

1. Preparatory work

First, let's create a Spring Boot project and introduce MyBatis, etc., as follows:

Choose the MyBatis and MySQL drivers and do a simple test case.

Let's first connect to the database:

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

Create a User entity class and do a simple query case, as follows:

Public class User {private Integer id; private String username; private String address; private String password; private String email; / / omit getter/setter} @ Mapperpublic interface UserMapper {List getUserByUsername (String username);} @ Servicepublic class UserService {@ Autowired UserMapper userMapper; public List getUserByUsername (String username) {return userMapper.getUserByUsername (username);}} @ RestControllerpublic class UserController {@ Autowired UserService userService @ GetMapping ("/ user") public List getUser (String username) {return userService.getUserByUsername (username);}}

UserMapper.xml is as follows:

Select * from user where username=# {username}

A very simple test, there is nothing to say.

If you already have a case of persistence, take a look at the introduction of Druid in the second section.

Now the default database connection pool used in this project is HikariDataSource, which is the default database connection pool in Spring Boot. In fact, this is not bad.

two。 Introduction of Druid

Next we introduce Druid:

Com.alibaba druid-spring-boot-starter 1.2.8

Note that the Druid introduced by Spring Boot is the one above, which is more convenient when configuring monitoring in the future.

Next, we configure WebStatFilter,WebStatFilter in application.properties to collect the data of web-jdbc associated monitoring:

# enable WebStatFilterspring.datasource.druid.web-stat-filter.enabled=true# configuration interception rule spring.datasource.druid.web-stat-filter.url-pattern=/*# to eliminate some unnecessary url These URL will not involve SQL query spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*# enable session statistics function spring.datasource.druid.web-stat-filter.session-stat-enable=true# default sessionStatMaxCount is 1000, we can configure spring.datasource.druid.web-stat-filter.session-stat-max-count=1000# configuration principalSessionName as needed Enables druid to know who the current session user is # according to the need, the value of this parameter is that the user information is stored in the sessionName#spring.datasource.druid.web-stat-filter.principal-session-name=# in session. The function of this configuration is similar to that of the above configuration. This is used to identify the user # spring.datasource.druid.web-stat-filter.principal-cookie-name=# through Cookie. When profile is enabled, you can monitor a single URL address call list # spring.datasource.druid.web-stat-filter.profile-enable=.

We can configure the first five, the last three can not be configured, the meaning of each configuration has been listed in the code.

Next, enable the configuration of StatViewServlet, as follows:

# enable the built-in monitoring page spring.datasource.druid.stat-view-servlet.enabled=true# address spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*# of the built-in monitoring page enable Reset All function spring.datasource.druid.stat-view-servlet.reset-enable=true# set login user name spring.datasource.druid.stat-view-servlet.login-username=javaboy# set login password spring.datasource.druid.stat-view-servlet .login-password=123# whitelist (if allow is not configured or empty Then all access is allowed) spring.datasource.druid.stat-view-servlet.allow=127.0.0.1# blacklist (deny takes precedence over allow, if in the deny list, even in the allow list, will be denied) spring.datasource.druid.stat-view-servlet.deny=

Configure the page address and configure the blacklist and whitelist.

It is important to note that the reset button will be displayed even if the reset-enable property is set to false, but clicking the button will not reset it.

All right, that's it.

3. test

All right, let's start the Spring Boot project to test it.

After the Spring Boot project starts successfully, first visit the following address:

Http://localhost:8080/druid/login.html

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

Enter the user name / password (javaboy/123) we configured earlier to log in. After the login is successful, you can see the following page:

From the title bar, you can see that data sources, SQL monitoring, SQL firewall and other functions are available.

Next, we visit the http://localhost:8080/user?username=aaa address and execute a SQL. After the execution is completed, let's look at the SQL monitoring:

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 one by one. If friends think the data shown here is not intuitive and want to draw their own HTML page, it is also OK. Click on the JSON API at the back, you can see the JSON address of each monitoring item, and you can display it as you want with JSON.

4. Go to advertising

If you want to use this monitoring page directly, it has Ali's advertisement on it, as shown in the following picture, which is particularly awkward for the company to use:

We may want to get rid of this ad, which is also easy.

First of all, after analysis, we find that the advertisement is built from a file called common.js, which is located in druidmai 1.2.8.jar directory support httphand resources.js. There are several lines in the common.js file:

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

This is roughly the logic above. The buildFooter method is responsible for building the advertisement at the end of the page and completing the call to the buildFooter method in the init method.

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

So our idea of going to advertising is also very simple, write a filter, intercept the request for common.js, and then make a little modification, as follows:

@ 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 intercepts / druid/js/common.js requests. After intercepting the request, read the common.js file in the file yourself, and then manually replace the this.buildFooter (); this sentence is fine, and then write out the file.

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

@ SpringBootApplication@ServletComponentScan ("org.javaboy.druid_monitor.filter") public class DruidMonitorApplication {public static void main (String [] args) {SpringApplication.run (DruidMonitorApplication.class, args);}} that's all for "SpringBoot Monitoring SQL instance Analysis". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.