页面刷新多次后，mysql数据库连接失败。数据连接过多。

[望远镜Monkey]

如题，项目有个页面主要是查询数据，刷新多次后就会出现数据库连接过多无法连接。想请问在使用hibernate大量查询时应该使用什么方法比较好呢？报错信息如下：
2014-10-13 14:31:38 org.hibernate.util.JDBCExceptionReporter logExceptions
警告: SQL Error: 0, SQLState: null
2014-10-13 14:31:38 org.hibernate.util.JDBCExceptionReporter logExceptions
严重: Connections could not be acquired from the underlying database!
2014-10-13 14:31:38 org.hibernate.util.JDBCExceptionReporter logExceptions
警告: SQL Error: 0, SQLState: null
2014-10-13 14:31:38 org.hibernate.util.JDBCExceptionReporter logExceptions
严重: Connections could not be acquired from the underlying database!
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: Cannot open connection; uncategorized SQLException for SQL [???]; SQL state [null]; error code [0]; Connections could not be acquired from the underlying database!; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)

at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:339)
at com.qixin.platform.persistent.database.basedao.HibernateEntityDao.findByCritMap(HibernateEntityDao.java:241)
at com.qixin.app.system.company.service.impl.CompanyServiceImpl.getCompanyByCode(CompanyServiceImpl.java:87)

at com.qixin.app.system.company.service.impl.CompanyServiceImpl$$FastClassByCGLIB$$3d53ab40.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:617)

at com.qixin.app.system.company.service.impl.CompanyServiceImpl$$EnhancerByCGLIB$$9e5cede2_2.getCompanyByCode(<generated>)
at com.hans.sn.web.SnCheckController.toIndex(SnCheckController.java:81)
at sun.reflect.GeneratedMethodAccessor187.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:175)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:421)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:409)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:774)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:549)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1070)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2440)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2429)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: Connections could not be acquired from the underlying database!
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:615)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)

at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573)
at org.hibernate.loader.Loader.doQuery(Loader.java:696)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)

at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
at com.qixin.platform.persistent.database.basedao.HibernateEntityDao$3.doInHibernate(HibernateEntityDao.java:250)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
... 42 more
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1406)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:594)

at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:514)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:681)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:608)
... 58 more
2013-01-03 14:31:38,220 DEBUG [com.qixin.platform.exception.DefaultHandlerExceptionResolver] - Resolving to view 'forward:/ecsystem/systemmgmt/mainmanage/error.sc' for exception of type [org.springframework.jdbc.UncategorizedSQLException], based on exception mapping [java.lang.Exception] [http-apr-8080-exec-26]
2013-01-03 14:31:38,220 DEBUG [com.qixin.platform.exception.DefaultHandlerExceptionResolver] - Exposing Exception as model attribute 'exception' [http-apr-8080-exec-26]

[ACMAIN_CHM]

检查一下连接数。 show processlists; 如果有大量连接，则检查你的程序代码为何没有及时释放连接。

[望远镜Monkey]

引用 1 楼 ACMAIN_CHM 的回复:

检查一下连接数。 show processlists; 如果有大量连接，则检查你的程序代码为何没有及时释放连接。

确实是代码没有释放连接，代码中查询获取session试过了这几种方法，encodingRulesDao.getHibernateSession().createSQLQuery(sql.toString())，getHibernateTemplate().getSessionFactory().openSession()， getHibernateTemplate().getSessionFactory().getCurrentSession()，发现连接都是只增不减。不知是不是哪里没配置对。上网看到说到spring事务管理，会自动管理session的释放，但是貌似没起到效果。

[wushangjimo]

改一下你的数据库链接池的配置，同时增加二级缓存以及页面缓存

[小笨和漂向北方]

hibernate definitely connects mysql using a connection pool, you might want to check the config file there make sure you have sufficient spare connections available. I am not familiar with hibernate, check if you have to explicitly close/release connection in your java codes...

[LOVE-漂泊]

即使释放资源，同时配置下连接池，连接池要自动连接

[dujie4752041]

1、检测 MYSQL 配置中的错误连接个数是否太小 2、每次刷新是否以释放原连接

[望远镜Monkey]

引用 7 楼 zzzrrr1989 的回复:

[quote=引用 1 楼 ACMAIN_CHM 的回复:] 检查一下连接数。 show processlists; 如果有大量连接，则检查你的程序代码为何没有及时释放连接。

---------------------------------------------------------------------------------------------------- show processlists; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'processlists' at line 1 请问这是啥原因？[/quote] 你的sql写错了，应该是SHOW FULL PROCESSLIST; 或者 SHOW PROCESSLIST; 没有's'

[望远镜Monkey]

引用 3 楼 sizhouzhou 的回复:

即使释放资源，同时配置下连接池，连接池要自动连接

请问数据库连接断了的话，怎么配置自动连接呢？

[LOVE-漂泊]

c3p0 或者其他连接池 都有重新连接的 都网上搜下 很懂得

[zzzrrr1989]

引用 1 楼 ACMAIN_CHM 的回复:

检查一下连接数。 show processlists; 如果有大量连接，则检查你的程序代码为何没有及时释放连接。

---------------------------------------------------------------------------------------------------- show processlists; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'processlists' at line 1 请问这是啥原因？

[Rotel-刘志东]

大量sql对数据库的连接后，做具体的sql操作，执行完是否释放。

[moon0312]

tcp连接建立了，但是没释放，超过你设置的最大连接数就这样了。