使用 Java 运行我的存储过程时遇到一些奇怪的问题, Hibernate , C3P0 , named query , MySQL .

存储过程

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调用存储过程

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;
    }
}

问题:存储过程第一次运行时没有报错。 但是，第二次运行时，出现以下异常

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)

备选方案 #1:我尝试注释掉

Transaction txD = session.beginTransaction();

和

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

但是，我在 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)

备选方案 #2:我尝试注释掉“nativeQuery.setReadOnly(false);”这一行只是为了看看行为。在这种情况下，会发生以下异常:

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)

最佳答案

Hibernate 将您的存储过程调用视为一个 sql 查询，这并不完全是因为它是一个存储过程。看起来它正在重复使用查询周围的某些状态，当您第二次使用它时会导致问题。最有可能在幕后打开/关闭一个语句，然后当您将它作为命名查询获取时，您会得到相同语句的副本。

尝试使用 CallableStatement 代替，以便 hibernate 知道正确处理调用。像这样的东西(手绘，未经测试):

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

使用可调用语句应防止任何语句重用。

关于Java Hibernate C3P0存储过程命名查询错误-语句关闭后不允许进行操作，无法释放JDBC连接，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/51025123/