Spring Boot学习(十五)之使用Druid连接池以及SQL监控和spring监控

╰+攻爆jí腚メ 2022-05-21 11:43 185阅读 0赞

一、引入maven依赖包

<?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>
    
    	<groupId>com.cicoding.druid</groupId>
    	<artifactId>springboot_druid</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    	<packaging>war</packaging>
    
    	<name>springboot_druid</name>
    	<description>Demo project for Spring Boot</description>
    
    	<parent>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-parent</artifactId>
    		<version>1.5.9.RELEASE</version>
    		<relativePath /> 
    	</parent>
    
    	<properties>
    		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    		<java.version>1.8</java.version>
    	</properties>
    
    	<dependencies>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-data-jpa</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-devtools</artifactId>
    			<scope>runtime</scope>
    		</dependency>
    	
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<scope>runtime</scope>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-tomcat</artifactId>
    			<scope>provided</scope>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-test</artifactId>
    			<scope>test</scope>
    		</dependency>
    
    		<dependency>
    			<groupId>javax.servlet</groupId>
    			<artifactId>javax.servlet-api</artifactId>
    			<scope>provided</scope>
    		</dependency>
    
    		<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>druid</artifactId>
    			<version>1.0.18</version>
    		</dependency>
    		<dependency>
    			<groupId>org.mybatis.spring.boot</groupId>
    			<artifactId>mybatis-spring-boot-starter</artifactId>
    			<version>1.2.2</version>
    		</dependency>
    	</dependencies>
    
    	<build>
    		<plugins>
    			<plugin>
    				<groupId>org.springframework.boot</groupId>
    				<artifactId>spring-boot-maven-plugin</artifactId>
    			</plugin>
    		</plugins>
    	</build>
    </project>

二、在application.properties配置文件中添加druid配置

