JPA, Hibernate with c3p0 and Postgres. Detect database connectivity issues

Question

I'm writing an application which connects to Postgres DB via Hibernate with c3p0 pooling. Before the main interface shows up I'd like to detect whatever database connectivity settings valid and connection to DB possible. If settings are not valid I'd like to show a message to the user and suggest to change the settings or close the application. But the problem is that EntityManagerFactory won't throw an exception or even return after unsuccessful connection.

Here is an example of code which produces an error with wrong connection settings:

public void connect(ConnectionSettingsModel conSet) throws Exception {
    Map<String, String> connectionProperties = new HashMap<>();
    connectionProperties.put("javax.persistence.jdbc.url", conSet.getUrl());
    connectionProperties.put("javax.persistence.jdbc.user", conSet.getUser());
    connectionProperties.put("javax.persistence.jdbc.password", conSet.getPassword());
    connectionProperties.put("hibernate.default_schema", conSet.getSchema());

    System.out.println("Before creating EM");
    EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("postgres-connect", connectionProperties);
    EntityManager entityManager = entityManagerFactory.createEntityManager();
    System.out.println("After creating EM");
 }

c3p0 configuration in persistence.xml:

<property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider"/>
<property name="hibernate.c3p0.min_size" value="0"/>
<property name="hibernate.c3p0.max_size" value="10"/>
<property name="hibernate.c3p0.timeout" value="300"/>
<property name="hibernate.c3p0.idle_test_period" value="3000"/>
<property name="hibernate.c3p0.max_statements" value="50"/>
<property name="hibernate.c3p0.acquireRetryAttempts" value="1"/>

An log example with non existent user name:

Before creating EM
14:47:07,009 INFO [com.mchange.v2.log.MLog] - MLog clients using log4j logging.
14:47:07,239 INFO [com.mchange.v2.c3p0.C3P0Registry] - Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
14:47:07,303 INFO [com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource] - Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@e945fe41 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@d1b84bda [ acquireIncrement -> 3, acquireRetryAttempts -> 1, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, debugUnreturnedConnectionStackTraces -> false, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, identityToken -> 1hge3hi9nk1ku80noopkx|3185ce3, idleConnectionTestPeriod -> 3000, initialPoolSize -> 0, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 300, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 50, maxStatementsPerConnection -> 0, minPoolSize -> 0, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@9480ea7b [ description -> null, driverClass -> null, factoryClassLocation -> null, forceUseNamedDriverClass -> false, identityToken -> 1hge3hi9nk1ku80noopkx|26e664, jdbcUrl -> jdbc:postgresql://localhost:5432/postgres, properties -> {user=******, password=******} ], preferredTestQuery -> null, privilegeSpawnedThreads -> false, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, extensions -> {}, factoryClassLocation -> null, identityToken -> 1hge3hi9nk1ku80noopkx|3d02a858, numHelperThreads -> 3 ]
квіт. 07, 2017 2:47:07 PM org.postgresql.Driver connect
SEVERE: Connection error: 
  org.postgresql.util.PSQLException: FATAL: password authentication failed for user "user"
  --code omitted
квіт. 07, 2017 2:47:07 PM org.postgresql.Driver connect
SEVERE: Connection error: 
  org.postgresql.util.PSQLException: FATAL: password authentication failed for user "user"
  --code omitted
  -- few attempts to connect
14:47:55,009 WARN [com.mchange.v2.resourcepool.BasicResourcePool] - Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@4519c236 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.

After this message, it doesn't throw any exception or return

Answer 1

You might consider modifying

<property name="hibernate.c3p0.acquireRetryAttempts" value="1"/>

back to its default of 30 (or maybe higher), and then try

<property name="hibernate.c3p0.breakAfterAcquireFailure" value="true"/>

Then, if a full cycle of attempts to acquire Connections from the database fails (again, which should be more than one time, or your application will be very fragile), your c3p0 DataSource will simply break, and further attempts to check out a Connection will fail immediately. The downside of this is you'll lose c3p0's capacity to "self-heal" following a temporary network or database outage. You'll have to reconstruct the DataSource yourself (or restart your application) if a temporary outage fails an acquisition cycle.

If you want the best of both worlds, set hibernate.c3p0.acquireRetryAttempts back to 30-ish, leave hibernate.c3p0.breakAfterAcquireFailure to its default of false, but write your own custom code to test the availability of your database. Modifying your test connect() function above (which needs to be reorganized, you only want to create the EntityManagerFactory once), might be as simple as...

public void connect(ConnectionSettingsModel conSet) throws Exception {

    try( Connection con = DriverManager.getConnection( conSet.getUrl(), conSet.getUser(), conSet.getPassword() ) ) {
        /* Nothing to do here, really... */
    } catch ( Exception e ) {
        System.out.println("Trouble connecting with DBMS, please check database url, username, and password.");
        throw e;  
    }

    Map<String, String> connectionProperties = new HashMap<>();
    connectionProperties.put("javax.persistence.jdbc.url", conSet.getUrl());
    connectionProperties.put("javax.persistence.jdbc.user", conSet.getUser());
    connectionProperties.put("javax.persistence.jdbc.password", conSet.getPassword());
    connectionProperties.put("hibernate.default_schema", conSet.getSchema());


    System.out.println("Before creating EM");
    EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("postgres-connect", connectionProperties);
    EntityManager entityManager = entityManagerFactory.createEntityManager();
    System.out.println("After creating EM");
 }