Check the output parameters (register output parameters failed) IN Ibatis

最新推荐文章于 2021-03-02 18:29:48 发布

iteye_15826

最新推荐文章于 2021-03-02 18:29:48 发布

阅读量674

点赞数

分类专栏： IBatis 文章标签： iBATIS Java Oracle junit AOP

IBatis 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

使用IBatis 在调用Oracle 的存储过程或者函数时出现以下异常, 内容如下:

-----------------------------------------------------------------------------------------------

org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [17004];
--- The error occurred in ibatis-conf/MpsBillPayMainBean.xml.
--- The error occurred while executing query procedure.
--- Check the {?=call jtosa_fun_billpaymainlistcount(?,?,?)}.
--- Check the output parameters (register output parameters failed).
--- Cause: java.sql.SQLException: 无效的列类型; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in ibatis-conf/MpsBillPayMainBean.xml.
--- The error occurred while executing query procedure.
--- Check the {?=call jtosa_fun_billpaymainlistcount(?,?,?)}.
--- Check the output parameters (register output parameters failed).
--- Cause: java.sql.SQLException: 无效的列类型
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212)
    at org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithMapResult(SqlMapClientTemplate.java:260)
    at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForMap(SqlMapClientTemplate.java:372)
    at com.jtosa.dao.impl.BillPayMainDaoImpl.findBillPayMainListCount(BillPayMainDaoImpl.java:31)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    at $Proxy11.findBillPayMainListCount(Unknown Source)
    at com.jtosa.service.impl.BillPayMainServiceImpl.searchBillPayMainListCount(BillPayMainServiceImpl.java:20)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    at $Proxy30.searchBillPayMainListCount(Unknown Source)
    at com.jtosa.service.impl.test.BillPayMainServiceImplTest.testSearchBillPayMainListCount(BillPayMainServiceImplTest.java:30)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMethodRunner.java:99)
    at org.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRunner.java:81)
    at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
    at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75)
    at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45)
    at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:66)
    at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35)
    at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42)
    at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
    at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in ibatis-conf/MpsBillPayMainBean.xml.
--- The error occurred while executing query procedure.
--- Check the {?=call jtosa_fun_billpaymainlistcount(?,?,?)}.
--- Check the output parameters (register output parameters failed).
--- Cause: java.sql.SQLException: 无效的列类型
    at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201)
    at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:567)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:541)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForMap(SqlMapExecutorDelegate.java:658)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForMap(SqlMapExecutorDelegate.java:640)
    at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForMap(SqlMapSessionImpl.java:148)
    at org.springframework.orm.ibatis.SqlMapClientTemplate$7.doInSqlMapClient(SqlMapClientTemplate.java:374)
    at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)
    ... 47 more
Caused by: java.sql.SQLException: 无效的列类型
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
    at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3424)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:125)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:268)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:348)
    at com.mchange.v2.c3p0.impl.NewProxyCallableStatement.registerOutParameter(NewProxyCallableStatement.java:311)
    at com.ibatis.sqlmap.engine.execution.SqlExecutor.registerOutputParameters(SqlExecutor.java:431)
    at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:274)
    at com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:39)
    at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
    ... 55 more

----------------------------------------------------------------------------------------------------

错误原因是:数据类型不正确.

    <parameterMap class="java.util.HashMap" id="paramMap">
        <parameter property="total" jdbcType="NUMBER " javaType="java.lang.Long" mode="OUT"/>
        <parameter property="payTime" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
        <parameter property="payEndTime" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
        <parameter property="cityCode" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
    </parameterMap>

    <procedure id="findBillPayMainListCount" parameterMap="paramMap" resultClass="java.lang.Long">
        {?=call jtosa_fun_billpaymainlistcount(?,?,?)}
    </procedure>

    <procedure id="findBillPayMainList" parameterMap="listparamMap" resultMap="BillPayMainBean">
        {call jtosa_porc_billPayMainList(?,?,?,?,?,?)}
    </procedure>

----------------------------------------------------------------------------------

NUMBER 是oracle 数据库中的数据类型,但是在java.sql.Types 中并没有些常量.

