[size=medium][b][color=red]存储过程: 返回一个结果集和两个output参数[/color][/b][/size]
[color=red][b]<二>java调用mysql存储过程返回多个结果集[/b][/color]
结果集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存储过程如下:
Create PROCEDURE proTest
(
@sql varchar(8000)= ' ',
@RecordCount int = 0 output,
@PageCount int = 1 output
)as
begin
exec(@sql)
set @PageCount = 1
set @RecordCount = 100
end
public static void execute(Connection con){
try {
CallableStatement cstmt = con.prepareCall("{call proTest(?,?,?)}");
cstmt.setString(1, "select * from temp");
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
ResultSet rs = cstmt.executeQuery();//记录集获取到后,把rs记录集循环取出后或者调用cstmt.getMoreResults()方法后,sqlserver才会处理output返回值,否则回抛出java.sql.SQLException:Output parameters have not yet been processed. Call getMoreResults()异常
while(rs.next){
system.out.println(rs.getString(1));
}
/**或者用
rs.getMoreResults()
System.out.println("PageCount : " + cstmt.getInt(2)); //不会抛出异常
System.out.println("RecordCount : " + cstmt.getInt(3));*/
}
catch (Exception e){
e.printStackTrace();
}
}
[color=red][b]<二>java调用mysql存储过程返回多个结果集[/b][/color]
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 = "root";
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;