at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
... 56 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
at sun.reflect.GeneratedConstructorAccessor276.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1184)
at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1179)
at com.zaxxer.hikari.pool.PoolBase.setNetworkTimeout(PoolBase.java:560)
at com.zaxxer.hikari.pool.PoolBase.isConnectionAlive(PoolBase.java:173)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:186)
... 60 more

jdbcUrl: jdbc:mysql://data-mysql-cantor-read.***.***:5002/cantor?useUnicode=true&characterEncoding=utf-8&useSSL=false
# hikari配置中的所有时间单位均为毫秒, 具体可参考：https://github.com/brettwooldridge/HikariCP
type: com.zaxxer.hikari.HikariDataSource
# 连接获取超时时间
connectionTimeout: 10000
# 连接最大生命周期: 半小时，保证比wait_timeout小即可。线上cantor库的wait_timeout=8小时
maxLifetime: 1800000
# minIdle以外的空闲连接存活时间，半小时
idleTimeout: 1800000
# 最小空闲连接数量
minimumIdle: 2
# 最大连接数量，默认为10
maximumPoolSize: 10
poolName: cantor_read_connenction_pool

//**
 * HouseKeeper这个任务类用来回收空闲连接，并保持连接数不少于minimunIdle参数
 * The house keeping task to retire and maintain minimum idle connections.
 */
// 
private final class HouseKeeper implements Runnable
{
   private volatile long previous = plusMillis(currentTime(), -housekeepingPeriodMs);

   @Override
   public void run()
   {
      try {
         // refresh values in case they changed via MBean
         connectionTimeout = config.getConnectionTimeout();
         validationTimeout = config.getValidationTimeout();
         leakTaskFactory.updateLeakDetectionThreshold(config.getLeakDetectionThreshold());
         catalog = (config.getCatalog() != null && !config.getCatalog().equals(catalog)) ? config.getCatalog() : catalog;

         final long idleTimeout = config.getIdleTimeout();
         final long now = currentTime();

         // 处理时钟回退问题
         // Detect retrograde time, allowing +128ms as per NTP spec.
         if (plusMillis(now, 128) < plusMillis(previous, housekeepingPeriodMs)) {
            logger.warn("{} - Retrograde clock change detected (housekeeper delta={}), soft-evicting connections from pool.",
                        poolName, elapsedDisplayString(previous, now));
            previous = now;
            softEvictConnections();
            return;
         }
         else if (now > plusMillis(previous, (3 * housekeepingPeriodMs) / 2)) {
            // No point evicting for forward clock motion, this merely accelerates connection retirement anyway
            logger.warn("{} - Thread starvation or clock leap detected (housekeeper delta={}).", poolName, elapsedDisplayString(previous, now));
         }

         previous = now;

         String afterPrefix = "Pool ";
         // 关键判断条件，如果idleTimeout参数大于0，且minimumIdle < maximumPoolSize，则进行空闲连接回收
         if (idleTimeout > 0L && config.getMinimumIdle() < config.getMaximumPoolSize()) {
            logPoolState("Before cleanup ");
            afterPrefix = "After cleanup  ";

            final List<PoolEntry> notInUse = connectionBag.values(STATE_NOT_IN_USE);
            // 判断最多需要移除几个连接
            int toRemove = notInUse.size() - config.getMinimumIdle();
            for (PoolEntry entry : notInUse) {
               // 如果连接的上次使用时间距今是否已经过去了idleTimeout
               if (toRemove > 0 && elapsedMillis(entry.lastAccessed, now) > idleTimeout && connectionBag.reserve(entry)) {
                  // 关闭连接
                  closeConnection(entry, "(connection has passed idleTimeout)");
                  toRemove--;
               }
            }
         }
         // 记录日志
         logPoolState(afterPrefix);

         // 填充连接池，维持最小连接数
         fillPool(); // Try to maintain minimum connections
      }
      catch (Exception e) {
         logger.error("Unexpected exception in housekeeping task", e);
      }
   }
}

