Druid声称是Java语言中最好的数据库连接池,Druid能够提供强大的监控和扩展功能。spring boot starter自动装配组件,简化组件引入的开发工作量,所以Druid推出了druid-spring-boot-starter。
pom.xml引入依赖包(parent中声明了spring-boot-starter-parent,所以可不声明版本号):
com.alibaba
druid-spring-boot-starter
推荐的配置:
spring.datasource.druid.url=jdbc:mysql://127.0.0.1:3306/yzh?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.druid.username=root
spring.datasource.druid.password=root
spring.datasource.druid.max-active=20
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.min-evictable-idle-time-millis=300000
spring.datasource.druid.max-wait=60000
spring.datasource.druid.validation-query=select 1
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.time-between-eviction-runs-millis=60000
开发环境可打印执行的sql,方便开发、排查问题,添加配置:
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
配置详解
# 数据库地址
spring.datasource.druid.url=jdbc:mysql://127.0.0.1:3306/yzh?useUnicode=true&characterEncoding=utf-8&useSSL=false
# 数据库用户名
spring.datasource.druid.username=root
# 数据库密码
spring.datasource.druid.password=root
# 数据库连接池最大值
spring.datasource.druid.max-active=20
# 数据库连接池初始值
spring.datasource.druid.initial-size=5
# 数据库连接池最小空闲值
spring.datasource.druid.min-idle=5
# 池中空闲连接大于minIdle且连接空闲时间大于该值,则关闭该连接,单位毫秒(5分钟,默认30分钟)
spring.datasource.druid.min-evictable-idle-time-millis=300000
# 获取连接时最大等待时间,单位毫秒(1分钟)
spring.datasource.druid.max-wait=60000
# 检测连接是否有效时执行的sql命令
spring.datasource.druid.validation-query=select 1
# 借用连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能
spring.datasource.druid.test-on-borrow=false
# 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能
spring.datasource.druid.test-on-return=false
# 连接空闲时检测,如果连接空闲时间大于timeBetweenEvictionRunsMillis指定的毫秒,执行validationQuery指定的SQL来检测连接是否有效
spring.datasource.druid.test-while-idle=true
# 空闲连接检查、废弃连接清理、空闲连接池大小调整的操作时间间隔,单位是毫秒(1分钟)
spring.datasource.druid.time-between-eviction-runs-millis=60000
1. testOnBorrow和testOnReturn在生产环境一般是不开启的,主要是性能考虑。失效连接主要通过testWhileIdle保证,如果获取到了不可用的数据库连接,一般由应用处理异常。
2. druid 1.1.10中的bug导致开启 testOnReturn和testOnBorrow 各有5倍的性能差距(1.1.24版本已经解决了该问题),试验发现性能大概损耗不到10%。
3. 如果你的应用不需要考虑高并发下的性能差距,且想要每次都获取到有效的连接,那就将 testOnReturn和testOnBorrow 都设置为true。
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
spring.datasource.druid.filters=stat,wall
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
spring.datasource.druid.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
#是否启用StatFilter默认值false,用于采集 web-jdbc 关联监控的数据。
spring.datasource.druid.web-stat-filter.enabled=true
#需要监控的 url
spring.datasource.druid.web-stat-filter.url-pattern=/*
#排除一些静态资源,以提高效率
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
#是否启用StatViewServlet(监控页面)默认值为false(考虑到安全问题默认并未启动,如需启用建议设置密码或白名单以保障安全)
spring.datasource.druid.stat-view-servlet.enabled=true
#内置的监控页面地址,例如 /druid/*,则内置监控页面的首页是 /druid/index.html
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
#是否允许清空统计数据
spring.datasource.druid.stat-view-servlet.reset-enable=false
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin
添加了监控配置后,启动项目访问http://localhost:8080/druid/,输入上面配置的用户名admin、密码admin,可看到如下监控页面:
官方文档:druid/druid-spring-boot-starter at master · alibaba/druid · GitHub
https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98
4.0.0
org.springframework.boot
spring-boot-starter-parent
2.4.6
org.example
yzh-maven
1.0-SNAPSHOT
8
8
1.8
2.4.6
org.springframework.boot
spring-boot-starter
org.springframework.boot
spring-boot-starter-test
test
org.springframework.boot
spring-boot-starter-web
org.mybatis.spring.boot
mybatis-spring-boot-starter
2.2.0
mysql
mysql-connector-java
org.apache.commons
commons-lang3
3.12.0
com.alibaba
druid-spring-boot-starter
org.springframework.boot
spring-boot-maven-plugin
spring boot2 默认连接池是HikariCP,号称是性能最好的连接池,不过国内使用较多的是阿里开源的druid连接池,在阿里的诸多项目中经过实践验证。
先回忆一下spring boot自动装配的原理:springboot是在SpringApplication.run(…)容器启动时执行了selectImports()方法,找到自动配置类的全限类名去加载对应的class,然后将自动配置类注入Spring容器中,即通过加载注入全限类名对应的自动配置类来完成容器启动时组件的自动装载。
那么,druid-spring-boot-starter的自动配置类是谁?DruidDataSourceAutoConfigure。
@Configuration
@ConditionalOnClass({DruidDataSource.class})
@AutoConfigureBefore({DataSourceAutoConfiguration.class})
@EnableConfigurationProperties({DruidStatProperties.class, DataSourceProperties.class})
@Import({DruidSpringAopConfiguration.class, DruidStatViewServletConfiguration.class, DruidWebStatFilterConfiguration.class, DruidFilterConfiguration.class})
public class DruidDataSourceAutoConfigure {
private static final Logger LOGGER = LoggerFactory.getLogger(DruidDataSourceAutoConfigure.class);
public DruidDataSourceAutoConfigure() {
}
@Bean(
initMethod = "init"
)
@ConditionalOnMissingBean
public DataSource dataSource() {
LOGGER.info("Init DruidDataSource");
return new DruidDataSourceWrapper();
}
}
加载DruidDataSourceAutoConfigure注入spring容器。
类路径上存在DruidDataSource的class,才会加载当前类。
public class DruidDataSource extends DruidAbstractDataSource implements DruidDataSourceMBean, ManagedDataSource, Referenceable, Closeable, Cloneable, ConnectionPoolDataSource, MBeanRegistration {}
看一下Druid的默认连接池类DruidAbstractDataSource,是所有连接池属性默认值声明的地方:
public DruidAbstractDataSource(boolean lockFair) {
this.validationQuery = DEFAULT_VALIDATION_QUERY;
this.validationQueryTimeout = -1;
this.testOnBorrow = false;
this.testOnReturn = false;
this.testWhileIdle = true;
this.poolPreparedStatements = false;
this.sharePreparedStatements = false;
this.maxPoolPreparedStatementPerConnectionSize = 10;
this.inited = false;
this.initExceptionThrow = true;
this.logWriter = new PrintWriter(System.out);
this.filters = new CopyOnWriteArrayList();
this.clearFiltersEnable = true;
this.exceptionSorter = null;
this.maxWaitThreadCount = -1;
this.accessToUnderlyingConnectionAllowed = true;
this.timeBetweenEvictionRunsMillis = 60000L;
this.numTestsPerEvictionRun = 3;
this.minEvictableIdleTimeMillis = 1800000L;
this.maxEvictableIdleTimeMillis = 25200000L;
this.keepAliveBetweenTimeMillis = 120000L;
this.phyTimeoutMillis = -1L;
this.phyMaxUseCount = -1L;
this.removeAbandonedTimeoutMillis = 300000L;
this.maxOpenPreparedStatements = -1;
this.timeBetweenConnectErrorMillis = 500L;
this.validConnectionChecker = null;
this.activeConnections = new IdentityHashMap();
this.connectionErrorRetryAttempts = 1;
this.breakAfterAcquireFailure = false;
this.transactionThresholdMillis = 0L;
this.createdTime = new Date();
this.errorCount = 0L;
this.dupCloseCount = 0L;
this.startTransactionCount = 0L;
this.commitCount = 0L;
this.rollbackCount = 0L;
this.cachedPreparedStatementHitCount = 0L;
this.preparedStatementCount = 0L;
this.closedPreparedStatementCount = 0L;
this.cachedPreparedStatementCount = 0L;
this.cachedPreparedStatementDeleteCount = 0L;
this.cachedPreparedStatementMissCount = 0L;
this.transactionHistogram = new Histogram(new long[]{1L, 10L, 100L, 1000L, 10000L, 100000L});
this.dupCloseLogEnable = false;
this.executeCount = 0L;
this.executeQueryCount = 0L;
this.executeUpdateCount = 0L;
this.executeBatchCount = 0L;
this.isOracle = false;
this.isMySql = false;
this.useOracleImplicitCache = true;
this.activeConnectionLock = new ReentrantLock();
this.createErrorCount = 0;
this.creatingCount = 0;
this.directCreateCount = 0;
this.createCount = 0L;
this.destroyCount = 0L;
this.createStartNanos = 0L;
this.useUnfairLock = null;
this.useLocalSessionState = true;
this.statLogger = new DruidDataSourceStatLoggerImpl();
this.asyncCloseConnectionEnable = false;
this.maxCreateTaskCount = 3;
this.failFast = false;
this.failContinuous = 0;
this.failContinuousTimeMillis = 0L;
this.initVariants = false;
this.initGlobalVariants = false;
this.onFatalError = false;
this.onFatalErrorMaxActive = 0;
this.fatalErrorCount = 0;
this.fatalErrorCountLastShrink = 0;
this.lastFatalErrorTimeMillis = 0L;
this.lastFatalErrorSql = null;
this.lastFatalError = null;
this.connectionIdSeed = 10000L;
this.statementIdSeed = 20000L;
this.resultSetIdSeed = 50000L;
this.transactionIdSeed = 60000L;
this.metaDataIdSeed = 80000L;
this.lock = new ReentrantLock(lockFair);
this.notEmpty = this.lock.newCondition();
this.empty = this.lock.newCondition();
}
加载当前类之后再去加载DataSourceAutoConfiguration类。
那为什么呢?看一下DataSourceAutoConfiguration类的PooledDataSourceConfiguration:
@Configuration(
proxyBeanMethods = false
)
@Conditional({DataSourceAutoConfiguration.PooledDataSourceCondition.class})
@ConditionalOnMissingBean({DataSource.class, XADataSource.class})
@Import({Hikari.class, Tomcat.class, Dbcp2.class, OracleUcp.class, Generic.class, DataSourceJmxConfiguration.class})
protected static class PooledDataSourceConfiguration {
protected PooledDataSourceConfiguration() {
}
}
Hikari.class:
@Configuration(
proxyBeanMethods = false
)
@ConditionalOnClass({HikariDataSource.class})
@ConditionalOnMissingBean({DataSource.class})
@ConditionalOnProperty(
name = {"spring.datasource.type"},
havingValue = "com.zaxxer.hikari.HikariDataSource",
matchIfMissing = true
)
static class Hikari {
Hikari() {
}
@Bean
@ConfigurationProperties(
prefix = "spring.datasource.hikari"
)
HikariDataSource dataSource(DataSourceProperties properties) {
HikariDataSource dataSource = (HikariDataSource)DataSourceConfiguration.createDataSource(properties, HikariDataSource.class);
if (StringUtils.hasText(properties.getName())) {
dataSource.setPoolName(properties.getName());
}
return dataSource;
}
}
PooledDataSourceConfiguration的@ConditionalOnMissingBean({ DataSource.class, XADataSource.class }) 表示如果存在数据源就不会被加载,相应Import里面默认的Hikari连接池就不会被spring加载,这样就避免了数据源的冲突,这个时候可能就会有人问了,不是可以配置多数据源吗?对的,可以,这个只是springboot将自己的数据源关闭掉,并不会影响你自定义的。
总结:自定义的DataSource优先级大于默认的DataSource(默认的DataSource使用的是HikariDataSource)。
将DruidStatProperties、DataSourceProperties 2个配置类注入spring容器。
DruidStatProperties:监控有关配置类。
DataSourceProperties:数据源配置类。
@Import注解就是之前xml配置中的import标签,可以用于依赖包、三方包中bean的配置和加载。这里是Druid监控有关配置类的加载。
@Bean(
initMethod = "init"
)
@ConditionalOnMissingBean
public DataSource dataSource() {
LOGGER.info("Init DruidDataSource");
return new DruidDataSourceWrapper();
}
创建DruidDataSourceWrapper(DruidDataSource的包装类),并且在创建Bean之后执行init方法。init方法在父类DruidDataSource中存在。
// 注入spring.datasource.druid的配置
@ConfigurationProperties("spring.datasource.druid")
public class DruidDataSourceWrapper extends DruidDataSource implements InitializingBean {
@Autowired
private DataSourceProperties basicProperties;
public DruidDataSourceWrapper() {
}
// 如果没有配置spring.datasource.druid.username/password/url/driverClassName,
// 而配置了spring.datasource.username/password/url/driverClassName,那么就使用spring.datasource.username/password/url/driverClassName的属性值
public void afterPropertiesSet() throws Exception {
if (super.getUsername() == null) {
super.setUsername(this.basicProperties.determineUsername());
}
if (super.getPassword() == null) {
super.setPassword(this.basicProperties.determinePassword());
}
if (super.getUrl() == null) {
super.setUrl(this.basicProperties.determineUrl());
}
if (super.getDriverClassName() == null) {
super.setDriverClassName(this.basicProperties.getDriverClassName());
}
}
...
}
@ConfigurationProperties(
prefix = "spring.datasource"
)
public class DataSourceProperties implements BeanClassLoaderAware, InitializingBean {}