JPA 2.1-portable way to get a Connection instance from Hibernate?

Question

I try to get a java.sql.Connection instance with EntityManager.unwrap in a JPA-portable way with Hibernate 5.2.5.Final.

Class<?> databaseDriver = EmbeddedDriver.class;
File databaseDir = File.createTempFile(NewClass.class.getSimpleName(), null);
FileUtils.deleteQuietly(databaseDir);
LOGGER.info(String.format("using '%s' as database directory", databaseDir.getAbsolutePath()));
String databaseURL = String.format("jdbc:derby:%s", databaseDir.getAbsolutePath());
Connection connection = DriverManager.getConnection(String.format("%s;create=true", databaseURL));
connection.close();

EntityManagerFactory entityManagerFactory = null;
EntityManager entityManager = null;
try {
    Map<Object, Object> properties = new HashMap<>();
    properties.put("javax.persistence.jdbc.url", databaseURL);
    properties.put("javax.persistence.jdbc.driver", databaseDriver.getName());
    entityManagerFactory = Persistence.createEntityManagerFactory("richtercloud_hibernate-missing-escape-chars_jar_1.0-beta2PU",
            properties);
    entityManager = entityManagerFactory.createEntityManager();
    entityManager.getTransaction().begin();
    connection = entityManager.unwrap(java.sql.Connection.class);
    //do something with connection...
}finally {
    if(entityManager != null) {
        entityManager.close();
    }
    if(entityManagerFactory != null) {
        entityManagerFactory.close();
    }
}

which fails due to Exception in thread "main" javax.persistence.PersistenceException: Hibernate cannot unwrap interface java.sql.Connection. It works fine in Eclipselink 2.6.4.

I know about the (unportable) possibility to unwrap a Hibernate Session and get a Connection from it, but I'd like to know if there's a portable way.

There's Get hold of a JDBC Connection object from a Stateless Bean, but it doesn't state explicitly that Hibernate doesn't support this by specification or due to a bug and it's from 2011.

Answer 1

Since you mentioned JPA I assume you are using j2ee.

First see if this helps: How can i get the session object if i have the entitymanager

If that doesn't help I can explain how I get the connection with Wildfly 9 and 8. You should have the postgresql datasource defined you your standalone.xml. Then you can access via the following java code:

import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.*;
import javax.sql.DataSource;

public class ConnectionFactory {
    /**
     *  'getConnection' method returns the JDBC connection from the DataSource.
     *
     * @return a value of type 'Connection'
     * @exception SQLException if an error occurs
     */
    public static Connection getConnection() throws SQLException{
        return getDBConnection().getConnection();
    }


    public static DataSource getDBConnection() throws SQLException{
        return getDataSource("java:jboss/datasources/PostgreSQLDS"); // jndi
    }

}

An example xml snippet of our postgresql datasource is below. Just set the environment variables to what you need:

        <datasource jta="true" jndi-name="java:jboss/datasources/PostgreSQLDS" pool-name="PostgreSQLDS" enabled="true" use-java-context="true" use-ccm="true">
            <connection-url>jdbc:postgresql://${env.OPENSHIFT_POSTGRESQL_DB_HOST}:${env.OPENSHIFT_POSTGRESQL_DB_PORT}/${env.OPENSHIFT_APP_NAME}</connection-url>
            <driver>postgresql</driver>
            <new-connection-sql>select 1</new-connection-sql>
            <pool>
                <min-pool-size>10</min-pool-size>
                <max-pool-size>50</max-pool-size>
                <!--idle-timeout-minutes>1</idle-timeout-minutes>  Default is 15 mins -->
                <prefill>true</prefill>
                <flush-strategy>IdleConnections</flush-strategy>
            </pool>
            <security>
                <user-name>${env.OPENSHIFT_POSTGRESQL_DB_USERNAME}</user-name>
                <password>${env.OPENSHIFT_POSTGRESQL_DB_PASSWORD}</password>
            </security>
            <validation>
                <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
                <background-validation>true</background-validation>
                <validate-on-match>true</validate-on-match>
            </validation>
            <statement>
                <track-statements>true</track-statements>  <!-- Warn when statements and result sets not closed -->
                <prepared-statement-cache-size>100</prepared-statement-cache-size>
                <share-prepared-statements>true</share-prepared-statements>
            </statement>
        </datasource>