JAVA調用Oracle預存程序和函數

標籤：lex   open   let   cursor   callable   drive   unit   ack   查詢   

串連資料庫的工具類：

import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class JDBCUtils {    private static String driver = "oracle.jdbc.OracleDriver";    private static String url = "jdbc:oracle:thin:@192.168.56.101:1521:orcl";    private static String user = "scott";    private static String password = "tiger";        static{        try {            Class.forName(driver);        } catch (ClassNotFoundException e) {            throw new ExceptionInInitializerError(e);        }    }    public static Connection getConnection(){        try {            return DriverManager.getConnection(url, user, password);        } catch (SQLException e) {            e.printStackTrace();        }        return null;    }      public static void release(Connection conn,Statement st,ResultSet rs){        if(rs != null){            try {                rs.close();            } catch (SQLException e) {                e.printStackTrace();            }finally{                rs = null;   //----> Java GC            }        }        if(st != null){            try {                st.close();            } catch (SQLException e) {                e.printStackTrace();            }finally{                st = null;            }        }        if(conn != null){            try {                conn.close();            } catch (SQLException e) {                e.printStackTrace();            }finally{                conn = null;            }        }    }}

調用預存程序和函數的JAVA代碼：

import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import oracle.jdbc.OracleCallableStatement;import oracle.jdbc.OracleTypes;import org.junit.Test;import JDBCUtils;public class TestOracle {/* * create or replace procedure queryEmpInfo(eno in number,                                         pename out varchar2,                                         psal   out number,                                         pjob   out varchar2) */    @Test    public void testProcedure(){        //{call <procedure-name>[(<arg1>,<arg2>, ...)]}        String sql = "{call queryEmpInfo(?,?,?,?)}";        Connection conn = null;        CallableStatement call = null;        try {            conn = JDBCUtils.getConnection();            call = conn.prepareCall(sql);                        //對於in參數，賦值            call.setInt(1, 7839);                        //對於out參數，申明            call.registerOutParameter(2, OracleTypes.VARCHAR);            call.registerOutParameter(3, OracleTypes.NUMBER);            call.registerOutParameter(4, OracleTypes.VARCHAR);                        //執行            call.execute();                        //取出結果            String name = call.getString(2);            double sal = call.getDouble(3);            String job = call.getString(4);            System.out.println(name+"\t"+sal+"\t"+job);        } catch (Exception e) {            e.printStackTrace();        }finally{            JDBCUtils.release(conn, call, null);        }    }    /* * create or replace function queryEmpIncome(eno in number)return number */    @Test    public void testFunction(){        //{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}        String sql = "{?=call queryEmpIncome(?)}";                Connection conn = null;        CallableStatement call = null;        try {            conn = JDBCUtils.getConnection();            call = conn.prepareCall(sql);                        //對於out參數，申明            call.registerOutParameter(1, OracleTypes.NUMBER);                        //對於in參數，賦值            call.setInt(2, 7839);            //執行            call.execute();                        //取出結果            double income = call.getDouble(1);            System.out.println(income);        } catch (Exception e) {            e.printStackTrace();        }finally{            JDBCUtils.release(conn, call, null);        }            }/*查詢某個部門中所有員工的所有資訊包頭CREATE OR REPLACE PACKAGE MYPACKAGE AS   type empcursor is ref cursor;  procedure queryEmpList(dno in number,empList out empcursor);END MYPACKAGE;包體CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS  procedure queryEmpList(dno in number,empList out empcursor) AS  BEGIN    open empList for select * from emp where deptno=dno;    END queryEmpList;END MYPACKAGE;*/    @Test    public void testCursor(){        String sql = "{call MYPACKAGE.queryEmpList(?,?)}";                Connection conn = null;        CallableStatement call = null;        ResultSet rs = null;        try {            conn = JDBCUtils.getConnection();            call = conn.prepareCall(sql);                            call.setInt(1, 20);            call.registerOutParameter(2, OracleTypes.CURSOR);            call.execute();                        //取出集合            rs = ((OracleCallableStatement)call).getCursor(2);            while(rs.next()){                String name = rs.getString("ename");                double sal = rs.getDouble("sal");                System.out.println(name+"\t"+sal);            }        } catch (Exception e) {            e.printStackTrace();        }finally{            JDBCUtils.release(conn, call, rs);        }        }}

 

JAVA調用Oracle預存程序和函數