Tomcat/Spring连接池-列出连接项

Question

null

Spring Web请求和Quartz触发的作业混合使用了这个池。运行大约5次后，池中没有可用的连接，initialsize=5，maxactive=10。增加maxActive没有什么作用，它只是在池空之前需要更长的时间和更多的石英运行。

2016-05-19 08:59:46,027 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_QuartzSchedulerThread   QuartzSchedulerThread.run  276 : batch acquisition of 1 triggers
2016-05-19 09:00:00,001 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_QuartzSchedulerThread   QuartzSchedulerThread.run  276 : batch acquisition of 0 triggers
2016-05-19 09:00:00,003 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-6   JobRunShell.run  201 : Calling execute on job DEFAULT.refMigrDetail
2016-05-19 09:00:00,003 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-6   DataSourceUtils.doGetConnection  110 : Fetching JDBC Connection from DataSource
2016-05-19 09:00:24,409 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_QuartzSchedulerThread   QuartzSchedulerThread.run  276 : batch acquisition of 0 triggers
2016-05-19 09:00:30,004 ERROR org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-6   CMRoreReferenceMigrator.run   93 : org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: [org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-6] Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[10 in use].
2016-05-19 09:00:49,378 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_QuartzSchedulerThread   QuartzSchedulerThread.run  276 : batch acquisition of 0 triggers

我到目前为止所做的：

• 从Apache Commons DBCP切换到Tomcat JDBC池。这起作用了。
• 为Tomcat JDBC池打开调试。也在工作
• 启用Tomcat上的JMX使用jvisualvm监视连接池-这不起作用，因为Spring实际上是在创建池，而不是Tomcat。

<bean id="rrfecfDataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
    <property name="driverClassName" value="${rrfecf.datasource.jdbcDriver}" />
    <property name="url" value="${rrfecf.dataousrce.databaseUrl}" />
    <property name="username" value="${rrfecf.datasource.username}" />
    <property name="password" value="${password:rrfecf.datasource}" />
    <property name="initialSize" value="5" />
    <property name="maxActive" value="10" />
    <property name="defaultAutoCommit" value="false" />
    <property name="maxIdle" value="5" />
    <property name="minIdle" value="0" />
    <property name="testOnBorrow" value="true" />
    <property name="testOnReturn" value="false" />
    <property name="testWhileIdle" value="true" />
    <property name="validationQuery" value="SELECT CURRENT_DATE FROM sysibm.sysdummy1" />
    <property name="timeBetweenEvictionRunsMillis" value="600000" /> <!-- 10 minutes -->
    <property name="minEvictableIdleTimeMillis" value="900000" /> <!-- 15 minutes -->
</bean>

<bean id="rrfecfSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="configLocation" value="WEB-INF/sqlMapConfig.xml"/>
    <property name="dataSource" ref="rrfecfDataSource" />
</bean>

Answer 1

这“似乎”起作用，不确定是否有更好的方法：

• 在Spring中创建了一个ApplicationContextAware Bean。
• 将ApplicationContextAware Bean连接到Quartz类。
• 在石英类中，我是：
  • 使用ApplicationContextAware Bean获取ConnectionPool。
  • 使用for循环遍历所有连接并转储属性

  ApplicationContextAware类：

  import org.apache.tomcat.jdbc.pool.DataSource;
  import org.springframework.beans.BeansException;
  import org.springframework.context.ApplicationContext;
  import org.springframework.context.ApplicationContextAware;
  
  /**
   * <p>
   * The <code>BatchApplicationContextProvider</code> provides...
   * </p>
   */
  
  public class BatchApplicationContextProvider implements ApplicationContextAware {
  
      public static final String ID              = BatchApplicationContextProvider.class.getName ();
      private String             SHORT_NAME      = "BatchApplicationContextProvider()";
      @SuppressWarnings("unused")
      private String              SYSTEM_IDENTITY = String.valueOf ( System.identityHashCode ( this ) );
  
      private ApplicationContext  context;
  
      /**
       * 
       */
      public BatchApplicationContextProvider() {
          // TODO Auto-generated constructor stub
      }
  
      /* (non-Javadoc)
       * @see org.springframework.context.ApplicationContextAware#setApplicationContext(org.springframework.context.ApplicationContext)
       */
      @Override
      public void setApplicationContext(ApplicationContext arg0)
          throws BeansException {
          this.context = arg0;
      }
  
      public ApplicationContext getApplicationContext() {
          return context;
      }
  
      public org.apache.tomcat.jdbc.pool.DataSource   getConnectionPool () { 
          return ( DataSource ) this.context.getBean ( "rrfecfDataSource" );
      }
  
  }
  

  public void        dumpConnectionPool () { 
      org.apache.tomcat.jdbc.pool.DataSource  lthePool = null;
      ConnectionPool  lbusyPool = null;
      try { 
          lthePool   = getBatchContext ().getConnectionPool ();
          lbusyPool  = lthePool.getPool ();
          if ( lbusyPool != null ) { 
              if ( lbusyPool.getSize () > 0 ) { 
                  getLog().info ( SHORT_NAME + ".run() - ConnectionPool - size....................[" + lbusyPool.getSize () + "] idle [" + lbusyPool.getIdle () + "] active [" + lbusyPool.getActive () + "]"  );
                  //getLog().info ( SHORT_NAME + ".run() - ConnectionPool - idle....................[" + lbusyPool.getIdle () + "]" );
                  //getLog().info ( SHORT_NAME + ".run() - ConnectionPool - active..................[" + lbusyPool.getActive () + "]" );
                  //PoolConfiguration       lpoolCfg = lbusyPool.getPoolProperties ();
              }
          }
  
          lbusyPool = null;
          lthePool = null;
  
      }
      catch ( Exception ltheXcp ) { 
          log.error ( ltheXcp );
      }
      finally { 
  
      }
  
  
  }
  

  EDIT：取出for循环...设置connection=null并没有将其释放回连接池。

  成功：

  dumpConnectionPool（）虽然不漂亮，但它本身显示了问题清单：

  2016-05-20 11:20:00,078  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.run  138 : -----------------------------------------------------------------------------------------------------------------
  2016-05-20 11:20:00,078  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.dumpConnectionPool  257 : RunReferenceMigrator().run() - ConnectionPool - size....................[5] idle [5] active [0]
  2016-05-20 11:20:00,078  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.run  140 : -----------------------------------------------------------------------------------------------------------------
  

  2016-05-20 11:24:45,063  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.run  161 : -----------------------------------------------------------------------------------------------------------------
  2016-05-20 11:24:45,063  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.dumpConnectionPool  257 : RunReferenceMigrator().run() - ConnectionPool - size....................[5] idle [4] active [1]
  2016-05-20 11:24:45,063  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.run  163 : -----------------------------------------------------------------------------------------------------------------