Example of a JDBC call stored procedure

Here's what I learned about Oracle stored procedures, and here's a simple way to share with you the example of using JDBC to invoke stored procedures:

Nonsense is not verbose, now directly on the machine code.

First, I used the EMP Employee Information table from the default Scott database in Oracle as an example:

If your Oracle does not have an EMP default employee table, you need to create an EMP table similar to the following.

In PL/SQL, you need to write the following code that calls the stored procedure:

1 --run the following line of code when you first open PL/SQL2 SetServeroutput on3 4 5 --Stored Procedures6 CREATE OR REPLACE PROCEDUREFindempjob (myempnoinch  Number, Myename outvarchar2, Myjob outvarchar2)7  as8 BEGIN9   SELECTEname,job intoMyename,myjob fromEmpWHEREEmpno=Myempno;Ten END; One /

Create a project in eclipse first, such as Jdbc_procedure. When created, it looks like this:

Example database is Oracle, Oracle's jar package that needs to be imported

First, create a jdbcutil underlying class to connect to the database, the code is as follows:

1  PackageTestjdbc;2 3 ImportJava.sql.*;4 5  Public classJdbcutil {6 7     PrivateString Driver = "Oracle.jdbc.driver.OracleDriver";8     //1521 is the primary port, or it may be a different port to connect to the Oracle database9     PrivateString url = "JDBC:ORACLE:THIN:@127.0.0.1:1521:ORCL";Ten     PrivateString username = "Scott"; One     PrivateString password = "123456"; A     PrivateConnection Conn; -     Privatecallablestatement cstmt; -  the      PublicString Findempjob (intmyempno) { -         //Load Driver -         Try { - Class.forName (driver); +}Catch(Exception e) { - e.printstacktrace (); +             Throw NewRuntimeException ("Oracle Driver registration Failed"); A         } at         //Get a connection -         Try { -conn =drivermanager.getconnection (URL, username, password); -}Catch(Exception e) { - e.printstacktrace (); -             Throw NewRuntimeException ("Oracle Connection Acquisition failed"); in         } -          toString sql = "{call Findempjob (?,?,?)}"; +         Try { -CSTMT =conn.preparecall (SQL); the             //hibernate starting from 0, JDBC starting from 1 *Cstmt.setint (1,myempno);//In value $Cstmt.registeroutparameter (2,types.varchar);//Out valuePanax NotoginsengCstmt.registeroutparameter (3,types.varchar);//Out value -             //Execute call stored procedure the Cstmt.execute (); +              A              theString ename = cstmt.getstring (2); +String job = cstmt.getstring (3); -  $             //a simple quiz on the console output $System.out.println (ename+ "'s job is:" +job);  - cstmt.close (); - conn.close (); the             returnjob; -}Catch(SQLException e) {Wuyi             //TODO auto-generated Catch block theSystem.out.println ("Exception during execution:" +e.getmessage ()); -         } Wu          -         return""; About     } $}

Second, create a servlet class such as Test.java

1  PackageTestjdbc;2 3 Importjava.io.IOException;4 Importjavax.servlet.ServletException;5 ImportJavax.servlet.annotation.WebServlet;6 ImportJavax.servlet.http.HttpServlet;7 Importjavax.servlet.http.HttpServletRequest;8 ImportJavax.servlet.http.HttpServletResponse;9 Ten /** One * Servlet Implementation class Test A  */ -@WebServlet ("/test") -  Public classTestextendsHttpServlet { the     Private Static Final LongSerialversionuid = 1L; -         -     /** -      * @seeHttpservlet#httpservlet () +      */ -      PublicTest () { +         Super(); A         //TODO auto-generated Constructor stub at     } -  -      -     protected voidDoget (HttpServletRequest request, httpservletresponse response)throwsservletexception, IOException { -         //TODO auto-generated Method Stub -         intEmpno = Integer.parseint (Request.getparameter ("Empno") ); inJdbcutil Ju =Newjdbcutil (); - ju.findempjob (empno); to     } +  -      the     protected voidDoPost (HttpServletRequest request, httpservletresponse response)throwsservletexception, IOException { *         //TODO auto-generated Method Stub $ doget (request, response);Panax Notoginseng     } -  the}

Third, write a JSP page, for example: index.jsp

1<%@ page language= "java" contenttype= "text/html; Charset=utf-8 "2pageencoding= "UTF-8"%>3<! DOCTYPE HTML PUBLIC "-//w3c//dtd HTML 4.01 transitional//en" "HTTP://WWW.W3.ORG/TR/HTML4/LOOSE.DTD" >456<meta http-equiv= "Content-type" content= "text/html; Charset=utf-8 ">7<title> testing JDBC calls stored procedures </title>89<body>Ten<form action= "Test" method= "POST" > OneEmployee Number: <input name= "empno" type= "text" > A<input type= "Submit" > -  -</form> the</body> -
The following is the effect of running the project: Verify the output in the console.
Run successfully and jump to the JSP page
The following output can be seen in the console:
Here, the stored procedures that use JDBC to invoke Oracle are covered.
Thank you for your support!
If you want to reprint please specify the Source: http://www.cnblogs.com/ZRJ-boke/p/6626851.html
Example of a JDBC call stored procedure