解决方法
在调用存储过程的方法上添加事务注解
解决过程
我使用springboot2.0.5版本,采用其内置的数据库连接池HikariCP2.7.9,在调用存储过程时报错,该存储过程需要两个入参和一个出参。其中一条出错信息为:Caused by: org.hibernate.exception.GenericJDBCException: Unable to extract OUT/INOUT parameter value,实际上该存储过程已经执行了,但是在接收出参时报错,看一下报错日志:
Caused by: com.mysql.cj.exceptions.StatementIsClosedException: No operations allowed after statement closed.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~\[?:1.8.0_181\]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~\[?:1.8.0_181\]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~\[?:1.8.0_181\]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~\[?:1.8.0_181\]
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~\[mysql-connector-java-8.0.12.jar:8.0.12\]
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85) ~\[mysql-connector-java-8.0.12.jar:8.0.12\]
at com.mysql.cj.jdbc.StatementImpl.checkClosed(StatementImpl.java:357) ~\[mysql-connector-java-8.0.12.jar:8.0.12\]
at com.mysql.cj.jdbc.CallableStatement.getInt(CallableStatement.java:1262) ~\[mysql-connector-java-8.0.12.jar:8.0.12\]
at com.zaxxer.hikari.pool.HikariProxyCallableStatement.getInt(HikariProxyCallableStatement.java) ~\[HikariCP-2.7.9.jar:?\]
at org.hibernate.type.descriptor.sql.IntegerTypeDescriptor$2.doExtract(IntegerTypeDescriptor.java:67) ~\[hibernate-core-5.2.17.Final.jar:5.2.17.Final\]
at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:90) ~\[hibernate-core-5.2.17.Final.jar:5.2.17.Final\]
at org.hibernate.type.AbstractStandardBasicType.extract(AbstractStandardBasicType.java:378) ~\[hibernate-core-5.2.17.Final.jar:5.2.17.Final\]
at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.extract(AbstractParameterRegistrationImpl.java:408) ~\[hibernate-core-5.2.17.Final.jar:5.2.17.Final\]
at org.hibernate.procedure.internal.ProcedureOutputsImpl.getOutputParameterValue(ProcedureOutputsImpl.java:48) ~\[hibernate-core-5.2.17.Final.jar:5.2.17.Final\]
at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputParameterValue(ProcedureCallImpl.java:702) ~\[hibernate-core-5.2.17.Final.jar:5.2.17.Final\]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~\[?:1.8.0_181\]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~\[?:1.8.0_181\]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~\[?:1.8.0_181\]
at java.lang.reflect.Method.invoke(Method.java:498) ~\[?:1.8.0_181\]
依据日志点击StatementImpl找到checkClosed方法,发现是由于connection为null导致,也就是说当我在获取出参时,已经获取不到连接了。于是我顺着spring-data-jpa执行存储过程的几个类一个个打断点,发现程序抛出了一个异常,位于TransactionAspectSupport类,提示没有事务(No transaction),于是在调用存储过程的方法上添加事务注解,得以解决(PS:由于没在方法上添加@Transactional,所以程序在执行完存储过程后就立刻提交了事务,而不是等我整个方法执行完成后才提交,导致checkClosed时获取不到connection)。
以下是我调用存储过程的方式:
@Transactional
@Procedure(procedureName = "bind", outputParameterName = "o_result")
Integer bindRedPacket(@Param("i\_id") String id, @Param("i\_tel") String tel);
补充:
最初我用springboot1.5.6是没有问题的,不需要加事务注解,原因打断点就知道了。换成1.5.16不行,报错Caused by: java.sql.SQLException: Statement closed,也需要添加事务注解,打断点,内部抛出异常:![](https://img.haomeiwen.com/i164132/f5e3bbd0c00888ff.jpg)
虽然和2x版本不同,但还是没加事务注解导致。
网友评论