JDBC多结果集处理

      一般情况下，statement执行execute后只会返回单一结果集，如果想一次返回多个结果集，就得用JDBC驱动的多结果集处理方式。各个数据库厂商有不同的实现方式，但共同的是他们都采用存储过程来实现。

 

一、Oracle多结果集是基于ref cursor实现的

 

Create table person (
Id  int, name varchar2(50)
)
Insert into person values(1, ‘test’);

 

 

Create or replace package pack as
Type cur is ref cursor;
End pack;

 

Create or replace procedure proc_test ( first 
out pack.cur ) as
Begin 
Open first for select * from person;
End proc_test;

 

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import oracle.jdbc.driver.OracleTypes;
public class TestProCur {
public static void  main(){
  Connection con = null;
  ResultSet rs = null;
  CallableStatement cst = null;
  try{
        Class.forName("oracle.jdbc.driver.OracleDriver);
        conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system", "system");
        String sql = "{call  proc_test(?)}";
        cst = con.prepareCall(sql);
        cst.registerOutParameter(1, OracleTypes.CURSOR);
        cst.execute();
        rs = (ResultSet)cst.getObject(1);
        while(rs.next()){
              System.out.println(rs.getString(2));
        }
  }catch(Exception e){
    e.printStackTrace();
  }finally{
      try{
         if(rs !=null){
              rs.close();
         }
         if(cst != null){
              cst.close();
         }
         if(con != null){
              con.close();
         }
      }catch(Exception e){
         e.printStackTrace();
      }
 }
}
 
}

 

二、神通数据库（国产）多结果集处理方式与Oracle类似

 

三、Mysql多结果集是基于存储过程中的查询语句的

 

CREATE PROCEDURE proc_test()  
BEGIN  
  select * from person
  select * from person;   
END;

 

String sql = "{call proc_test()}";  
ctmt = conn.prepareCall(sql);  
boolean hadResults = ctmt.execute();  
int i=0;  
ResultSet rs = null;
while (hadResults) {  
    System.out.println("result No:----"+(++i));  
    rs = ctmt.getResultSet();  
    while (rs != null && rs.next()) {  
        int id1 = rs.getInt(1);  
        String name1 = rs.getString(2);  
        System.out.println(id1 + ":" + name1);  
    }  
    hadResults = ctmt.getMoreResults(); //检查是否存在更多结果集  
}  

 

四、SqlServer多结果集处理方式与Mysql类似，不再累述