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

9/6/2015来源:Java教程人气:1768

java中执行存储过程和函数(web基础学习笔记十四)一、概述

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

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

CallableStatement 常用方法:

返回类型方法签名说明
booleanexecute()

执行 SQL 语句,如果第一个结果是 ResultSet 对象,则返回 true;如果第一个结果是更新计数或者没有结果,则返回 false

void

registerOutParameter(int parameterIndex,int sqlType)

按顺序位置parameterIndex 将OUT 参数注册为JDBC 类型sqlType,sqlType 为Types 类中的常量

Type

getType(intparameterIndex)

根据参数的序号获取指定的 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)ASBEGIN 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)ASBEGINSELECT COUNT(*) INTO v_count FROM empWHERE 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)ASBEGIN  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 VARCHAR2ASv_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();            }                    }        

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