MySQL database connection error: ManagedConnectionFactory is null

Question

In my application I have used Hibernate with mysql for connecting to the database. This application connects to the db when ever there is a request from GUI. When ever there is no request from GUI for a long period of time, after trying to connect again to the database. I get the following error. What could be the possible solution ?

 org.jboss.util.NestedSQLException: You are trying to use a connection factory that has been shut down: ManagedConnectionFactory is null.; - nested throwable: 
(javax.resource.ResourceException: You are trying to use a connection factory that has been shut down: ManagedConnectionFactory is null.)
    at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:95)
    at org.hibernate.connection.DatasourceConnectionProvider.getConnection(DatasourceConnectionProvider.java:92)`

Here is my code connecting to the database.

SessionFactory factory;
factory = new Configuration().configure().buildSessionFactory();
session = factory.openSession();
SessionFactoryImplementor impl = (SessionFactoryImplementor)session.getSessionFactory();
ConnectionProvider cp = impl.getConnectionProvider();
conn = cp.getConnection();//This conn is used for prepared statement and so on..

Here is my mysql ds file

<datasources>
  <local-tx-datasource>
    <jndi-name>MySqlDS</jndi-name>
    <use-java-context>true</use-java-context>
    <url-delimiter>|</url-delimiter>
    <connection-url>jdbc:mysql://localhost:3306/TEST_DB?zeroDateTimeBehavior=convertToNull</connection-url>

    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>user</user-name>
    <password>****</password>
    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
    <new-connection-sql>select current_date()</new-connection-sql>
    <check-valid-connection-sql>select current_date()</check-valid-connection-sql>
    <min-pool-size>1</min-pool-size>
    <max-pool-size>5</max-pool-size>
    <set-tx-query-timeout/> 
    <query-timeout>300</query-timeout>

    <metadata>
       <type-mapping>mySQL</type-mapping>
    </metadata>
  </local-tx-datasource>

</datasources>

Answer 1

You're doing it all wrong. The Hibernate session should only be connected to the database for the lifetime of a single transaction. Once you are done with a transaction you should close the session too.

You can define a TransactionCallback first:

public interface TransactionCallable<T> {
    public abstract T execute(Session session);
}

Then you define a Helper session management method:

protected <T> T doInTransaction(TransactionCallable<T> callable) {
    T result = null;
    Session session = null;
    Transaction txn = null;
    try {
        session = sf.openSession();
        txn = session.beginTransaction();

        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {

            }
        });

        result = callable.execute(session);
        txn.commit();
    } catch (RuntimeException e) {
        if ( txn != null && txn.isActive() ) txn.rollback();
        throw e;
    } finally {
        if (session != null) {
            session.close();
        }
    }
    return result;
}

So you can even run native queries as you suggested:

final String sql = ...;

doInTransaction(new TransactionCallable<Object>() {
    Boolean result;
    @Override
    public Object execute(Session session) {
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                Statement statement = connection.createStatement();
                result = statement.execute(sql);
            }
        });
        return result;
    }
});