六：SpringBoot-集成Druid连接池，配置监控界面

落日映苍穹つ 2023-02-23 08:25 60阅读 0赞

### SpringBoot-集成Druid连接池，配置监控界面 ###

*  1、Druid连接池
 *   *  1.1 Druid特点
 *  2、SpringBoot整合Druid
 *   *  2.1 引入核心依赖
     *  2.2 数据源配置文件
     *  2.3 核心配置类
     *  2.4 简单测试类

# 1、Druid连接池 #

Druid连接池是阿里巴巴开源的数据库连接池项目。Druid连接池为监控而生，内置强大的监控功能，监控特性不影响性能。功能强大，能防SQL注入，内置Loging能诊断Hack应用行为。  
Druid连接池是阿里巴巴内部唯一使用的连接池，在内部数据库相关中间件TDDL/DRDS 都内置使用强依赖了Druid连接池，经过阿里内部数千上万的系统大规模验证，经过历年双十一超大规模并发验证。

## 1.1 Druid特点 ##

1.  稳定性特性，阿里巴巴的业务验证
2.  完备的监控信息，快速诊断系统的瓶颈
3.  内置了WallFilter 提供防SQL注入功能

# 2、SpringBoot整合Druid #

## 2.1 引入核心依赖 ##

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.21</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.13</version>
    </dependency>
    
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

## 2.2 数据源配置文件 ##

spring:
      application:
        # 应用名称
        name: node07-boot-druid
      datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        druid:
          driverClassName: com.mysql.jdbc.Driver
          url: jdbc:mysql://localhost:3306/data_one?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false
          username: root
          password: 123
          initial-size: 10
          max-active: 100
          min-idle: 10
          max-wait: 60000
          pool-prepared-statements: true
          max-pool-prepared-statement-per-connection-size: 20
          time-between-eviction-runs-millis: 60000
          min-evictable-idle-time-millis: 300000
          max-evictable-idle-time-millis: 60000
          validation-query: SELECT 1 FROM DUAL
          # validation-query-timeout: 5000
          test-on-borrow: false
          test-on-return: false
          test-while-idle: true
          connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
          #filters: #配置多个英文逗号分隔(统计，sql注入，log4j过滤)
          filters: stat,wall
          stat-view-servlet:
            enabled: true
            url-pattern: /druid/*

## 2.3 核心配置类 ##

import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Value;
    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 org.springframework.jdbc.core.JdbcTemplate;
    
    /** * Druid数据库连接池配置文件 */
    @Configuration
    public class DruidConfig { 
        private static final Logger logger = LoggerFactory.getLogger(DruidConfig.class);
        @Value("${spring.datasource.druid.url}")
        private String dbUrl;
        @Value("${spring.datasource.druid.username}")
        private String username;
        @Value("${spring.datasource.druid.password}")
        private String password;
        @Value("${spring.datasource.druid.driverClassName}")
        private String driverClassName;
        @Value("${spring.datasource.druid.initial-size}")
        private int initialSize;
        @Value("${spring.datasource.druid.max-active}")
        private int maxActive;
        @Value("${spring.datasource.druid.min-idle}")
        private int minIdle;
        @Value("${spring.datasource.druid.max-wait}")
        private int maxWait;
        @Value("${spring.datasource.druid.pool-prepared-statements}")
        private boolean poolPreparedStatements;
        @Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")
        private int maxPoolPreparedStatementPerConnectionSize;
        @Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
        private int timeBetweenEvictionRunsMillis;
        @Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
        private int minEvictableIdleTimeMillis;
        @Value("${spring.datasource.druid.max-evictable-idle-time-millis}")
        private int maxEvictableIdleTimeMillis;
        @Value("${spring.datasource.druid.validation-query}")
        private String validationQuery;
        @Value("${spring.datasource.druid.test-while-idle}")
        private boolean testWhileIdle;
        @Value("${spring.datasource.druid.test-on-borrow}")
        private boolean testOnBorrow;
        @Value("${spring.datasource.druid.test-on-return}")
        private boolean testOnReturn;
        @Value("${spring.datasource.druid.filters}")
        private String filters;
        @Value("{spring.datasource.druid.connection-properties}")
        private String connectionProperties;
        /** * Druid 连接池配置 */
        @Bean     //声明其为Bean实例
        public DruidDataSource dataSource() { 
            DruidDataSource datasource = new DruidDataSource();
            datasource.setUrl(dbUrl);
            datasource.setUsername(username);
            datasource.setPassword(password);
            datasource.setDriverClassName(driverClassName);
            datasource.setInitialSize(initialSize);
            datasource.setMinIdle(minIdle);
            datasource.setMaxActive(maxActive);
            datasource.setMaxWait(maxWait);
            datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
            datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            datasource.setMaxEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            datasource.setValidationQuery(validationQuery);
            datasource.setTestWhileIdle(testWhileIdle);
            datasource.setTestOnBorrow(testOnBorrow);
            datasource.setTestOnReturn(testOnReturn);
            datasource.setPoolPreparedStatements(poolPreparedStatements);
            datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
            try { 
                datasource.setFilters(filters);
            } catch (Exception e) { 
                logger.error("druid configuration initialization filter", e);
            }
            datasource.setConnectionProperties(connectionProperties);
            return datasource;
        }
        /** * JDBC操作配置 */
        @Bean(name = "dataOneTemplate")
        public JdbcTemplate jdbcTemplate (@Autowired DruidDataSource dataSource){ 
            return new JdbcTemplate(dataSource) ;
        }
    
        /** * 配置 Druid 监控界面 */
        @Bean
        public ServletRegistrationBean statViewServlet(){ 
            ServletRegistrationBean srb =
                    new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
            //设置控制台管理用户
            srb.addInitParameter("loginUsername","root");
            srb.addInitParameter("loginPassword","root");
            //是否可以重置数据
            srb.addInitParameter("resetEnable","false");
            return srb;
        }
        @Bean
        public FilterRegistrationBean statFilter(){ 
            //创建过滤器
            FilterRegistrationBean frb =
                    new FilterRegistrationBean(new WebStatFilter());
            //设置过滤器过滤路径
            frb.addUrlPatterns("/*");
            //忽略过滤的形式
            frb.addInitParameter("exclusions",
                    "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
            return frb;
        }
    }

## 2.4 简单测试类 ##

@RestController
    public class DruidController { 
        private static final Logger LOG = LoggerFactory.getLogger(DruidController.class);
        @Resource
        private JdbcTemplate jdbcTemplate ;
        @RequestMapping("/druidData")
        public String druidData (){ 
            String sql = "SELECT COUNT(1) FROM d_phone" ;
            Integer countOne = jdbcTemplate.queryForObject(sql,Integer.class) ;
            // countOne==2
            LOG.info("countOne=="+countOne);
            return "success" ;
        }
    }