存储过程的总结

首先我们了解一下存储过程的创建

这个是在PL/SQL（Oracle数据库）的command window中执行，在执行之前，需要先执行 set serveroutput on / 开启服务【我们里面用到的是数据库自带的emp表】

create or replace procedure findEmpJob(myempno in number,myjob out varchar2,myename out varchar2)
as
begin
select job,ename into myjob,myename from emp where empno=myempno;
end;

之后我们在eclipse中写我们的JAVA代码：

如上图：

我们先创建一个测试jdbc项目,名字为testjdbc1

首先需要的是将jdbc需要的包导入到webcontent/web-inf/lib中.

定义一个类TestPro.java

 1 2 
 3 import java.sql.*;
 4 
 5 public class TestPro {
 6 
 7     private String driver = "oracle.jdbc.driver.OracleDriver";
 8     // 1521是主端口，也可能是其它端口去连接oracle数据库
 9     private String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
10     private String username = "scott";
11     private String password = "123456";
12     private Connection conn;
13     private CallableStatement cstmt;
14 
15 
16     public String findEmpJob(int myempno) {
17         // 加载驱动
18         try {
19             Class.forName(driver);
20         } catch (Exception e) {
21             e.printStackTrace();
22             throw new RuntimeException("oracle驱动注册失败");
23         }
24         // 获取一个连接
25         try {
26             conn = DriverManager.getConnection(url, username, password);
27         } catch (Exception e) {
28             e.printStackTrace();
29             throw new RuntimeException("oracle连接获取失败");
30         }
31         String sql = "{call findEmpJob(?,?,?)}";
32         try {
33             cstmt = conn.prepareCall(sql);
34             cstmt.setInt(1,myempno);//in值
35             cstmt.registerOutParameter(2,Types.VARCHAR);//out值
36             cstmt.registerOutParameter(3,Types.VARCHAR);//out值
37             cstmt.execute();
38             
39             
40             String job = cstmt.getString(2);
41             String name = cstmt.getString(3);
42 
43             System.out.println(job+"  "+name); 
44             cstmt.close();
45             conn.close();
46             return job;
47         } catch (SQLException e) {
48             System.out.println("执行过程中异常:"+e.getMessage());
49         }
50         
51 
52         return "";
53     }
54 
55 }

定义一个servlet，名字为Test.java,获取jsp中我们输入的数据，放入我们的TestProd的数据存储里面。

 1 package testjdbc1;
 2 
 3 import java.io.IOException;
 4 import javax.servlet.ServletException;
 5 import javax.servlet.annotation.WebServlet;
 6 import javax.servlet.http.HttpServlet;
 7 import javax.servlet.http.HttpServletRequest;
 8 import javax.servlet.http.HttpServletResponse;
 9 
10 /**
11  * Servlet implementation class Test
12  */
13 @WebServlet("/Test")
14 public class Test extends HttpServlet {
15     private static final long serialVersionUID = 1L;
16        
17     /**
18      * @see HttpServlet#HttpServlet()
19      */
20     public Test() {
21         super();
22         // TODO Auto-generated constructor stub
23     }
24 
25     
26     protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
27         // TODO Auto-generated method stub
28         int empno = Integer.parseInt( request.getParameter("empno") );
29         TestPro tp = new TestPro();
30         tp.findEmpJob(empno);
31     }
32 
33     
34     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
35         // TODO Auto-generated method stub
36         doGet(request, response);
37     }
38 
39 }

在我们的jsp页面中，进行要求用户输入的布局

 1 <%@ page language="java" contentType="text/html; charset=UTF-8"
 2     pageEncoding="UTF-8"%>
 3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 4 <html>
 5 <head>
 6 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 7 <title>Insert title here</title>
 8 </head>
 9 <body>
10 <form action="Test" method="post">
11 员工编号：<input name="empno" type="text">
12         <input type="submit">
13 
14 </form>
15 </body>
16 </html>

jsp页面效果图

当我们运行之后，就会在下面的Console控制台命令中输出： MANAGER CLARK.

说明我们的项目是完全OK的。