##########################  mysql   ##########################
    spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8
    spring.datasource.username=root
    spring.datasource.password=root
    spring.datasource.driver-class-name=com.mysql.jdbc.Driver
    
    logging.level.com.cicoding.mapper=debug
    
    
    ##########################  mybatis   ##########################
    mybatis.mapper-locations=classpath:mybatis/*.xml
    
    ##########################  druid配置   ##########################
    spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
    
    # 初始化大小，最小，最大  
    spring.datasource.initialSize=5
    spring.datasource.minIdle=5
    spring.datasource.maxActive=20
    # 配置获取连接等待超时的时间  
    spring.datasource.maxWait=60000
    # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒  
    spring.datasource.timeBetweenEvictionRunsMillis=60000
    # 配置一个连接在池中最小生存的时间，单位是毫秒  
    spring.datasource.minEvictableIdleTimeMillis=300000
    # 校验SQL，Oracle配置 spring.datasource.validationQuery=SELECT 1 FROM DUAL，如果不配validationQuery项，则下面三项配置无用  
    spring.datasource.validationQuery=SELECT 'x'
    spring.datasource.testWhileIdle=true
    spring.datasource.testOnBorrow=false
    spring.datasource.testOnReturn=false
    # 打开PSCache，并且指定每个连接上PSCache的大小  
    spring.datasource.poolPreparedStatements=true
    spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙  
    spring.datasource.filters=stat,wall,log4j
    # 通过connectProperties属性来打开mergeSql功能；慢SQL记录  
    spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # 合并多个DruidDataSource的监控数据  
    spring.datasource.useGlobalDataSourceStat=true

三、配置druid连接池的webServlet

package com.cicoding.servlet;
    
    import javax.servlet.annotation.WebInitParam;
    import javax.servlet.annotation.WebServlet;
    
    import com.alibaba.druid.support.http.StatViewServlet;
    @WebServlet(urlPatterns = "/druid/*", 
        initParams={
                @WebInitParam(name="loginUsername",value="admin"),// 用户名
                @WebInitParam(name="loginPassword",value="admin"),// 密码
                @WebInitParam(name="resetEnable",value="false")// 禁用HTML页面上的“Reset All”功能
        })
    public class DruidServlet extends StatViewServlet {
    
    	private static final long serialVersionUID = 1L;
    }

四、配置Druid连接池webFitler

package com.cicoding.filter;
    
    import javax.servlet.annotation.WebFilter;
    import javax.servlet.annotation.WebInitParam;
    
    import com.alibaba.druid.support.http.WebStatFilter;
    
    @WebFilter(filterName="druidWebStatFilter",urlPatterns="/*",
    initParams={
        @WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")// 忽略资源
    })
    public class DruidFilter extends WebStatFilter {
    
    }

五、扫描filter和servlet

package com.cicoding;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.boot.web.servlet.ServletComponentScan;
    import org.springframework.context.annotation.ImportResource;
    
    @SpringBootApplication
    @ServletComponentScan
    @ImportResource(locations = { "classpath:druid-bean.xml" })
    public class DruidApplication {
    
    	public static void main(String[] args) {
    		SpringApplication.run(DruidApplication.class, args);
    	}
    }

六、不使用spring boot自动配置功能，手动初始化DataSource

描如果不使用代码手动初始化DataSource的话，监控界面的SQL监控会没有数据("是spring boot的bug??")

package com.cicoding.configuration;
    
    import java.sql.SQLException;
    
    import javax.sql.DataSource;
    
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    
    import com.alibaba.druid.pool.DruidDataSource;
    
    @Configuration  
    public class DruidConfigration {  
        @Value("${spring.datasource.url}")  
        private String dbUrl;  
        @Value("${spring.datasource.username}")  
        private String username;  
        @Value("${spring.datasource.password}")  
        private String password;  
        @Value("${spring.datasource.driver-class-name}")  
        private String driverClassName;  
        @Value("${spring.datasource.initialSize}")  
        private int initialSize;  
        @Value("${spring.datasource.minIdle}")  
        private int minIdle;  
        @Value("${spring.datasource.maxActive}")  
        private int maxActive;  
        @Value("${spring.datasource.maxWait}")  
        private int maxWait;  
        @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")  
        private int timeBetweenEvictionRunsMillis;  
        @Value("${spring.datasource.minEvictableIdleTimeMillis}")  
        private int minEvictableIdleTimeMillis;  
        @Value("${spring.datasource.validationQuery}")  
        private String validationQuery;  
        @Value("${spring.datasource.testWhileIdle}")  
        private boolean testWhileIdle;  
        @Value("${spring.datasource.testOnBorrow}")  
        private boolean testOnBorrow;  
        @Value("${spring.datasource.testOnReturn}")  
        private boolean testOnReturn;  
        @Value("${spring.datasource.poolPreparedStatements}")  
        private boolean poolPreparedStatements;  
        @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")  
        private int maxPoolPreparedStatementPerConnectionSize;  
        @Value("${spring.datasource.filters}")  
        private String filters;  
        @Value("${spring.datasource.connectionProperties}")  
        private String connectionProperties;  
        @Value("${spring.datasource.useGlobalDataSourceStat}")  
        private boolean useGlobalDataSourceStat;  
      
        @Bean     //声明其为Bean实例  
        @Primary  //在同样的DataSource中，首先使用被标注的DataSource  
        public DataSource dataSource(){  
            DruidDataSource datasource = new DruidDataSource();  
            datasource.setUrl(this.dbUrl);  
            datasource.setUsername(username);  
            datasource.setPassword(password);  
            datasource.setDriverClassName(driverClassName);  
      
            //configuration  
            datasource.setInitialSize(initialSize);  
            datasource.setMinIdle(minIdle);  
            datasource.setMaxActive(maxActive);  
            datasource.setMaxWait(maxWait);  
            datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);  
            datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);  
            datasource.setValidationQuery(validationQuery);  
            datasource.setTestWhileIdle(testWhileIdle);  
            datasource.setTestOnBorrow(testOnBorrow);  
            datasource.setTestOnReturn(testOnReturn);  
            datasource.setPoolPreparedStatements(poolPreparedStatements);  
            datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);  
            datasource.setUseGlobalDataSourceStat(useGlobalDataSourceStat);  
            try {  
                datasource.setFilters(filters);  
            } catch (SQLException e) {  
                System.err.println("druid configuration initialization filter: "+ e);  
            }  
            datasource.setConnectionProperties(connectionProperties);  
            return datasource;  
        }  
    }

八、spring监控之方法名正则匹配拦截配置druid-bean.xml

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
    	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xsi:schemaLocation="
            http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans.xsd
    		http://www.springframework.org/schema/aop
    		http://www.springframework.org/schema/aop/spring-aop.xsd">
    
    	
    	<bean id="druid-stat-interceptor"
    		class="com.alibaba.druid.support.spring.stat.DruidStatInterceptor"></bean>
    
    	
    	<bean id="druid-stat-pointcut" class="org.springframework.aop.support.JdkRegexpMethodPointcut"
    		scope="prototype">
    		<property name="patterns">
    			<list>
    				<value>com.cicoding.mapper.*</value>
    			</list>
    		</property>
    	</bean>
    
    	<aop:config proxy-target-class="true">
    		<aop:advisor advice-ref="druid-stat-interceptor"
    			pointcut-ref="druid-stat-pointcut" />
    	</aop:config>
    
    </beans>

九、引入配置xml

@SpringBootApplication
    @ServletComponentScan
    @ImportResource(locations = { "classpath:druid-bean.xml" })
    public class DruidApplication {
    
    	public static void main(String[] args) {
    		SpringApplication.run(DruidApplication.class, args);
    	}
    }

十、controller配置

package com.cicoding.controller;
    
    import java.util.List;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.ResponseBody;
    
    import com.cicoding.bean.User;
    import com.cicoding.mapper.UserMapper;
    
    @Controller
    @RequestMapping(value="/user")
    public class UserController {
    
    	@Autowired
    	private UserMapper userMapper;
    	
    	@ResponseBody
    	@RequestMapping(value="/get")
    	public User get() {
    		return userMapper.getOne(1);
    	}
    	
    	@ResponseBody
    	@RequestMapping(value="/list")
    	public List<User> list() {
    		return userMapper.list();
    	}
    }

十一、Mapper的dao接口和mapper xml配置

Mapper接口

package com.cicoding.mapper;
    import java.util.List;
    import org.apache.ibatis.annotations.Mapper;
    import com.cicoding.bean.User;
    @Mapper
    public interface UserMapper{
    	List<User> list();
    	User getOne(int id);
    }

mapper xml配置

*  因为application.properties配置了mybatis.mapper-locations=classpath:mybatis/\*.xml
 *  放在mybatis目录下扫描

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
      PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.cicoding.mapper.UserMapper">
    
    	<resultMap type="com.cicoding.bean.User" id="userMap">
    		<id column="id" property="id" />
    		<result column="user_name" property="userName" />
    		<result column="pass_word" property="passWord" />
    		<result column="date" property="date" />
    	</resultMap>
    
    	<select id="list" resultMap="userMap">
    		select * from users
    	</select>
    
    	<select id="getOne" parameterType="java.lang.Integer" resultMap="userMap">
    		select * from users where id=#{id}
    	</select>
    </mapper>

十二、测试

![70][]

![70 1][]

![70 2][]

![70 3][]

大功搞成！

[70]: /images/20220521/46b5f12cc50d48c08bd9943e3af2a3b2.png
[70 1]: /images/20220521/9886870e14f644e1a6506a2993b8b63a.png
[70 2]: /images/20220521/6f862cf8a456436b845d8747d300fe04.png
[70 3]: /images/20220521/207ef324efa84187af0f4f5ea4b5d7ed.png