JAVA中使用参数含有游标的存储过程

关于存储过程： 存储过程是一组为了完成特定功能的SQL语句集， 经编译后存储在数据库中，用户通过指定存储过程的名字（给出参数）来执行它

游标：游标（CURSOR）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果

首先创建一个含有游标参数的存储过程

包头部分：
create or replace package mypackage is
type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor);
end mypackage;
包体部分:
create or replace package body mypackage is
procedure queryEmpList(dno in number,empList out empcursor)
as
begin
open empList for select * from emp where deptno=dno;
end;
end mypackage;

1. JDBC调用存储过程（参数含有游标的）

书写DBUtils工具类，例如：

package demotest;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class DBUtils {    private static String driver = "oracle.jdbc.OracleDriver";    private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";    private static String user = "ksxx_26";    private static String password = "ksxx_26";    static{        try {            Class.forName(driver);        } catch (ClassNotFoundException e) {            throw new ExceptionInInitializerError(e);        }    }    public static Connection getConn(){        try {            return DriverManager.getConnection(url, user, password);        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return null;    }    public static void close(Connection conn, Statement st, ResultSet rs){        if(conn != null){            try {                conn.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }        if(st != null){            try {                st.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }        if(rs != null){            try {                rs.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }    }    public static void main(String[] args) {        System.out.println(DBUtils.getConn());    }}

书写Junit单元测试工具类，例如：

package demotest;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import oracle.jdbc.internal.OracleCallableStatement;import oracle.jdbc.internal.OracleTypes;import oracle.jdbc.oracore.OracleType;import org.junit.Test;public class TestProcedure {    @Test    public void test(){        String sql = "{call mypackage.QUERYEMPLIST(?,?)}";        Connection conn = null;        CallableStatement call = null;        ResultSet rs = null;        try{            conn = DBUtils.getConn();            call = conn.prepareCall(sql);            call.setInt(1, 10);            call.registerOutParameter(2, OracleTypes.CURSOR);            call.execute();            rs = ((OracleCallableStatement)call).getCursor(2);            while(rs.next()){                String name = rs.getString("ename");                double sal = rs.getDouble("sal");                System.out.println(name+"\t"+sal);            }        }catch (Exception e) {            e.printStackTrace();        }    }}

2.在MyBatis中调用存储过程（参数含有游标的）

DAO中代码：

public interface EmpDao {    List<Emp> findbyDeptno(Map map);}

EmpMapper.xml文件代码

<mapper namespace="org.ks.dao.EmpDao">     <resultMap type ="org.ks.entity.Emp" id= "cursorMap">         <result column ="empno" property="empno" />           <result column ="ename" property="ename"  />           <result column ="job" property="job" />           <result column ="mgr" property="mgr"  />           <result column ="hiredate" property="hiredate" />           <result column ="sal" property="sal" />           <result column ="comm" property="comm" />         <result column ="deptno" property="deptno" />      </resultMap >     <select id ="findbyDeptno" statementType="CALLABLE"   parameterType="java.util.Map" >              {call mypackage.QUERYEMPLIST(         #{deptno, jdbcType=VARCHAR, mode=IN},         #{empList, mode=OUT, jdbcType=CURSOR,resultMap=cursorMap})}    </select >   </mapper>

Junit单元测试部分：

package org.ks.test;import java.util.ArrayList;import java.util.HashMap;import java.util.LinkedList;import java.util.List;import java.util.Map;import org.junit.Test;import org.ks.dao.EmpDao;import org.ks.entity.Emp;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;public class TestEmpProcedure {    @Test    public void test(){        ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");        EmpDao dao = ac.getBean("empDao", EmpDao.class);        List<Emp> empList = new ArrayList<Emp>();//声明一个集合用来接收查询后返回的游标结果        Map<String, Object> paramMap = new HashMap<String, Object>();        paramMap.put("deptno", 20);        paramMap.put("empList", empList);        dao.findbyDeptno(paramMap);        empList = (List<Emp>)paramMap.get("empList");        for(Emp emp : empList){            System.out.println(emp);        }    }}