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];

---Theerroroccurredinibatis-conf/MpsBillPayMainBean.xml.

---Theerroroccurredwhileexecutingqueryprocedure.

---Checkthe{?=calljtosa_fun_billpaymainlistcount(?,?,?)}.

---Checktheoutputparameters(registeroutputparametersfailed).

---Cause:java.sql.SQLException:无效的列类型;nestedexceptioniscom.ibatis.common.jdbc.exception.NestedSQLException:

---Theerroroccurredinibatis-conf/MpsBillPayMainBean.xml.

---Theerroroccurredwhileexecutingqueryprocedure.

---Checkthe{?=calljtosa_fun_billpaymainlistcount(?,?,?)}.

---Checktheoutputparameters(registeroutputparametersfailed).

---Cause:java.sql.SQLException:无效的列类型

atorg.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)

atorg.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)

atorg.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212)

atorg.springframework.orm.ibatis.SqlMapClientTemplate.executeWithMapResult(SqlMapClientTemplate.java:260)

atorg.springframework.orm.ibatis.SqlMapClientTemplate.queryForMap(SqlMapClientTemplate.java:372)

atcom.jtosa.dao.impl.BillPayMainDaoImpl.findBillPayMainListCount(BillPayMainDaoImpl.java:31)

atsun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)

atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

atjava.lang.reflect.Method.invoke(Method.java:597)

atorg.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)

atorg.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)

atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)

atorg.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)

atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)

atorg.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)

at$Proxy11.findBillPayMainListCount(UnknownSource)

atcom.jtosa.service.impl.BillPayMainServiceImpl.searchBillPayMainListCount(BillPayMainServiceImpl.java:20)

atsun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)

atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

atjava.lang.reflect.Method.invoke(Method.java:597)

atorg.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)

atorg.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)

atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)

atorg.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)

atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)

atorg.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)

at$Proxy30.searchBillPayMainListCount(UnknownSource)

atcom.jtosa.service.impl.test.BillPayMainServiceImplTest.testSearchBillPayMainListCount(BillPayMainServiceImplTest.java:30)

atsun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)

atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

atjava.lang.reflect.Method.invoke(Method.java:597)

atorg.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMethodRunner.java:99)

atorg.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRunner.java:81)

atorg.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)

atorg.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75)

atorg.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45)

atorg.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:66)

atorg.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35)

atorg.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42)

atorg.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)

atorg.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52)

atorg.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)

atorg.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)

atorg.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)

atorg.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)

atorg.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)

atorg.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

Causedby:com.ibatis.common.jdbc.exception.NestedSQLException:

---Theerroroccurredinibatis-conf/MpsBillPayMainBean.xml.

---Theerroroccurredwhileexecutingqueryprocedure.

---Checkthe{?=calljtosa_fun_billpaymainlistcount(?,?,?)}.

---Checktheoutputparameters(registeroutputparametersfailed).

---Cause:java.sql.SQLException:无效的列类型

atcom.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201)

atcom.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)

atcom.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:567)

atcom.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:541)

atcom.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForMap(SqlMapExecutorDelegate.java:658)

atcom.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForMap(SqlMapExecutorDelegate.java:640)

atcom.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForMap(SqlMapSessionImpl.java:148)

atorg.springframework.orm.ibatis.SqlMapClientTemplate$7.doInSqlMapClient(SqlMapClientTemplate.java:374)

atorg.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)

...47more

Causedby:java.sql.SQLException:无效的列类型

atoracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

atoracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)

atoracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)

atoracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3424)

atoracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:125)

atoracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:268)

atoracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:348)

atcom.mchange.v2.c3p0.impl.NewProxyCallableStatement.registerOutParameter(NewProxyCallableStatement.java:311)

atcom.ibatis.sqlmap.engine.execution.SqlExecutor.registerOutputParameters(SqlExecutor.java:431)

atcom.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:274)

atcom.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:39)

atcom.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)

...55more

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

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

<parameterproperty="skipSize"javaType="java.lang.Integer"jdbcType="NUMBER"mode="IN"/>

<parameterproperty="pageSize"javaType="java.lang.Integer"jdbcType="NUMBER"mode="IN"/>

<parameterproperty="payTime"javaType="java.lang.String"jdbcType="VARCHAR2"mode="IN"/>

<parameterproperty="payEndTime"javaType="java.lang.String"jdbcType="VARCHAR2"mode="IN"/>

<parameterproperty="cityCode"javaType="java.lang.String"jdbcType="VARCHAR2"mode="IN"/>

<parameterproperty="u_cursor"javaType="java.sql.ResultSet"jdbcType="ORACLECURSOR"mode="OUT"/>

</parameterMap>

<parameterproperty="total"jdbcType="NUMBER"javaType="java.lang.Long"mode="OUT"/>

<parameterproperty="payTime"jdbcType="VARCHAR"javaType="java.lang.String"mode="IN"/>

<parameterproperty="payEndTime"jdbcType="VARCHAR"javaType="java.lang.String"mode="IN"/>

<parameterproperty="cityCode"jdbcType="VARCHAR"javaType="java.lang.String"mode="IN"/>

</parameterMap>

<procedureid="findBillPayMainListCount"parameterMap="paramMap"resultclass="java.lang.Long">

{?=calljtosa_fun_billpaymainlistcount(?,?,?)}

</procedure>

{calljtosa_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 的映射文件)

正确的映射:

<parameterproperty="skipSize"javaType="java.lang.Integer"jdbcType="NUMBER"mode="IN"/>

<parameterproperty="pageSize"javaType="java.lang.Integer"jdbcType="NUMBER"mode="IN"/>

<parameterproperty="payTime"javaType="java.lang.String"jdbcType="VARCHAR2"mode="IN"/>

<parameterproperty="payEndTime"javaType="java.lang.String"jdbcType="VARCHAR2"mode="IN"/>

<parameterproperty="cityCode"javaType="java.lang.String"jdbcType="VARCHAR2"mode="IN"/>

<parameterproperty="u_cursor"javaType="java.sql.ResultSet"jdbcType="ORACLECURSOR"mode="OUT"/>

</parameterMap>

<parameterMapclass="java.util.HashMap"id="paramMap">

<parameterproperty="total"javaType="java.lang.Long"jdbcType="DOUBLE"mode="OUT"/>

<parameterproperty="payTime"javaType="java.lang.String"jdbcType="VARCHAR2"mode="IN"/>

<parameterproperty="payEndTime"javaType="java.lang.String"jdbcType="VARCHAR2"mode="IN"/>

<parameterproperty="cityCode"javaType="java.lang.String"jdbcType="VARCHAR2"mode="IN"/>

</parameterMap>

<procedureid="findBillPayMainListCount"parameterMap="paramMap"resultclass="java.lang.Long">

{?=calljtosa_fun_billpaymainlistcount(?,?,?)}

</procedure>

<procedureid="findBillPayMainList"parameterMap="listparamMap"resultMap="BillPayMainBean">

{calljtosa_porc_billPayMainList(?,?,?,?,?,?)}

</procedure>

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

相关推荐