ibatis中调用oracle的函数、返回数据集

第一步：映射文件配置。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="ROP">
<typeAlias alias="ROP" type="com.sgai.sm.entity.ROP"/>
<resultMap id="ROPResult"  class="ROP">
 <result property="sid" column="SID" jdbcType="NUMERIC"/>
 <result property="createdBy" column="CREATED_BY" jdbcType="VARCHAR"/>
 <result property="createdDt" column="CREATED_DT" jdbcType="TIMESTAMP"/>
 <result property="version" column="VERSION" jdbcType="NUMERIC"/>
 <result property="updatedBy" column="UPDATED_BY" jdbcType="VARCHAR"/>
 <result property="updatedDt" column="UPDATED_DT" jdbcType="TIMESTAMP"/>
 <result property="remarks" column="REMARKS" jdbcType="VARCHAR"/>
 <result property="taskCounter" column="TASK_COUNTER" jdbcType="VARCHAR"/>
 <result property="msgCounter" column="MSG_COUNTER" jdbcType="NUMERIC"/>
 <result property="areaId" column="AREA_ID" jdbcType="VARCHAR"/>
 <result property="stationNumber" column="STATION_NUMBER" jdbcType="VARCHAR"/>
 <result property="msgTimeStamp" column="MSG_TIME_STAMP" jdbcType="TIMESTAMP"/>
 <result property="sampleStatus" column="SAMPLE_STATUS" jdbcType="NUMERIC"/>
 <result property="heatId" column="HEAT_ID" jdbcType="VARCHAR"/>
 <result property="ironPretreatmentId" column="IRON_PRETREATMENT_ID" jdbcType="VARCHAR"/>
 <result property="ironId" column="IRON_ID" jdbcType="VARCHAR"/>
 <result property="sampleTime" column="SAMPLE_TIME" jdbcType="TIMESTAMP"/>
 <result property="sampleId" column="SAMPLE_ID" jdbcType="VARCHAR"/>
 <result property="sampleNumber" column="SAMPLE_NUMBER" jdbcType="NUMERIC"/>
 <result property="sampleStartTime" column="SAMPLE_START_TIME" jdbcType="TIMESTAMP"/>
 <result property="sampleReleaseTime" column="SAMPLE_RELEASE_TIME" jdbcType="TIMESTAMP"/>
 <result property="steelGrade" column="STEEL_GRADE" jdbcType="VARCHAR"/>
 <result property="steelGroup" column="STEEL_GROUP" jdbcType="VARCHAR"/>
 <result property="caId" column="CA_ID" jdbcType="VARCHAR"/>
 <result property="archiveTmstmp" column="ARCHIVE_TMSTMP" jdbcType="TIMESTAMP"/>
 <result property="treatmentCounter" column="TREATMENT_COUNTER" jdbcType="NUMERIC"/>
 <result property="msgFlag" column="MSG_FLAG" jdbcType="VARCHAR"/>
</resultMap>
 <parameterMap id="test" class="java.util.HashMap"> 
 <parameter property="result" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="ROPResult" /> 
 <parameter property="STANO" jdbcType="VARCHAR"  javaType="java.lang.String" mode="IN" /> 
 <parameter property="AREA" jdbcType="VARCHAR"  javaType="java.lang.String" mode="IN" /> 
 <parameter property="heatid" jdbcType="VARCHAR"  javaType="java.lang.String" mode="IN" /> 
  </parameterMap> 
   <procedure id="testCur" parameterMap="test"> 
        {?= call GM_Test(?,?,?)}  
  </procedure> 
</sqlMap>
第二步：编写oracle的函数

CREATE OR REPLACE FUNCTION TMSDEV.GM_Test(STANO VARCHAR2,AREA VARCHAR2,heatid VARCHAR2
               
        )RETURN SYS_REFCURSOR
        is
             
              -- strsql1   varchar2(500):=''; 
                   -- type SYS_REFCURSOR is ref cursor;
                     type_cur SYS_REFCURSOR;
   

        BEGIN
            OPEN type_cur FOR
                   select * from tms_rop_iface_element_main WHERE AREA_ID=AREA AND STATION_NUMBER=STANO
                 AND HEAT_ID=heatid;
                
                    
                  RETURN  type_cur;
                 
                 
                 --  close type_cur;
        END;
/

第三步：测试方法：

public class ROPDaoImpl extends IBatisGenericDaoImpl<ROP> implements ROPDao {

    /* (non-Javadoc)
     * @see com.sgai.sm.dao.ROPDao#getRecords(java.util.Map)
     */
    public List<ROP> getRecords(Map map) {
 return (List<ROP>)this.getSqlMapClientTemplate().queryForObject("ROP.testCur", map);
    }

}

 

第四步：单元测试成功！

public class ROPServiceTest extends BaseSpringTest {

    private ROPService rOPService;

    public ROPServiceTest() throws Exception {
 super();
 // TODO Auto-generated constructor stub
    }

    public void onSetUp() throws Exception {
 super.onSetUp();
 rOPService = (ROPService) this.findBean("rOPService");
 // 获取bean对象
    }

    public void prepareData() {
 Map<String, Object> p = new HashMap<String, Object>();
 p.put("STANO", "2");
 p.put("AREA", "CAS");
 p.put("heatid", "10500474");
 rOPService.getRecords(p);
 List list = (List) p.get("result");
 for (int i = 0; i < list.size(); i++) {
     ROP emp = (ROP) list.get(i);
     System.out.println(emp.getHeatId());
 }
    }

    public void testFind() {
 prepareData(); 
    }

}

第五步：测试结果

0123506

0123507