SpringBoot 集成 druid Monitor进行sql监控

喜欢ヅ旅行 2021-11-09 10:06 1437阅读 0赞

今天，日月在这里教大家如何使用springboot集成druid-Monitor进行sql监控、数据源监控，sql慢查询监控等。还是先套用一波网上的官方话语。。。

## Druid ##

Druid：一款为监控而生的数据库连接池框架，整个项目由数据库连接池、插件框架和SQL解析器组成。  
Druid功能介于PowerDrill和Dremel之间，它几乎实现了Dremel的所有功能，并且从PowerDrill吸收一些有趣的数据格式。Druid允许以类似Dremel和PowerDrill的方式进行单表查询，同时还增加了一些新特性，如为局部嵌套数据结构提供列式存储格式、为快速过滤做索引、实时摄取和查询、高容错的分布式体系架构等。

## Spring Boot ##

spring框架作为JavaEE框架领域的一款重要的开源框架，在企业应用开发中有着很重要的作用，同时Spring框架及其子框架很多，所以知识量很广。  
**Spring Boot**：一款Spring框架的子框架，也可以叫微框架，是2014年推出的一款使Spring框架开发变得容易的框架。学过Spring框架的都知识，Spring框架难以避免地需要配置不少XMl，而使用Spring Boot框架的话，就可以使用注解开发，极大地简化基于Spring框架的开发。Spring Boot充分利用了JavaConfig的配置模式以及“约定优于配置”的理念，能够极大的简化基于Spring MVC的Web应用和REST服务开发。

依然，我们还是在上一章项目 [springBoot集成redis][springBoot_redis] 的基础上进行集成

**1、配置中心新增Druid配置**

#master 数据源配置
    master.datasource.url=jdbc:mysql://localhost:3306/test3?useSSL=false&useUnicode=true&characterEncoding=utf8
    master.datasource.username=root
    master.datasource.password=123456
    master.datasource.maxActive=50
    master.datasource.maxWait=60000
    master.datasource.driverClassName=com.mysql.jdbc.Driver
     
    #cluster 数据源配置
    cluster.datasource.url=jdbc:mysql://localhost:3306/test4?useSSL=false&useUnicode=true&characterEncoding=utf8
    cluster.datasource.username=root
    cluster.datasource.password=123456
    cluster.datasource.maxActive=50
    cluster.datasource.maxWait=60000
    cluster.datasource.driverClassName=com.mysql.jdbc.Driver
    
    spring.redis.host=127.0.0.1
    #Redis服务器连接端口
    spring.redis.port=6379
    #Redis服务器连接密码（默认为空）
    spring.redis.password=
    #连接池最大连接数（使用负值表示没有限制）
    spring.redis.pool.max-active=8
    #连接池最大阻塞等待时间（使用负值表示没有限制）
    spring.redis.pool.max-wait=-1
    #连接池中的最大空闲连接
    spring.redis.pool.max-idle=8
    #连接池中的最小空闲连接
    spring.redis.pool.min-idle=0
    #连接超时时间（毫秒）
    spring.redis.timeout=30000
    
    #打开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

**2、新增数据源dataSource的配置**  
**master 数据源**

