The source of get connection of oracle and call procedure of oracle

秋枫2656

　　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 :)