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_stuastype 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 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" 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());}@Overridepublic 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 blocke.printStackTrace();}return result;}@Overridepublic List queryPkgProStudentList(Map parameterMap) {// TODO Auto-generated method stubList result = null;try {result = sqlMapClient.queryForList("pkgPro_stu_cursor",parameterMap);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return result;}我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html