import java.sql.SQLException;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.cloud.context.config.annotation.RefreshScope;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import com.alibaba.druid.pool.DruidDataSource;
    @RefreshScope
    @Configuration
    // 扫描 Mapper 接口并容器管理
    @MapperScan(basePackages = MasterDataSourceConfiguration.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
    public class MasterDataSourceConfiguration {
        // 精确到 master 目录，以便跟其他数据源隔离
        static final String PACKAGE = "com.chenqi.springboot.dao.master";
        static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";
        @Value("${master.datasource.url}")
        private String url;
        @Value("${master.datasource.username}")
        private String user;
        @Value("${master.datasource.password}")
        private String password;
        
        @Value("${master.datasource.maxActive}")
        private Integer maxActive;
        
        @Value("${master.datasource.maxWait}")
        private Integer maxWait;
        @Value("${master.datasource.driverClassName}")
        private String driverClass;
        
        @Value("${spring.datasource.poolPreparedStatements}")
        private boolean poolPreparedStatements;
        
        @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
        private Integer maxPoolPreparedStatementPerConnectionSize;
        
        @Value("${spring.datasource.filters}")
        private String filters;
        
        @Value("${spring.datasource.connectionProperties}")
        private String connectionProperties;
        @ConfigurationProperties(prefix="spring.datasource")
        @RefreshScope
        @Bean(name = "dataSource")
        @Primary
        public DruidDataSource masterDataSource() {
            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setDriverClassName(driverClass);
            dataSource.setUrl(url);
            dataSource.setUsername(user);
            dataSource.setPassword(password);
            dataSource.setMaxActive(maxActive);
            dataSource.setMaxWait(maxWait);
            dataSource.setSharePreparedStatements(poolPreparedStatements);
            dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
            dataSource.setConnectionProperties(connectionProperties);
            try {
                dataSource.setFilters(filters);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return dataSource;
        }
        @RefreshScope
        @Bean(name = "masterTransactionManager")
        @Primary
        public DataSourceTransactionManager masterTransactionManager() {
            return new DataSourceTransactionManager(masterDataSource());
        }
        @RefreshScope
        @Bean(name = "masterSqlSessionFactory")
        @Primary
        public SqlSessionFactory masterSqlSessionFactory(@Qualifier("dataSource") DruidDataSource masterDataSource)
                throws Exception {
            final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(masterDataSource);
            sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(MasterDataSourceConfiguration.MAPPER_LOCATION));
            return sessionFactory.getObject();
        }
    }

**cluster 数据源**

import java.sql.SQLException;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.cloud.context.config.annotation.RefreshScope;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import com.alibaba.druid.pool.DruidDataSource;
    @RefreshScope
    @Configuration
    //扫描 Mapper 接口并容器管理
    @MapperScan(basePackages = ClusterDataSourceConfiguration.PACKAGE, sqlSessionFactoryRef = "clusterSqlSessionFactory")
    public class ClusterDataSourceConfiguration {
        
        // 精确到 cluster 目录，以便跟其他数据源隔离
        static final String PACKAGE = "com.chenqi.springboot.dao.cluster";
        static final String MAPPER_LOCATION = "classpath:mapper/cluster/*.xml";
        @Value("${cluster.datasource.url}")
        private String url;
        @Value("${cluster.datasource.username}")
        private String user;
        @Value("${cluster.datasource.password}")
        private String password;
        
        @Value("${cluster.datasource.maxActive}")
        private Integer maxActive;
        
        @Value("${cluster.datasource.maxWait}")
        private Integer maxWait;
        @Value("${cluster.datasource.driverClassName}")
        private String driverClass;
        
        @Value("${spring.datasource.poolPreparedStatements}")
        private boolean poolPreparedStatements;
        
        @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
        private Integer maxPoolPreparedStatementPerConnectionSize;
        
        @Value("${spring.datasource.filters}")
        private String filters;
        
        @Value("${spring.datasource.connectionProperties}")
        private String connectionProperties;
        @ConfigurationProperties(prefix="spring.datasource")
        @RefreshScope
        @Bean(name = "clusterDataSource")
        public DruidDataSource clusterDataSource() {
            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setDriverClassName(driverClass);
            dataSource.setUrl(url);
            dataSource.setUsername(user);
            dataSource.setPassword(password);
            dataSource.setMaxActive(maxActive);
            dataSource.setMaxWait(maxWait);
            dataSource.setSharePreparedStatements(poolPreparedStatements);
            dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
            dataSource.setConnectionProperties(connectionProperties);
            try {
                dataSource.setFilters(filters);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return dataSource;
        }
        @RefreshScope
        @Bean(name = "clusterTransactionManager")
        public DataSourceTransactionManager clusterTransactionManager() {
            return new DataSourceTransactionManager(clusterDataSource());
        }
        @RefreshScope
        @Bean(name = "clusterSqlSessionFactory")
        public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("clusterDataSource") DruidDataSource clusterDataSource)
                throws Exception {
            final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(clusterDataSource);
            sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(ClusterDataSourceConfiguration.MAPPER_LOCATION));
            return sessionFactory.getObject();
        }
    }

**3、添加servlet 和 filter**

