-- Create table
create table ABIN1
(
ID1 NUMBER,
NAME1 NVARCHAR2(100),
CREATETIME1 DATE default sysdate
)
create or replace procedure getList(myname varchar2,mylist out sys_refcursor)
is
v_sql varchar2(4000);
begin
v_sql:='select * from abin1 where name1=:1';
open myList for v_sql using myname;
end;
package com.abin.lee.db.oracle;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class OracleConnection {
private static final String USER="abin";
private static final String PWD="abin";
private static final String URL="jdbc:oracle:thin:@localhost:1521:XE";
private static final String DRIVER="oracle.jdbc.driver.OracleDriver";
private static ThreadLocal<Connection> threadLocal=new ThreadLocal<Connection>(){
protected Connection initialValue(){
Connection conn=null;
try {
Class.forName(DRIVER);
if(null==conn||conn.isClosed()){
conn=DriverManager.getConnection(URL, USER, PWD);
}
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
};
public static Connection getConnection() throws ClassNotFoundException, SQLException{
Connection conn=null;
if(threadLocal.get()==null){
Class.forName(DRIVER);
conn=DriverManager.getConnection(URL, USER, PWD);
threadLocal.set(conn);
}else {
return threadLocal.get();
}
return conn;
}
}
package com.abin.lee.db.oracle;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import junit.framework.TestCase;
import oracle.jdbc.OracleTypes;
public class GetProcedureResultSet extends TestCase{
public void test(){
Connection conn=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
conn=OracleConnection.getConnection();
cs=conn.prepareCall("{call getList(?,?)}");
cs.setString(1, "a1");
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.execute();
rs=(ResultSet)cs.getObject(2);
while(rs.next()&&rs!=null){
System.out.println("ID="+rs.getInt("id1"));
System.out.println("NAME="+rs.getString("name1"));
System.out.println("NAME="+rs.getString("createtime1"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* create or replace procedure getList(myname varchar2,mylist out sys_refcursor)
is
v_sql varchar2(4000);
begin
v_sql:='select * from abin1 where name1=:1';
open myList for v_sql using myname;
end;
*/
}