preface
If you’re new to Spring Boot, check out my Spring Boot series: click here
The development environment
-
The idea of 2019.1
-
maven
-
Spring Boot 2.1.5
-
JDK 1.8
-
Win 10
.
Mainly depends on
Mysql > create a Spring Boot project automatically using IDEA. We will not go into details here, but we need to note that we need to check Mysql driver:
If you need other dependencies, you can check them by yourself.
Complete POM file:
<?xml version="1.0" encoding="UTF-8"? >
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.5. RELEASE</version>
<relativePath/> <! -- lookup parent from repository -->
</parent>
<groupId>com.carson</groupId>
<artifactId>spring-boot-06-data-jdbc</artifactId>
<version>0.0.1 - the SNAPSHOT</version>
<name>spring-boot-06-data-jdbc</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.16</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
Copy the codeAdd the druid connection pool to the druid connection pool and add the log4j dependency
Start the configuration
Then I chose the yML suffix for my config file:
spring:
datasource:
password: root
username: root
url: jdbc:mysql://localhost:3306/ssm? serverTimezone=UTC
driver-class-name: com.mysql.cj.jdbc.Driver
# specify the connection pool type
type: com.alibaba.druid.pool.DruidDataSource
# ------------ split line ---------------------------
# Do not add the following to your profile yet, because it will not work
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
# Configure the filters for monitoring statistics interception. After removing the filters, the MONITORING interface SQL cannot be counted. 'wall' is used for the firewall
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=500
Copy the codeConfiguration from below the dividing line will not take effect, so we will need special configuration later,
But let’s test if our connection pool is Druid connection pool,
Open the test class under our test package and I’ll put my complete code here:
package com.carson.springboot;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.lang.model.element.VariableElement;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringBoot06DataJdbcApplicationTests {
@Autowired
DataSource dataSource;
@Test
public void contextLoads(a) throws SQLException { System.out.println(dataSource.getClass()); Connection connection = dataSource.getConnection(); System.out.println(connection); connection.close(); }}Copy the codeRun this test and you should see the connection pool type output from the console:
Just the type of connection pool we need
Remember those configurations that didn’t work? Now let’s set it up;
First create a config config class:
package com.carson.springboot.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druid(a) {
return newDruidDataSource(); }}Copy the code@ConfigurationProperties: indicates that the prefix configuration takes effect
Then make a breakpoint on the test class and debug it:
Results:
You can see that the property is correct
Configure Druid monitoring
1) Configure a Servlet to manage the background
Using the same DruidConfig config class, let’s add the following methods:
@Bean
public ServletRegistrationBean statViewServlet(a) {
ServletRegistrationBean bean = new ServletRegistrationBean (new StatViewServlet(), "/druid/*");
Map<String, String> initParams = new HashMap<>();
/ / account,
initParams.put("loginUsername"."admin");
/ / password,
initParams.put("loginPassword"."123456");
// Allow login IP (empty means all allowed)
initParams.put("allow"."");
// Then there is the disallowed IP address
initParams.put("deny"."192.123.11.11");
// Set the initialization parameters
bean.setInitParameters(initParams);
return bean;
}
Copy the codeWhere do these parameters come from? Here it is:
2) Configure a filter for monitoring
// 2) Configure a filter for monitoring
@Bean
public FilterRegistrationBean webStatFilter(a) {
FilterRegistrationBean bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());
Map<String, String> initParams = new HashMap<>();
// Do not intercept those attributes
initParams.put("exclusions"."*.js,*.css,/druid/*");
// Set the initialization parameters
bean.setInitParameters(initParams);
// Intercepts all by default
bean.setUrlPatterns(Arrays.asList("/ *"));
return bean;
}
Copy the codeSet up this, we can start the Spring main class of the Boot, and then access the Druid (Druid) monitor: http://localhost:8080/druid
This path is druid’s default path, and you’ll see a login page:
The passwords are admin and 123456, which we just set
View the effect:
To see the following effect of our SQL monitoring, let’s write a controller:
package com.carson.springboot.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.List;
import java.util.Map;
@Controller
public class HelloController {
@Autowired
JdbcTemplate jdbcTemplate;
@ResponseBody
@GetMapping
public Map<String, Object> map(a) {
List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from user ");
return list.get(0); }}Copy the codeAnd send a query request to the page:
Then look at the SQL monitor:
END~~