配置方法
基于当前的1.5.2.RELEASE的Spring Boot。
依照官方文档,如果增加了如下依赖的配置,或者类路径中存在spring-boot-starter-jdbc的jar,那么已默认启用了数据库链接池。
org.springframework.boot
spring-boot-starter-jdbc
Spring Boot选择数据库链接池实现的判断逻辑:
检查Tomcat的数据库链接池实现是否可用,如可用,则启用。使用spring.datasource.tomcat.*可以控制链接池的行为。
检查HikariCP是否可用,如可用,则启用。使用spring.datasource.hikari.*可以控制链接池的行为。
检查Commons DBCP是否可用,如可用,则启用;但Spring Boot不建议在生产环境使用该链接池的实现。
检查Commons DBCP2是否可用,如可用,则启用。使用spring.datasource.dbcp2.*可以控制链接池的行为。
使用tomcat-jdbc时,可在application.yml增加配置项spring.datasource.tomcat.*来控制链接池的行为。比如如下配置。
spring:
datasource:
url: jdbc:mysql://localhost:3306/jackieathome?useSSL=false
username: root
password: mypassword
# 6.x版本的MySQL JDBC驱动类为com.mysql.cj.jdbc.Driver
# 5.X版本的MySQL JDBC驱动类为com.mysql.jdbc.Driver
driver-class-name: com.mysql.cj.jdbc.Driver
tomcat:
max-wait: 10000
max-active: 30
test-on-borrow: true
# 传递MySQL JDBC特有的参数
db-properties:
logger: net.jackieathome.db.customized.MySQLLogger
gatherPerfMetrics: 'true'
profileSQL: 'true'
reportMetricsIntervalMillis: '60000'
logSlowQueries: 'true'
explainSlowQueries: 'true'
logging:
level:
# 关闭其它软件的日志,减少干扰
org: ERROR
net: ERROR
com: ERROR
# 开启MySQL JDBC驱动的日志
MySQL: DEBUG
依据tomcat-jdbc的文档,如需要向数据库的JDBC驱动传入控制参数,可以使用db-properties字段。需要注意的是,当使用MySQL驱动时,控制参数的值需要强制转换为字符串,否则创建数据库链接时会报错。配置方法如上述样例中的 reportMetricsIntervalMillis: '60000'和logSlowQueries: 'true'。
依照MySQL JDBC驱动文档,可以配置一个日志记录器,用于记录其工作时的输出,如下是实现样例。
package net.jackieathome.db.customized;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class MySQLLogger implements com.mysql.cj.api.log.Log{
private static Logger LOG;
public MySQLLogger(String name){
LOG = LoggerFactory.getLogger(name);
}
@Override
public boolean isDebugEnabled(){
return LOG.isDebugEnabled();
}
@Override
public boolean isErrorEnabled(){
return LOG.isErrorEnabled();
}
@Override
public boolean isFatalEnabled(){
return LOG.isErrorEnabled();
}
@Override
public boolean isInfoEnabled(){
return LOG.isInfoEnabled();
}
@Override
public boolean isTraceEnabled(){
return LOG.isTraceEnabled();
}
@Override
public boolean isWarnEnabled(){
return LOG.isWarnEnabled();
}
@Override
public void logDebug(Object msg){
LOG.debug("{}", msg);
}
@Override
public void logDebug(Object msg, Throwable thrown){
LOG.debug("{}", msg, thrown);
}
@Override
public void logError(Object msg){
LOG.error("{}", msg);
}
@Override
public void logError(Object msg, Throwable thrown){
LOG.error("{}", msg, thrown);
}
@Override
public void logFatal(Object msg){
LOG.error("{}", msg);
}
@Override
public void logFatal(Object msg, Throwable thrown){
LOG.error("{}", msg, thrown);
}
@Override
public void logInfo(Object msg){
LOG.info("{}", msg);
}
@Override
public void logInfo(Object msg, Throwable thrown){
LOG.info("{}", msg, thrown);
}
@Override
public void logTrace(Object msg){
LOG.trace("{}", msg);
}
@Override
public void logTrace(Object msg, Throwable thrown){
LOG.trace("{}", msg, thrown);
}
@Override
public void logWarn(Object msg){
LOG.warn("{}", msg);
}
@Override
public void logWarn(Object msg, Throwable thrown){
LOG.warn("{}", msg, thrown);
}
}
同时修改application.yml,增加相应的日志配置,如下。
logging:
level:
# 开启MySQL JDBC驱动的日志
MySQL: DEBUG
如下是MySQL JDBC驱动输出的样例日志。
2017-04-16 00:51:32.626 QUERY created: Sun Apr 16 00:51:32 CST 2017 duration: 0 connection: 93 statement: 1 resultset: 1 message: /* mysql-connector-java-6.0.6 ( Revision: 3dab84f4d9bede3cdd14d57b99e9e98a02a5b97d ) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
2017-04-16 00:51:32.629 FETCH created: Sun Apr 16 00:51:32 CST 2017 duration: 36 connection: 93 statement: 1 resultset: 1
2017-04-16 00:51:32.639 QUERY created: Sun Apr 16 00:51:32 CST 2017 duration: 1 connection: 93 statement: 999 resultset: 0 message: SET NAMES latin1
2017-04-16 00:51:32.640 FETCH created: Sun Apr 16 00:51:32 CST 2017 duration: 0 connection: 93 statement: 999 resultset: 0
2017-04-16 00:51:32.642 QUERY created: Sun Apr 16 00:51:32 CST 2017 duration: 2 connection: 93 statement: 999 resultset: 0 message: SET character_set_results = NULL
2017-04-16 00:51:32.643 FETCH created: Sun Apr 16 00:51:32 CST 2017 duration: 0 connection: 93 statement: 999 resultset: 0
2017-04-16 00:51:32.645 QUERY created: Sun Apr 16 00:51:32 CST 2017 duration: 0 connection: 93 statement: 999 resultset: 0 message: SET autocommit=1
2017-04-16 00:51:32.646 FETCH created: Sun Apr 16 00:51:32 CST 2017 duration: 0 connection: 93 statement: 999 resultset: 0
2017-04-16 00:51:32.667 QUERY created: Sun Apr 16 00:51:32 CST 2017 duration: 1 connection: 94 statement: 2 resultset: 2 message: /* mysql-connector-java-6.0.6 ( Revision: 3dab84f4d9bede3cdd14d57b99e9e98a02a5b97d ) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
参考资料
Spring的链接池配置
Spring Boot的链接池的配置
Tomcat的链接池的配置
MySQL JDBC
若非注明,均为原创,欢迎转载,转载请注明来源:Spring Boot配置数据库链接池