Java calls Oracle stored procedures and functions

Tool classes to connect to the database:

Importjava.sql.Connection;ImportJava.sql.DriverManager;ImportJava.sql.ResultSet;Importjava.sql.SQLException;Importjava.sql.Statement; Public classJdbcutils {Private StaticString Driver = "Oracle.jdbc.OracleDriver"; Private StaticString url = "JDBC:ORACLE:THIN:@192.168.56.101:1521:ORCL"; Private StaticString user = "Scott"; Private StaticString password = "Tiger"; Static{        Try{class.forname (driver); } Catch(ClassNotFoundException e) {Throw NewExceptionininitializererror (e); }    }     Public StaticConnection getconnection () {Try {            returndrivermanager.getconnection (URL, user, password); } Catch(SQLException e) {e.printstacktrace (); }        return NULL; }       Public Static voidrelease (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 code that calls stored procedures and functions:

Importjava.sql.CallableStatement;Importjava.sql.Connection;ImportJava.sql.ResultSet;Importoracle.jdbc.OracleCallableStatement;Importoracle.jdbc.OracleTypes;Importorg.junit.Test;Importjdbcutils; Public classtestoracle {/** Create or Replace procedure Queryempinfo (Eno in number, pename out varchar  2, psal out number, pjob out VARCHAR2) */@Test Public voidTestProcedure () {//{call <procedure-name>[(<ARG1>,<ARG2>,. ...)]}String sql = "{call Queryempinfo (?,?,?,?)}"; Connection Conn=NULL; CallableStatement Call=NULL; Try{conn=jdbcutils.getconnection (); Pager=conn.preparecall (SQL); //for the in parameter, assign the valueCall.setint (1, 7839); //for Out parameters, declareCall.registeroutparameter (2, Oracletypes.varchar); Call.registeroutparameter (3, Oracletypes.number); Call.registeroutparameter (4, Oracletypes.varchar); //ExecutionCall.execute (); //Remove ResultsString name = call.getstring (2); DoubleSal = 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 voidtestfunction () {//{? = call <procedure-name>[(<ARG1>,<ARG2>,. ...)]}String sql = "{? =call queryempincome (?)}"; Connection Conn=NULL; CallableStatement Call=NULL; Try{conn=jdbcutils.getconnection (); Pager=conn.preparecall (SQL); //for Out parameters, declareCall.registeroutparameter (1, Oracletypes.number); //for the in parameter, assign the valueCall.setint (2, 7839); //ExecutionCall.execute (); //Remove Results            DoubleIncome = call.getdouble (1);        System.out.println (income); } Catch(Exception e) {e.printstacktrace (); }finally{jdbcutils.release (conn, call,NULL); }            }/*query All information about all employees in a department Baotou 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 on number,emplist out empcursor) A    S BEGIN Open emplist for SELECT * from EMP where deptno=dno; END queryemplist; END MyPackage;*/@Test Public voidtestcursor () {String SQL= "{call Mypackage.queryemplist (?,?)}"; Connection Conn=NULL; CallableStatement Call=NULL; ResultSet RS=NULL; Try{conn=jdbcutils.getconnection (); Pager=conn.preparecall (SQL); Call.setint (1, 20); Call.registeroutparameter (2, Oracletypes.cursor);                        Call.execute (); //Remove Collectionrs = ((oraclecallablestatement) call). GetCursor (2);  while(Rs.next ()) {String name= Rs.getstring ("ename"); DoubleSal = rs.getdouble ("Sal"); SYSTEM.OUT.PRINTLN (Name+ "\ T" +Sal); }        } Catch(Exception e) {e.printstacktrace (); }finally{jdbcutils.release (conn, call, RS); }        }}

Java calls Oracle stored procedures and functions