一般情况下,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类似,不再累述
0
顶
0
踩
分享到:
2010-06-10 18:06
浏览 6016
分类:数据库
评论