import javax.servlet.annotation.WebInitParam;
    import javax.servlet.annotation.WebServlet;
    import com.alibaba.druid.support.http.StatViewServlet;
    @SuppressWarnings("serial")
    @WebServlet(urlPatterns = "/druid/*",
            initParams={
                    @WebInitParam(name="allow",value="127.0.0.1"),// IP白名单 (没有配置或者为空，则允许所有访问,可配置多个)
                    //@WebInitParam(name="deny",value="192.168.16.111"),// IP黑名单 (存在共同时，deny优先于allow)
                    @WebInitParam(name="loginUsername",value="admin"),// 用户名
                    @WebInitParam(name="loginPassword",value="123456"),// 密码
                    @WebInitParam(name="resetEnable",value="false")// 禁用HTML页面上的“Reset All”功能
            })
    public class DruidStatViewServlet extends StatViewServlet {
    }

**DruidStatFilter.java**

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

**4、启动类添加servlet扫描**

import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.boot.web.servlet.ServletComponentScan;
    import org.springframework.cloud.client.discovery.EnableDiscoveryClient;
    @EnableDiscoveryClient
    @SpringBootApplication
    @ServletComponentScan(value = "com.chenqi.springboot.config.servlet")//servlet的扫描
    public class SpringbootApplication {
        public static void main(String[] args) {
            SpringApplication.run(SpringbootApplication.class, args);
        }
    }

仅仅只需要这四步，就完成了，OK，我们先将上章reids的代码注释掉，方便测试

import java.util.concurrent.TimeUnit;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.PathVariable;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    import com.chenqi.springboot.redis.RedisUtils;
    import com.chenqi.springboot.service.TestService;
    @RestController
    public class SpringBootController {
        
        public static final Logger log = LoggerFactory.getLogger(SpringBootController.class);
        
        @Autowired
        TestService testService;
        
        @Autowired
        private RedisUtils redisUtils;
        @RequestMapping(value = "/hello/{id}")
        public String hello(@PathVariable(value = "id") String id){
            //查询缓存中是否存在
            boolean hasKey = redisUtils.exists(id);
            String str = "";
            /*if(hasKey){
                //获取缓存
                Object object =  redisUtils.get(id);
                log.info("从缓存获取的数据"+ object);
                str = object.toString();
            }else{
                //从数据库中获取信息
                log.info("从数据库中获取数据");
                str = testService.test();
                //数据插入缓存（set中的参数含义：key值，user对象，缓存存在时间10（long类型），时间单位）
                redisUtils.set(id,str,10L,TimeUnit.MINUTES);
                log.info("数据插入缓存" + str);
            }*/
            
            str = testService.test();
            return str;
        }
    }

依次启动spring-cloud-eureka、spring-cloud-config-server、springboot  
访问：[http://localhost:8002/druid/index.html][http_localhost_8002_druid_index.html]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70]

使用我们在DruidStatViewServlet中配置的用户名密码进行登录

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 1]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 2]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 3]  
这个时候，因为还没有请求，所以数据源和sql监控页面都是空的  
OK，我们发生一个查询请求：[http://localhost:8002/hello/111][http_localhost_8002_hello_111]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 4]

再次刷新druid-Monitor中的数据源窗口和sql监控窗口

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 5]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 6]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 7]

可以发现，我们配置的两个数据源和两条查询sql都已经被监控到了，至此，springboot集成druid-Monitor就完成了

[springBoot_redis]: https://blog.csdn.net/weixin_40623736/article/details/98097708
[http_localhost_8002_druid_index.html]: http://localhost:8002/druid/index.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70]: /images/20211109/925b001eb8234250b4d9b6ec182a6d08.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 1]: /images/20211109/bf726645b8ae409c8341c6a138fcbb2c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 2]: /images/20211109/fc30f5af6f9f4c7a96a3e8d2e1229fc2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 3]: /images/20211109/e997a0dee21643eda7e990363d7f7fb6.png
[http_localhost_8002_hello_111]: http://localhost:8002/hello/111
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 4]: /images/20211109/17dcc16255d9478a8468d47abd74a06f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 5]: /images/20211109/5011c682ed984cf781915111d22efd67.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 6]: /images/20211109/2388f131e01c48568c2c9b0a797eadad.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 7]: /images/20211109/7033113f27004115863d5d75392a0810.png