Solution to the inability to free connections in Spring+hebernate

Cannot get a connection in spring+hebernate, pool error Timeout waiting for idle object exception resolution

Recently wrote a message board, Tomcat+spring+hibernate+myeclipse

Using JDBC to connect to the database is all right, and it's okay to publish to Tomcat, but if you're using Tomcat's data source to connect to the database, it's normal to start, but it's going to happen a few times. 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 Wai Ting 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 Wai Ting 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 fo R 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.java:104)

At Org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection (basicdatasource.java:880)

At Org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection ( LOCALDATASOURCECONNECTIONPROVIDER.JAVA:81)

At Org.hibernate.jdbc.ConnectionManager.openConnection (connectionmanager.java:417)

At Org.hibernate.jdbc.ConnectionManager.getConnection (connectionmanager.java:144)

At Org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement (abstractbatcher.java:105)

At Org.hibernate.loader.Loader.prepareQueryStatement (loader.java:1561)

At Org.hibernate.loader.Loader.doQuery (loader.java:661)

At Org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections (loader.java:224)

At Org.hibernate.loader.Loader.doList (loader.java:2145)

At Org.hibernate.loader.Loader.listIgnoreQueryCache (loader.java:2029)

At Org.hibernate.loader.Loader.list (loader.java:2024)

At Org.hibernate.loader.hql.QueryLoader.list (queryloader.java:375)

At Org.hibernate.hql.ast.QueryTranslatorImpl.list (querytranslatorimpl.java:308)

At Org.hibernate.engine.query.HQLQueryPlan.performList (hqlqueryplan.java:153)

At Org.hibernate.impl.SessionImpl.list (sessionimpl.java:1106)

At Org.hibernate.impl.QueryImpl.list (queryimpl.java:79)

At Org.springframework.orm.hibernate3.hibernatetemplate$29.doinhibernate (hibernatetemplate.java:849)

At Org.springframework.orm.hibernate3.HibernateTemplate.execute (hibernatetemplate.java:372)

At Org.springframework.orm.hibernate3.HibernateTemplate.find (hibernatetemplate.java:840)

At Org.springframework.orm.hibernate3.HibernateTemplate.find (hibernatetemplate.java:832)

........................

began to make a half-day also do not know how, and later found that I wrote the paging code has problems ... The original code is as follows:

/**

* Custom, fuzzy query with attributes

*

* */

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;

}

}

/**

*

* A custom method that gets the data for the specified page

*

* */

Public List gotoPage (int page,int pageSize) {

int totitem = This.findall (). Size ()//record total number of articles

int pagenum = totitem/pagesize +1;//Total pages

int begin = 0;//number of current start records

begin=page*pagesize-pagesize+1; Calculate the current start position

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

}

In this sentence: session s =this.getsession ();

String hql = "from message message order by message.time DESC";

Query q =s.createquery (HQL);

When querying for data, spring is not able to manage connections automatically, that is to say, the code in use takes into a disconnected database connection and requests a connection every time it is invoked ... Until the connections in the Tomcat connection pool are exhausted ... So you can no longer apply for the connection ... This exception is resolved by using a transaction to manage this code, so that spring automatically manages the connection requested in this code. I used the spring AOP automatic transaction agent ... The configuration file is as follows ...

<!--a Tomcat data source from Jndi with a connection pool. But how to use it can not release the connection. The program only knows the application, does not know the release. -->

<bean id= "Jndidatasource"

class= "Org.springframework.jndi.JndiObjectFactoryBean" >

<property name= "Jndiname" >

<value>java:comp/env/SqlServer</value>

</property>

<property name= "Resourceref" >

<value>true</value>

</property>

</bean>

<!--hibernate session factory-->

<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>

<!--display SQL, in order to facilitate testing-->

<prop key= "Hibernate.show_sql" >true</prop>

</props>

</property>

<property name= "Mappingresources" >

<list><!--Mapping File-->

<value>./Message.hbm.xml</value>

<value>./Setting.hbm.xml</value>

<value>./Admin.hbm.xml</value>

</list>

</property>

</bean>

<!--transaction manager-->

<bean id= "Transactionmanger"

class= "Org.springframework.orm.hibernate3.HibernateTransactionManager" >

<property name= "Sessionfactory" >

<ref bean= "Sessionfactory"/>

</property>

</bean>

<!--Configure Transaction Interceptor-->

<bean id= "Transactioninterceptor"

class= "Org.springframework.transaction.interceptor.TransactionInterceptor" >

<property name= "TransactionManager" >

<ref bean= "Transactionmanger"/>

</property>

<!--The following defines the transaction propagation properties-->

<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>

<!--automatic Agent-->

<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>

<!--the configuration here is required, otherwise the proxy type conversion cannot be completed.

This is using Cglib to generate the agent

-->

<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, the problem is solved successfully.