Spring Boot官方推荐的数据库连接池是HikariCP，从一些第三方的评测结果看，HikariCP的性能比Druid要好，但是Druid自带各种监控工具，背后又有阿里一直在为它背书，还是迎得了很多人的欢迎，本文就讲述Spring Boot如何集成Druid数据源。
由于Spring Boot的1.3.X和1.4.X版本对druid的配置方法略有不同，下面分开来描述。
Spring Boot 1.3.X 配置
引入Druid依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.23</version>
</dependency>
application.properties中新增数据库连接池配置
#druid datasouce database settings begin
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/spring_boot?characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=123456
# 下面为连接池的补充设置，应用到上面所有数据源中
# 初始化大小，最小，最大
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
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,log4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
spring.datasource.useGlobalDataSourceStat=true
#druid datasouce database settings end
定义Filter，忽略静态资源的拦截
package com.bluecoffee.filter;
import com.alibaba.druid.support.http.WebStatFilter;
import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;
/**
* Created by qianlong on 16/12/21.
*/
@WebFilter(filterName="druidStatFilter",urlPatterns="/*",
initParams={
@WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")// 忽略资源
})
public class DruidStatFilter extends WebStatFilter {
}
定义Servlet，用于Druid控制台的查看
package com.bluecoffee.servlet;
import com.alibaba.druid.support.http.StatViewServlet;
import javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;
/**
* Created by qianlong on 16/8/25.
*/
@SuppressWarnings("serial")
@WebServlet(urlPatterns = "/druid/*",
initParams={
@WebInitParam(name="allow",value="192.168.16.110,127.0.0.1"),// IP白名单 (没有配置或者为空，则允许所有访问)
@WebInitParam(name="deny",value="192.168.16.111"),// 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 {
}
主执行类上注意要加上@ServletComponentScan注解，否则Servlet无法生效
@SpringBootApplication
@ServletComponentScan
@Configuration
@EnableAutoConfiguration
public class Application {
public static void main(String[] args) throws Exception {
System.out.println("------------Application is start---------------");
SpringApplication.run(Application.class, args);
}
}
接下来就可以打开http://localhost:8080/druid/index.html看到效果了，如下图所示
Druid控制台
Spring Boot 1.4.X配置
我一开始是在1.3.3.RELEASE版本上集成Druid，迁移到Spring Boot1.4.2版本时发现SQL控制台无论如何都无法监控到SQL操作，不得已使用@Bean装配方式来配置Druid数据源，终于发现可以正常工作了。没看过源码，但猜想可能是由于Spring Boot升级版本的时候，与Druid有冲突，下面讲述@Bean装配方式实现Druid数据源配置。
新增Druid数据源配置文件
删除原有application.properties中druid的相关配置，新增druid-config.properties配置文件
#druid datasouce database settings begin
spring.druid.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.druid.datasource.driverClassName=com.mysql.jdbc.Driver
spring.druid.datasource.url=jdbc:mysql://localhost:3306/spring_boot?characterEncoding=utf-8
spring.druid.datasource.username=root
spring.druid.datasource.password=123456
# 下面为连接池的补充设置，应用到上面所有数据源中
# 初始化大小，最小，最大
spring.druid.datasource.initialSize=5
spring.druid.datasource.minIdle=5
spring.druid.datasource.maxActive=20
# 配置获取连接等待超时的时间
spring.druid.datasource.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.druid.datasource.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.druid.datasource.minEvictableIdleTimeMillis=300000
spring.druid.datasource.validationQuery=SELECT 1 FROM DUAL
spring.druid.datasource.testWhileIdle=true
spring.druid.datasource.testOnBorrow=false
spring.druid.datasource.testOnReturn=false
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.druid.datasource.poolPreparedStatements=true
spring.druid.datasource.maxPoolPreparedStatementPerConnectionSize=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.druid.datasource.filters=stat,wall,log4j,config
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.druid.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
spring.druid.datasource.useGlobalDataSourceStat=true
#druid datasouce database settings end
新增属性读取类DruidSettings.java
package com.bluecoffee.configuration;
import org.springframework.boot.context.properties.ConfigurationProperties;
/**
* Created by qianlong on 2016/12/21.
*/
@ConfigurationProperties(prefix = "spring.druid.datasource",locations = "classpath:druid-config.properties")
public class DruidSettings {
private String type;
private String driverClassName;
private String url;
private String username;
private String password;
private Integer initialSize;
private Integer minIdle;
private Integer maxActive;
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 Integer maxPoolPreparedStatementPerConnectionSize;
private String filters;
private String connectionProperties;
private boolean useGlobalDataSourceStat;
//省略getter/setter方法
}
用Bean方式读取配置并实例化数据源
package com.bluecoffee.configuration;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import java.util.Properties;
/**
* Created by qianlong on 2016/12/21.
*/
@Configuration
@EnableConfigurationProperties(DruidSettings.class)
public class DruidDataSourceConfig {
@Autowired
private DruidSettings druidSettings;
@Bean
@ConfigurationProperties("spring.druid.datasource")
public DruidDataSource dataSource(
DataSourceProperties properties) throws Exception{
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(druidSettings.getDriverClassName());
dataSource.setUrl(druidSettings.getUrl());
dataSource.setUsername(druidSettings.getUsername());
dataSource.setPassword(druidSettings.getPassword());
dataSource.setInitialSize(druidSettings.getInitialSize());
dataSource.setMinIdle(druidSettings.getMinIdle());
dataSource.setMaxActive(druidSettings.getMaxActive());
dataSource.setMaxWait(druidSettings.getMaxWait());
dataSource.setTimeBetweenEvictionRunsMillis(druidSettings.getTimeBetweenEvictionRunsMillis());
dataSource.setMinEvictableIdleTimeMillis(druidSettings.getMinEvictableIdleTimeMillis());
String validationQuery = druidSettings.getValidationQuery();
if (validationQuery != null && !"".equals(validationQuery)) {
dataSource.setValidationQuery(validationQuery);
}
dataSource.setTestWhileIdle(druidSettings.isTestWhileIdle());
dataSource.setTestOnBorrow(druidSettings.isTestOnBorrow());
dataSource.setTestOnReturn(druidSettings.isTestOnReturn());
if(druidSettings.isPoolPreparedStatements()){
dataSource.setMaxPoolPreparedStatementPerConnectionSize(druidSettings.getMaxPoolPreparedStatementPerConnectionSize());
}
dataSource.setFilters(druidSettings.getFilters());//这是最关键的,否则SQL监控无法生效
String connectionPropertiesStr = druidSettings.getConnectionProperties();
if(connectionPropertiesStr != null && !"".equals(connectionPropertiesStr)){
Properties connectProperties = new Properties();
String[] propertiesList = connectionPropertiesStr.split(";");
for(String propertiesTmp:propertiesList){
String[] obj = propertiesTmp.split("=");
String key = obj[0];
String value = obj[1];
connectProperties.put(key,value);
}
dataSource.setConnectProperties(connectProperties);
}
dataSource.setUseGlobalDataSourceStat(druidSettings.isUseGlobalDataSourceStat());
return dataSource;
}
}
网友评论