如何处理数据库连接失败与Java EE上的Hibernate，C3P0？ [英] How to handle database connection fail with Hibernate, C3P0 on Java EE?

问题描述

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

I am working on Java EE JSF application using Hibernate with C3P0 connection pool. I have tried to search anything possible and impossible and tried many things, but couldnt figure this out.

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

The problem is handling database connection fail, for example when database is shut down. I couldn't find a way how to catch ecfeption which I could use to show error status on user interface. The only thing I could do is see some exceptions in console:

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浏览器响应中打印（客户端线程不会收到任何异常，它只是在尝试加载无限页面时挂起）。

But I can't catch this, it's just on console and it's not even printed in web browser response (client thread does not receive any exception, it's just hanging while trying to load the page for infinity).

这是我的C3P0配置：

Here is my C3P0 configuration:

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

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

My question is: How to handle case of database connection fail in the user-friendly way?

推荐答案

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

OK, after of few days of work I have constructed a solution.

问题是，典型的Hibernate会话在没有连接数据库的情况下没有抛出任何异常。但是这段代码在数据库没有连接时会引发异常（所以它可以用作测试）：

The problem was that just typical Hibernate session did not throw any exception while using it without database connected. But this piece of code does throw an exception when database not connected (so it can be used as a test):

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

所以，可以使用简单的try-catch。如果发现异常，连接就会失效。

So, simple try-catch can be used. If an exception is caught, connection is dead.

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

I used this for regular checking of database connection. On application deploy, a TimerTask is scheduled to run every minute. When it catches an exception, it sets a static variable dbAvailable to false (otherwise to true). This variable is being checked on every HTTP client request and if it's true, error 503 is sent back in response.

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

For scheduling the timer I used ServletContextListener. C3P0 configuration mentioned in the question.

这篇关于如何处理数据库连接失败与Java EE上的Hibernate，C3P0？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！