/** 如果maxLifetime配置了（并且需要大于10000ms）,创建一个单次回收任务，执行时机为maxLifetime前后浮动2.5%，避免瞬时回收大量连接
 * Creating new poolEntry.  If maxLifetime is configured, create a future End-of-life task with 2.5% variance from
 * the maxLifetime time to ensure there is no massive die-off of Connections in the pool.
 */
private PoolEntry createPoolEntry()
{
   try {
      // 创建一个poolEntry对象，该对象是实际连接的包装
      final PoolEntry poolEntry = newPoolEntry();
      // 获取maxLifetime配置值
      final long maxLifetime = config.getMaxLifetime();
      if (maxLifetime > 0) {
         // variance up to 2.5% of the maxlifetime
         // 回收时间为maxLifetime 前后浮动 2.5%
         final long variance = maxLifetime > 10_000 ? ThreadLocalRandom.current().nextLong( maxLifetime / 40 ) : 0;
         final long lifetime = maxLifetime - variance;
         // 在schedule线程池中提交一个单次执行任务，用于回收该连接，maxLifeTime后会标记该连接为待清除
         poolEntry.setFutureEol(houseKeepingExecutorService.schedule(
            () -> {
               if (softEvictConnection(poolEntry, "(connection has passed maxLifetime)", false /* not owner */)) {
                  // 如果回收成功，需要判断需不需要创建新的连接来替代
                  addBagItem(connectionBag.getWaitingThreadCount());
               }
            },
            lifetime, MILLISECONDS));
      }
  
      return poolEntry;
   }
   catch (ConnectionSetupException e) {
      if (poolState == POOL_NORMAL) { // we check POOL_NORMAL to avoid a flood of messages if shutdown() is running concurrently
         logger.error("{} - Error thrown while acquiring connection from data source", poolName, e.getCause());
         lastConnectionFailure.set(e);
      }
   }
   catch (Exception e) {
      if (poolState == POOL_NORMAL) { // we check POOL_NORMAL to avoid a flood of messages if shutdown() is running concurrently
         logger.debug("{} - Cannot acquire connection from data source", poolName, e);
      }
   }
  
   return null;
}

 /**
 * "Soft" evict a Connection (/PoolEntry) from the pool.  If this method is being called by the user directly
 * through {@link com.zaxxer.hikari.HikariDataSource#evictConnection(Connection)} then {@code owner} is {@code true}.
 *
 * If the caller is the owner, or if the Connection is idle (i.e. can be "reserved" in the {@link ConcurrentBag}),
 * then we can close the connection immediately.  Otherwise, we leave it "marked" for eviction so that it is evicted
 * the next time someone tries to acquire it from the pool.
 *
 * @param poolEntry the PoolEntry (/Connection) to "soft" evict from the pool
 * @param reason the reason that the connection is being evicted
 * @param owner true if the caller is the owner of the connection, false otherwise
 * @return true if the connection was evicted (closed), false if it was merely marked for eviction
 */
private boolean softEvictConnection(final PoolEntry poolEntry, final String reason, final boolean owner)
{
    // 先将poolEntry标记为待清除，待清除的连接不会被hikari取到并使用
   poolEntry.markEvicted();
   // 先从connectionBag中回收该对象
   if (owner || connectionBag.reserve(poolEntry)) {
      // 再物理关闭连接
      closeConnection(poolEntry, reason);
      return true;
   }

   return false;
}

 /**
 * The method is used to make an item in the bag "unavailable" for
 * borrowing.  It is primarily used when wanting to operate on items
 * returned by the <code>values(int)</code> method.  Items that are
 * reserved can be removed from the bag via <code>remove(T)</code>
 * without the need to unreserve them.  Items that are not removed
 * from the bag can be make available for borrowing again by calling
 * the <code>unreserve(T)</code> method.
 *
 * @param bagEntry the item to reserve
 * @return true if the item was able to be reserved, false otherwise
 */
public boolean reserve(final T bagEntry)
{    
   // CAS操作，替换poolEntry的状态为RESERVED
   return bagEntry.compareAndSet(STATE_NOT_IN_USE, STATE_RESERVED);
}

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

maxLifetime: 1800000
idleTimeout: 1800000

