-
spring mybatis 调用存储过程报的错误,帮忙看一下。20
最近遇到一个问题,遇到问题,自己也做过一些调试,存储过程不涉及到游标没问题,当加入打开游标便会报错,希望大家 帮老弟解决,出谋划策。drop PROCEDURE qtds.sp_qtds_de_reasontimesmake @ CREATE PROCEDURE qtds.sp_qtds_de_reasontimesmake (IN RUN_FLAG VARCHAR(10),OUT RTN_CODE INT) DYNAMIC RESULT SETS 0 LANGUAGE SQL MODIFIES SQL DATA BEGIN ----[-----------------------------------------------------------------> -- VARIABLES ----[-----------------------------------------------------------------> DECLARE SQLSTATE CHAR(5); DECLARE S_SQLSTATE CHAR(5); DECLARE I_ERR INTEGER DEFAULT 0; ----[-----------------------------------------------------------------> -- CONDITIONS ----[-----------------------------------------------------------------> DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000'; DECLARE OUT_OF_RANGE CONDITION FOR SQLSTATE '22003'; DECLARE OUT_OF_RESOURCE CONDITION FOR SQLSTATE '57011'; ----[-----------------------------------------------------------------> -- HANDLES ----[-----------------------------------------------------------------> DECLARE EXIT HANDLER FOR SQLEXCEPTION P0:BEGIN SET S_SQLSTATE=SQLSTATE; CALL qtds.SP_qtds_PROCMONITOR('qtds.reasontimesmake','!!!SQL执行异常!!!','错误原因是:SQLSTATE:'||S_SQLSTATE,-1,CURRENT TIME,CURRENT DATE); SET I_ERR = -1; SET RTN_CODE= -1; END P0; SET RTN_CODE= -1; P1: BEGIN DECLARE GLOBAL TEMPORARY TABLE session.msconditions1( credno varchar(21), dereason varchar(10), enterdate date ) on COMMIT PRESERVE ROWS not LOGGED IN systoolstmpspace with replace; DECLARE GLOBAL TEMPORARY TABLE session.msconditions( credno varchar(21), dereason varchar(10), enterdate date ) on COMMIT PRESERVE ROWS not LOGGED IN systoolstmpspace with replace; P2: BEGIN declare current_pos int default 0; declare history_pos int default 1; declare sep_code varchar(5) default ','; declare splitString varchar(100); declare cellString varchar(10); declare RESULT_SET_END int default 0; declare credno varchar(20); declare dereason varchar(20); declare rs1 cursor with return for select credno,dereason from qtds.de_Historycredlist where dereason is not null; declare rs2 cursor with return for select credno,dxdereason from qtds.de_Historycredlist where dxdereason is not null; DECLARE CONTINUE HANDLER FOR NOT FOUND SET RESULT_SET_END=1; open rs1; ins_loop: LOOP FETCH rs1 INTO credno, dereason; if RESULT_SET_END=1 then leave ins_loop; end if; if length(dereason) >0 then set splitString = dereason; innerloop: loop set current_pos = locate(',',splitString); if length(rtrim(splitString)) = 0 then set history_pos = 1; leave innerloop; end if; if current_pos = 0 then set cellString = splitString; --set RESULT_SET_BREAK = 1; insert into session.msconditions values (credno,cellString,'1900-01-01'); set history_pos = 1; leave innerloop; else set cellString = substr(splitString,1,current_pos-1); if rtrim(cellString) !='' then insert into session.msconditions values (credno,cellString,'1900-01-01'); set history_pos = history_pos +current_pos; set splitString = substr(dereason,history_pos); end if; end if; end loop; end if; END LOOP; SET RESULT_SET_END=0; open rs2; ins_loop2: LOOP FETCH rs2 INTO credno, dereason; if RESULT_SET_END=1 then leave ins_loop2; end if; if length(dereason) >0 then set splitString = dereason; innerloop2: loop set current_pos = locate(',',splitString); if length(rtrim(splitString)) = 0 then set history_pos = 1; leave innerloop2; end if; if current_pos = 0 then set cellString = splitString; insert into session.msconditions values (credno,cellString,'1900-01-01'); set history_pos = 1; leave innerloop2; else set cellString = substr(splitString,1,current_pos-1); if rtrim(cellString) !='' then insert into session.msconditions values (credno,cellString,'1900-01-01'); set history_pos = history_pos +current_pos; set splitString = substr(dereason,history_pos); end if; end if; end loop; end if; END LOOP; END P2; insert into session.msconditions1(credno,dereason,enterdate) select credno,dereason,enterdate from qtds.de_reasontimes; insert into session.msconditions(credno,dereason,enterdate) select credno,dereason,enterdate from session.msconditions1 a where not exists (select 1 from session.msconditions b where a.credno=b.credno and a.dereason = b.dereason); delete from qtds.de_conditions_qtms; insert into qtds.de_conditions_qtms(credno,dereason,enterdate) select credno,dereason,enterdate from session.msconditions; IF( I_ERR = -1 ) THEN SET RTN_CODE= -1; RETURN -1; ELSE SET RTN_CODE= 100; RETURN 100; END IF; END P1; END @
mybatis 配置文件:<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.chinauion.dao.export.IDeReasonsToQTMSMapper"> <select id="splitCredDeReasons" statementType="CALLABLE" parameterType="map"> {call qtds.sp_qtds_de_reasontimesmake (#{RUN_FLAG,mode=IN,jdbcType=VARCHAR},#{RTN_CODE,mode=OUT,jdbcType=INTEGER})} </select> </mapper>
spring mybatis中调用存储过程public Integer splitConditions() { final Map<String, Object> paramsMap = new HashMap<String, Object>(); paramsMap.put("paramsMap", 0); deReasonsToQTMSMapper.splitCredDeReasons(paramsMap); return (Integer) paramsMap.get("RTN_CODE"); }
spring junittest@Test public void testSplitConditions(){ final Map<String, Object> paramsMap = new HashMap<String, Object>(); cmisMapper.splitCredDeReasons(paramsMap); final int i = Integer.parseInt(paramsMap.get("RTN_CODE") == null ? "0" : paramsMap.get("RTN_CODE").toString()); Assert.assertTrue(i >= 0); }
执行junit test 错误提示:org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: A query was run and no Result Maps were found for the Mapped Statement 'com.bankcomm.arms.dao.export.IDeReasonsToCMISMapper.splitCredDeReasons'. It's likely that neither a Result Type nor a Result Map was specified. at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:346) at $Proxy23.selectOne(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:154) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38) at $Proxy54.splitCredDeReasons(Unknown Source) at com.bankcomm.arms.dao.export.IDeReasonsToCMISMapperTest.testSplitCredDeReasons(IDeReasonsToCMISMapperTest.java:31) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20) at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74) at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:30) at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82) at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222) at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70) at org.junit.runners.ParentRunner.run(ParentRunner.java:300) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197) Caused by: org.apache.ibatis.executor.ExecutorException: A query was run and no Result Maps were found for the Mapped Statement 'com.bankcomm.arms.dao.export.IDeReasonsToCMISMapper.splitCredDeReasons'. It's likely that neither a Result Type nor a Result Map was specified. at org.apache.ibatis.executor.resultset.FastResultSetHandler.validateResultMapsCount(FastResultSetHandler.java:135) at org.apache.ibatis.executor.resultset.FastResultSetHandler.handleResultSets(FastResultSetHandler.java:109) at org.apache.ibatis.executor.statement.CallableStatementHandler.query(CallableStatementHandler.java:43) at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:55) at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:41) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:238) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:112) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:72) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:78) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:72) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:38) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:338) ... 35 more
2012年7月02日 19:56
2个答案 按时间排序 按投票排序
-
首先必须说明 select标签是完全没问题的
我写的配置文件如下:<!-- 将充值金额 以及奖励金额 存入奖励账户 --> <select id="transferRechareFee" statementType="CALLABLE" parameterType="map"> {call PROC_TRANSFER_RECHARGE_FEE( #{rechargeFee,mode=IN,jdbcType=NUMERIC}, #{activityFee,mode=IN,jdbcType=NUMERIC}, #{payOrderId,mode=IN,jdbcType=VARCHAR}, #{payThirdType,mode=IN,jdbcType=NUMERIC}, #{rewardOrderId,mode=IN,jdbcType=VARCHAR}, #{rewardThirdType,mode=IN,jdbcType=NUMERIC}, #{activityOrderId,mode=IN,jdbcType=VARCHAR}, #{activityThirdType,mode=IN,jdbcType=NUMERIC}, #{userName,mode=IN,jdbcType=VARCHAR}, #{memo,mode=IN,jdbcType=VARCHAR}, #{code,mode=OUT,jdbcType=INTEGER}) } </select>
日志如下:
2012-07-02 21:22:51 DEBUG java.sql.PreparedStatement:debug(28) - ==> Executing: {call PROC_TRANSFER_RECHARGE_FEE( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
2012-07-02 21:22:51 DEBUG java.sql.PreparedStatement:debug(28) - ==> Parameters: XX(String), XX(String), XXXXXX(String), XXX(String), XX(String), XX(String), XX(String), XX(String), XXX@163.com(String), XX(String)
返回只是:0
调用如下:
final Map callMap = new HashMap(); callMap.put("rechargeFee" ,"100") ;//充值金额 callMap.put("activityFee" ,"5");//活动金额 callMap.put("payOrderId" ,System.currentTimeMillis() + "" + String.valueOf(1000 + (int) (Math.random() * 9000))); callMap.put("payThirdType" ,"91"); callMap.put("rewardOrderId" ,System.currentTimeMillis() + "" + String.valueOf(1000 + (int) (Math.random() * 9000))); callMap.put("rewardThirdType" ,"95"); callMap.put("activityOrderId" ,System.currentTimeMillis() + "" + String.valueOf(1000 + (int) (Math.random() * 9000))); callMap.put("activityThirdType" ,"96"); callMap.put("userName" ,"xxxxx@163.com"); callMap.put("memo" ,"奖励"); int i = accountService.transferRechareFee(callMap) ; System.out.println(i);
我非常怀疑你传递传错了 你看下:
paramsMap.put("paramsMap", 0);
这里我觉得是:
paramsMap.put("RUN_FLAG", 0);
请仔细看一下这段 我的运行是没有问题的 。
2012年7月02日 21:25
相关推荐
在Spring和MyBatis的集成中,通过MyBatis的SqlSession对象可以调用存储过程。Oracle存储过程是一组预编译的SQL语句,可以在数据库服务器端执行,提供更好的性能和安全性。 1. **无返回值的存储过程**: 当存储过程...
以上就是使用Spring MVC和MyBatis调用MySQL存储过程的完整步骤。需要注意的是,对于复杂的存储过程,可能需要处理输入和输出参数,以及可能的异常情况。此外,还要考虑事务管理和性能优化等问题。通过这种方式,你...
MyBatis作为一款流行的Java持久层框架,提供了调用存储过程的功能。下面将详细介绍如何在MyBatis中配置和使用存储过程。 ### 1. 配置MyBatis XML映射文件 在MyBatis的映射文件(mapper.xml)中,我们需要为存储...
在调用存储过程时,我们需要利用MyBatis提供的API和配置机制来实现。 1. **配置MyBatis**:在`mybatis-config.xml`配置文件中,你需要添加数据库连接信息,例如数据源、驱动类等。同时,也需要将你的Mapper接口文件...
至此,我们就完成了Spring Boot整合MyBatis调用Oracle存储过程并处理游标返回数据的过程。在实际开发中,可以根据需求调整存储过程的逻辑和结果处理方式,以满足各种复杂业务场景。注意,对于大数据量的查询,使用...
能不能写个动态的业务,只输入存储过程名称,自动获取存储过程参数,并且参数的数据从前台传递过来...只根据输入不同的存储过程名称、参数内容,自动调用不同的存储过程。 已经使用在多个项目中 全开源项目 请放心下载
这个文档合集应该详细讲解了这些步骤和最佳实践,帮助开发者熟练掌握在Java中利用MyBatis调用MySQL存储过程和函数的技巧,从而更高效地进行数据库操作。通过深入学习和实践,开发者可以更好地理解这两者之间的交互,...
"Mybatis调用Oracle存储过程传List参数解决方法" Mybatis是当前最流行的持久层框架之一,它提供了强大的数据访问和操作功能。但是,如何将List类型参数传递给Oracle存储过程,实现批量插入数据却是一个棘手的问题,...
MyBatis 是一款优秀的持久层框架,它支持定制化SQL、存储过程以及高级映射。与传统的JDBC相比,MyBatis降低了数据库操作的复杂性,使得开发者可以专注于SQL语句的编写和优化。在Spring中,MyBatis可以与Spring无缝...
在开发Web应用时,我们经常...以上就是基于"Maven+SpringMVC+Spring+Mybatis"的图片上传、本地存储、显示及调用存储过程的相关知识点,这些内容构成了一个完整的Web应用功能模块,对于开发者来说是非常实用的技术实践。
MyBatis是一个优秀的持久层框架,它支持定制化SQL、存储过程以及高级映射。MyBatis避免了几乎所有的JDBC代码和手动设置参数以及获取结果集。MyBatis可以使用简单的XML或注解进行配置和原始映射,将接口和Java的...
Struts、Spring 和 MyBatis 是Java开发领域中三大著名框架,它们的组合常被称为SSM框架,用于构建高效、可扩展的企业级Web应用程序。在这个项目中,它们被用来实现一个基本的增删改查(CRUD)功能,这是任何数据库...
2. MyBatis:MyBatis是一个优秀的持久层框架,它支持定制化SQL、存储过程以及高级映射。MyBatis避免了几乎所有的JDBC代码和手动设置参数以及获取结果集。它允许开发者编写动态SQL,使数据库操作更加便捷。MyBatis...
MyBatis 是一个持久层框架,它支持自定义 SQL、存储过程以及高级映射,降低了数据库操作的复杂性。Redis 是一款高性能的键值存储系统,常用于缓存和实时数据处理。 【描述】"dubbo+spring+mybatis+redis 完美整合,...
- 对于复杂的SQL,考虑使用存储过程,通过MyBatis的CallStatement进行调用。 通过以上步骤,我们可以成功地整合Spring与MyBatis,并利用工具自动生成实体类,从而提升开发效率。在实际开发中,了解并掌握这些知识...
MyBatis是一个优秀的持久层框架,它支持定制化SQL、存储过程以及高级映射。MyBatis避免了几乎所有的JDBC代码和手动设置参数以及获取结果集。MyBatis可以使用简单的XML或注解进行配置和原始映射,将接口和Java的...
本项目"smartGwt、spring、Mybatis整合"就是一个很好的例子,它将三种流行的技术——SmartGWT、Spring和Mybatis融合在一起,创建了一个包含登录功能和分页查询功能的应用。以下是关于这三个技术及其整合方式的详细...
3. **MyBatis**:MyBatis 是一个优秀的持久层框架,它支持定制化SQL、存储过程以及高级映射。MyBatis避免了几乎所有的JDBC代码和手动设置参数以及获取结果集。MyBatis可以使用简单的XML或注解进行配置和原始映射,将...
在IT行业中,SpringMyBatis框架的使用是构建高效、可维护性高的Web应用程序的关键技术之一。本项目“SpringMyBatis - 预约接种”显然涉及到的是使用这两个技术来开发一个预约接种服务,这可能是一个医疗信息化系统的...