Calling stored procedures, stored functions, headers, and packages in Java

The path to copy the jar package connecting to Oracle is shown in:

<喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4KPHA + way9009yYWNsZcr9vt2/4rXEtPrC66O6PC9wPgo8cD48cHJlIGNsYXNzPQ = "brush: java;"> package demo. utils; 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 "; privat E 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;}/** run Java program * java-Xms100M-Xmx200M HellWorld ** Technical direction: * 1. Performance Tuning * 2. Fault Diagnosis: deadlock */public static void release (Connection conn, Statement st, ResultSet rs) {if (rs! = Null) {try {rs. close () ;}catch (SQLException e) {e. printStackTrace () ;}finally {rs = null; // why? --> For Java GC, we cannot control the Garbage Collector even if we call System. gc () and cannot be recycled immediately. Java's garbage collector is uncontrollable .}} 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 GC, let the Java Garbage Collector recycle, we cannot control the Garbage Collector, even if we call the System. gc () cannot be recycled immediately. Java's garbage collector is uncontrollable. System. gc () only requests jvm to recycle.

Test code:

The methods include calling stored procedures in Java, calling stored functions in Java, and calling headers and packages in Java.

-- Query the name, position, and monthly salary of an employee/* thoughts: 1. Too many out parameters ??? 2. query all information of all employees in a department */create or replace procedure queryEmpInfoma (eno in number, pename out varchar2, pjob out varchar2, psal out number) asbegin select ename, empjob, sal into pename, pjob, psal from emp where empno = eno; end ;/

-- Query the annual income of an employee. create or replace function queryEmpIncome (eno in number) return numberas -- Define the variable to save the monthly salary and bonus psal emp. sal % type; pcomm emp. comm % type; begin select sal, comm into psal, pcomm from emp where empno = eno; -- return annual income return psal * 12 + nvl (pcomm, 0); end; /declare income number; beginincome: = queryEmpIncome (7369); dbms_output.put_line (income); end ;/

Query all information headers of all employees in a department. create or replace package mypackage as type empcursor is ref cursor; procedure queryEmpList (dno in number, empList out empcursor); end mypackage; package body 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;

Package demo. oracle; 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 demo. utils. JDBCUtils;/** think ** 1. is the cursor close? When the cursor is closed, the cursor is obtained through call, and the call is obtained through conn. Therefore, if the conn is closed, the cursor is closed * 2. can the following code be run on mysql? No, there is always a difference between theory and practice. */Public class TestOracle {/** create or replace procedure queryEmpInfo (eno in number, pename out varchar2, pjob out varchar2, psal out number) */@ Testpublic void tesProcedure () {// {call
 
  
[(,...)]} String SQL = "{call queryEmpInfoma (?,?,?,?)} "; Connection conn = null; CallableStatement call = null; try {conn = JDBCUtils. getConnection (); call = conn. prepareCall (SQL); // For the in parameter, assign a call value. setInt (1, 7839); // For the out parameter, declare call. registerOutParameter (2, OracleTypes. VARCHAR); call. registerOutParameter (3, OracleTypes. VARCHAR); call. registerOutParameter (4, OracleTypes. NUMBER); // execute call.exe cute (); // retrieve the result String name = call. getString (2); String job = call. getString (3 ); Double sal = call. getDouble (4); System. out. println (name); System. out. println (job); System. out. println (sal);} catch (Exception e) {e. printStackTrace ();} finally {JDBCUtils. release (conn, call, null) ;}/ ** create or replace function queryEmpIncome (eno in number) return number */@ Testpublic void tesFunction (){//{? = Call
  
   
[(,...)]} String SQL = "{? = Call queryEmpIncome (?)} "; Connection conn = null; CallableStatement call = null; try {conn = JDBCUtils. getConnection (); call = conn. prepareCall (SQL); // For the out parameter, declare call. registerOutParameter (1, OracleTypes. NUMBER); // For the in parameter, assign call. setInt (2, 7839); // execute call.exe cute (); // retrieve the result double income = call. getDouble (1); System. out. println (income);} catch (Exception e) {e. printStackTrace ();} finally {JDBCUtils. release (conn, call, null) ;}@ Testpubli C void testCursor () {String SQL = "{call MYPACKAGE. queryEmpList (?,?)} "; Connection conn = null; CallableStatement call = null; ResultSet rs = null; try {conn = JDBCUtils. getConnection (); call = conn. prepareCall (SQL); // For the in parameter, assign a call value. setInt (); // For the out parameter, declare call. registerOutParameter (2, OracleTypes. CURSOR); // execute call.exe cute (); // retrieve the result rs = (OracleCallableStatement) call ). getCursor (2); while (rs. next () {String name = rs. getString ("ename"); String job = rs. getString ("job"); double sal = rs. getDouble ("sal"); System. out. println (name + "\ t" + job + "\ t" + sal) ;}} catch (Exception e) {e. printStackTrace ();} finally {JDBCUtils. release (conn, call, rs );}}}
  
 

Zookeeper