Mybatis調用Oracle返回結果集預存程序

Mapper.xml 配置

  <resultMap type="emp" id="empMap">

  <id property="empno" column="empno"/>

  <result property="ename" column="ename"/>

  <result property="mgr" column="mgr"/>

  <result property="job" column="job"/>

  <result property="hiredate" column="hiredate"/>

  <result property="sal" column="sal"/>    

  <result property="comm" column="comm"/>          

 </resultMap>

<!-- 調用預存程序返回結果集 -->

<select id="getEmps" parameterType="java.util.Map" statementType="CALLABLE">    

<![CDATA[

call pro_emp(#{emps,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=empMap})  ]]>    

</select>

預存程序：

create or replace procedure pro_emp(cur_sys out sys_refcursor)

as

begin

    open cur_sys for select empno, ename, job, mgr, hiredate, sal, comm, deptno

     from tb_emp;    

end;

IOperation介面裡的方法 ：

public interface IOperation {

void getEmps(Map<String, Object> param);

}

測試類別：

public class Mytest {

private SqlSession sqlSession;

private IOperation mapper;

@Before

public void before(){

try {

SqlSessionFactory factory=MyBatisUtil.getSqlSessionFactory();

sqlSession=factory.openSession();

mapper=sqlSession.getMapper(IOperation.class);

System.out.println("start");

} catch (Exception e) {

System.out.println(e.getMessage());

}

}

@Test

public void getEmps(){

try {

   Map<String, Object> param = new HashMap<String, Object>();

       param.put("emps",OracleTypes.CURSOR);

       mapper.getEmps(param);        

       List<Emp> depts =(List<Emp>)param.get("emps");

       for (Emp emp : depts) {

System.out.println(emp.getEmpno()+" "+emp.getEname());

}

} catch (Exception e) {

System.out.println("getEmp: "+e.getMessage());

}

}

 @After

 public void after(){

 System.out.println("close");

 sqlSession.close();

 }

}

結果：

start

DEBUG - Openning JDBC Connection

DEBUG - Created connection 8344960.

DEBUG - ooo Using Connection [oracle.jdbc.driver.T4CConnection@7f5580]

DEBUG - ==>  Preparing: call pro_emp(?)

DEBUG - ==> Parameters:

123 liuzan

7369 SMITH

7499 ALLEN

7521 WARD

7566 JONES

7654 MARTIN

7698 BLAKE

7782 CLARK

7788 SCOTT

7839 KING

7844 TURNER

7876 ADAMS

7900 JAMES

7902 FORD

7934 MILLER

close

DEBUG - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@7f5580]

DEBUG - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@7f5580]

DEBUG - Returned connection 8344960 to pool.

本文出自 “喜歡這裡” 部落格，請務必保留此出處http://1197680861.blog.51cto.com/6103676/1211162