[java] Getting Database connection in pure JPA setup

We have a JPA application (using hibernate) and we need to pass a call to a legacy reporting tool that needs a JDBC database connection as a parameter. Is there a simple way to get access to the JDBC connection hibernate has setup?

Since the code suggested by @Pascal is deprecated as mentioned by @Jacob, I found this another way that works for me.

import org.hibernate.classic.Session;
import org.hibernate.connection.ConnectionProvider;
import org.hibernate.engine.SessionFactoryImplementor;

Session session = (Session) em.getDelegate();
SessionFactoryImplementor sfi = (SessionFactoryImplementor) session.getSessionFactory();
ConnectionProvider cp = sfi.getConnectionProvider();
Connection connection = cp.getConnection();

if you use EclipseLink: You should be in a JPA transaction to access the Connection

entityManager.getTransaction().begin();
java.sql.Connection connection = entityManager.unwrap(java.sql.Connection.class);
...
entityManager.getTransaction().commit();

I'm using a old version of Hibernate (3.3.0) with a newest version of OpenEJB (4.6.0). My solution was:

EntityManagerImpl entityManager = (EntityManagerImpl)em.getDelegate();
Session session = entityManager.getSession();
Connection connection = session.connection();
Statement statement = null;
try {
    statement = connection.createStatement();
    statement.execute(sql);
    connection.commit();
} catch (SQLException e) {
    throw new RuntimeException(e);
}

I had an error after that:

Commit can not be set while enrolled in a transaction

Because this code above was inside a EJB Controller (you can't commit inside a transaction). I annotated the method with @TransactionAttribute(value = TransactionAttributeType.NOT_SUPPORTED) and the problem was gone.

If you are using JAVA EE 5.0, the best way to do this is to use the @Resource annotation to inject the datasource in an attribute of a class (for instance an EJB) to hold the datasource resource (for instance an Oracle datasource) for the legacy reporting tool, this way:

@Resource(mappedName="jdbc:/OracleDefaultDS") DataSource datasource;

Later you can obtain the connection, and pass it to the legacy reporting tool in this way:

Connection conn = dataSource.getConnection();

Here is a code snippet that works with Hibernate 4 based on Dominik's answer

Connection getConnection() {
    Session session = entityManager.unwrap(Session.class);
    MyWork myWork = new MyWork();
    session.doWork(myWork);
    return myWork.getConnection();
}

private static class MyWork implements Work {

    Connection conn;

    @Override
    public void execute(Connection arg0) throws SQLException {
        this.conn = arg0;
    }

    Connection getConnection() {
        return conn;
    }

}

Below is the code that worked for me. We use jpa 1.0, Apache openjpa implementation.

import java.sql.Connection;
import org.apache.openjpa.persistence.OpenJPAEntityManager;
import org.apache.openjpa.persistence.OpenJPAPersistence;

public final class MsSqlDaoFactory {


       public static final Connection getConnection(final EntityManager entityManager) {
              OpenJPAEntityManager openJPAEntityManager = OpenJPAPersistence.cast(entityManager);
              Connection connection = (Connection) openJPAEntityManager.getConnection();
              return connection;

        }

}

As per the hibernate docs here,

Connection connection()

Deprecated. (scheduled for removal in 4.x). Replacement depends on need; for doing direct JDBC stuff use doWork(org.hibernate.jdbc.Work) ...

Use Hibernate Work API instead:

Session session = entityManager.unwrap(Session.class);
session.doWork(new Work() {

    @Override
    public void execute(Connection connection) throws SQLException {
        // do whatever you need to do with the connection
    }
});

Hibernate uses a ConnectionProvider internally to obtain connections. From the hibernate javadoc:

The ConnectionProvider interface is not intended to be exposed to the application. Instead it is used internally by Hibernate to obtain connections.

The more elegant way of solving this would be to create a database connection pool yourself and hand connections to hibernate and your legacy tool from there.

Hibernate 4 / 5:

Session session = entityManager.unwrap(Session.class);
session.doWork(connection -> doSomeStuffWith(connection));

I ran into this problem today and this was the trick I did, which worked for me:

   EntityManagerFactory emf = Persistence.createEntityManagerFactory("DAOMANAGER");
   EntityManagerem = emf.createEntityManager();

   org.hibernate.Session session = ((EntityManagerImpl) em).getSession();
   java.sql.Connection connectionObj = session.connection();

Though not the best way but does the job.

The word pure doesn't match to the word hibernate.

EclipseLink

• Getting a JDBC Connection from an EntityManager

It's somewhat straightforward as described in above link.

• Note that the EntityManager must be joined to a Transaction or the unwrap method will return null. (Not a good move at all.)
• I'm not sure the responsibility of closing the connection.

// --------------------------------------------------------- EclipseLink
try {
    final Connection connection = manager.unwrap(Connection.class);
    if (connection != null) { // manage is not in any transaction
        return function.apply(connection);
    }
} catch (final PersistenceException pe) {
    logger.log(FINE, pe, () -> "failed to unwrap as a connection");
}

Hibernate

It should be, basically, done with following codes.

// using vendor specific APIs
final Session session = (Session) manager.unwrap(Session.class);
//return session.doReturningWork<R>(function::apply);
return session.doReturningWork(new ReturningWork<R>() {
    @Override public R execute(final Connection connection) {
        return function.apply(connection);
    }
});

Well, we (at least I) might don't want any vendor-specific dependencies. Proxy comes in rescue.

try {
    // See? You shouldn't fire me, ass hole!!!
    final Class<?> sessionClass
            = Class.forName("org.hibernate.Session");
    final Object session = manager.unwrap(sessionClass);
    final Class<?> returningWorkClass
            = Class.forName("org.hibernate.jdbc.ReturningWork");
    final Method executeMethod
            = returningWorkClass.getMethod("execute", Connection.class);
    final Object workProxy = Proxy.newProxyInstance(
            lookup().lookupClass().getClassLoader(),
            new Class[]{returningWorkClass},
            (proxy, method, args) -> {
                if (method.equals(executeMethod)) {
                    final Connection connection = (Connection) args[0];
                    return function.apply(connection);
                }
                return null;
            });
    final Method doReturningWorkMethod = sessionClass.getMethod(
            "doReturningWork", returningWorkClass);
    return (R) doReturningWorkMethod.invoke(session, workProxy);
} catch (final ReflectiveOperationException roe) {
    logger.log(Level.FINE, roe, () -> "failed to work with hibernate");
}

OpenJPA

• Runtime Access to DataSource
• OPENJPA-1803 Unwrap EntityManager to Connection

I'm not sure OpenJPA already serves a way using unwrap(Connection.class) but can be done with the way described in one of above links.

It's not clear the responsibility of closing the connection. The document (one of above links) seems saying clearly but I'm not good at English.

try {
    final Class<?> k = Class.forName(
            "org.apache.openjpa.persistence.OpenJPAEntityManager");
    if (k.isInstance(manager)) {
        final Method m = k.getMethod("getConnection");
        try {
            try (Connection c = (Connection) m.invoke(manager)) {
                return function.apply(c);
            }
        } catch (final SQLException sqle) {
            logger.log(FINE, sqle, () -> "failed to work with openjpa");
        }
    }
} catch (final ReflectiveOperationException roe) {
    logger.log(Level.FINE, roe, () -> "failed to work with openjpa");
}