The source of get connection of oracle and call procedure of oracle-优快云博客

本文介绍如何使用Java连接Oracle数据库并执行查询操作，同时展示了创建存储过程的方法及如何通过Java调用该存储过程获取数据。

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