如何配置C3P0+Hibernate在指定时段拒绝请求并实现数据库恢复后重连？

Hey there, let's work through your C3P0+Hibernate fault tolerance challenges. I've dealt with similar scenarios before, so here's a breakdown of solutions for both your issues:

The problem with just setting breakAfterAcquireFailure=true is that once the connection pool enters a "broken" state, it won't automatically check if the database has recovered unless you add additional configuration to test connections periodically. Here's what you need to add:

• acquireRetryAttempts: Set this to a number of retry attempts before marking the pool as broken (e.g., 5). This gives the database a chance to recover immediately before disabling connections.
• acquireRetryDelay: Define the delay between each retry (e.g., 1000 milliseconds, so 1 second between attempts).
• idleConnectionTestPeriod: Configure how often the pool tests idle connections (e.g., 30 seconds). This is critical—it lets the pool detect when the database is back online and exit the broken state.
• preferredTestQuery: A simple SQL query to validate connections (for MySQL, use SELECT 1).
• testConnectionOnCheckout: Optional (not recommended for performance), but you can set this to true if you want to test every connection when it's checked out. Stick with idleConnectionTestPeriod for better efficiency.

Here's how these look in your Hibernate configuration (either hibernate.cfg.xml or persistence.xml):

<property name="hibernate.c3p0.breakAfterAcquireFailure">true</property>
<property name="hibernate.c3p0.acquireRetryAttempts">5</property>
<property name="hibernate.c3p0.acquireRetryDelay">1000</property>
<property name="hibernate.c3p0.idleConnectionTestPeriod">30</property>
<property name="hibernate.c3p0.preferredTestQuery">SELECT 1</property>
<property name="hibernate.c3p0.testConnectionOnCheckout">false</property>

With these settings, the pool will retry 5 times (1 second apart) when a connection fails, mark itself as broken if all retries fail, then every 30 seconds test idle connections. Once the database is back, the test query will succeed, and the pool will start accepting connection requests again.

If you want all requests to fail immediately for a specific window (e.g., 30 minutes) after a database outage, adjust the C3P0 settings to control when recovery attempts start:

• Set acquireRetryAttempts to 0: This skips retries and immediately marks the pool as broken when a connection fails.
• Set idleConnectionTestPeriod to your desired cool-down period in seconds: For 30 minutes, that's 1800. This means the pool won't check if the database is back until 30 minutes have passed.

Modified config example:

<property name="hibernate.c3p0.breakAfterAcquireFailure">true</property>
<property name="hibernate.c3p0.acquireRetryAttempts">0</property>
<property name="hibernate.c3p0.idleConnectionTestPeriod">1800</property>
<property name="hibernate.c3p0.preferredTestQuery">SELECT 1</property>

During the 30-minute window, any request trying to get a connection will fail immediately (since the pool is broken and not attempting retries). After 30 minutes, the pool will test idle connections; if the database is back, it'll exit the broken state and resume normal operations.

For more control, you could add a simple check in your data access layer to detect if the pool is broken (using C3P0's ComboPooledDataSource.isBroken() method) and throw a custom exception during the cool-down period. But the config-based approach is cleaner for most use cases.

内容的提问来源于stack exchange，提问作者lazyloader