Spring Boot系列文章之-使用 Druid 数据库连接池
导读
Spring Boot系列文章之-使用 Druid 数据库连接池，最近恰好最近用到Druid，就将Spring Boot 使用 Druid作为数据源做一个简单的介绍。
Druid介绍：
Druid是阿里巴巴开源的数据库连接池，Druid号称是Java语言中最好的数据库连接池，并且能够提供强大的监控和扩展功能，Druid的官方地址
了解更多：
快速上手：
下面来说明如何在 spring Boot 中配置使用Druid ，本例使用的持久层框为Spring Boot JPA。
第一步：添加pom依赖:
org.springframework.bootspring-boot-starter-data-jpacom.alibabadruid1.0.20
第二步：配置yml文件 数据源相关信息
spring: datasource: url:jdbc:mysql://localhost:3306/sanji-boot?useUnicode=true&characterEncoding=utf-8&useSSL=false username:root password:123456 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# 合并多个DruidDataSource的监控数据#useGlobalDataSourceStat: true jpa: show-sql:true properties: hibernate: hbm2ddl: auto:create
第三步：配置Druid的监控统计功能
这里有两种方式 ：
第一种方式@Confing注解 配置类:
@ConfigurationpublicclassDruidConfiguration{privatestaticfinalLogger logger = LoggerFactory.getLogger(DruidConfiguration.class);privatestaticfinalString DB_PREFIX ="spring.datasource";@BeanpublicServletRegistrationBeandruidServlet(){ logger.info("init Druid Servlet Configuration "); ServletRegistrationBean servletRegistrationBean =newServletRegistrationBean(newStatViewServlet(),"/druid/*");// IP白名单servletRegistrationBean.addInitParameter("allow","192.168.2.25,127.0.0.1");// 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");returnservletRegistrationBean; }@BeanpublicFilterRegistrationBeanfilterRegistrationBean(){ FilterRegistrationBean filterRegistrationBean =newFilterRegistrationBean(newWebStatFilter()); filterRegistrationBean.addUrlPatterns("/*"); filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");returnfilterRegistrationBean; }//解决 spring.datasource.filters=stat,wall,log4j 无法正常注册进去@ConfigurationProperties(prefix = DB_PREFIX)classIDataSourceProperties{privateString url;privateString username;privateString password;privateString driverClassName;privateintinitialSize;privateintminIdle;privateintmaxActive;privateintmaxWait;privateinttimeBetweenEvictionRunsMillis;privateintminEvictableIdleTimeMillis;privateString validationQuery;privatebooleantestWhileIdle;privatebooleantestOnBorrow;privatebooleantestOnReturn;privatebooleanpoolPreparedStatements;privateintmaxPoolPreparedStatementPerConnectionSize;privateString filters;privateString connectionProperties;@Bean//声明其为Bean实例@Primary//在同样的DataSource中，首先使用被标注的DataSourcepublicDataSourcedataSource(){ DruidDataSource datasource =newDruidDataSource(); datasource.setUrl(url); datasource.setUsername(username); datasource.setPassword(password); datasource.setDriverClassName(driverClassName);//configurationdatasource.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);returndatasource; }publicStringgetUrl(){returnurl; }publicvoidsetUrl(String url){this.url = url; }publicStringgetUsername(){returnusername; }publicvoidsetUsername(String username){this.username = username; }publicStringgetPassword(){returnpassword; }publicvoidsetPassword(String password){this.password = password; }publicStringgetDriverClassName(){returndriverClassName; }publicvoidsetDriverClassName(String driverClassName){this.driverClassName = driverClassName; }publicintgetInitialSize(){returninitialSize; }publicvoidsetInitialSize(intinitialSize){this.initialSize = initialSize; }publicintgetMinIdle(){returnminIdle; }publicvoidsetMinIdle(intminIdle){this.minIdle = minIdle; }publicintgetMaxActive(){returnmaxActive; }publicvoidsetMaxActive(intmaxActive){this.maxActive = maxActive; }publicintgetMaxWait(){returnmaxWait; }publicvoidsetMaxWait(intmaxWait){this.maxWait = maxWait; }publicintgetTimeBetweenEvictionRunsMillis(){returntimeBetweenEvictionRunsMillis; }publicvoidsetTimeBetweenEvictionRunsMillis(inttimeBetweenEvictionRunsMillis){this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis; }publicintgetMinEvictableIdleTimeMillis(){returnminEvictableIdleTimeMillis; }publicvoidsetMinEvictableIdleTimeMillis(intminEvictableIdleTimeMillis){this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis; }publicStringgetValidationQuery(){returnvalidationQuery; }publicvoidsetValidationQuery(String validationQuery){this.validationQuery = validationQuery; }publicbooleanisTestWhileIdle(){returntestWhileIdle; }publicvoidsetTestWhileIdle(booleantestWhileIdle){this.testWhileIdle = testWhileIdle; }publicbooleanisTestOnBorrow(){returntestOnBorrow; }publicvoidsetTestOnBorrow(booleantestOnBorrow){this.testOnBorrow = testOnBorrow; }publicbooleanisTestOnReturn(){returntestOnReturn; }publicvoidsetTestOnReturn(booleantestOnReturn){this.testOnReturn = testOnReturn; }publicbooleanisPoolPreparedStatements(){returnpoolPreparedStatements; }publicvoidsetPoolPreparedStatements(booleanpoolPreparedStatements){this.poolPreparedStatements = poolPreparedStatements; }publicintgetMaxPoolPreparedStatementPerConnectionSize(){returnmaxPoolPreparedStatementPerConnectionSize; }publicvoidsetMaxPoolPreparedStatementPerConnectionSize(intmaxPoolPreparedStatementPerConnectionSize){this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize; }publicStringgetFilters(){returnfilters; }publicvoidsetFilters(String filters){this.filters = filters; }publicStringgetConnectionProperties(){returnconnectionProperties; }publicvoidsetConnectionProperties(String connectionProperties){this.connectionProperties = connectionProperties; } }}
第二种方式基于注解的配置:
首先在启动类上加上注解：@ServletComponentScan使Spring能够扫描到我们自己编写的servlet和filter。
//druid数据源状态监控.@WebServlet(urlPatterns ="/druid/*", initParams = {// IP白名单 (没有配置或者为空，则允许所有访问)@WebInitParam(name ="allow", value ="192.168.1.72,127.0.0.1"),// IP黑名单 (存在共同时，deny优先于allow)@WebInitParam(name ="deny", value ="192.168.1.73"),// 用户名@WebInitParam(name ="loginUsername", value ="admin"),// 密码@WebInitParam(name ="loginPassword", value ="admin"),// 禁用HTML页面上的“Reset All”功能@WebInitParam(name ="resetEnable", value ="false") })public class DruidStatViewServlet extends StatViewServlet {}
// druid过滤器. @WebFilter(filterName ="druidWebStatFilter", urlPatterns ="/*", initParams = {// 忽略资源@WebInitParam(name ="exclusions", value ="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*") })publicclassDruidStatFilterextendsWebStatFilter{}
等应用启动后，可以访问地址：http://localhost:8080/druid/，登录之后，即可查看数据源及SQL统计等。
效果图如下：
