Spring Boot 连接池

2022-12-11 20:33:18

配置方法

基于当前的1.5.2.RELEASE的Spring Boot。依照官方文档，如果增加了如下依赖的配置，或者类路径中存在spring-boot-starter-jdbc的jar，那么已默认启用了数据库链接池。

    <dependency>

        <groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-jdbc</artifactId>

    </dependency>

Tomcat7之前，Tomcat本质应用了DBCP连接池技术来实现的JDBC数据源，但在Tomcat7之后，Tomcat提供了新的JDBC连接池方案，作为DBCP的替换或备选方案，解决了许多之前使用DBCP的不利之处，并提高了性能。详细请参考：http://wiki.jikexueyuan.com/project/tomcat/tomcat-jdbc-pool.html

Spring Boot为我们准备了最佳的数据库连接池方案，只需要在属性文件（例如application.properties）中配置需要的连接池参数即可。
我们使用Tomcat数据源连接池，需要依赖tomcat-jdbc，只要应用中添加了spring-boot-starter-jdbc 或 spring-boot-starter-data-jpa依赖，则无需担心这点，因为将会自动添加 tomcat-jdbc 依赖。

Spring Boot选择数据库链接池实现的判断逻辑：

检查Tomcat的数据库链接池实现是否可用，如可用，则启用。使用spring.datasource.tomcat.*可以控制链接池的行为。
检查HikariCP是否可用，如可用，则启用。使用spring.datasource.hikari.*可以控制链接池的行为。
检查Commons DBCP是否可用，如可用，则启用；但Spring Boot不建议在生产环境使用该链接池的实现。
检查Commons DBCP2是否可用，如可用，则启用。使用spring.datasource.dbcp2.*可以控制链接池的行为。

默认连接池规则

在引入spring-boot-starter-jdbc后，内部包含了tomcat-jdbc包，里面有tomcat连接池.然后通过自动配置DataSourceAutoConfigurer创建DataSource对象。

SpringBoot创建默认DataSource时，规则如下：

优先寻找创建Tomcat连接池
如果没有Tomcat连接池，会查找创建HikariCP
如果没有HikariCP连接池，会查找创建dbcp
如果没有dbcp连接池，会查找创建dbcp2

可以使用spring.datasource.type属性指定连接池类型

spring.datasource.type=org.apache.commons.dbcp.BasicDataSource

使用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

上述spring.datasource.tomcat.*代表的配置项，可参考tomcat-jdbc的官方文档Apache Tomcat 8.5 - The Tomcat JDBC Connection Pool或者Apache Tomcat 8.0 - The Tomcat JDBC Connection Pool。 依据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

码农公寓

配置方法

相关文章