第一步:映射文件配置。
<?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