SpringBoot开发案例之配置Druid数据库连接池

• 2019 年 12 月 9 日
• 筆記

前言

好久没有更新Spring Boot系列文章，你说忙么？也可能是，前段时间的关注点也许在其他方面了，最近项目中需要开发小程序，正好采用Spring Boot实现一个后端服务，后面会把相关的代码案例分享出来，不至于大家做小程序后端服务的时候一头雾水。

在Spring Boot下默认提供了若干种可用的连接池(dbcp,dbcp2, tomcat, hikari)，当然并不支持Druid，Druid来自于阿里系的一个开源连接池，它提供了非常优秀的监控功能，下面跟大家分享一下如何与Spring Boot集成。

版本环境

Spring Boot 1.5.2.RELEASE、Druid 1.1.6、JDK1.7

系统集成

添加pom.xml依赖:

<!-- Jpa -->  <dependency>      <groupId>org.springframework.boot</groupId>      <artifactId>spring-boot-starter-data-jpa</artifactId>  </dependency>  <!-- MySql -->  <dependency>      <groupId>mysql</groupId>      <artifactId>mysql-connector-java</artifactId>  </dependency>  <!-- druid -->  <dependency>      <groupId>com.alibaba</groupId>      <artifactId>druid</artifactId>      <version>1.1.6</version>  </dependency>

配置application.properties：

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

配置yml文件(与上二选一)

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

配置Druid的监控统计功能

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;  /**   * 阿里数据库连接池 Druid配置   * 创建者 柒   * 创建时间    2018年3月15日   */  @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/， 输入配置的账号密码登录之后，即可查看数据源及SQL统计等监控。效果图如下：

当然，阿里巴巴也提供了Druid的SpringBoot集成版(druid-spring-boot-starter)，可参考以下链接。

参考：

https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

https://github.com/alibaba/druid/wiki