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

前言

好久没有更新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)，可参考以下链接。

参考：

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。