只要将NUMBER 替换成java.sql.Types 中存在即可.

    setType("ARRAY", Types.ARRAY);
    setType("BIGINT", Types.BIGINT);
    setType("BINARY", Types.BINARY);
    setType("BIT", Types.BIT);
    setType("BLOB", Types.BLOB);
    setType("BOOLEAN", JDBC_30_BOOLEAN);
    setType("CHAR", Types.CHAR);
    setType("CLOB", Types.CLOB);
    setType("DATALINK", JDBC_30_DATALINK);
    setType("DATE", Types.DATE);
    setType("DECIMAL", Types.DECIMAL);
    setType("DISTINCT", Types.DISTINCT);
    setType("DOUBLE", Types.DOUBLE);
    setType("FLOAT", Types.FLOAT);
    setType("INTEGER", Types.INTEGER);
    setType("JAVA_OBJECT", Types.JAVA_OBJECT);
    setType("LONGVARBINARY", Types.LONGVARBINARY);
    setType("LONGVARCHAR", Types.LONGVARCHAR);
    setType("NULL", Types.NULL);
    setType("NUMERIC", Types.NUMERIC);
    setType("OTHER", Types.OTHER);
    setType("REAL", Types.REAL);
    setType("REF", Types.REF);
    setType("SMALLINT", Types.SMALLINT);
    setType("STRUCT", Types.STRUCT);
    setType("TIME", Types.TIME);
    setType("TIMESTAMP", Types.TIMESTAMP);
    setType("TINYINT", Types.TINYINT);
    setType("VARBINARY", Types.VARBINARY);
    setType("VARCHAR", Types.VARCHAR);

    setType("CH", Types.CHAR);
    setType("VC", Types.VARCHAR);

    setType("DT", Types.DATE);
    setType("TM", Types.TIME);
    setType("TS", Types.TIMESTAMP);

    setType("NM", Types.NUMERIC);
    setType("II", Types.INTEGER);
    setType("BI", Types.BIGINT);
    setType("SI", Types.SMALLINT);
    setType("TI", Types.TINYINT);

    setType("DC", Types.DECIMAL);
    setType("DB", Types.DOUBLE);
    setType("FL", Types.FLOAT);

    setType("ORACLECURSOR", -10);

-----------------------------------------------------------------------------

看到ORACLECURSOR 你也许会得到什么启示.......... (看下IBatis Bean 的映射文件)

正确的映射:

    <parameterMap class="java.util.HashMap" id="listparamMap">
        <parameter property="skipSize" javaType="java.lang.Integer" jdbcType="NUMBER" mode="IN"/>
        <parameter property="pageSize" javaType="java.lang.Integer" jdbcType="NUMBER" mode="IN"/>
        <parameter property="payTime" javaType="java.lang.String" jdbcType="VARCHAR2" mode="IN"/>
        <parameter property="payEndTime" javaType="java.lang.String" jdbcType="VARCHAR2" mode="IN"/>
        <parameter property="cityCode" javaType="java.lang.String" jdbcType="VARCHAR2" mode="IN"/>
        <parameter property="u_cursor" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT"/>
    </parameterMap>

    <parameterMap class="java.util.HashMap" id="paramMap">
        <parameter property="total" javaType="java.lang.Long" jdbcType="DOUBLE" mode="OUT"/>
        <parameter property="payTime" javaType="java.lang.String" jdbcType="VARCHAR2" mode="IN"/>
        <parameter property="payEndTime" javaType="java.lang.String" jdbcType="VARCHAR2" mode="IN"/>
        <parameter property="cityCode" javaType="java.lang.String" jdbcType="VARCHAR2" mode="IN"/>
    </parameterMap>

    <procedure id="findBillPayMainListCount" parameterMap="paramMap" resultClass="java.lang.Long">
        {?=call jtosa_fun_billpaymainlistcount(?,?,?)}
    </procedure>

    <procedure id="findBillPayMainList" parameterMap="listparamMap" resultMap="BillPayMainBean">
        {call jtosa_porc_billPayMainList(?,?,?,?,?,?)}
    </procedure>