springboot整合druid数据源并开启druid后台监控

桃扇骨 2023-01-22 06:00 26阅读 0赞

### springboot整合druid数据源并开启druid后台监控 ###

*  1、引入依赖
 *  2、application.yml文件：
 *  3、编写配置文件：
 *   *  1、SprinbBoot1.x版本
     *  2、SpringBoot2.x版本：
 *  4、运行测试
 *   *  4.1 访问:\`http://localhost:8080/druid\`
     *  4.2 输入代码里面写的admin和123456
     *  4.3 写个controller测试:
     *  4.4 访问：\`http://localhost:8080/query\`
     *  4.5 \*\*再次查看druid的SQL监控：(可以看到刚才查询的SQL了)\*\*
     *  ps:数据库表结构：

# 1、引入依赖 #

**pom.xml文件：**

<?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 https://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.4.5</version>
            <relativePath/> 
        </parent>
        <groupId>com.atguigu</groupId>
        <artifactId>springboot-06-data-jdbc</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>springboot-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-data-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>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.1.17</version>
            </dependency>
    
    
            <dependency>
                <groupId>log4j</groupId>
                <artifactId>log4j</artifactId>
                <version>1.2.17</version>
            </dependency>
    
    
    
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

# 2、application.yml文件： #

spring:
      datasource:
        #数据库基本配置
        username: root
        password: 123456
        url: jdbc:mysql://192.168.56.101:3306/jdbc
        driver-class-name: com.mysql.cj.jdbc.Driver
        initialization-mode: always
        type: com.alibaba.druid.pool.DruidDataSource
        #其他配置
        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
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
        filters: stat,wall,log4j
        maxPoolPreparedStatementPerConnectionSize: 20
        useGlobalDataSourceStat: true
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
    # schema:
    # - classpath:department.sql

# 3、编写配置文件： #

## 1、SprinbBoot1.x版本 ##

package com.atguigu.springboot.config;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    import jdk.nashorn.internal.ir.ReturnNode;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.boot.web.servlet.ServletRegistrationBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    import javax.sql.DataSource;
    import java.util.Arrays;
    import java.util.HashMap;
    import java.util.Map;
    
    @Configuration
    public class DruidConfig { 
        @ConfigurationProperties(prefix = "spring.datasource")
        @Bean
        public DataSource druid(){ 
            return new DruidDataSource();
        }
        //配置Druid监控
        /** * 1、配置管理后台的Servlet * 2、配置一个Web监控的filter */
        //配置一个管理后台的Servlet
        @Bean
        public ServletRegistrationBean<StatViewServlet> statViewServlet(){ 
            ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
            Map<String,String> initParams=new HashMap<>();
            //增加配置
            initParams.put("loginUsername","admin");//登录key 固定的loginUsername
            initParams.put("loginPassword","123456");//登录密码 固定loginPassword
            initParams.put("allow",""); //允许谁可以访问，默认都可访问
            initParams.put("deny","172.16.208.208");//禁止谁能访问
            
            bean.setInitParameters(initParams); //初始化参数
            return bean;
        }
        //配置一个Web监控的filter
        @Bean
        public FilterRegistrationBean webStatFilter(){ 
            FilterRegistrationBean bean = new FilterRegistrationBean();
            bean.setFilter(new WebStatFilter());
    
            Map<String,String> initParams = new HashMap<>();
            initParams.put("exclusions","*.css,*.png,*.jpg,*.js,/druid/*");
    
            bean.setInitParameters(initParams);
    
            bean.setUrlPatterns(Arrays.asList("/*"));
            bean.setInitParameters(initParams);
            return  bean;
    
    
        }
        
    }

## 2、SpringBoot2.x版本： ##

package com.atguigu.springboot.config;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.boot.web.servlet.ServletRegistrationBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    import javax.sql.DataSource;
    import java.util.Arrays;
    import java.util.HashMap;
    import java.util.Map;
    
    @Configuration
    public class DruidConfig { 
        @ConfigurationProperties(prefix = "spring.datasource")
        @Bean
        public DataSource druid(){ 
            return new DruidDataSource();
        }
        //配置Druid监控
        /** * 1、配置管理后台的Servlet * 2、配置一个Web监控的filter */
        //配置一个管理后台的Servlet
        @Bean
        public ServletRegistrationBean<StatViewServlet> statViewServlet(){ 
            ServletRegistrationBean<StatViewServlet> servletServletRegistrationBean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
    // Map<String,String> initParams=new HashMap<>();
            //增加配置
    //        initParams.put("loginUsername","admin");//登录key 固定的loginUsername
    //        initParams.put("loginPassword","123456");//登录密码 固定loginPassword
    //        initParams.put("allow",""); //允许谁可以访问，默认都可访问
    //        initParams.put("deny","172.16.208.208");//禁止谁能访问
            
            //bean.setInitParameters(initParams); //初始化参数
            servletServletRegistrationBean.addInitParameter("loginUsername","admin");
            servletServletRegistrationBean.addInitParameter("loginPassword","123456");
            servletServletRegistrationBean.addInitParameter("allow","");
            servletServletRegistrationBean.addInitParameter("deny","172.16.208.208");
            return servletServletRegistrationBean;
        }
        //配置一个Web监控的filter
        @Bean
        public FilterRegistrationBean<WebStatFilter> webStatFilter(){ 
            FilterRegistrationBean<WebStatFilter> bean = new FilterRegistrationBean<>();
            bean.setFilter(new WebStatFilter());
            Map<String,String> initParams = new HashMap<>();
            initParams.put("exclusions","*.css,*.png,*.jpg,*.js,/druid/*");
            bean.setInitParameters(initParams);
            bean.setUrlPatterns(Arrays.asList("/*"));
            bean.setInitParameters(initParams);
            //添加过滤规则
    //        filterRegistrationBean.addUrlPatterns("/*");
            //添加不需要忽略的格式信息
    //        filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
            return  bean;
        }
        
    }

# 4、运行测试 #

## 4.1 访问:`http://localhost:8080/druid` ##

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

