使用IDEA，通过jsp页面获取数据并调用存储过程对Oracle数据库进行操作，再将查询结果回显到jsp页面(极简版)

java调用存储过程演示极简版

兔诗弟

3417人浏览 · 2021-12-06 20:37:54

兔诗弟 · 2021-12-06 20:37:54 发布

在IDEA中搭建连接Oracle的环境

步骤一：打开IDEA新建一个Java Enterprise项目，点击next后仅使用默认勾选的servlet，点击下一步，创建完成。

步骤二：进入新建的项目后，在IDEA右上角的Database,

然后在弹出的界面中点击“+”号，在Data Source 中选择Oracle，如下图。

步骤三：在弹出的如下界面中设置相应的参数

将相应的参数更改如图

第一次设置时注意点击左下角的"Test Connection"进行连接测试

完成这一步就能得到一个与Oracle数据库连通的控制台,输入sql语句测试一下

这个界面就代表成功了。

然后开始写代码，目录结构如下：

IndexServlet中的代码:

package com.example.second_do_it.servlet;
import com.example.second_do_it.until.bak;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
@WebServlet(name = "IndexServlet", value = "/IndexServlet")
public class IndexServlet extends HttpServlet {
    /*将stu表的数据备份到stu_bak*/
   /* protected void mycopy(HttpServletRequest request, HttpServletResponse response) throws Exception {
        System.out.println("开始调用存储过程");
        bak thebak=new bak();
        thebak.stu_copy();
        request.getRequestDispatcher("index.jsp").forward(request,response);
    }*/

    /*根据学号删除stu表中的信息*/
    protected void mydelete(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String Sno=request.getParameter("xuehao");
        System.out.println("开始调用存储过程");
        bak thebak=new bak();
        thebak.stu_de(Sno);
        request.getRequestDispatcher("index.jsp").forward(request,response);
    }

    /*将学生信息插入到stu表中*/
    protected void myinsert(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String Sno=request.getParameter("xuehao");
        String Sname=request.getParameter("xinming");
        String Ssex=request.getParameter("sex");
        System.out.println("开始调用存储过程");
        bak thebak=new bak();
        thebak.stu_in(Sno,Sname,Ssex);
        request.getRequestDispatcher("index.jsp").forward(request,response);
    }


    /*更新stu表*/
    protected void myupdate(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String Sno=request.getParameter("xuehao");
        String Sname=request.getParameter("xinming");
        String Ssex=request.getParameter("sex");
        System.out.println("开始调用存储过程");
        bak thebak=new bak();
        thebak.stu_up(Sno,Sname,Ssex);
        request.getRequestDispatcher("index.jsp").forward(request,response);
    }

