java hibernate c3p0存储过程名为query error语句关闭后不允许操作，无法释放jdbc连接

o2rvlv0m  于 2021-06-24  发布在  Mysql
关注(0)|答案(2)|浏览(413)

在运行存储过程时遇到了一些奇怪的问题 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>

休眠.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(); 行（即：尝试在没有事务的情况下调用存储过程）。
然而，我得到以下例外的行 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)

Javamysqlstored-proceduresHibernatec3p0

2条答案

按热度按时间
k5ifujac

k5ifujac1#

hibernate将存储过程调用视为sql查询，但由于它是一个存储过程，所以并不完全是sql查询。它看起来像是在重用查询周围的某些状态，当您第二次使用它时，这些状态会导致问题。很可能是在幕后打开/关闭一条语句，然后当您将其作为命名查询获取时，会得到同一条语句的副本。
尝试改用callablestatement，以便hibernate知道如何正确处理调用。类似这样的内容（徒手画，未经测试）：

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

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

赞(0）分享  回复(0）举报  2021-06-24
7nbnzgx9

7nbnzgx92#

下面是使用eclipselink的解决方案。我已经在一个循环中运行了多次。它运行良好，没有任何问题。https://wiki.eclipse.org/eclipselink/examples
持久性.xml

<persistence-unit name="mytrial" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
    <mapping-file>WebUserData.xml</mapping-file>
    <properties>
         <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
         <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/xxxx"/>
         <property name="javax.persistence.jdbc.user" value="root"/>
         <property name="javax.persistence.jdbc.password" value="xxxxxxx"/>
    </properties>
</persistence-unit>

Mapxml文件（webuserdata.xml）

<named-stored-procedure-query name="GetWebUserData" procedure-name="GetWebUserData">
    <parameter class="java.lang.Integer" mode="IN" name="requestId" />
    <result-class>WebUserData</result-class>
</named-stored-procedure-query>

<entity class="WebUserData">
    <attributes>
        <id name="keyId"></id>
        <basic name="name"></basic>
    </attributes>
</entity>

调用存储过程

EntityManager em = emFactory.createEntityManager();
EntityTransaction tx = em.getTransaction();
tx.begin();
StoredProcedureQuery spQuery = em.createNamedStoredProcedureQuery("GetWebUserData");
spQuery.setParameter("requestId",IntegerType.INSTANCE.fromString("1"));
List result = spQuery.getResultList();
tx.commit();
em.close();
赞(0）分享  回复(0）举报  2021-06-24
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com