mybatis mysql 关于调用存储过程获取查询结果

背景：  由于使用了存储过程进行坚转横的动态查询，查询出来的列是不固定的，也就无法使用实体对象自动转换；但关于查询结果返回困扰了很久，网上很多都是将查询结果做为游标 out 输出，但本人试了很多方法，不知道是不是mysql 不支持游标做为输出参数还是什么原因，也没有成功，最终还是把在执行存储过程的时候也就执行了查询结果；然后将结果在Mapper里面配置好返回给java; 

首先定义存储过程；

DELIMITER  $$
DROP PROCEDURE if EXISTS `getReport`$$
CREATE PROCEDURE getReport(in $years INT,in $quarter INT)
BEGIN	
	DECLARE 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 =' SELECT	c.na, c.uid ,c.uname ,';
	OPEN _Cur; 
	 read_loop: LOOP
			FETCH _Cur INTO aid, aname, adate;  
			IF done THEN
				LEAVE 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(2016,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); 就会出现如下异常

2017-11-25 09:59:28.130 DEBUG 1132 --- [nio-9090-exec-2] t.m.s.m.UserAppMapper.getYearReport      : <==      Total: 917
2017-11-25 09:59:28.130 DEBUG 1132 --- [nio-9090-exec-2] t.m.s.m.UserAppMapper.getYearReport      : <==    Updates: 0
2017-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]
2017-11-25 09:59:28.132 DEBUG 1132 --- [nio-9090-exec-2] o.s.jdbc.datasource.DataSourceUtils      : Returning JDBC Connection to DataSource
2017-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
2017-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
2017-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
2017-11-25 09:59:28.143 DEBUG 1132 --- [nio-9090-exec-2] o.s.web.servlet.DispatcherServlet        : Could not complete request

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
	at 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.

每个人的实现 方法都不一样，只是满足自己需求 就好；