    protected void put_stu(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String sql="select * from stu";
        try(
                Connection conn=bak.open();
                //PrepareStatement 对象来执行sql语句
                PreparedStatement pst=conn.prepareStatement(sql);
        )    {
            ResultSet rs = pst.executeQuery();//接收sql语句返回的结果

            response.setContentType("text/html; charset=utf-8");//修改页面响应的编码格式和编码结果
            PrintWriter out=response.getWriter();//out来接收响应并打印到页面
            out.println("<html><head><title>用户信息</title></head><body>");
            out.println("<h2>stu表</h2><table border='1' width='200px'>");
            out.println("<tr><td width='40px'>Sno</td><td width='40px'>Sname</td><td width='40px'>Ssex</td></tr></table>");
            System.out.println(rs);
            while(rs.next()){
                out.println("<table border='1' width='200px'><tr><td width='40px'>"+rs.getString("Sno")+
                        "</td><td width='40px'>"+rs.getString("Sname")+
                        "</td><td width='40px'>"+rs.getString("Ssex")+
                        "</td></tr></table>");
            }
            bak.close(rs,conn,pst);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }



    protected void put_stuAndstu_bak(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String sql1="select * from stu";
        String sql2="select * from stu_bak";
        try(
                Connection conn1=bak.open();
                //PrepareStatement 对象来执行sql语句
                Connection conn2=bak.open();
                PreparedStatement pst1=conn1.prepareStatement(sql1);
                PreparedStatement pst2=conn2.prepareStatement(sql2);
        )    {
            ResultSet rs1 = pst1.executeQuery();//接收sql语句返回的结果
            ResultSet rs2 = pst2.executeQuery();//接收sql语句返回的结果
            response.setContentType("text/html; charset=utf-8");//修改页面响应的编码格式和编码结果
            PrintWriter out=response.getWriter();//out来接收响应并打印到页面
            out.println("<html><head><title>用户信息</title></head><body>");
            out.println("<h2>stu表</h2><table border='1' width='200px'>");
            out.println("<tr><td width='40px'>Sno</td><td width='40px'>Sname</td><td width='40px'>Ssex</td></tr></table>");
            System.out.println(rs1);
            while(rs1.next()){
                out.println("<table border='1' width='200px'><tr><td width='40px'>"+rs1.getString("Sno")+
                        "</td><td width='40px'>"+rs1.getString("Sname")+
                        "</td><td width='40px'>"+rs1.getString("Ssex")+
                        "</td></tr></table>");
            }

            out.println("<h2>stu_bak表</h2><table border='1' width='200px'>");
            out.println("<tr><td width='40px'>Sno</td><td width='40px'>Sname</td><td width='40px'>Ssex</td></tr></table>");
            System.out.println(rs2);
            while(rs2.next()){
                out.println("<table border='1' width='200px'><tr><td width='40px'>"+rs2.getString("Sno")+
                        "</td><td width='40px'>"+rs2.getString("Sname")+
                        "</td><td width='40px'>"+rs2.getString("Ssex")+
                        "</td></tr></table>");
            }


            bak.close(rs1,conn1,pst1);
            bak.close(rs2,conn2,pst2);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }



    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        String action = request.getParameter("action");
        if("mycopy".equals(action)){
            try {
                //this.mycopy(request,response);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }else if("mydelete".equals(action)){
            try {
                this.mydelete(request,response);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }else if("myupdate".equals(action)){
            try {
                this.myupdate(request,response);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }else if("myinsert".equals(action)){
            try {
                this.myinsert(request,response);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }else if("put_stu".equals(action)){
            try {
                this.put_stu(request,response);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }else if("put_stuAndstu_bak".equals(action)){
            try {
                this.put_stuAndstu_bak(request,response);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

bak中的代码：

package com.example.second_do_it.until;
import java.sql.*;
import java.sql.Connection;
import java.sql.SQLException;
public class bak {
    public static Connection open() throws  Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接
        Connection conn= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system as sysdba", "a123");//获得数据库连接
        return conn;
    }

    public static  void close(ResultSet rs, Connection conn, PreparedStatement pst){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(pst!=null){
            try {
                pst.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

   /* public void stu_copy() throws Exception{
        //加载数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
        //得到Connection连接
        Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/book_software", "root", "3966298");
        //得到预编译的Statement对象
        CallableStatement pstm=connection.prepareCall("{call THEBAK}");
        //执行执行存储过程
        pstm.execute();
        System.out.println("flag2");
        //释放资源
        pstm.close();
        connection.close();
    }*/

    public void stu_in(String Sno,String Sname,String Ssex) throws Exception{
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接
        Connection connection= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system as sysdba", "a123");
        //得到预编译的Statement对象
        CallableStatement pstm=connection.prepareCall("{call STU_INSERT('"+Sno+"','"+Sname+"','"+Ssex+"')}");
        //执行数据库存储过程操作
        pstm.execute();
        System.out.println("调用存储过程STU_INSERT进行插入");
        //释放资源
        pstm.close();
        connection.close();
    }

    public void stu_up(String Sno,String Sname,String Ssex) throws Exception{
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接
        Connection connection= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system as sysdba", "a123");
        //得到预编译的Statement对象
        CallableStatement pstm=connection.prepareCall("{call STU_UPDATE('"+Sno+"','"+Sname+"','"+Ssex+"')}");
        //执行数据库存储过程操作
        pstm.execute();
        System.out.println("调用存储过程STU_UPDATE进行更新");
        //释放资源
        pstm.close();
        connection.close();
    }

    public void stu_de(String Sno) throws Exception{
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接
        Connection connection= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system as sysdba", "a123");
        //得到预编译的Statement对象
        CallableStatement pstm=connection.prepareCall("{call STU_DELETE('"+Sno+"')}");
        //执行数据库存储过程操作
        pstm.execute();
        System.out.println("调用存储过程STU_DELETE进行删除");
        //释放资源
        pstm.close();
        connection.close();
    }
}

index.jsp中的代码：

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" language="java" %>
<!DOCTYPE html>
<html>
<head>
    <title>学生信息</title>
</head>
<body>
<form action="IndexServlet" method="post">
    <input type="hidden" name="action" value="myinsert">
    学号:<input type="text" name='xuehao' id="sn"/><br />
    姓名:<input type="text" name="xinming" id="sm"/><br />
    性别:<input type="radio" name="sex" value="男" id="se"/>男
    <input type="radio" name="sex"  value="女" id="se">女<br />
    &nbsp;&nbsp; <input type="submit" value="录入" />&nbsp;
    <input type="button" value="删除" onclick="mydelete()"/>
    <input type="button" value="更改" onclick="myupdate()"/>
    <input type="button" value="查看stu表" onclick="lookstu()">
    <input type="button" value="查看stu表和stu_bak表" onclick="lookall()">
</form>
<br />
&nbsp;&nbsp;
<script>
    function mydelete(){
        var thesno=document.getElementById("sn").value;
        console.log(thesno);
        window.location.href ="IndexServlet?action=mydelete&xuehao="+thesno;
    }
    function mycopy(){
        window.location.href ="IndexServlet?action=mycopy";
    }
    function myupdate(){
        var thesno=document.getElementById("sn").value;
        var thesname=document.getElementById("sm").value;
        var thessex=document.getElementById("se").value;
        window.location.href ="IndexServlet?action=myupdate&xuehao="+thesno+"&xinming="+thesname+"&sex="+thessex;
    }
    function lookstu(){
        window.location.href ="IndexServlet?action=put_stu";
    }
    function lookall(){
        window.location.href ="IndexServlet?action=put_stuAndstu_bak";
    }
</script>
</body>
</html>

以上代码全部写好后，不出意外的话，你应该运行不了，哈哈

你可能会遇到：

以及

第一个错误是因为缺少下面这个依赖项

 <!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8 -->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>19.7.0.0</version>
        </dependency>

第二个错误是因为缺少下面这个依赖项

  <!-- https://mvnrepository.com/artifact/com.oracle.database.nls/orai18n -->
        <dependency>
            <groupId>com.oracle.database.nls</groupId>
            <artifactId>orai18n</artifactId>
            <version>19.7.0.0</version>
        </dependency>

pom.xml文件中dependencise标签中的内容:

<dependencies>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>4.0.1</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-engine</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8 -->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>19.7.0.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.oracle.database.nls/orai18n -->
        <dependency>
            <groupId>com.oracle.database.nls</groupId>
            <artifactId>orai18n</artifactId>
            <version>19.7.0.0</version>
        </dependency>
    </dependencies>

到此为止，这个小demo就完成了，前端获取数据并调用存储过程对oracle数据库进行操作的流程大概就是这样了，改成连接mysql或连接到云服务器上的mysql数据库也是可以的。

ok,祝你成功！