Get hold of a JDBC Connection object from a Stateless Bean

Question

In a Stateless Session Bean an EntityManager is injected but I would like to get hold of a Connection object in order to invoke a DB Procedure. Is there any solution to this ?

Answer 1

This is going to be JPA provider specific code. Typically this is done by invoking unwrap() on the EntityManager class.

If you are using EclipseLink, the following code (from the EclipseLink wiki) will be useful (in the case you are using an application-managed EntityManager) :

JPA 2.0

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

JPA 1.0

entityManager.getTransaction().begin();
UnitOfWork unitOfWork = (UnitOfWork)((JpaEntityManager)entityManager.getDelegate()).getActiveSession();
unitOfWork.beginEarlyTransaction();
Accessor accessor = unitOfWork.getAccessor();
accessor.incrementCallCount(unitOfWork.getParent());
accessor.decrementCallCount();
java.sql.Connection connection = accessor.getConnection();
...
entityManager.getTransaction().commit();

Note, that the solution provided for JPA 2.0 will fail for Hibernate 3.6.5 with a PersistenceException containing the message

Hibernate cannot unwrap interface java.sql.Connection

Use the code provided by Skaffman to get it to work against Hibernate (verified to work under 3.6.5 even for container managed persistence contexts).

However, the EclipseLink wiki points out one useful bit of info - if you are using JTA managed datasources, you should be injecting it using the @Resource annotation or retrieving it using a JNDI lookup. As long as you need to perform transactional work against the database, it is immaterial as to whether you are obtaining a new connection from the data source or an existing one; most connection pools will anyway provide the same connection that is associated with the current thread (i.e. the one already used by the entity manager). You would therefore avoiding unwrapping the entity manager this way, and also perform transactional activity against the database; do remember that the persistence context cache, and a second-level cache may not be synchronized if you do this.

Answer 2

In Hibernate, the solution posted by skaffman resulted in the following error message:

Hibernate cannot unwrap class org.hsqldb.Session

I did get it to work using SessionImpl rather than Session:

Connection connection = entityManager().unwrap(SessionImpl.class).connection();

An example of solving the problem using Session.doWork() is as follows:

private void executeNative(final String query) {
    Session session = entityManager.unwrap(Session.class);
    session.doWork(new Work() {

        @Override
        public void execute(Connection connection) throws SQLException {
            Statement s = null;
            try {
                s = connection.createStatement();
                s.executeUpdate(query);
            } 
            finally {
                if (s != null) {
                    s.close();
                }
            }
        }

    });
}

Answer 3

The JPA API itself doesn't seem to offer this, not surprisingly, but if you're willing to couple your code to a specific implementation, then you can use something like this (Hibernate):

Session hibernateSession = entityManager.unwrap(Session.class);
Connection jdbcConnection = hibernateSession.connection(); 

Note that Session.connection() is deprecated for removal in Hibernate 4. Consider using Session.doWork() instead.

Answer 4

You must take the underlying delegate using entitymanager.getDelegate() or entitymanager.unwrap(which is the better way), cast it to the specific implementation(in Hibernate it is called Session). Then you can call the connection() method. Be aware this is deprecated, use the Work class instead. Read more here.

Answer 5

This works awesomely and you can use the connection object elsewhere if needed

SessionImpl sessionImpl = (SessionImpl) session;
Connection conn = sessionImpl.connection();

Where session is the name of the Hibernate Session object

Answer 6

In JPA2.0, if need JDBC is por DTO nomodel or entity for query more complex. Sometimes JPA is not all...

I hope this will help you:

Statement statement = null;
EntityManager em = null;
em = emf.createEntityManager();
EntityTransaction et = em.getTransaction();

if(!et.isActive()) {
    et.begin();
}

java.sql.Connection connection = em.unwrap(java.sql.Connection.class);

    String qquerry="SELE ...
    try {   
        statement = connection.createStatement();
        ResultSet rs =  statement.executeQuery(qquerry);                                                              

        if (!rs.next()) {
            return null;
        }
        else{
            wwwwas=rs.getString(4);                                 
        }         
        statement.close();
        } 
    catch (SQLException e) {
        System.out.println("\n b-03:"+e);
        throw new RuntimeException(e.getMessage(), e);
    } 
    finally {
        try {
            //  em.getTransaction().commit();
            if(connection != null )
            connection.close();
        } 
        catch (Exception e) {
            throw new RuntimeException(e.getMessage(), e);
       }
    }

Answer 7

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;

    }

}