# 30秒，maxLifetime的最小值
maxLifetime: 30001
# 30秒
idleTimeout: 30001
minimumIdle: 1
maximumPoolSize: 2

@Component
public class HikariTestService {
    // case中的mapper，进行真是的查库操作，占用数据库连接
    @Autowired
    private CantorTaskMapper cantorTaskMapper;  
    
    // 注入case中的hikari数据源，需要通过该数据源拿到具体的连接池        
    @Resource(name = "cantor")
    private HikariDataSource hikariDataSource;
    
    // 线程池，用于触发查库操作
    ExecutorService executorService = Executors.newFixedThreadPool(10);

    public String test() throws InterruptedException {
        // 提前触发连接池初始化，此时连接池中会常见2个连接
        for (int i = 0; i < 2; i++) {
            executorService.submit(() -> {
                // 实际执行select sleep(1), 占用连接1秒
                cantorTaskMapper.test(1);
            });
        }

        // 确保上面的任务已经开始执行，即连接池已经初始化
        TimeUnit.SECONDS.sleep(10);
        
        for (int i = 0; i < 2; i++) {
            executorService.submit(() -> {
                try {
                    // 休息到18秒，加上前面等待的10秒，执行完后大约过去了28秒
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
                // 开始持续占用连接5秒，从28秒到30秒
                cantorTaskMapper.test(5);
            });
        }
        // 开始进行监控，执行到此时，主线程休眠了10秒
        watchdog(5);
        return "ok";
    }
    
    // 定时周期调度任务线程池
    ScheduledExecutorService scheduledExecutor;
    
    // 定时输出线程池中的连接状态，及存活时间。
    public void watchdog(int delay) {
        // 判空操作，保证此逻辑单例
        if (scheduledExecutor == null) {
            scheduledExecutor = Executors.newSingleThreadScheduledExecutor();
            try {
                // 通过反射在HikariDataSource中获取pool字段 (HikariPool)
                Field poolField = HikariDataSource.class.getDeclaredField("pool");
                poolField.setAccessible(true);
                HikariPool hikariPool = (HikariPool) poolField.get(hikariDataSource);

                // 通过反射在HiKariPool中获取connectionBag字段 (ConcurrentBag)
                Field connectionBagField = HikariPool.class.getDeclaredField("connectionBag");
                connectionBagField.setAccessible(true);
                ConcurrentBag concurrentBag = (ConcurrentBag) connectionBagField.get(hikariPool);

                // 通过反射在 ConcurrentBag中获取sharedList (CopyOnWriteArrayList<PoolEntry>)
                Field sharedListField = ConcurrentBag.class.getDeclaredField("sharedList");
                sharedListField.setAccessible(true);
                CopyOnWriteArrayList sharedList = (CopyOnWriteArrayList) sharedListField.get(concurrentBag);

                // k: poolEntry对象, 可以任务是数据库连接， value: 该连接的存货时间，单位:秒
                Map<Object, Integer> survivalMap = new ConcurrentHashMap<>();

                scheduledExecutor.scheduleWithFixedDelay(() -> {
                    StringBuilder sb = new StringBuilder();
                    for (Object poolEntry : sharedList) {
                        // 补偿初始化之后最开始TimeUnit.SECONDS.sleep(10) 的10秒
                        survivalMap.putIfAbsent(poolEntry, 10);
                        // 获取poolEntry对象在JVM中的地址
                        sb.append(System.identityHashCode(poolEntry));
                        sb.append(":");
                        try {
                            // 反射获取poolEntry的stateToString方法
                            Method stateMethod = poolEntry.getClass().getDeclaredMethod("stateToString");
                            stateMethod.setAccessible(true);
                            sb.append(stateMethod.invoke(poolEntry));
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                        sb.append(":");
                        sb.append(survivalMap.get(poolEntry));
                        sb.append(",\t");
                        // 存活时间增加一个周期
                        survivalMap.computeIfPresent(poolEntry, (k, v) -> v + delay);
                    }
                    // 输出连接池中的连接状态
                    log.info(sb.toString());
                }, 0, delay, TimeUnit.SECONDS);

            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

<select id="test" resultType="java.lang.String">
  select sleep(#{sleepSeconds});
</select>