java调用存储过程返回结果集和output参数

存储过程:   返回一个结果集和两个output参数

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();
  }
}

<二>java调用mysql存储过程返回多个结果集

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;