背景： 由于使用了存储过程进行坚转横的动态查询，查询出来的列是不固定的，也就无法使用实体对象自动转换；但关于查询结果返回困扰了很久，网上很多都是将查询结果做为游标 out输出，但本人试了很多方法，不知道是不是mysql不支持游标做为输出参数还是什么原因，也没有成功，最终还是把在执行存储过程的时候也就执行了查询结果；然后将结果在Mapper里面配置好返回给java;
首先定义存储过程；
DELIMITER $$DROP PROCEDURE if EXISTS `getReport`$$CREATE PROCEDURE getReport(in $years INT,in $quarter INT)BEGINDECLARE aid int;DECLARE aname VARCHAR(64);DECLARE adate date;DECLARE done INT DEFAULT FALSE; DECLARE _Cur CURSOR FOR select _id, _activityName,_date from tb_activity where year(_date) = $years AND quarter(_date ) = $quarter ;-- 将结束标志绑定到游标DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;SET @sqlstr =' SELECTc.na, c.uid ,c.uname ,';OPEN _Cur; read_loop: LOOPFETCH _Cur INTO aid, aname, adate; IF done THENLEAVE read_loop;END IF;SET @sqlstr = CONCAT(@sqlstr, 'sum(if( _activityId=',aid,', _cv, 0)),');END LOOP;CLOSE _Cur; SET @len=LENGTH(@sqlstr) -1;-- select @len;SET @sqlstr = LEFT(@sqlstr ,@len);-- select @sqlstr;-- 此处为sql 拼接 省略-- select @sqlstr;PREPARE s1 from @sqlstr; EXECUTE s1; DEALLOCATE PREPARE s1;END $$DELIMITER ;CALL getReport(,2);
mybatis:userMapper.xml
<select id="getReport" statementType="CALLABLE" parameterType="java.util.HashMap" resultType="java.util.HashMap">call getReport(#{years,jdbcType=INTEGER,mode=IN},#{quarter,jdbcType=INTEGER,mode=IN})</select>
UserAppMapper,java
public List<Map<String, String>> getReport(HashMap<String, String> map);
@Service
public class UserAppService {
public List<Map<String,String>> getReport(int year,int quarter){HashMap<String, String> map = new HashMap<String, String>();map.put("years", ""+year);map.put("quarter", ""+quarter);List<Map<String, String>> str =userAppMapper.getReport(map);;System.out.println(str);return str;}
在这个过程中一直困惑我的就是不知道怎样把查询结果输出，以什么对象输入，其实针对不知道能查询出有多少列的结果，都可以使用
resultType="java.util.HashMap"
而查询当查询结果不只一条时需要在 java代码里面 使用List来接收；所以很重要在于 返回值List<Map<String,String>>；
当查询出来的结果多于一条的时候，而我们定义又不是List就会出现正面异常；
当定义成public Map<String,String> getYearReport(HashMap<String, String> map);就会出现如下异常
-11-25 09:59:28.130 DEBUG 1132 --- [nio-9090-exec-2] t.m.s.m.UserAppMapper.getYearReport: <==Total: 917-11-25 09:59:28.130 DEBUG 1132 --- [nio-9090-exec-2] t.m.s.m.UserAppMapper.getYearReport: <== Updates: 0-11-25 09:59:28.132 DEBUG 1132 --- [nio-9090-exec-2] org.mybatis.spring.SqlSessionUtils : Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@36fd289c]-11-25 09:59:28.132 DEBUG 1132 --- [nio-9090-exec-2] o.s.jdbc.datasource.DataSourceUtils: Returning JDBC Connection to DataSource-11-25 09:59:28.133 DEBUG 1132 --- [nio-9090-exec-2] .m.m.a.ExceptionHandlerExceptionResolver : Resolving exception from handler [public java.util.List<java.util.Map<java.lang.String, java.lang.String>> tk.mybatis.springboot.controller.UserAppController.getYearReport(java.lang.Integer)]: org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 917-11-25 09:59:28.134 DEBUG 1132 --- [nio-9090-exec-2] .w.s.m.a.ResponseStatusExceptionResolver : Resolving exception from handler [public java.util.List<java.util.Map<java.lang.String, java.lang.String>> tk.mybatis.springboot.controller.UserAppController.getYearReport(java.lang.Integer)]: org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 917-11-25 09:59:28.135 DEBUG 1132 --- [nio-9090-exec-2] .w.s.m.s.DefaultHandlerExceptionResolver : Resolving exception from handler [public java.util.List<java.util.Map<java.lang.String, java.lang.String>> tk.mybatis.springboot.controller.UserAppController.getYearReport(java.lang.Integer)]: org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 917-11-25 09:59:28.143 DEBUG 1132 --- [nio-9090-exec-2] o.s.web.servlet.DispatcherServlet : Could not complete requestorg.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 917at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77) ~[mybatis-spring-1.3.1.jar:1.3.1]at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) ~[mybatis-spring-1.3.1.jar:1.3.1]at com.sun.proxy.$Proxy76.selectOne(Unknown Source) ~[na:na]
当查询出来的数据不能进行匹配的时候，就是定义了错误的resultType；
A query was run and no Result Maps were found for the Mapped Statement 'user.insertUser!selectKey'. It's likely that neither a Result Type nor a Result Map was specified.
每个人的实现方法都不一样，只是满足自己需求就好；
