如何处理 Java EE 上的 Hibernate、C3P0 数据库连接失败

Question

我正在使用带有C3P0连接池的Hibernate开发Java EE JSF应用程序。我试图寻找任何可能和不可能的东西，并尝试了很多事情，但无法弄清楚这一点。

问题是处理数据库连接失败，例如，当数据库关闭时。我找不到一种方法来捕获可用于在用户界面上显示错误状态的ecfeption。我唯一能做的就是在控制台中看到一些异常：

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    at sun.reflect.GeneratedConstructorAccessor408.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2404)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2325)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    at sun.reflect.GeneratedConstructorAccessor407.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:347)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:146)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:195)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:184)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1086)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1810)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at sun.reflect.GeneratedConstructorAccessor404.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1129)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:358)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2498)
    at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2343)
    ... 18 more
Caused by: java.net.ConnectException: Connection timed out: connect
    at java.net.DualStackPlainSocketImpl.connect0(Native Method)
    at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:79)
    at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)
    at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)
    at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)
    at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
    at java.net.Socket.connect(Socket.java:579)
    at java.net.Socket.connect(Socket.java:528)
    at java.net.Socket.<init>(Socket.java:425)
    at java.net.Socket.<init>(Socket.java:241)
    at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:308)
    ... 20 more
]]

但是我无法抓住这个，它只是在控制台上，甚至没有打印在 Web 浏览器响应中（客户端线程没有收到任何异常，它只是在尝试无限加载页面时挂起）。

这是我的 C3P0 配置：

c3p0.testConnectionOnCheckout=true
c3p0.idleConnectionTestPeriod=60
c3p0.acquireIncrement=1
c3p0.preferredTestQuery=SELECT 1
c3p0.acquireRetryAttempts=1

我的问题是：如何以用户友好的方式处理数据库连接失败的情况？

Answer 1

您可以使用 Omnifaces FullAjaxExceptionHandler 来显示任何您想要的异常。请参阅以下文档链接：

http://showcase.omnifaces.org/exceptionhandlers/FullAjaxExceptionHandler

Answer 2

好的，经过几天的工作，我已经构建了一个解决方案。

问题是，在没有连接数据库的情况下使用它时，只有典型的休眠会话不会引发任何异常。但是这段代码在数据库未连接时确实会引发异常（因此可以用作测试）：

Properties p = hibernateConnection.getCfg().getProperties();
String url = p.getProperty("hibernate.connection.url");
String user = p.getProperty("hibernate.connection.username");
String password = p.getProperty("hibernate.connection.password");
DriverManager.getConnection(url, user, password).close();

因此，可以使用简单的尝试捕获。如果捕获异常，则连接将失效。

我用它来定期检查数据库连接。在应用程序部署中，计划每分钟运行一次TimerTask。当它捕获异常时，它会将静态变量dbAvailable设置为 false（否则设置为 true）。在每个 HTTP 客户端请求上都会检查此变量，如果为 true，则会在响应中发回错误503。

为了安排计时器，我使用了ServletContextListener.问题中提到的 C3P0 配置。