一般情况下,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类似,不再累述