Java中执行存储过程和函数(web基础学习笔记十四)

2022-11-04 12:33:29

一、概述

如果想要执行存储过程，我们应该使用 CallableStatement 接口。

CallableStatement 接口继承自PreparedStatement 接口。所以CallableStatement 接口包含有Statement 接口和PreparedStatement 接口定义的全部方法，但是并不是所有的方法我们都要使用，主要使用的方法有这样几个：

CallableStatement 常用方法：

返回类型	方法签名	说明
boolean	execute()	执行 SQL 语句，如果第一个结果是 ResultSet 对象，则返回 true；如果第一个结果是更新计数或者没有结果，则返回 false
void	registerOutParameter(int parameterIndex,int sqlType)	按顺序位置parameterIndex 将OUT 参数注册为 JDBC 类型sqlType，sqlType 为Types 类中的常量
Type	getType(int parameterIndex)	根据参数的序号获取指定的 JDBC 参数的值。第一个参数是 1，第二个参数是 2，依此类推

我们可以使用execute()方法来执行存储过程。CallableStatement 为所有的数据库提供了一种统一的标准形式调用存储过程。所以，你将会看到我们使用execute()调用存储过程的语法与在Oracle 中会所有不同。

为了获得存储过程或函数的返回值，我们需要使用 registerOutParameter()方法将返回的参数注册为JDBC 的类型。 registerOutParameter()方法的第一个参数是参数的序号，第一个为1，第二个为2，以此类推。第二个参数需要一个int 值，用来标记JDBC 的类型，我们可以使用java.sql.Types 类中的常量来设置这个参数。比如VARCHAR、DOUBLE 等类型。如果类型不够用，也可以从具体数据库的驱动中寻找合适的类型常量。如果存储过程或函数有返回值，这个方法是必须要调用的，否则无法得到返回值，甚至会发生异常。

CallableStatement 接口中定义了很多get 方法，用于获取存储过程返回的值，根据值的类型不同，你可以使用不同get 方法，比如getInt()、getString()、getDouble()等等。
我们看一下使用CallableStatement 接口执行存储过程和函数的语法格式。

