今天想利用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(); //检查是否存在更多结果集
}