JDBC Operations stored procedures, stored functions, out parameters using cursors

JDBC Tool class

 Public classJdbcutils {Private StaticString Driver = "Oracle.jdbc.OracleDriver"; Private StaticString url = "JDBC:ORACLE:THIN:@192.168.79.128:1521/ORCL"; Private StaticString user = "Scott"; Private StaticString password = "Tiger"; Static{        //Registration Driver//drivermanager.registerdriver (Driver)        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;///-----> Reason: The GC of Java Gc:java is not controlled by code            }        }        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; }        }    }}

Stored Procedures

--Stored ProceduresCreate or Replace procedureQueryempinformation (Enoinch  Number, Pename outvarchar2, Psal out Number, Pjob outvarchar2) isbegin  SelectEname,sal,job intoPename,psal,pjob fromEmpwhereEmpno=Eno;EndQueryempinformation;

Java code

@Test Public voidTestProcedure () {//JDBC calls the stored procedure SQL statement://{call <procedure-name>[(<ARG1>,<ARG2>,. ...)]}String sql = "{call Queryempinformation (?,?,?,?)}"; Connection Conn=NULL; CallableStatement Call=NULL; Try{conn=jdbcutils.getconnection (); Pager=conn.preparecall (SQL); //assigning values to input parametersCall.setint (1, 7839); //output parameters, declared as output parametersCall.registeroutparameter (2, Oracletypes.varchar); Call.registeroutparameter (3, Oracletypes.number); Call.registeroutparameter (4, Oracletypes.varchar); //ExecutionCall.execute (); //Get ValueString 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); }    }

Storage functions

--Storage FunctionsCreate or Replace functionQueryempincome (Enoinch  Number)return  Number ispsal emp.sal%Type;pcomm Emp.comm%type;begin  SelectSal,comm intoPsal,pcomm fromEmpwhereEmpno=Eno; returnPsal* A+NVL (Pcomm,0);EndQueryempincome;

Java code

@Test Public voidtestfuntion () {//JDBC calls the stored function SQL statement://{? = call <procedure-name>[(<ARG1>,<ARG2>,. ...)]}String sql = "{? = Call Queryempincome (?)} "; Connection Conn=NULL; CallableStatement Call=NULL; Try{conn=jdbcutils.getconnection (); Pager=conn.preparecall (SQL); //Registering output ParametersCall.registeroutparameter (1, Oracletypes.number); //Input ParametersCall.setint (2, 7839); //ExecutionCall.execute (); //Remove return value            DoubleIncome = call.getdouble (1); System.out.println ("Annual Salary:" +income); }Catch(Exception e) {e.printstacktrace (); }finally{jdbcutils.release (conn, call,NULL); }    }

Out parameter Cursors

--Create BaotouCreate or ReplacePackage MyPackage is--define a cursor type variableType Empcursor isRefcursor;procedureQueryemplist (DNOinch  Number, emplist out empcursor);EndMyPackage;--Create a package bodyCreate or ReplacePackage Body MyPackage is  procedureQueryemplist (DNOinch  Number, emplist out Empcursor) as    begin      OpenEmplist for Select *  fromEmpwhereDeptno=DNO; End;EndMyPackage;

Java code

@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 (); RS= ((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,NULL); }    }

JDBC Operations stored procedures, stored functions, out parameters using cursors