存储过程：{call <procedure-name>[(<arg1>,<arg2>, ...)]}
函数：{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

如果要调用存储过程，则使用第一种语法，就是开头不带问号的语法，call 后面是过程名，
如果没有参数，可以省略小括号。

如果要调用函数，则使用第二种语法，开头带有一个问号加等号，实际上这个问号就是一个占位符，这个问号总是调用函数的第一个占位符。其它部分与过程的语法相同

二、CallableStatement 执行存储过程

2.1、建立基类

package com.pb.emp.dao;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import com.pb.emp.untily.ConfigManager;

public class BaseDao {

    protected Connection conn;

    protected PreparedStatement ps;

    protected ResultSet rs;

    //建立连接

    public boolean getConnection(){

        String driver=ConfigManager.getInstance().getString("jdbc.driver_class");

        String url=ConfigManager.getInstance().getString("jdbc.connection.url");

        String username=ConfigManager.getInstance().getString("jdbc.connection.username");

        String password=ConfigManager.getInstance().getString("jdbc.connection.password");

        try {

            Class.forName(driver);

            conn=DriverManager.getConnection(url,username, password);

        } catch (ClassNotFoundException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

            return false;

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

            return false;

        }

        return true;

    }

    //增加，修改，删除

    public int executeUpdate(String sql, Object[] params){

        getConnection();

        int updateRow=0;

        try {

            ps=conn.prepareStatement(sql);

            //填充占位符

            for(int i=0;i<params.length;i++){

                ps.setObject(i+1, params[i]);

            }

            updateRow = ps.executeUpdate();

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return updateRow;

    }

    //

    //查询

        public ResultSet executeSQL(String sql, Object[] params){

            getConnection();

            try {

                ps=conn.prepareStatement(sql);

                //填充占位符

                for(int i=0;i<params.length;i++){

                    ps.setObject(i+1, params[i]);

                }

                rs = ps.executeQuery();

            } catch (SQLException e) {

                // TODO Auto-generated catch block

                e.printStackTrace();

            }

            return rs;

        }

    // 关闭资源

        public boolean closeResource() {

            if(rs!=null){

                try {

                    rs.close();

                } catch (SQLException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                    return false;

                }

            }

            if(ps!=null){

                try {

                    ps.close();

                } catch (SQLException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                    return false;

                }

            }

            if(conn!=null){

                try {

                    conn.close();

                } catch (SQLException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                    return false;

                }

            }

            return true;

        }

}

2.2、执行不带参但是有返回值的存储过程

新建类来继承上面的类也可以继承,下面建立存储过程

--查询emp表记录数

CREATE OR REPLACE PROCEDURE getEmpCount(v_count OUT NUMBER)

AS

BEGIN

 SELECT COUNT(*) INTO v_count FROM emp;

END;

调用

//执行不带参但是有返回值的存储过程获取emp表总记录数

    public int getTotalCountProc(){

        //定义一个变量来接收结果

        int totalCount=0;

        //声明CallableStatement对象

        CallableStatement proc=null;

        String sql="{call getEmpCount(?)}";

        try {

            //建立连接

            getConnection();

            //CallableStatement对象

            proc=conn.prepareCall(sql);

            //将数据库对象数据类型注册为java中的类型

            proc.registerOutParameter(1, Types.INTEGER);

            //执行

            proc.execute();

            //接收返回值

            totalCount=proc.getInt(1);

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return totalCount;

    }

2.3、执行带参带返回值的存储过程

--根据部门编号和姓名查询人数

CREATE OR REPLACE PROCEDURE getEmpCount(v_deptno  NUMBER, v_ename VARCHAR2,v_count OUT NUMBER)

AS

BEGIN

SELECT COUNT(*) INTO v_count FROM emp

WHERE ename LIKE '%'||v_ename||'%' AND deptno=v_deptno;

END;

//执行带参带返回值的存储过程

    public int getTotalCountProc1(int deptno,String ename){

        //定义一个变量来接收结果

        int totalCount=0;

        //声明CallableStatement对象

        CallableStatement proc=null;

        String sql="{call getEmpCount(?,?,?)}";

        //建立连接

        getConnection();

        //CallableStatement对象

        try {

            proc=conn.prepareCall(sql);

            //设置占位符

            //Object [] params={deptno,ename};

            //只设置输入参数即可

            proc.setInt(1, deptno);

            proc.setString(2, ename);

            //proc.setInt(3, totalCount);

            ////将数据库对象数据类型注册为java中的类型，将输出参数转换

            proc.registerOutParameter(3, Types.INTEGER);

            //执行

            proc.execute();

            //获取结果

            totalCount=proc.getInt(3);

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }finally{

            this.closeResource();

            if(proc!=null){

                try {

                    proc.close();

                } catch (SQLException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                }

            }

        }

        return totalCount;

    }

2.3、执行返回值为游标的存储过程

--查询员工所有信息

CREATE OR REPLACE PROCEDURE emp_cur(emp_cur OUT SYS_REFCURSOR)

AS

BEGIN

  OPEN emp_cur FOR SELECT * FROM emp;

END;

    //执行返回值为游标的存储过程 游标名emp_cur

    public List<Emp> getempProc1(){

        List<Emp> emplist=new ArrayList<Emp>();

        String sql="{call emp_cur(?) }";

        //声明CallableStatement对象

        CallableStatement proc=null;

        //建立连接

        getConnection();

        try {

            //执行

            proc=conn.prepareCall(sql);

            //注册类型为数据库游标类型

            proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);

            //接收结果集

            proc.execute();

            //获取结果第一个对象

            rs=(ResultSet) proc.getObject(1);

            while(rs.next()){

                int empno=rs.getInt("empno");

                 String ename=rs.getString("ename");

                 String job=rs.getString("job");

                 int mgr=rs.getInt("mgr");

                 Date hiredate=rs.getDate("hiredate");

                 double sal=rs.getDouble("sal");

                 double comm=rs.getDouble("comm");

                 int deptno=rs.getInt("deptno");

                 //声明Emp对象

                 Emp emp=new Emp();

                 //将得到的值添加到对象中

                 emp.setEmpno(empno);

                 emp.setEname(ename);

                 emp.setJob(job);

                 emp.setMgr(mgr);

                 emp.setHiredate(hiredate);

                 emp.setSal(sal);

                 emp.setComm(comm);

                 emp.setDeptno(deptno);

                 //将对象添加到集合

                 emplist.add(emp);

            }

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }finally{

            this.closeResource();

            if(proc!=null){

                try {

                    proc.close();

                } catch (SQLException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                }

            }

        }

        return emplist;

    }

以上看出，需要将输出的参数，和结果注册，输入的参数不要注册,

但输入参数需要设置占位符

三、执行函数

3.1 、函数功能为根据雇员id 返回姓名

CREATE OR REPLACE FUNCTION getename(v_empno NUMBER)

RETURN VARCHAR2

AS

v_ename VARCHAR2(20);

BEGIN

  SELECT ename INTO v_ename FROM emp WHERE empno=v_empno;

  RETURN v_ename;

END;

public void getenamefun(int empno){

            //sql

            String ename="";

            String sql="{?=call getename(?)}";

            CallableStatement fun=null;

            getConnection();

            try {

                fun=conn.prepareCall(sql);

                fun.setInt(2, empno);

                fun.registerOutParameter(1, Types.VARCHAR);

                fun.execute();

                ename=fun.getString(1);

                System.out.println(ename);

            } catch (SQLException e) {

                // TODO Auto-generated catch block

                e.printStackTrace();

            }

        }

其它的方法与过程一样，只是多了个返回值类型

码农公寓