Hikari 数据源配置

Hikari的配置

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://马赛克xxx:3306/database?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8
    password: password
    username: username
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      pool-name: DataSource
      auto-commit: true
      read-only: false
      # 最小空闲连接数，被注释了，不设置该属性
#      minimum-idle: 1
      # 最大连接数。缺省值：10；推荐的公式：((core_count * 2) + effective_spindle_count)
      maximum-pool-size: 10
      # 空闲连接状态的最大时长（毫秒），超时则被释放（retired），缺省:10分钟
      idle-timeout: 600000
      # 连接存活最大时长（毫秒），超时而且没被使用则被释放（retired），缺省:30分钟，建议设置比数据库超时时长少30秒，参考MySQL wait_timeout参数（show variables like '%timeout%';）
      max-lifetime: 1800000
      # 验证连接是否有效的最大等待响应时间
      validation-timeout: 5000
      # 连接测试，如果是JDBC4驱动程序，不要设置这个属性
      # connection-test-query: SELECT 1
      # 连接到数据库时等待的最长时间（秒）
      login-timeout: 5
      # 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒
      connection-timeout: 20000

注意点

• maxLifetime != 0 且 maxLifetime < 30 s，将重置成它的默认值，30分钟

private static final long MAX_LIFETIME = MINUTES.toMillis(30);

if (maxLifetime != 0 && maxLifetime < SECONDS.toMillis(30)) {
         LOGGER.warn("{} - maxLifetime is less than 30000ms, setting to default {}ms.", poolName, MAX_LIFETIME);
         maxLifetime = MAX_LIFETIME;
      }

• idleTimeout + 1秒 不要超过 maxLifetime。否则 idleTimeout 将失效，源码如下：

if (idleTimeout + SECONDS.toMillis(1) > maxLifetime && maxLifetime > 0) {
         LOGGER.warn("{} - idleTimeout is close to or more than maxLifetime, disabling it.", poolName);
         idleTimeout = 0;
      }

• idleTimeout != 0 且 idleTimeout < 10 秒，则还原成默认值。

注意这里的默认值是10分钟（IDLE_TIMEOUT = MINUTES.toMillis(10)），网上很多文章说是10秒钟，不知道怎么抄来的。

private static final long IDLE_TIMEOUT = MINUTES.toMillis(10);

if (idleTimeout != 0 && idleTimeout < SECONDS.toMillis(10)) {
         LOGGER.warn("{} - idleTimeout is less than 10000ms, setting to default {}ms.", poolName, IDLE_TIMEOUT);
         idleTimeout = IDLE_TIMEOUT;
      }

• connectionTimeout 和 validationTimeout 时间设置小于250 ms，都会重置成它们各自的默认值 30s 和 5s

private static final long CONNECTION_TIMEOUT = SECONDS.toMillis(30);
if (connectionTimeout < 250) {
         LOGGER.warn("{} - connectionTimeout is less than 250ms, setting to {}ms.", poolName, CONNECTION_TIMEOUT);
         connectionTimeout = CONNECTION_TIMEOUT;
      }

private static final long VALIDATION_TIMEOUT = SECONDS.toMillis(5);
      if (validationTimeout < 250) {
         LOGGER.warn("{} - validationTimeout is less than 250ms, setting to {}ms.", poolName, VALIDATION_TIMEOUT);
         validationTimeout = VALIDATION_TIMEOUT;
      }

 

• 最近项目中出现无法获取数据库连接池的错误

2020-08-05 00:00:54.024 - WARN 6794 [pool-1-thread-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: null
2020-08-05 00:00:54.024 -ERROR 6794 [pool-1-thread-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : HikariCP_Mysql - Connection is not available, request timed out after 60000ms.
2020-08-05 00:00:54.025 -ERROR 6794 [pool-1-thread-3] o.s.s.s.TaskUtils$LoggingErrorHandler    : Unexpected error occurred in scheduled task.
org.springframework.dao.DataAccessResourceFailureException: Unable to acquire JDBC Connection; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:275)
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:253)
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:527)
        at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
        at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
        at com.huarjk.crm.dao.CommonDao$$EnhancerBySpringCGLIB$$957aeb2d.templateJpqlQuery(<generated>)
        at com.huarjk.crm.service.SmsMarketingService.queryUnSyncCouponMarketing(SmsMarketingService.java:367)
        at com.huarjk.crm.service.SmsMarketingService$$FastClassBySpringCGLIB$$6a21cb1d.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684)
        at com.huarjk.crm.service.SmsMarketingService$$EnhancerBySpringCGLIB$$2777a045.queryUnSyncCouponMarketing(<generated>)
        at com.huarjk.crm.service.schedule.WriteCouponToErpOracleSchedule.syncCoupon(WriteCouponToErpOracleSchedule.java:56)
        at sun.reflect.GeneratedMethodAccessor184.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84)
        at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection

项目中使用的是SpringDataJpa，JPA选择Hibernate实现版本，采用Hikari数据连接池。解决办法如下：

spring:
  jpa:
    properties:
      - hibernate.connection.release_mode: after_transaction