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

/********************************************************************************

包中存储过程及函数 (database Oracle 10G)

********************************************************************************/

----包声明

CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE proc_findResult(u_cursor OUT myrctype, u_id NUMBER);
FUNCTION fun_findResult(u_id NUMBER) return myrctype;
END pkg_test;

 

----包主体的声明

CREATE OR REPLACE PACKAGE BODY pkg_test
  AS
  PROCEDURE proc_findResult(u_cursor OUT myrctype ,u_id NUMBER)
  IS
  sqlString VARCHAR2 (500);
  BEGIN
     IF u_id = 0 THEN
        OPEN u_cursor FOR SELECT userId, userName, password, groupName FROM user_account ORDER BY userId;
     ELSE
        sqlString :='SELECT userId, userName, password, groupName FROM user_account where userId=:w_id';
        OPEN u_cursor FOR sqlString USING u_id;
     END IF;
  END proc_findResult;

  function fun_findResult(u_id NUMBER) RETURN myrctype
  IS
  u_cursor myrctype;
  sqlString VARCHAR2 (500);
  BEGIN
     IF u_id = 0 THEN
        OPEN u_cursor FOR SELECT userId, userName, password, groupName FROM user_account ORDER BY userId;
     ELSE
        sqlString :='SELECT userId, userName, password, groupName FROM user_account where userId=:w_id';
        OPEN u_cursor FOR sqlString USING u_id;
     END IF;
     return u_cursor;
   END fun_findResult;

END pkg_test;

 

 

 

 

/********************************************************************************

IBatis SQLMap 中的配置

********************************************************************************/

    <resultMap id="userAccount" class="org.taink.entity.UserAccount" >
        <result property="userId" column="userId" columnIndex="1"/>
        <result property="userName" column="userName" columnIndex="2"/>
        <result property="password" column="password" columnIndex="3"/>
        <result property="groupName" column="groupName" columnIndex="4"/>
    </resultMap>

 

 

    <!-- call procedure parameter
    <parameterMap  id="parameterMap" class="java.util.HashMap">
        <parameter property="u_cursor" jdbcType="ORACLECURSOR"
         javaType="java.sql.ResultSet" mode="OUT" />
        <parameter property="u_id" jdbcType="NUMBER" javaType="java.lang.Integer" mode="IN"/>
    </parameterMap>
    -->
   
    <!--  call procedure
    <procedure id="test_cursor" parameterMap="parameterMap" resultMap="userAccount">
        {call pkg_test.proc_findResult(?,?)}
    </procedure>
    -->
   
   
    <!-- call function parameter     -->
    <parameterMap  id="parameterMap" class="java.util.HashMap">
        <parameter property="u_cursor" jdbcType="ORACLECURSOR"
         javaType="java.sql.ResultSet" mode="OUT" />
        <parameter property="u_id" jdbcType="NUMBER" javaType="java.lang.Integer" mode="IN"/>
    </parameterMap>
   
    <!--  call function     -->
    <procedure id="test_cursor" parameterMap="parameterMap" resultMap="userAccount">
        {? = call pkg_test.fun_findResult(?)}
    </procedure>

 

/********************************************************************************

Dao layer java code

********************************************************************************/

 

    @SuppressWarnings("unchecked")
    @Override
    public List findAllUserAccountByProcOrFun(Map parameterMap) {
        List result = null;
        try {
            result = super.sqlMapClient.queryForList("test_cursor",parameterMap);
        } catch (SQLException e) {
            System.out.println("-------------error-------------");
            e.printStackTrace();
        }
        return result;
    }

 

/********************************************************************************

java Test class

********************************************************************************/

public class TestProcOrFun {

    public static void main(String[] args) {

        IUserAccountDao userAccountDao = new UserAccountDaoImpl();

 

       // find all object by procedure or function  operation
        Map parameterMap = new HashMap();
        parameterMap.put("u_id", 15);
        List accountList =
            userAccountDao.findAllUserAccountByProcOrFun(parameterMap);
        if (accountList.size()>0 ) {
            for (Object temp : accountList) {
                if (temp != null) {
                    System.out.println(((UserAccount)temp).toString());
                }
            }
        } else {
            System.out.println("result is null");
        }

    }

}