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

一、概述

如果想要执行存储过程，我们应该使用 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();
            }
            
        }        

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