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

使用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="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" 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>

 

 

 

 

 

 

评论

3 楼 mikey_5 2013-09-02  

非常感谢楼主的分享，<parameter property="returnId" javaType="java.lang.Integer"
jdbcType="DOUBLE" mode="OUT" /> 原来要使用Double，谢谢

2 楼 影子_890314 2012-11-06  

我现在也有这个错误，求解！

1 楼 wjpiao 2011-11-28  

你下面的“正确的映射”里面不是还是有number类型吗？