前段時間剛完成一個家教網(wǎng)項目，數(shù)據(jù)庫為MySQL5.0，持久層使用Hibernate 3.1，沒有使用額外的連接池，那么Hibernate會默認使用它自帶的一個默認連接池，也就是DriverManagerConnectionProvider。
先在本機上調(diào)試都毫無問題，于是部署到服務器上，也都沒什么問題。由于這是新網(wǎng)站，根本還沒正式對外發(fā)布和宣傳，所以頭兩天根本沒人訪問。等到第二天，我再次訪問網(wǎng)站時，問題就出現(xiàn)了，錯誤信息如下：
root cause
javax.servlet.ServletException: org.hibernate.exception.JDBCConnectionException: could not execute query
org.apache.struts.action.RequestProcessor.processException(RequestProcessor.java:535)
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:433)
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jsp.index_jsp._jspService(index_jsp.java:57)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:337)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
com.lab1000.jcom.system.filter.RequestEncodingFilter.doFilter(RequestEncodingFilter.java:30)
root cause
org.hibernate.exception.JDBCConnectionException: could not execute query
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:74)
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
org.hibernate.loader.Loader.doList(Loader.java:2148)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
org.hibernate.loader.Loader.list(Loader.java:2024)
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
org.hibernate.impl.SessionImpl.list(SessionImpl.java:1533)
org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
com.lab1000.jcom.dao.ArticleDAO.getArticlesUnder(ArticleDAO.java:163)
com.lab1000.jcom.struts.action.HomeAction.execute(HomeAction.java:40)
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jsp.index_jsp._jspService(index_jsp.java:57)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:337)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
com.lab1000.jcom.system.filter.RequestEncodingFilter.doFilter(RequestEncodingFilter.java:30)
root cause
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:
** BEGIN NESTED EXCEPTION **
com.mysql.jdbc.CommunicationsException
MESSAGE: Communications link failure due to underlying exception:
** BEGIN NESTED EXCEPTION **
java.io.EOFException
STACKTRACE:
java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1963)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2375)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2874)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1403)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
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.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1533)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at com.lab1000.jcom.dao.ArticleDAO.getArticlesUnder(ArticleDAO.java:163)
at com.lab1000.jcom.struts.action.HomeAction.execute(HomeAction.java:40)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:630)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:436)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:374)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:302)
at org.apache.jsp.index_jsp._jspService(index_jsp.java:57)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:337)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.lab1000.jcom.system.filter.RequestEncodingFilter.doFilter(RequestEncodingFilter.java:30)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:190)
at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:283)
at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:767)
at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:697)
at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:889)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:690)
at java.lang.Thread.run(Unknown Source)
** END NESTED EXCEPTION **
Last packet sent to the server was 15 ms ago.
STACKTRACE:
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:
** BEGIN NESTED EXCEPTION **
java.io.EOFException
STACKTRACE:
java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1963)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2375)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2874)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1403)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
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.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1533)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at com.lab1000.jcom.dao.ArticleDAO.getArticlesUnder(ArticleDAO.java:163)
at com.lab1000.jcom.struts.action.HomeAction.execute(HomeAction.java:40)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:630)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:436)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:374)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:302)
at org.apache.jsp.index_jsp._jspService(index_jsp.java:57)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:337)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.lab1000.jcom.system.filter.RequestEncodingFilter.doFilter(RequestEncodingFilter.java:30)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:190)
at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:283)
at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:767)
at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:697)
at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:889)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:690)
at java.lang.Thread.run(Unknown Source)
** END NESTED EXCEPTION **
Last packet sent to the server was 15 ms ago.
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2586)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2874)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1403)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
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.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1533)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at com.lab1000.jcom.dao.ArticleDAO.getArticlesUnder(ArticleDAO.java:163)
at com.lab1000.jcom.struts.action.HomeAction.execute(HomeAction.java:40)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:630)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:436)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:374)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:302)
at org.apache.jsp.index_jsp._jspService(index_jsp.java:57)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:337)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.lab1000.jcom.system.filter.RequestEncodingFilter.doFilter(RequestEncodingFilter.java:30)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:190)
at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:283)
at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:767)
at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:697)
at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:889)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:690)
at java.lang.Thread.run(Unknown Source)
** END NESTED EXCEPTION **
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888)
com.mysql.jdbc.Connection.checkClosed(Connection.java:1931)
com.mysql.jdbc.Connection.prepareStatement(Connection.java:4705)
com.mysql.jdbc.Connection.prepareStatement(Connection.java:4671)
org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:442)
org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:368)
org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:105)
org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1561)
org.hibernate.loader.Loader.doQuery(Loader.java:661)
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
org.hibernate.loader.Loader.doList(Loader.java:2145)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
org.hibernate.loader.Loader.list(Loader.java:2024)
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
org.hibernate.impl.SessionImpl.list(SessionImpl.java:1533)
org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
com.lab1000.jcom.dao.ArticleDAO.getArticlesUnder(ArticleDAO.java:163)
com.lab1000.jcom.struts.action.HomeAction.execute(HomeAction.java:40)
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jsp.index_jsp._jspService(index_jsp.java:57)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:337)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
com.lab1000.jcom.system.filter.RequestEncodingFilter.doFilter(RequestEncodingFilter.java:30)
當時也搞不清楚為什么，但重啟Tomcat后就一切回復正常了，但是又過了一天，問題再次出現(xiàn)。仔細分析錯誤信息，問題肯定出在數(shù)據(jù)庫連接部分，在網(wǎng)上查閱大量信息后，明確了問題的原因：MySQL對所有連接的有效時間默認為28800秒，正好8小時，也就是說，如果一個連接8小時沒有請求和操作，就會自動斷開；但是對于Hibernate來說，它的連接池并不知道它所管理的連接中是否有被MySQL斷開的。如果一個程序要使用數(shù)據(jù)庫連接，而Hibernte的連接池分配一個已經(jīng)被MySQL斷開了的給程序使用，那么便會出現(xiàn)錯誤。
為了證實確實是這個錯誤，我在本機上做了如下測試：首先啟動Tomcat，網(wǎng)站能夠正常打開；然后修改系統(tǒng)時間，往后調(diào)1天；然后再打開網(wǎng)站，同樣的問題果然出現(xiàn)！MySQL + Hibernate架構(gòu)相當普遍，所以這個問題也相當普遍，若讀者也有這樣的項目，建議做一下同樣的測試，看看是否存在此問題！
問題找到了，怎么解決呢？
思路1：增大MySQL的連接有效時間；
思路2：從程序自身出發(fā)，修改連接池的相關(guān)參數(shù)。
顯然，思路1是不可行的，一方面這個思路不可能保證這個問題絕對的解決，如果無訪問時間夠長，還是大于MySQL的連接有效時間，問題同樣會發(fā)生；另一方面，一般情況下，我們都是將程序部署在別人服務器上，我們不可能去修改MySQL配置。那么只能從思路2入手了。
經(jīng)查資料發(fā)現(xiàn)，Hibernate支持如下的連接池：
DriverManagerConnectionProvider：代表由Hibernate提供的默認的數(shù)據(jù)庫連接池
C3P0ConnectionProvider：代表C3P0連接池
ProxoolConnectionProvider：代表Proxool連接池
DBCPConnectionProvider：代表DBCP連接池
DatasourceConnectionProvider：代表在受管理環(huán)境中由容器提供的數(shù)據(jù)源
其中，默認連接池并不支持在分配一個連接時，測試其有效與否的功能，而C3P0、Proxool、DBCP都提供了這樣的功能，正好可以解決上述問題。綜合考慮各個連接池的效率、穩(wěn)定性、易用性，決定換用Proxool，它確實在各方面表現(xiàn)優(yōu)良，方便配置。
下面就看看如何配置Proxool：
1、Hibernate配置文件：
<session-factory>
<property name="hibernate.connection.provider_class">org.hibernate.connection.ProxoolConnectionProvider</property>
<property name="hibernate.proxool.xml">proxool.xml</property>
<property name="hibernate.proxool.pool_alias">mysql</property>
<property name="show_sql">false</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<mapping resource="com/lab1000/jcom/pojo/Admin.hbm.xml" />
...
</session-factory>
其中各屬性含義如下：
hibernate.connection.provider_class：指明使用Proxool連接池
hibernate.proxool.xml：指明Proxool配置文件所在位置，這里與Hibernate的配置文件在同一目錄下
hibernate.proxool.pool_alias：指明要使用的proxool.xml中定義的proxool別名。
2、Proxool配置文件（proxool.xml）：
<?xml version="1.0" encoding="UTF-8"?>
<!-- the proxool configuration can be embedded within your own application's.
Anything outside the "proxool" tag is ignored. -->
<something-else-entirely>
<proxool>
<!-- proxool別名 -->
<alias>mysql</alias>
<!-- 數(shù)據(jù)庫連接Url -->
<driver-url>
jdbc:mysql://localhost/yourDatebase?useUnicode=true&characterEncoding=UTF-8
</driver-url>
<!-- JDBC驅(qū)動名稱 -->
<driver-class>com.mysql.jdbc.Driver</driver-class>
<!-- 數(shù)據(jù)庫連接帳號 -->
<driver-properties>
<property name="user" value="root" />
<property name="password" value="password" />
</driver-properties>
<!-- proxool自動偵察各個連接狀態(tài)的時間間隔(毫秒),偵察到空閑的連接就馬上回收,超時的銷毀 -->
<house-keeping-sleep-time>90000</house-keeping-sleep-time>
<!-- 指因未有空閑連接可以分配而在隊列中等候的最大請求數(shù),超過這個請求數(shù)的用戶連接就不會被接受 -->
<maximum-new-connections>20</maximum-new-connections>
<!-- 最少保持的空閑連接數(shù) -->
<prototype-count>3</prototype-count>
<!-- 允許最大連接數(shù),超過了這個連接，再有請求時，就排在隊列中等候，最大的等待請求數(shù)由maximum-new-connections決定 -->
<maximum-connection-count>20</maximum-connection-count>
<!-- 最小連接數(shù) -->
<minimum-connection-count>3</minimum-connection-count>
<!-- 在分配連接前后是否進行有效性測試，這個是解決本問題的關(guān)鍵 -->
<test-before-use>true</test-before-use>
<test-after-use>true</test-after-use>
<!-- 用于測試的SQL語句 -->
<house-keeping-test-sql>SELECT CURRENT_USER</house-keeping-test-sql>
</proxool>
</something-else-entirely>
3、下載和安裝Proxool的包文件
下載地址：http://proxool.sourceforge.net/download.html
下載后并解壓后，將其中l(wèi)ib文件夾下的jar文件拷貝到你站點的WEB-INF/lib下
自此，Proxool配置成功。重新啟動Tomcat，再次做上述測試，問題解決。
此外，如果要使用C3P0或DHCP，可以參考以下資料：
http://blog.csdn.net/lip8654/archive/2008/02/26/2121387.aspx
http://azi.javaeye.com/blog/182146
http://fishyych.javaeye.com/blog/90793