Java调用Oracle带结果集返回的存储过程

公司项目中强制使用存储过程,顺势学习下如何在程序中调用过程,借javaeye点地方保存点代码片段..........

创建过程:
--创建一张测试的表
create table t1(a number,b number ,c number,d number);
--创建一个包定义游标类型
create or replace package types
as
type cursorType is ref cursor;
end;
--创建存储过程
create or replace procedure getemps( io_cursor in out types.cursorType)
as
begin
       open io_cursor for select * from t1;
end;

借用Javaeye的地方保存点代码:

package org.sf.proc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;

import oracle.jdbc.driver.OracleTypes;

public class Callprocedure {
	public static Connection getConnection() throws Exception {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection con = DriverManager.getConnection(
				"jdbc:oracle:thin:@172.16.29.39:1521:ORA10G", "fjh", "fjh");
		return con;
	}
	public static void callProcedure(Connection con) throws Exception {
		
		CallableStatement pstm = con.prepareCall("{call GETEMPS(?)}");
		pstm.registerOutParameter(1, OracleTypes.CURSOR);
		//pstm.setString(2, "30");
		pstm.execute();
		ResultSet rs = (ResultSet) pstm.getObject(1);

//		while (rs.next()) {
//			for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
//				System.out.print(rs.getString(i) + "  ");
//			}
//			System.out.println();
//		}
		pstm.close();
		rs.close();
	}
	public static void callSQL(Connection con) throws Exception {
		PreparedStatement ps=con.prepareStatement("select * from t1");
		//ps.setString(1, "30");
		ResultSet rs=ps.executeQuery();
//		while (rs.next()) {
//			for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
//				System.out.print(rs.getString(i) + "  ");
//			}
//			System.out.println();
//		}
		ps.close();
		rs.close();
	}
	public static void main(String[] args) throws Exception {
		Connection con=getConnection();
		
		long s1=System.currentTimeMillis();
		callProcedure(getConnection());
		long e1=System.currentTimeMillis();
		
		long s2=System.currentTimeMillis();
		callSQL(getConnection());
		long e2=System.currentTimeMillis();
		
		System.out.println("过程调用耗时:"+(e1-s1));
		System.out.println("SQL调用耗时:"+(e2-s2));
		
		//test(con);
	}
	
	public static void test(Connection con) throws Exception {
		PreparedStatement ps=con.prepareStatement("insert into t1 values(?,?,?,?)");
		ps.setInt(1, 1);
		ps.setInt(2, 2);
		ps.setInt(3, 3);
		ps.setInt(4, 4);
		for (int i = 0; i < 50000; i++) {
			ps.execute();
		}
		ps.close();
	}

	
}

评论

4 楼 javatracker 2009-01-10  

发到入门讨论也行

3 楼 showcup 2009-01-10  

哦这样子啊,那我以后不贴到论坛上去了!

2 楼 javatracker 2009-01-10  

错不在楼主贴代码，写在自己blog中作为笔记也无可厚非，错就错在楼主不该把他发到论坛上

1 楼 mmwy 2009-01-09  

很奇怪，javaeye最近怎么越来越流行贴代码，而且还是动不动就连渣带汤的一鼓脑贴上来这种，贴关键性的代码不就行了么？