If using Hibernate with Database (and c3p0 dbpooling) is is still possible and safe to directly get connection bypassing Hibernate

Question

Using Hibernate 4.3.11 with H2 1.4.199 and C3p0

If using Hibernate with Database is is still possible and safe to directly get connection from pool bypassing Hibernate?

Im trying to write an sql query, now I know I can use session.createSQLQuery() but this returns a Hibernate org.hibernate SQLQuery class rather than a java.sql.Connection and this is causing a problem for me trying to set the parameters, so I wanted to try using a Connection instead.

Answer 1

Try this.

import org.hibernate.engine.jdbc.connections.spi.ConnectionProvider;

sessionFactory.getSessionFactoryOptions().getServiceRegistry().getService(ConnectionProvider.class).getConnection();

I have not tested this and cannot do it at this time. I am only 50-50 on the ConnectionProvider, since I am not sure what the provider class for c3p0. May be org.hibernate.c3p0.internal.C3P0ConnectionProvider or you already know it.

EDIT 11-10-2019

The connections are returned from the implementations of org.hibernate.engine.jdbc.connections.spi.ConnectionProvider interface, as far as I can see.

org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl
org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl

Both these implementations are fetching a java.sql.Connection from Datasource or PooledConnections that are internally maintained at the time of service registry.

DatasourceConnectionProviderImpl has the following methods.

    @Override
    public Connection getConnection() throws SQLException {
        if ( !available ) {
            throw new HibernateException( "Provider is closed!" );
        }
        return useCredentials ? dataSource.getConnection( user, pass ) : dataSource.getConnection();
    }

    @Override
    public void closeConnection(Connection connection) throws SQLException {
        connection.close();
    }

DriverManagerConnectionProviderImpl methods follow

@Override
public Connection getConnection() throws SQLException {
    if ( !active ) {
        throw new HibernateException( "Connection pool is no longer active" );
    }

    return pool.poll();
}

public Connection poll() throws SQLException {
        Connection conn = availableConnections.poll();
        if ( conn == null ) {
            synchronized (allConnections) {
                if(allConnections.size() < maxSize) {
                    addConnections( 1 );
                    return poll();
                }
            }
            throw new HibernateException( "The internal connection pool has reached its maximum size and no connection is currently available!" );
        }
        conn.setAutoCommit( autoCommit );
        return conn;
    }


@Override
public void closeConnection(Connection conn) throws SQLException {
    if (conn == null) {
        return;
    }

    pool.add( conn );
}

And as you can see, they both have different ways of handling connections. I would not imagine major issues if you close the connection, since the first one returns new connection and the second one replenishes the pool if the connection is closed. However, if you are not going to close, know which implementation is getting invoked in your application and make sure you do not have a leak.

The other two implementations are for Hikari and UserSupplied mode.