详解SpringBoot配置连接池

内置的连接池

目前spring boot中默认支持的连接池有dbcp,dbcp2, tomcat, hikari三种连接池。

数据库连接可以使用datasource池进行自动配置。

1. 由于tomcat数据源连接池的性能和并发，在tomcat可用时，我们总是优先使用它。
2. 如果hikaricp可用，我们将使用它。
3. 如果commons dbcp可用，我们将使用它，但在生产环境不推荐使用它。
4. 最后，如果commons dbcp2可用，我们将使用它。

以上的几种连接池，可以通过在配置application文件中配置。

spring.datasource.url=jdbc:mysql://localhost/test
spring.datasource.username=dbuser
spring.datasource.password=dbpass
spring.datasource.driver-class-name=com.mysql.jdbc.driver

spring boot能够从大多数数据库的url上推断出driver-class-name，你就不需要再指定它了。对于一个将要创建的datasource连接池，我们需要能够验证driver是否可用，所以我们会在做任何事情之前检查它。比如，如果你设置spring.datasource.driverclassname=com.mysql.jdbc.driver，然后这个类就会被加载。

配置druid连接池

在springboot 的1.5.2版本中不在支持自动配druid，，即无法通过配置项直接支持相应的连接池；这里列出的这些配置项可以通过定制化datasource来实现。

druid介绍

druid是一个jdbc组件，它包括三部分：

1. druiddriver 代理driver，能够提供基于filter－chain模式的插件体系。
2. druiddatasource 高效可管理的数据库连接池。
3. sqlparser

druid可以做什么

1. 可以监控数据库访问性能，druid内置提供了一个功能强大的statfilter插件，能够详细统计sql的执行性能，这对于线上分析数据库访问性能有帮助。
2. 替换dbcp和c3p0。druid提供了一个高效、功能强大、可扩展性好的数据库连接池。
3. 数据库密码加密。直接把数据库密码写在配置文件中，这是不好的行为，容易导致安全问题。druiddruiver和druiddatasource都支持passwordcallback。
4. sql执行日志，druid提供了不同的logfilter，能够支持common-logging、log4j和jdklog，你可以按需要选择相应的logfilter，监控你应用的数据库访问情况。
5. 扩展jdbc，如果你要对jdbc层有编程的需求，可以通过druid提供的filter-chain机制，很方便编写jdbc层的扩展插件。 

项目地址：

spring boot与druid的集成

mysql driver驱动包：

    <dependency>
      <groupid>mysql</groupid>
      <artifactid>mysql-connector-java</artifactid>
      <scope>runtime</scope>
    </dependency>

阿里系的druid依赖包：

    <dependency>
      <groupid>com.alibaba</groupid>
      <artifactid>druid</artifactid>
      <version>1.0.25</version>
    </dependency>

application.properties配置信息

# 驱动配置信息
spring.datasource.type=com.alibaba.druid.pool.druiddatasource
spring.datasource.url = jdbc:mysql://127.0.0.1:3306/test?useunicode=true&characterencoding=utf-8 
spring.datasource.username = root
spring.datasource.password = adminter
spring.datasource.driverclassname = com.mysql.jdbc.driver

#连接池的配置信息
## 初始化大小，最小，最大
spring.druid.initialsize=5
spring.druid.minidle=5
spring.druid.maxactive=20
## 配置获取连接等待超时的时间
spring.druid.maxwait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.druid.timebetweenevictionrunsmillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.druid.minevictableidletimemillis=300000
spring.druid.validationquery=select 1 from dual
spring.druid.testwhileidle=true
spring.druid.testonborrow=false
spring.druid.testonreturn=false
spring.druid.poolpreparedstatements=true
spring.druid.maxpoolpreparedstatementperconnectionsize=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.druid.filters=stat,wall,log4j
# 通过connectproperties属性来打开mergesql功能；慢sql记录
spring.druid.connectionproperties=druid.stat.mergesql=true;druid.stat.slowsqlmillis=5000

druidsource配置信息的定制

package com.lf.datasource;

import com.alibaba.druid.pool.druiddatasource;
import org.springframework.boot.context.properties.configurationproperties;
import org.springframework.context.annotation.bean;
import org.springframework.context.annotation.primary;

import javax.sql.datasource;
import java.sql.sqlexception;

/**
 * created by lf on 2017/4/18.
 */
@configurationproperties(prefix = "spring.druid")
public class druidsource {

  private string dburl;

  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;

  public string getdburl() {
    return dburl;
  }

  public void setdburl(string dburl) {
    this.dburl = dburl;
  }

  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;
  }

  @bean   //声明其为bean实例
  @primary //在同样的datasource中，首先使用被标注的datasource
  public datasource datasource() throws sqlexception {
    druiddatasource datasource = new druiddatasource();

    datasource.seturl(this.dburl);
    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);

    datasource.setfilters(filters);


    return datasource;
  }
}

基于注解的配置statview的servlet

filter的实现类：

package com.lf.config;

import com.alibaba.druid.support.http.webstatfilter;

import javax.servlet.annotation.webfilter;
import javax.servlet.annotation.webinitparam;

/**
 * created by lf on 2017/4/18.
 */
@webfilter(filtername = "druidwebstatfilter", urlpatterns = "/*",
    initparams = {
        @webinitparam(name = "exclusions", value = "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")//忽略资源
    }
)
public class druidstatfilter extends webstatfilter {
}

statviewservlet:

package com.lf.config;

import com.alibaba.druid.support.http.statviewservlet;

import javax.servlet.annotation.webinitparam;
import javax.servlet.annotation.webservlet;

/**
 * created by lf on 2017/4/18.
 */

@webservlet(urlpatterns = "/druid/*",
    initparams = {
        @webinitparam(name = "allow", value = "127.0.0.1,192.168.163.1"),// ip白名单(没有配置或者为空，则允许所有访问)
        @webinitparam(name = "deny", value = "192.168.1.73"),// ip黑名单 (存在共同时，deny优先于allow)
        @webinitparam(name = "loginusername", value = "admin"),// 用户名
        @webinitparam(name = "loginpassword", value = "123456"),// 密码
        @webinitparam(name = "resetenable", value = "false")// 禁用html页面上的“reset all”功能
    })
public class druidstatviewservlet extends statviewservlet {
  private static final long serialversionuid = -2688872071445249539l;
}

在启动类上加入@servletcomponentscan注解

基于java的配置statview的servlet

package com.lf.config;

import com.alibaba.druid.support.http.statviewservlet;
import com.alibaba.druid.support.http.webstatfilter;
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;

/**
 * created by lf on 2017/4/18.
 */
@configuration
public class druidconfig {
  @bean
  public servletregistrationbean registrationbean() {
    servletregistrationbean servletregistrationbean = new servletregistrationbean(new statviewservlet());    //添加初始化参数：initparams
    servletregistrationbean.addurlmappings("/druid/*");
    //白名单：
    servletregistrationbean.addinitparameter("allow", "127.0.0.1");
    //ip黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:sorry, you are not permitted to view this page.
    servletregistrationbean.addinitparameter("deny", "192.168.1.73");
    //登录查看信息的账号密码.
    servletregistrationbean.addinitparameter("loginusername", "admin");
    servletregistrationbean.addinitparameter("loginpassword", "123456");
    //是否能够重置数据.
    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;
  }
}

运行界面以及介绍

访问地址： http://192.168.163.1:8080/druid/index.html

 

账号和密码为在servlet中配置的

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