IBatis调用ORACLE的存储过程、函数的返回结果集例子

 
import java.io.Serializable;  
import java.util.Date;  
  
public class User implements Serializable{  
    private static final long serialVersionUID = -6919964218508186044L;  
    private int id;  
    private String name;  
    private Date birthday;  
    public int getId() {  
        return id;  
    }  
    public void setId(int id) {  
        this.id = id;  
    }  
    public String getName() {  
        return name;  
    }  
    public void setName(String name) {  
        this.name = name;  
    }  
    public Date getBirthday() {  
        return birthday;  
    }  
    public void setBirthday(Date birthday) {  
        this.birthday = birthday;  
    }  
}  

<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD iBatis Mapper 3.0 //EN" "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">  
<mapper namespace="org.yhb.ibatis.dao.UserDAO">  
    <!-- 表结构  
        create table IBATIS_USER  
        (  
          ID       NUMBER not null,  
          NAME     VARCHAR2(20) not null,  
          BRITHDAY DATE not null  
        )  
    -->  
      
    <!-- 存储过程  
        create or replace procedure getAllUser(userList out sys_refcursor)  
        as  
        begin  
           open userList for select * from ibatis_user;  
        end;  
     -->  
       
    <!-- resultMap -->  
    <resultMap type="User" id="userMap">  
        <id column="id" property="id" />  
        <result column="name" property="name" />  
        <result column="birthday" property="birthday" />  
    </resultMap>  
  
    <!-- 调用存储过程 -->  
    <select id="getAllUser" statementType="CALLABLE">  
        {call  
          getAllUser(#{userList,<!-- 参数 -->  
                       mode=OUT,<!-- 参数类型 -->  
                       javajavaType=java.sql.ResultSet,<!-- 参数java类型 -->  
                       jdbcType=CURSOR,<!-- 参数jdbc类型 -->  
                       resultMap=userMap<!-- ResultSet需要resultMap参数 -->  
                       })}  
    </select>  
</mapper>  

@Test  
public void testProcedure() throws Exception {  
    Reader reader = null;  
    reader = Resources.getResourceAsReader("configuration.xml");  
    SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);  
    reader.close();  
      
    SqlSession session = ssf.openSession();  
    Map map = new HashMap();  
    session.selectOne("org.yhb.ibatis.dao.UserDAO.getAllUser", map);  
    System.out.println(map);  
    //返回的集合被放入了map中  
    List<User> userList = (List<User>) map.get("userList");  
    System.out.println(userList);  
    session.close();  
}  

---------------------------------------------------------------------

--定义包声明
create or replace package pkg_stu
as
type list_stu is ref cursor;--定义游标，通过游标将数据模型结果集返回给关系模型
procedure proc_findStuList(stulist out list_stu,s_id number);
function fun_findStuList(s_id number) return list_stu;
end pkg_stu;

--包主体声明
create or replace package body pkg_stu
  as
  procedure proc_findStuList(stulist out list_stu,s_id number)--实现查找所有学生的存储过程
    is
            sqlString varchar2(500);
    begin
            if s_id = 0 then
               open stulist for select sid,sname,major,birth,socre from student order by sid;
            else
               sqlString := 'select sid,sname,major,birth,socre from student where sid=:sid';
               open stulist for sqlString using s_id;
            end if;
    end proc_findStuList;
  function fun_findStuList(s_id number)   --实现查找所有学生的函数
  return list_stu
  is
         stulist list_stu;
         sqlString varchar2(500);
  begin
         if s_id = 0 then
            open stulist for select sid,sname,major,birth,socre from student order by sid;
         else
            sqlString := 'select sid,sname,major,birth,socre from student where sid=:sid';
            open stulist for sqlString using s_id;
         end if;
         return stulist;
  end fun_findStuList;
end pkg_stu;

<!-- 声明结果集类型参数 -->
	<resultMap class="Student" id="stuAccount">
		<result property="sid" column="SID" columnIndex="1"/>
		<result property="sname" column="SNAME" columnIndex="2"/>
		<result property="major" column="MAJOR" columnIndex="3"/>
		<result property="birth" column="BIRTH" columnIndex="4"/>
		<result property="socre" column="SOCRE" columnIndex="4"/>
	</resultMap>
	
	<!--  -->
	<parameterMap  id="parameterMap" class="java.util.HashMap">
        <parameter property="stulist" jdbcType="ORACLECURSOR" 
         javaType="java.sql.ResultSet" mode="OUT" />
        <parameter property="s_id" jdbcType="NUMBER" javaType="java.lang.Integer" mode="IN"/>
    </parameterMap>
    
    <!-- 存储过程的调用方式 -->
    <procedure id="pkgPro_stu_cursor" parameterMap="parameterMap" resultMap="stuAccount">
        {call pkg_stu.proc_findStuList(?,?)}
    </procedure>
    
    <!-- 函数的调用方式 -->
    <procedure id="fun_stu_cursor" parameterMap="parameterMap" resultMap="stuAccount">
    	{? = call pkg_stu.fun_findStuList(?) }
    </procedure>

	@SuppressWarnings("unchecked")
	public static void main(String[] args) {
//存储过程的调用
		
//		IStudentDAO dao = new IStudentDAOImpl();
//		
//		Map parameterMap = new HashMap();
//		parameterMap.put("s_id", 0);
//		List<Student> accoutList = dao.queryPkgProStudentList(parameterMap);
//		if(accoutList.size()>0){
//			for(Student temp:accoutList){
//				if(temp!=null){
//					System.out.println(temp.getBirth().toLocaleString()+"---"+temp.getMajor());
//				}
//			}
//		}
//		System.out.println("-------------------------");
//		System.out.println("size:"+accoutList.size());

//函数的调用		
		IStudentDAO dao = new IStudentDAOImpl();
		
		Map parameterMap = new HashMap();
		parameterMap.put("s_id", 0);
		List<Student> accoutList = dao.queryFunStudentList(parameterMap);
		if(accoutList.size()>0){
			for(Student temp:accoutList){
				if(temp!=null){
					System.out.println(temp.getBirth().toLocaleString()+"---"+temp.getMajor());
				}
			}
		}
		System.out.println("-------------------------");
		System.out.println("size:"+accoutList.size());
	}
	
	@Override
	public List queryFunStudentList(Map parameterMap) {
		List result = null;
		try {
			result = sqlMapClient.queryForList("fun_stu_cursor",parameterMap);
			System.out.println("size:"+result.size());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
@Override
	public List queryPkgProStudentList(Map parameterMap) {
		// TODO Auto-generated method stub
		List result = null;
		try {
			result = sqlMapClient.queryForList("pkgPro_stu_cursor",parameterMap);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}