SpringBoot精通系列-开发案例之配置Druid数据库连接池

落日映苍穹つ 2021-11-17 08:26 181阅读 0赞

> **导语**  
>   在使用SpringBoot的时候默认情况下提供了若干的数据库连接池，例如(dbcp,dbcp2,tomcat,hikari)等等，当然并不支持我们接下来所说的Druid，Druid是来自与阿里系的一个开源连接池，提供了非常优秀的监控功能。下面就来一起探索一下关于SpringBoot整合Druid的方式。

#### 版本环境 ####

Spring Boot 2.1.6，Druid 1.1.10，JDK1.8

#### Pom文件 ####

这里提供一个由阿里巴巴提供的SpringBoot关于Druid的整合方式

[https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter][https_github.com_alibaba_druid_tree_master_druid-spring-boot-starter]

<dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
     </dependency>
            
     <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>druid</artifactId>
          <version>1.1.10</version>
     </dependency>

#### 配置文件 ####

spring:
      datasource:
          url: jdbc:mysql://192.168.1.66:3306/spring-boot?useUnicode=true&characterEncoding=utf-8&useSSL=false
          username: root
          password: root
          driver-class-name: com.mysql.jdbc.Driver
          platform: mysql
          type: com.alibaba.druid.pool.DruidDataSource
          # 下面为连接池的补充设置，应用到上面所有数据源中
          # 初始化大小，最小，最大
          initialSize: 1
          minIdle: 3
          maxActive: 20
          # 配置获取连接等待超时的时间
          maxWait: 60000
          # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
          timeBetweenEvictionRunsMillis: 60000
          # 配置一个连接在池中最小生存的时间，单位是毫秒
          minEvictableIdleTimeMillis: 30000
          validationQuery: select 'x'
          testWhileIdle: true
          testOnBorrow: false
          testOnReturn: false
          # 打开PSCache，并且指定每个连接上PSCache的大小
          poolPreparedStatements: true
          maxPoolPreparedStatementPerConnectionSize: 20
          # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
          filters: stat,wall,slf4j
          # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
          connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

#### 编写配置类 ####

package com.nh.haiyan.ordermanager.config;
    
    import java.sql.SQLException;
    import javax.sql.DataSource;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    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 com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    
    /** * nihui */
    @Configuration
    public class DruidConfiguration { 
    
        private static final Logger logger = LoggerFactory.getLogger(DruidConfiguration.class);
    
        private static final String DB_PREFIX = "spring.datasource";
    
        @Bean
        public ServletRegistrationBean druidServlet() { 
            logger.info("init Druid Servlet Configuration ");
            ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
            // IP白名单 (没有配置或者为空，则允许所有访问)
            servletRegistrationBean.addInitParameter("allow", "");
            // IP黑名单(共同存在时，deny优先于allow)
            //servletRegistrationBean.addInitParameter("deny", "192.168.1.100");
            //控制台管理用户
            servletRegistrationBean.addInitParameter("loginUsername", "admin");
            servletRegistrationBean.addInitParameter("loginPassword", "admin");
            //是否能够重置数据 禁用HTML页面上的“Reset All”功能
            servletRegistrationBean.addInitParameter("resetEnable", "false");
            return servletRegistrationBean;
        }
    
        @Bean
        public FilterRegistrationBean filterRegistrationBean() { 
            FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
            filterRegistrationBean.addUrlPatterns("/*");
            filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
            return filterRegistrationBean;
        }
    
        @ConfigurationProperties(prefix = DB_PREFIX)
        class IDataSourceProperties { 
            private String url;
            private String username;
            private String password;
            private String driverClassName;
            private int initialSize;
            private int minIdle;
            private int maxActive;
            private int maxWait;
            private int timeBetweenEvictionRunsMillis;
            private int minEvictableIdleTimeMillis;
            private String validationQuery;
            private boolean testWhileIdle;
            private boolean testOnBorrow;
            private boolean testOnReturn;
            private boolean poolPreparedStatements;
            private int maxPoolPreparedStatementPerConnectionSize;
            private String filters;
            private String connectionProperties;
    
            @Bean  
            public DataSource dataSource() { 
                DruidDataSource datasource = new DruidDataSource();
                datasource.setUrl(url);
                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);
                try { 
                    datasource.setFilters(filters);
                } catch (SQLException e) { 
                    System.err.println("druid configuration initialization filter: " + e);
                }
                datasource.setConnectionProperties(connectionProperties);
                return datasource;
            }
    
            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;
            }
    
            public String getDriverClassName() { 
                return driverClassName;
            }
    
            public void setDriverClassName(String driverClassName) { 
                this.driverClassName = driverClassName;
            }
    
            public int getInitialSize() { 
                return initialSize;
            }
    
            public void setInitialSize(int initialSize) { 
                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 int getMaxWait() { 
                return maxWait;
            }
    
            public void setMaxWait(int maxWait) { 
                this.maxWait = maxWait;
            }
    
            public int getTimeBetweenEvictionRunsMillis() { 
                return timeBetweenEvictionRunsMillis;
            }
    
            public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) { 
                this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
            }
    
            public int getMinEvictableIdleTimeMillis() { 
                return minEvictableIdleTimeMillis;
            }
    
            public void setMinEvictableIdleTimeMillis(int 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 getConnectionProperties() { 
                return connectionProperties;
            }
    
            public void setConnectionProperties(String connectionProperties) { 
                this.connectionProperties = connectionProperties;
            }
        }
    
    }

#### 查看结果 ####

在本地浏览器中输入http://localhost:8080/druid/login.html  
输入用户名admin，密码admin即可查看  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25paHVpMTIz_size_16_color_FFFFFF_t_70]  
效果如下  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25paHVpMTIz_size_16_color_FFFFFF_t_70 1]

[https_github.com_alibaba_druid_tree_master_druid-spring-boot-starter]: https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25paHVpMTIz_size_16_color_FFFFFF_t_70]: /images/20211116/b6d1959f202a4dd392b9e6d911d29948.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25paHVpMTIz_size_16_color_FFFFFF_t_70 1]: /images/20211116/ad0e2fdf9e4b499dac55a28dd973fb1f.png