Java Hibernate C3P0 stored procedure named query error-No operations allowed after statement closed, Unable to release JDBC Connection

Question

Facing some weird issues running my stored procedure using Java, Hibernate, C3P0, named query, MySQL.

Stored procedure

DELIMITER $$
USE `testdb`;
CREATE PROCEDURE `GetWebUserData`(IN requestId INT)
BEGIN
    DECLARE keyId LONG DEFAULT 1;
    DECLARE name VARCHAR(255) DEFAULT 'testname';

    Select keyId,name;
END;
$$
DELIMITER ;

WebUserData.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="com.WebUserData" table="WEBUSERDATA">
        <id name="keyId" type="java.lang.Long">
            <column name="keyId" />
            <generator class="assigned" />
        </id>
        <property generated="never" lazy="false" name="name" type="java.lang.String">
            <column name="name" />
        </property>
        <loader query-ref="GetWebUserData" />
    </class>
    <sql-query callable="true" name="GetWebUserData" read-only="false">
        <return alias="gwud" class="com.WebUserData">
            <return-property column="KEYID" name="keyId" />
            <return-property column="NAME" name="name" />
        </return>
        <query-param name="requestId" type="java.lang.long" />
        <![CDATA[CALL GetWebUserData(:requestId)]]>
    </sql-query>
</hibernate-mapping>

Hibernate.cfg.xml

    <property name="hibernate.c3p0.idle_test_period">3000</property>
    <property name="hibernate.c3p0.timeout">3600</property>
    <property name="hibernate.c3p0.testConnectionOnCheckin">false</property>
    <property name="hibernate.c3p0.testConnectionOnCheckout">true</property>
    <property name="hibernate.c3p0.min_size">1</property>
    <property name="hibernate.c3p0.max_size">10</property>
    <property name="hibernate.c3p0.numHelperThreads">25</property>
    <property name="hibernate.c3p0.max_statements">0</property>
    <property name="hibernate.c3p0.maxStatementsPerConnection">12</property>
    <property name="hibernate.c3p0.acquire_increment">1</property>
    <property name="hibernate.c3p0.idle_test_periods">3000</property>

Java version: 8
Hibernate core version: 25.1-jre
Hibernate-c3p0 version: 5.3.1.Final
com.mchange.c3p0 version: 0.9.5.2
MySQL connection version: 8.0.11

Java calling stored procedure

public List<?> callStoredProc() {
    try {
        Session session = sessionFactory.openSession();
        Transaction txD = session.beginTransaction();
        NativeQuery<?> nativeQuery = session.getNamedNativeQuery("GetWebUserData");
        nativeQuery.setParameter("requestId", "1");
        nativeQuery.setReadOnly(false);

        List<?> rows = nativeQuery.list();

        txD.commit();
        session.close();

        return rows;
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }
}

Issue: When the stored procedure is run for the first time, there is no error. However, when it is run the second time, below exception occurs at the line

List rows = nativeQuery.list();

2018-06-23 16:33:47 WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: S1009
2018-06-23 16:33:47 WARN  SqlExceptionHelper:129 - SQL Error: 0, SQLState: S1009
2018-06-23 16:33:47 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - No operations allowed after statement closed.
2018-06-23 16:33:47 ERROR SqlExceptionHelper:131 - No operations allowed after statement closed.
2018-06-23 16:33:47 DEBUG org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl - JDBC transaction marked for rollback-only (exception provided for stack trace)
java.lang.Exception: exception just for purpose of providing stack trace
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.markRollbackOnly(JdbcResourceLocalTransactionCoordinatorImpl.java:314)
    at org.hibernate.engine.transaction.internal.TransactionImpl.markRollbackOnly(TransactionImpl.java:200)
    at org.hibernate.internal.AbstractSharedSessionContract.markForRollbackOnly(AbstractSharedSessionContract.java:378)
    at org.hibernate.internal.ExceptionConverterImpl.handlePersistenceException(ExceptionConverterImpl.java:273)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:150)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1515)
    at com.mnox.core.hibernate.DatabaseEngine.callStoredProc(DatabaseEngine.java:712)

Alternative #1: I have tried commenting out the

Transaction txD = session.beginTransaction();

and

txD.commit(); lines (i.e: trying calling the stored procedure without a transaction).

However, I get below exception at the line List<?> rows = nativeQuery.list();

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Unable to release JDBC Connection
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1515)
    at com.mnox.core.hibernate.DatabaseEngine.callStoredProc(DatabaseEngine.java:712)

Alternative #2: I tried commenting out the line “nativeQuery.setReadOnly(false);” just to see the behavior. In that case, below exception occurs:

2018-06-23 17:58:03 DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger - CALL GetWebUserData(?)
2018-06-23 17:58:03 DEBUG SQL:94 - CALL GetWebUserData(?)
2018-06-23 17:58:04 DEBUG org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl - Exception clearing maxRows/queryTimeout [No operations allowed after statement closed.]
2018-06-23 17:58:04 DEBUG ResourceRegistryStandardImpl:175 - Exception clearing maxRows/queryTimeout [No operations allowed after statement closed.]
2018-06-23 17:58:04 DEBUG org.hibernate.engine.jdbc.spi.SqlExceptionHelper - could not execute query [CALL GetWebUserData(?)]
java.sql.SQLException: No operations allowed after statement closed.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:127)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:87)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:61)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:71)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:82)
    at com.mysql.cj.jdbc.ClientPreparedStatement.setString(ClientPreparedStatement.java:1764)
    at com.mchange.v2.c3p0.impl.NewProxyCallableStatement.setString(NewProxyCallableStatement.java:3687)
    at org.hibernate.type.descriptor.sql.VarcharTypeDescriptor$1.doBind(VarcharTypeDescriptor.java:46)
    at org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:74)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:280)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:275)
    at org.hibernate.loader.custom.sql.NamedParamBinder.bind(NamedParamBinder.java:34)
    at org.hibernate.loader.custom.CustomLoader.bindParameterValues(CustomLoader.java:475)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:2000)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1914)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892)
    at org.hibernate.loader.Loader.doQuery(Loader.java:937)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340)
    at org.hibernate.loader.Loader.doList(Loader.java:2689)
    at org.hibernate.loader.Loader.doList(Loader.java:2672)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506)
    at org.hibernate.loader.Loader.list(Loader.java:2501)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2223)
    at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1053)
    at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:168)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1506)
    at com.mnox.core.hibernate.DatabaseEngine.callStoredProc(DatabaseEngine.java:711)

Answer 1

Hibernate is treating your stored procedure call as a sql query, which it isn't exactly since it is a stored procedure. It looks like it is reusing some state around the query that will cause problems when you use it the second time. Most likely behind the scenes a Statement is opened/closed and then you get a copy of the same statement when you fetch it as a named query.

Try using a CallableStatement instead so that hibernate knows to handle the invocation correctly. Something like this (freehand, not tested):

CallableStatment stat = conn.prepareCall("{? = CALL GetWebUserData (?)}");
stat.setString(1, 1); 
stat.execute();
ResultSet set = stat..getResultSet();
...

Using a callable statement should prevent any statement reuse.