java mysql结果集_java调用mysql存储过程返回多个结果集

说明：mysql存储过程返回两个结果集，即两个table，用java的ResultSet接收并处理，

使用comm.getResultSet()，如果返回不为null，则表示还有结果集，

与.net中的DataSet不同(ds.table[0] ds.table[1])。

一、java代码如下：

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

public class MainClass {

public static void main(String[] args) throws SQLException {

Connection conn = null;

CallableStatement comm = null;

ResultSet ds = null;

String commStr = "";

String content = "";

String dbUrl = "jdbc:mysql://localhost:3306/mydb1";

String theUser = "root";

String thePw = "bxdz";

try {

Class.forName("com.mysql.jdbc.Driver").newInstance();

conn = (Connection) DriverManager.getConnection(dbUrl, theUser,

thePw);

commStr = "call my_proc('2011-08-01')";

comm = ((java.sql.Connection) conn).prepareCall(commStr);

comm.execute();

ds = comm.getResultSet();

while (ds.next()) {

if (content == "") {

content += "结果集1：\nC1\tC2\tC3";

}

content += "\n" + ds.getString(1) + "\t" + ds.getInt(2) + "\t"

+ ds.getDate(3);

}

if (comm.getMoreResults() == true) {

content += "\n\n结果集2：\nC1\tC3";

ds = comm.getResultSet();

while (ds.next()) {

content += "\n" + ds.getString(1) + "\t" + ds.getDate(3);

}

}

System.out.println(content);

} catch (Exception e) {

} finally {

if (ds != null)

ds.close();

if (comm != null)

comm.close();

if (conn != null)

conn.close();

}

}

}

结果集1：

C1 C2 C3

zhao 10 2011-08-15

zhao 2 2011-09-16

结果集2：

C1 C3

zhenlong 2011-08-05

zhenlong 2011-09-30

二、mysql存储过程如下：

DROP PROCEDURE IF EXISTS mydb1.my_proc;

CREATE PROCEDURE mydb1.`my_proc`(pDate Date)

BEGIN

select * from table1 where c1 = 'zhao' and C3 > pDate;

select * from table1 where c1 = 'zhenlong' and C3 > pDate;

END;

c1 C2 C3

zhao 10 2011/8/15 0:00:00

zhenlong 5 2011/8/5 0:00:00

zhao 2 2011/9/16 0:00:00

zhenlong 6 2011/9/30 0:00:00