## 4.2 输入代码里面写的admin和123456 ##

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

## 4.3 写个controller测试: ##

package com.atguigu.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.RequestMapping;
    import org.springframework.web.bind.annotation.ResponseBody;
    
    import java.util.List;
    import java.util.Map;
    
    @Controller
    public class HelloController { 
        @Autowired
        JdbcTemplate jdbcTemplate;
        @RequestMapping("/query")
        @ResponseBody
        public Map<String,Object> map(){ 
            List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from department");
            return list.get(0);
        }
    }

## 4.4 访问：`http://localhost:8080/query` ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70 3]

## 4.5 **再次查看druid的SQL监控：(可以看到刚才查询的SQL了)** ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70 4]

## ps:数据库表结构： ##

/*
    Navicat MySQL Data Transfer
    
    Source Server         : 本地
    Source Server Version : 50528
    Source Host           : 127.0.0.1:3306
    Source Database       : restful_crud
    
    Target Server Type    : MYSQL
    Target Server Version : 50528
    File Encoding         : 65001
    
    Date: 2018-03-05 10:41:58
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for employee
    -- ----------------------------
    DROP TABLE IF EXISTS `employee`;
    CREATE TABLE `employee` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `lastName` varchar(255) DEFAULT NULL,
      `email` varchar(255) DEFAULT NULL,
      `gender` int(2) DEFAULT NULL,
      `d_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

![在这里插入图片描述][20210518153347755.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70]: /images/20221020/15e762e81bb44f678fbccfc5747477df.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70 1]: /images/20221020/2e625f2bd8d14a2f98e19273745e0c3c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70 2]: /images/20221020/34abdf2dc51a43bca3238d2a5793caf2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70 3]: /images/20221020/0fc3dc26261f4c569feac70ebf9949d9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUzNzI0_size_16_color_FFFFFF_t_70 4]: /images/20221020/1539234703c74ef8b6661c0e31dcb8b5.png
[20210518153347755.png]: /images/20221020/f0d834e750d64af7b6f192745ba93eb1.png