Of Stack

Spring Boot database link pool configuration method

  • 2020-06-23 00:28:02
  • OfStack

Configuration method

Spring Boot based on the current 1.5.2.RELEASE.

According to the official documentation, database link pooling is enabled by default if the following dependent configuration is added, or if jar of ES8en-ES9en-ES10en-ES11en exists in the classpath. 


 <dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
 </dependency>

Spring Boot Selection of database link pool realization judgment logic:

1. Check if the database link pool implementation of Tomcat is available, and if so, enable it. You can control the behavior of the link pool using ES21en. datasource. tomcat.*.

2. Check that HikariCP is available and, if so, enable it. You can control the behavior of the link pool using spring.datasource.hikari.*.

3. Check whether Commons DBCP is available, and if so, enable it; However, Spring Boot does not recommend using this link pool implementation in a production environment.

4. Check that Commons DBCP2 is available and, if so, enable it. You can control the behavior of the link pool using ES40en.datasource.dbcp2.*.

When using ES45en-ES46en, add the configuration item spring.datasource.tomcat.* to control the behavior of the link pool. Consider the following configuration.

spring: 


datasource:
  url: jdbc:mysql://localhost:3306/jackieathome?useSSL=false
  username: root
  password: mypassword
  # 6.x Version of the MySQL JDBC Driving classes for com.mysql.cj.jdbc.Driver
  # 5.X Version of the MySQL JDBC Driving classes for com.mysql.jdbc.Driver
  driver-class-name: com.mysql.cj.jdbc.Driver
  tomcat:
   max-wait: 10000
   max-active: 30
   test-on-borrow: true
   #  pass MySQL JDBC Specific parameter 
   db-properties:
    logger: net.jackieathome.db.customized.MySQLLogger
    gatherPerfMetrics: 'true'
    profileSQL: 'true'
    reportMetricsIntervalMillis: '60000'
    logSlowQueries: 'true'
    explainSlowQueries: 'true'

logging: 


 level:
  #  Turn off other software logs to reduce interference 
  org: ERROR
  net: ERROR
  com: ERROR
  #  open MySQL JDBC Driven log 
  MySQL: DEBUG

spring. datasource.tomcat.* refer to the official documents of Apache Tomcat 8.5-ES69en Tomcat JDBC or Apache Tomcat 8.0-ES76en JDBC Connection Pool for configuration items above.

According to the ES83en-ES84en documentation, if you need to pass control parameters to the JDBC driver of the database, you can use the ES86en-ES87en field. Note that when using the MySQL driver, the value of the control parameter needs to be cast to a string, otherwise an error will be reported when creating the database link. The configuration methods are reportMetricsIntervalMillis: '60000' and logSlowQueries: 'true' in the above sample.

According to the MySQL JDBC driver documentation, you can configure a logger to record its output at work, as shown in the implementation sample below. 


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);
  }
}

Also modify application.yml to add the corresponding log configuration as shown below. 


logging:
  level:
    #  open MySQL JDBC Driven log 
    MySQL: DEBUG

Here is a sample log of the MySQL JDBC driver output.

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

Related articles: