spring-boot - CreateNamedStoredProcedureQuery 无法获取参数

Question

我正在尝试通过 JPA 执行存储过程。我通过注释设置了存储过程:

@NamedStoredProcedureQuery(
    name = 'getNextKey',
    procedureName = 'usp_getNextKey',
    parameters = [
            @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name='table_name'),
            @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name='field_name'),
            @StoredProcedureParameter(mode = ParameterMode.OUT, type = Integer.class, name = 'nextValue')
    ]
)

该参数存在于数据库中，我试图按如下方式调用该过程:

    StoredProcedureQuery getNextKeyQuery = this.entityManager.createNamedStoredProcedureQuery("getNextKey")

    getNextKeyQuery.setParameter('table_name', "SESSION_CONTROL")
    getNextKeyQuery.setParameter('field_name', "session_key")

    getNextKeyQuery.execute()
    Object sessionKey = getNextKeyQuery.getOutputParameterValue('nextValue')

当我运行上面的代码时，出现以下错误:

org.hibernate.exception.GenericJDBCException: Unable to extract OUT/INOUT parameter value
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.extract(AbstractParameterRegistrationImpl.java:417)
    at org.hibernate.procedure.internal.ProcedureOutputsImpl.getOutputParameterValue(ProcedureOutputsImpl.java:46)
    at org.hibernate.jpa.internal.StoredProcedureQueryImpl.getOutputParameterValue(StoredProcedureQueryImpl.java:273)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:374)
    at com.sun.proxy.$Proxy118.getOutputParameterValue(Unknown Source)
    at javax.persistence.StoredProcedureQuery$getOutputParameterValue$1.call(Unknown Source)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
    at com.fhlbdm.collateral.cukes.steps.LoanListingSteps.aNewLoanListingFileIsSubmitted(LoanListingSteps.groovy:54)
    at ✽.a new loan listing file is submitted(com/fhlbdm/collateral/cukes/light-listing-loan-to-rai.feature:6)
Caused by: java.sql.SQLException: Statement closed.
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:110)
    at com.sun.proxy.$Proxy119.getInt(Unknown Source)
    at org.hibernate.type.descriptor.sql.IntegerTypeDescriptor$2.doExtract(IntegerTypeDescriptor.java:67)
    at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:90)
    at org.hibernate.type.AbstractStandardBasicType.extract(AbstractStandardBasicType.java:341)
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.extract(AbstractParameterRegistrationImpl.java:400)
    at org.hibernate.procedure.internal.ProcedureOutputsImpl.getOutputParameterValue(ProcedureOutputsImpl.java:46)
    at org.hibernate.jpa.internal.StoredProcedureQueryImpl.getOutputParameterValue(StoredProcedureQueryImpl.java:273)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:374)
    at com.sun.proxy.$Proxy118.getOutputParameterValue(Unknown Source)
    at javax.persistence.StoredProcedureQuery$getOutputParameterValue$1.call(Unknown Source)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
    at com.fhlbdm.collateral.cukes.steps.LoanListingSteps.aNewLoanListingFileIsSubmitted(LoanListingSteps.groovy:54)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at cucumber.runtime.Utils$1.call(Utils.java:32)
    at cucumber.runtime.Timeout.timeout(Timeout.java:16)
    at cucumber.runtime.Utils.invoke(Utils.java:26)
    at cucumber.runtime.java.JavaStepDefinition.execute(JavaStepDefinition.java:37)
    at cucumber.runtime.StepDefinitionMatch.runStep(StepDefinitionMatch.java:40)
    at cucumber.api.TestStep.executeStep(TestStep.java:102)
    at cucumber.api.TestStep.run(TestStep.java:83)
    at cucumber.api.TestCase.run(TestCase.java:58)
    at cucumber.runner.Runner.runPickle(Runner.java:80)
    at cucumber.runtime.junit.PickleRunners$NoStepDescriptions.run(PickleRunners.java:140)
    at cucumber.runtime.junit.FeatureRunner.runChild(FeatureRunner.java:68)
    at cucumber.runtime.junit.FeatureRunner.runChild(FeatureRunner.java:23)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at cucumber.runtime.junit.FeatureRunner.run(FeatureRunner.java:73)
    at cucumber.api.junit.Cucumber.runChild(Cucumber.java:117)
    at cucumber.api.junit.Cucumber.runChild(Cucumber.java:55)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at cucumber.api.junit.Cucumber$1.evaluate(Cucumber.java:126)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)

我被困在它所说的由 Statement Closed 引起的地方。我试过将它包装在一个事务中，但实体管理器是共享的，我无法在那里创建一个事务。当我将它包装在事务模板中时，我得到与上面相同的错误。

这是正在使用的配置:

logging.level.org.hibernate.SQL=INFO
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
spring.jpa.properties.hibernate.type=trace
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true

spring.jpa.show-sql=true
spring.jpa.generate-ddl=false
spring.jpa.hibernate.ddl-auto=create

#dburl=jdbc:hsqldb:mem:collateral-staging
dburl=jdbc:hsqldb:file:/Users/${user.name}/hsql/processes/staging

datasource.collateral.staging.driverClassName=org.hsqldb.jdbcDriver
datasource.collateral.staging.url=${dburl}
datasource.collateral.staging.username=SA
datasource.collateral.staging.password=

Answer 1

@tfitzger 您遇到的问题与存储过程定义的代理无法识别的事务设置有关。您应该使用 @Transactional (JPA 或 Spring 视情况而定)符号来解决您的 Statements 在您能够提取输出参数的值之前已关闭的问题。通过这种方式，两个句子( getNextKeyQuery.execute() 和 getNextKeyQuery.getOutputParameterValue('nextValue') )将共享相同的数据库事务并以预期的方式运行。

另外不要忘记阅读适用于您的用例的 StoredProcedureQuery 规范。

https://javaee.github.io/javaee-spec/javadocs/javax/persistence/StoredProcedureQuery.html