开发者

Spring Boot集成Druid实现数据源管理与监控的详细步骤

目录
  • 1. 引言
    • 1.1 环境准备
    • 1.2 Druid介绍
  • 2. 配置Druid连接池
    • 3. 查看Druid监控

      1. 引言

      在程序员的日常工作中, 经常需要编写数据库操作相关的程序,而这就需要数据连接池中间件用于管理数据库连接。数据库连接池负责分配、管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是再重新建立一个;释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。通过数据库连接池能明显提高对数据库操作的性能。在Java应用程序开发中,常用的连接池有DBCP、C3P0、Proxool等。

      而在Spring Boot开发框架下,它默认提供了若干种可用的连接池,其中包括Druid连接池(来自于阿里系的一个开源连接池),除在连接池之外,Druid还提供了非常优秀的数据库监控和扩展功能。在此,根据项目实践中的应用,讲解如何实现Spring Boot与Druid连接池的集成。

      1.1 环境准备

      • JDK 1.8
      • Spring Boot 2.0.0.RELEASE
      • mysql 5.7
      • Druid 1.1.9

      1.2 Druid介绍

      Druid是阿里开源的一个JDBC应用组件, 其包括三部分:

      • DruidDriver 代理Driver,能够提供基于Filter-Chain模式的插件体系。
      • DruidDataSource 高效可管理的数据库连接池。
      • SQLParser SQL语法分析

      通过Druid连接池中间件, 我们可以实现:

      • 可以监控数据库访问性能,Druid内置提供了一个功能强大的StatFilter插件,能够详细统计SQL的执行性能,这对于线上分析数据库访问性能有帮助。
      • 替换传统的DBCP和C3P0连接池中间件。Druid提供了一个高效、功能强大、可扩展性好的数据库连接池。
      • 数据库密码加密。直接把数据库密码写在配置文件中,这是不好的行为,容易导致安全问题。DruidDruiver和DruidDataSource都支持PasswordCallback。
      • SQL执行日志,Druid提供了不同的LogFilter,能够支持Common-Logging、Log4j和JdkLog,你可以按需要选择相应的LogFilter,监控你应用的数据库访问情况。
      • 扩展JDBC,如果你要对JDBC层有编程的需求,可以通过Druid提供的Filter-Chain机制,很方便编写JDBC层的扩展插件。

      关于Druid的更多详细信息可以参考Druid官方文档

      2. 配置Druid连接池

      (1) 添加Maven依赖

      <dependencies>
            ... 此处省略其他配置 ...
            <!-- Configuration Module -->
            <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-configuration-processor</artifactId>
              <optional>true</optional>
            </dependency>
            <!-- MySQL Driver -->
            <dependency>
              <groupId>mysql</groupId>
              <artifactId>mysql-connector-java</artifactId>
              <version>5.1.45</version>
                  <scope>runtime</scope>
            </dependency>
            <!-- Druid Pool -->
            <dependency>
              <groupId>com.alibaba</groupId>
              <artifactId>druid</artifactId>
              <version>1.1.9</version>
            </dependency>
            <dependency>
                  <groupId>com.alibaba</groupId>
                  <artifactId>druid-spring-boot-starter</artifactId>
                  <version>1.1.9</version>
            </dependency>
      </dependencies>

      (2) Spring Boot配置文件配置

      Spring Boot配置文件有application.properties和application.yml两种配置文件方式 , 此处采用的是application.yml的配置方式。

      # Spring Datasource Settings
      spring:
        datasource:
          name: druidDataSource
          type: com.alibaba.druid.pool.DruidDataSource
          druid:
            driver-class-name: com.mysql.jdbc.Driver
            url: jdbc:mysql://192.168.202.17:3306/auth_service?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false
            username: root
            password: 123456
            filters: stat,wall,log4j,config
            max-active: 100
            initial-size: 1
            max-wait: 60000
            min-idle: 1
            time-between-eviction-runs-millis: 60000
            min-evictable-idle-time-millis: 300000
            validation-query: select 'x'
            test-while-idle: true
            test-on-borrow: false
            test-on-return: false
            pool-prepared-statements: true
            max-open-prepared-statements: 50
            max-pool-prepared-statement-per-connection-size: 20

      说明:

      - spring.datasource.druid.max-active 最大连接数

      - spring.datasource.druid.initial-size 初始化大小

      - spring.datasource.druid.min-idle 最小连接数

      - spring.datasource.druid.max-wait 获取连接等待超时时间

      - spring.datasource.druid.time-between-eviction-runs-millis 间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒

      - spring.datasource.druid.min-evictable-idle-time-millis 一个连接在池中最小生存的时间,单位是毫秒

      - spring.datasource.druid.filters=config,stat,wall,log4j 配置监控统计拦截的filters,去掉后监控界面SQL无法进行统计,’wall’用于防火墙

      Druid提供以下几种Filter信息:

      Filter类名别名
      defaultcom.alibaba.druid.filter.stat.StatFilter
      statcom.alibaba.druid.filter.stat.StatFilter
      mergeStatcom.alibaba.druid.filter.stat.MergeStatFilter
      encodingcom.alibaba.druid.filter.encoding.EncodingConvertFilter
      log4jcom.alibaba.druid.filter.logging.Log4jFilter
      log4j2com.alibaba.druid.filter.logging.Log4j2Filter
      slf4jcom.alibaba.druid.filter.logging.Slf4jLogFilter
      commonloggingcom.alibaba.druid.filter.logging.CommonsLogFilter
      wallcom.alibaba.druid.wall.WallFilter

      (3) Druid配置信息定制

      通过Druid-Spring-Boot-Starter可以自动完成相关的配置, 而无须自定义配置文件, 具体参考Druid-Spring-Boot-Starter。

      通过Druid-Spring-Boot-Starter的Spring Boot配置信息示例:

      sprwww.devze.coming:
        datasource:
          name: druidDataSource
          type: com.alibaba.druid.pool.DruidDataSource
          driver-class-name: com.mysql.jdbc.Driver
          url: jdbc:mysql://192.168.202.17:3306/auth_service?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false
          username: root
          password: 123456
          druid:
            filters: stat,wall,log4j,config
            max-active: 100
            initial-size: 1
            max-wait: 60000
            min-idle: 1
            time-between-eviction-runs-millis: 60000
            min-evictable-idle-time-millis: 300000
            validation-query: select 'x'
            test-while-idle: true
            test-on-borrow: false
            test-on-return: false
            pool-prepared-statements: true
            max-open-prepared-statements: 50
            max-pool-prepared-statement-per-connection-size: 20

      在此, 主要通过定制的配置文件对Druid进行自定义属性配置, 配置文件如下:

      package com.garyond.hurricane.config;
      import org.springframework.boot.context.properties.ConfigurationProperties;
      @ConfigurationProperties(prefix = "spring.datasource.druid")
      public class DruidDataSourceProperties {
          private String driverClassName;
          private String url;
          private String username;
          private String password;
          private int initialSize;
          private int minIdle;
          private int maxActive = 100;
          private long maxWait;
          private long timeBetweenEvictionRunsMillis;
          private long minEvictableIdleTimeMillis;
          private String validationQuery;
          private boolean testWhileIdle;
          private boolean testOnBorrow;
          private boolean testOnReturn;
          private boolean poolPreparedStatements;
          private int maxPoolPreparedStatementPerConnectionSize;
          private String filters;
          public int getInitialSize() {
              return initialSize;
          }
          public void setInitialSize(int initialSizjavascripte) {
              this.initialSize = initialSize;
          }
          public int getMinIdle() {
              return minIdle;
          }
          public void setMinIdle(int minIdle) {
              this.minIdle = minIdle;
          }
          public int getMaxActive() {
              return maxActive;
          }
          public void setMaxActive(int maxActive) {
              this.maxActive = maxActive;
          }
          public long getMaxWait() {
              return maxWait;
          }
          public void setMaxWait(long maxWait) {
              this.maxWait = maxWait;
          }
          public long getTimeBetweenEvictionRunsMillis() {
              return timeBetweenEvictionRunsMillis;
          }
          public void setTimeBetweenEvictionRunsMillis(long timeBetweenEvictionRunsMillis) {
              this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
          }
          public long getMinEvictableIdleTimeMillis() {
              return minEvictableIdleTimeMillis;
          }
          public void setMinEvictableIdleTimeMillis(long minEvictableIdleTimeMillis) {
              this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
          }
          public String getValidationQuery() {
              return validationQuery;
          }
          public void setValidationQuery(String validationQuery) {
              this.validationQuery = validationQuery;
          }
          public boolean isTestWhileIdle() {
              return testWhileIdle;
          }
          public void setTestWhileIdle(boolean testWhileIdle) {
              this.testWhileIdle = testWhileIdle;
          }
          public boolean isTestOnBorrow() {
              return testOnBorrow;
          }
          public void setTestOnBorrow(boolean testOnBorrow) {
              this.testOnBorrow = testOnBorrow;
          }
          public boolean isTestOnReturn() {
              return testOnReturn;
          }
          public void setTestOnReturn(boolean testOnReturn) {
              this.testOnReturn = testOnReturn;
          }
          public boolean isPoolPreparedStatements() {
              return poolPreparedStatements;
          }
          public void setPoolPreparedStatements(boolean poolPreparedStatements) {
              this.poolPreparedStatements = poolPreparedStatements;
          }
          public int getMaxPoolPreparedStatementPerConnectionSize() {
              return maxPoolPreparedStatementPerConnectionSize;
          }
          public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {
              this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
          }
          public String getFilters() {
              return filters;
          }
          public void setFilters(String filters) {
              this.filters = filters;
          }
          public String getDriverClassName() {
              return driverClassName;
          }
          public void setDriverClassName(String driverClassName) {
              this.driverClassName = driverClassName;
          }
          public String getUrl() {
              return url;
          }
          public void setUrl(String url) {
              this.url = url;
          }
          public String getUsername() {
              return username;
          }
          public void setUsername(String username) {
              this.username = username;
          }
          public String getPassword() {
              return password;
          }
          public void setPassword(String password) {
              this.password = password;
          }
      }

      (3) 配置Druid相关的Servlet和Filter

      package com.garyond.hurricane.config;
      import com.alibaba.druid.pool.DruidDataSource;
      import com.alibaba.druid.support.http.StatViewServlet;
      import com.alibaba.druid.support.http.WebStatFilter;
      import org.springframework.beans.factory.annotation.Autowired;
      import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
      import org.springframework.boot.context.properties.EnableConfigurationProperties;
      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.sql.SQLException;
      @Configuration
      @EnableConfigurationProperties({DruidDataSourceProperties.class})
      public class DruidConfig {
          @Autowired
          private DruidDataSourceProperties properties;
          @Bean
          @ConditionalOnMissingBean
          public DataSource druidDataSource() {
              DruidDataSource druidDataSource = new DruidDataSource();
              druidDataSource.setDriverClassName(properties.getDriverClassName());
              druidDataSource.setUrl(properties.getUrl());
              druidDataSource.setUsername(properties.getUsername());
              druidDataSource.setPassword(properties.getPassword());
              druidDataSource.setInitialSize(properties.getInitialSize());
              druidDataSource.setMinIdle(properties.getMinIdle());
              druidDataSource.setMaxActive(properties.getMaxActive());
              druidDataSource.setMaxWait(properties.getMaxWait());
              druidDataSource.setTimeBetweenEvictionRunsMillis(properties.getTimeBetweenEvictionRunsMillis());
              druidDataSource.setMinEvictableIdleTimeMillis(properties.getMinEvictableIdleTimeMillis());
              druidDataSource.setValidationQuery(properties.getValidationQuery());
              druidDataSource.setTestWhileIdle(properties.isTestWhileIdle());
              druidDataSource.setTestOnBorrow(properties.isTestOnBorrow());
              druidDataSource.setTestOnReturn(properties.isTestOnReturn())http://www.devze.com;
              druidDataSource.setPoolPreparedStatements(properties.isPoolPreparedStatements());
              druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(properties.getMaxPoolPreparedStatementPerConnectionSize());
              try {
                  druidDataSource.setFilters(properties.getFilters());
                  druidDataSource.init();
              } catch (SQLException e) {
                  e.printStackTrace();
              }
              return druidDataSource;
          }
          /**
           * 注册Servlet信息, 配置监控视图
           *
           * @return
           */
          @Bean
          @ConditionalOnMissingBean
          public ServletRegistrationBean druidServlet() {
              ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
              //白名单:
              servletRegistrationBean.addInitParameter("allow","192.168.6.195");
              //IP黑名单 (存在共同时,deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
              servletRegistrationBean.addInitParameter("deny","192.168.6.73");
              //登录查看信息的账号密码, 用于登录Druid监控后台
              servletRegistrationBean.addInitParameter("loginUsername", "admin");
              servletRegistrationBean.addInitParameter("loginPassword", "admin");
              //是否能够重置数据.
              servletRegistrationBean.addInitParameter("resetEnable", "true");
              return servletRegistrationBean;
          }
          /**
           * 注册Filter信息, 监控拦截器
           *
           * @return
           */
          @Bean
          @ConditionalOnMissingBean
          public FilterRegistrationBean filterRegistrationBean() {
              FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
              filterRegistrationBean.setFilter(new WebStatFilter());
              filterRegistrationBean.addUrlPatterns("/*");
              filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
              return filterRephpgistrationBean;
          }
      }

      注:

      - @EnableConfigurationProperties({DruidDataSourceProperties.class}) 用于导入上一步Druid的配置信息

      - public ServletRegistrationBean druidServlet() 相当于Web Servlet配置

      - public FilterRegistrationBean filterRegistrationBean() 相当于Web Filter配置

      如果不使用上述的Servlet和Filter配置, 也可以通过下述监控器配置实现:

      配置监控拦截器(相当于FilterRegistrationBean)

      /**   
       * 配置监控拦截器, druid监控拦截器   
       * @ClassName: DruidStatFilter    
       * @author garyond
       * @date 2018年4月24日
       */    
      @WebFilter(filterName="druidwebStatFilter",    
      urlPatterns="/*",    
      initParams={    
          @WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"),// 忽略资源    
      })   
      public class DruidStatFilter extends WebStatFilter {  
      }  

      配置Druid监控视图(相当于ServletRegistrationBean)

      /**   
       * druid监控视图配置   
       *
       * @ClassName: DruidStatViewServlet    
       * @author garyond  
       * @date 2018年4月24日 
       */    
      @WebServlet(urlPatterns = "/druid/*", initParams={    
          @WebInitParam(name="allow",value="192.168.6.195"),// IP白名单 (没有配置或者为空,则允许所有访问)    
          @WebInitParam(name="deny",value="192.168.6.73"),// IP黑名单 (存在共同时,deny优先于allow)    
          @WebInitParam(name="loginUsername",value="admin"),// 用户名    
          @WebInitParam(name="loginPassword",value="admin"),// 密码    
          @WebInitParam(name="resetEnable",value="true")// 禁用html页面上的“Reset All”功能    
      })   
      public class DruidStatViewServlet extends StatViewServlet {  
          private static final long serialVersionUID = 7359758657306626394L;  
      }  

      3. 查看Druid监控

      配置完成后, 并完成相关的数据库操作配置, 启动Spring Boot应用程序,就可以通过访问: http://localhost:8080/druid/index.html 访问Druid监控后台页面。

      Spring Boot集成Druid实现数据源管理与监控的详细步骤

      输入用户名和密码可以查看Druid监控信息:

      Spring Boot集成Druid实现数据源管理与监控的详细步骤

      Spring Boot集成Druid实现数据源管理与监控的详细步骤

      至此, Spring Boot集成Druid连接池已配置完毕。

      到此这篇关于Spring Boot集成Druidandroid实现数据源管理与监控的详细步骤的文章就介绍到这了,更多相关Spring Boot Druid数据源管理内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)!

      0

      上一篇:

      下一篇:

      精彩评论

      暂无评论...
      验证码 换一张
      取 消

      最新开发

      开发排行榜