For get the connection of oracle database ,you should add the oracle driver to the classpath first.and when you finished ,should close all the resouces.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class ConnectDatabase {
public static void main(String args[]){
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
PreparedStatement stmt = null;
Connection conn = null;
ResultSet rs = null;
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url,"jil","jil");
stmt = conn.prepareStatement("select * from class");
rs = stmt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("CLASS_NUMBER"));
System.out.println(rs.getString("CLASS_ID"));
System.out.println(rs.getString("CLASS_REMARK"));
System.out.println(rs.getString("CLASS_NAME"));
}
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//release the resouces,the order is resultset,preparestatement,connection
try {
if(rs!=null){
rs.close();
}
if(stmt!=null){
stmt.close();
}
f(conn!=null){
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
In order to call the procedure, we should create the procedure first:
create or replace procedure TQ_PROC8(classInfo out myCursor.mycur) is
begin
open classinfo for select * from class;
end TQ_PROC8;
create or replace package myCursor is
-- Author : TANGLEI
-- Created : 2009-9-15 16:04:50
-- Purpose : for using cursor
-- Public type declarations
type mycur is ref cursor;
procedure TQ_PROC8(p_cur out mycur);
end myCursor;
create or replace package body myCursor is
procedure tq_proc8(p_cur out mycur) is
begin
open p_cur for select * from class;
end;
end myCursor;
then:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class CallProcedure {
public static void main(String args[]){
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
CallableStatement stmt = null;
Connection conn = null;
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url,"jil","jil");
stmt = conn.prepareCall("call JIL.TQ_PROC8(?)");
stmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
stmt.execute();
ResultSet outRS =(ResultSet) stmt.getObject(1);
while(outRS.next()){
System.out.println(outRS.getString(1));
}
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally{
//release the resouces
try {
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
Both of these two examples have been tested :)