mysql返回集合

今天想利用mysql存储过程返回多条记录,以前用oracle的游标可以直接返回.mysql不支持游标返回

通过找了资料原来可以这么做

CREATE DEFINER=`xxxx`@`%` PROCEDURE `getprizesuminfo`(opid VARCHAR(32),aid INT)
BEGIN
    /**
     * 获取微信活动奖品数量,及参加次数
     * 
     * @author xuyw
     * @email xyw10000@163.com
     * @date 2014-04-02
     */
                    SELECT
  p.id,
  IFNULL((p.prize_actual_sum-l.zjsum),0)    ptotal,
  (SELECT
     COUNT(activityid)    playsum
   FROM wxactivity_prize_log
   WHERE activityid = aid
       AND openid = opid)    playsum
FROM (SELECT
        id,
        prize_actual_sum
      FROM wxactivity_prize
      WHERE wxactivity_id = aid
      ORDER BY id) p
  LEFT JOIN (SELECT
               l.prizeid,
               COUNT(l.prizeid)    zjsum
             FROM wxactivity_prize_log l
             WHERE l.is_prize = 2
                 AND l.activityid = aid
             GROUP BY l.prizeid) l
    ON p.id = l.prizeid;
    END$$

DELIMITER ;

这样就可以直接返回

List list=this.execute(sql,new CallableStatementCallback<List<Object[]>>() {
			@Override
	           public List<Object[]> doInCallableStatement(
	                   CallableStatement cs) throws SQLException,
	                   DataAccessException {
	           	   cs.setString(1, id);
	               cs.setString(2, userid);
	               List<Object[]> objects = new ArrayList<Object[]>();
	              boolean hadResults = cs.execute();   
	                int i=0; 
	                while (hadResults) {   
	                System.out.println("result No:----"+(++i));  
	                 ResultSet rs = cs.getResultSet();   
	                while (rs != null && rs.next()) {  
	                	  ResultSetMetaData rsmd =rs.getMetaData();
		                  int count=rsmd.getColumnCount();
	                      Object[] objArr = new Object[count];  
	                      for (int j = 1; j <= count; j++) {
		                	   objArr[j-1]=rs.getObject(j);
						  }
	                      objects.add(objArr);   
	                 }   
	                hadResults = cs.getMoreResults(); //检查是否存在更多结果集  
	               } 
	               return objects;
	           }
	       });

若不用spring可以这样

cs = con.prepareCall(sql);  
List<Object[]> objects = new ArrayList<Object[]>();
 boolean hadResults = cs.execute(); 
int i=0; while (hadResults) { 
System.out.println("result No:----"+(++i));
 ResultSet rs = cs.getResultSet(); 
while (rs != null && rs.next()) {
Object[] objArr = new Object[3];
                    objArr[0] = rs.getString("id");
                    objArr[1] = rs.getString("ptotal");
                    objArr[2] = rs.getString("playsum");
                    objects.add(objArr);
 } 
hadResults = cs.getMoreResults(); //检查是否存在更多结果集
 }