ｓｓｈ　不能释放ｔｏｍｃａｔ数据库连接池中的连接

yshao81710

浏览: 94063 次
性别:
来自: 北京

最近访客更多访客>>

tangshengpan

wwmin86

chwnchwn

yanghuxz

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

Tomcat SSH Hibernate Bean Java

在Spring+hebernate中 Cannot get a connection, pool error Timeout waiting for idle object异常的解决办法
最近写了个留言板，tomcat+Spring+hibernate+MyEclipse
使用jdbc连接数据库所有功能都没问题，发布到tomcat中也没问题，可是如果使用tomcat的数据源，来连接数据库，开始很正常，但是刷新几次就会出现这个异常……2008-04-26 22:35:40,812 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 0, SQLState: null
2008-04-26 22:35:40,812 ERROR [org.hibernate.util.JDBCExceptionReporter] - Cannot get a connection, pool error Timeout waiting for idle object
2008-04-26 22:35:40,812 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 0, SQLState: null
2008-04-26 22:35:40,812 ERROR [org.hibernate.util.JDBCExceptionReporter] - Cannot get a connection, pool error Timeout waiting for idle object
2008-04-26 22:35:40,812 ERROR [com.dao.MessageDAO] - find all failed
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: Cannot open connection; uncategorized SQLException for SQL [???]; SQL state [null]; error code [0]; Cannot get a connection, pool error Timeout waiting for idle object; nested exception is org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
Caused by:
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.ｊａｖａ:104)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.ｊａｖａ:880)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection (LocalDataSourceConnectionProvider.ｊａｖａ:81)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.ｊａｖａ:417)
at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.ｊａｖａ:144)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.ｊａｖａ:105)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.ｊａｖａ:1561)
at org.hibernate.loader.Loader.doQuery(Loader.ｊａｖａ:661)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.ｊａｖａ:224)
at org.hibernate.loader.Loader.doList(Loader.ｊａｖａ:2145)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.ｊａｖａ:2029)
at org.hibernate.loader.Loader.list(Loader.ｊａｖａ:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.ｊａｖａ:375)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.ｊａｖａ:308)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.ｊａｖａ:153)
at org.hibernate.impl.SessionImpl.list(SessionImpl.ｊａｖａ:1106)
at org.hibernate.impl.QueryImpl.list(QueryImpl.ｊａｖａ:79)
at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.ｊａｖａ:849)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.ｊａｖａ:372)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.ｊａｖａ:840)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.ｊａｖａ:832)
……………………
开始弄了半天也不知道怎么回事，后来发现是我自己写的分页代码有问题……原来的代码如下：
/**
* 自定义的，用属性模糊查询
*
* */
public List find(String propertyName, Object value) {
      log.debug("finding Message instance with property: " + propertyName
            + ", value: " + value);
      try {
         String queryString = "from Message as model where model."
                                 + propertyName + " like "+value+" order by model.time desc";
      return getHibernateTemplate().find(queryString);
      } catch (RuntimeException re) {
         log.error("find by property name failed", re);
         throw re;
      }
}

/**
   *
   * 自定义的方法，获取指定页的数据
   *
   * */
public List gotoPage(int page,int pageSize){

      int totItem = this.findAll().size();//记录总条数
      int pageNum = totItem / pageSize +1;//总页数
      int begin = 0;//当前起始记录数

      begin=page*pageSize-pageSize+1; //计算当前起始位置

      Session s =this.getSession();
      String hql = "from Message message order by message.time desc";
      Query q =s.createQuery(hql);
      q.setFirstResult(begin);
      q.setMaxResults(pageSize);
      return q.list();
}
在这句中： Session s =this.getSession();
      String hql = "from Message message order by message.time desc";
      Query q =s.createQuery(hql);
查询数据时，Spring并不能够自动管理连接，也就是说，在使用中这几句代码重视不段的获取数据库的连接，每调用一次就申请一个连接……直到 tomcat连接池中的连接耗尽……所以就再也申请不到连接了……出现了这个异常，解决办法是使用事务来管理这段代码，让Spring自动管理这段代码中申请的连接。我使用了Spring AOP自动事务代理……配置文件如下……

<bean id="JndiDataSource"
      class="org.springframework.jndi.JndiObjectFactoryBean">
      <property name="jndiName">
         <value>ｊａｖａ:comp/env/SqlServer</value>
      </property>
      <property name="resourceRef">
         <value>true</value>
      </property>
</bean>

<bean id="sessionFactory"
      class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
      <property name="dataSource">
         <ref bean="JndiDataSource"></ref>
      </property>
      <property name="hibernateProperties">
         <props>
            <prop key="hibernate.dialect">
                  org.hibernate.dialect.SQLServerDialect
            </prop>
            
            <prop key="hibernate.show_sql">true</prop>
         </props>
      </property>
      <property name="mappingResources">
         <list>
            <value>./Message.hbm.xml</value>
            <value>./Setting.hbm.xml</value>
            <value>./Admin.hbm.xml</value>
         </list>
      </property>
</bean>

<bean id="transactionManger"
      class="org.springframework.orm.hibernate3.HibernateTransactionManager">
      <property name="sessionFactory">
         <ref bean="sessionFactory" />
      </property>
</bean>

<bean id="transactionInterceptor"
      class="org.springframework.transaction.interceptor.TransactionInterceptor">
      <property name="transactionManager">
         <ref bean="transactionManger" />
      </property>
      
      <property name="transactionAttributes">
         <props>
            <prop key="find*">PROPAGATION_REQUIRED</prop>
            <prop key="delete*">PROPAGATION_REQUIRED</prop>
            <prop key="save*">PROPAGATION_REQUIRED</prop>
            <prop key="merge*">PROPAGATION_REQUIRED</prop>
            <prop key="attach*">PROPAGATION_REQUIRED</prop>
            <prop key="gotoPage">PROPAGATION_REQUIRED</prop>
         </props>
      </property>
</bean>

<bean id="autoBeanNameProxyCreator"
      class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator">
      <property name="beanNames">
         <list>
            <value>*DAO</value>
            <value>gotoPage</value>
            <value>find</value>
         </list>
      </property>
      <property name="interceptorNames">
         <list>
            <idref local="transactionInterceptor" />
         </list>
      </property>

      
      <property name="proxyTargetClass" value="true"/>
</bean>
<bean id="MessageDAO" class="com.dao.MessageDAO">
      <property name="sessionFactory">
         <ref bean="sessionFactory"></ref>
      </property>
</bean>
<bean id="SettingDAO" class="com.dao.SettingDAO">
      <property name="sessionFactory">
         <ref bean="sessionFactory"></ref>
      </property>
</bean>
<bean id="AdminDAO" class="com.dao.AdminDAO">
      <property name="sessionFactory">
         <ref bean="sessionFactory"></ref>
      </property>
</bean>
</beans>

OK，问题成功解决！