How to detect open database connection with Hibernate / JPA?

Question

I am learning JPA w/Hibernate using a Java SE 6 project. I'd simply like to be able to detect if the connection between Hibernate and my database (MS SQL Server) is open. For example, I'd like to be able to detect this, log it, and try reconnecting again in 60 seconds.

This is what I thought would work but isOpen() doesn't appear to be what I want (always is true):

EntityManagerFactory emf = Persistence.createEntityManagerFactory("rcc", props);
if (emf != null && emf.isOpen()) {
    EntityManager em = emf.createEntityManager();
    if (em == null || !emf.isOpen())
        // error connecting to database
    else ...

This seems to me to be a simple problem, but I cannot find an answer!

Answer 1

My experience was with NHibernate. I hope it translates. In the early versions it closed the database connection very aggressively. There's an option in the later versions to leave it open. You might check that on Hibernate.

Answer 2

Usually you access your database using a datasource, and you configure it to do what you need, I paste here the basic configuration, for detailed configuration google for it ;)

<?xml version="1.0" encoding="UTF-8"?>

<datasources>
    <local-tx-datasource>
    <jndi-name>EnterpriseDS</jndi-name>
    <connection-url>jdbc:oracle:thin:@oracle10g:1521:sid</connection-url>
    <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
    <user-name>username</user-name>
    <password>password</password>

    <min-pool-size>5</min-pool-size>
    <max-pool-size>100</max-pool-size>
    <query-timeout>60</query-timeout>

    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>  

    <metadata>
        <type-mapping>Oracle10g</type-mapping>
    </metadata>

  </local-tx-datasource>

</datasources>

Answer 3

I'd simply like to be able to detect if the connection between Hibernate and my database (MS SQL Server) is open. For example, I'd like to be able to detect this, log it, and try reconnecting again in 60 seconds.

You could indeed use getDelegate() to get the underlying provider object for the EntityManager and then access the underlying JDBC Connection. But read carefully the javadoc:

Return the underlying provider object for the EntityManager, if available. The result of this method is implementation specific.

This makes your code doubly non portable. First it ties you to the underlying implementation (Hibernate's Session here). Second, you may not always get the same result (at least not in an Java EE container).

Really, you don't want this as this totally defeats the point of JPA. Plus, you don't want to handle this low level connection verification yourself.

No, instead, you should use a (standalone) connection pool that will take care of that for you. c3p0 is one of them and it can be configured for connection testing and logging. Going the connection pool way means less code, better reliability, better recovery capabilities, better checks, without loosing portability. This looks like a 100% win-win situation to me.