遇到这么一个问题,需要通过Ibatis调用一个返回两个游标的存储过程。在网上找资料来着,发现问此问题的挺多的,但难以找到解决方案,于是,估摸着调用返回一个游标的存储过程处理方式,整出了调用返回两个游标的存储过程。现贴出来与大家共享。
举例如下:
存储过程:
create or replace function fn_tester(
cursor_data1 out sys_refcursor ,
cursor_data2
out sys_refcursor
out sys_refcursor
)
return number
as
begin
begin
open cursor_data1
for SELECT
e.data_1,
e.data_2,
e.data_3 from a_table
e;
e;
open cursor_data2
for
for
for SELECT
e.data_4,
e.data_5,
e.data_6 from b_table
e;
e;
end fn_tester;
sqlmap 写法如下:
<!-- 游标1数据配置-->
<resultMap class="com.table1" id="result1">
<result property="data1" column="data_1"/>
<result property="data2" column="data_2"/>
<result property="data3" column="data_3"/>
</resultMap>
<!-- 游标2数据配置-->
<resultMap class="com.table2" id="result2">
<result property="data4" column="data_4"/>
<result property="data5" column="data_5"/>
<result property="data6" column="data_6"/>
</resultMap>
<!-- 调用存储过程传入参数配置-->
<parameterMap class="java.util.HashMap" id="testParam">
<parameter property="result" jdbcType="INTEGER"
javaType="java.lang.Long" mode="OUT" />
<parameter property="table1Data" jdbcType="ORACLECURSOR"
javaType="java.sql.ResultSet" resultMap="result1" mode="OUT" />
<parameter property="table2Data" jdbcType="ORACLECURSOR"
javaType="java.sql.ResultSet" resultMap="result2" mode="OUT" />
</parameterMap>
<!-- 存储过程调起-->
<procedure id="testId" parameterMap="testParam">
<![CDATA[
{? = call fn_tester(?,?)}
]]>
</procedure>
<result property="data1" column="data_1"/>
<result property="data2" column="data_2"/>
<result property="data3" column="data_3"/>
</resultMap>
<!-- 游标2数据配置-->
<resultMap class="com.table2" id="result2">
<result property="data4" column="data_4"/>
<result property="data5" column="data_5"/>
<result property="data6" column="data_6"/>
</resultMap>
<!-- 调用存储过程传入参数配置-->
<parameterMap class="java.util.HashMap" id="testParam">
<parameter property="result" jdbcType="INTEGER"
javaType="java.lang.Long" mode="OUT" />
<parameter property="table1Data" jdbcType="ORACLECURSOR"
javaType="java.sql.ResultSet" resultMap="result1" mode="OUT" />
<parameter property="table2Data" jdbcType="ORACLECURSOR"
javaType="java.sql.ResultSet" resultMap="result2" mode="OUT" />
</parameterMap>
<!-- 存储过程调起-->
<procedure id="testId" parameterMap="testParam">
<![CDATA[
{? = call fn_tester(?,?)}
]]>
</procedure>
dao层数据获取:
其中table1、table2是两个pojo;
public void getData(Map paramMap) throws SqlException {
try {
getSqlMapClientTemplate().queryForObject( NAMESPACE + "testId" ,paramMap);
// 游标1获取
List< table1> dataList1= (List<table1>)paramMap.get( "table1Data" );
// 游标2获取
List< table2> dataList2= (List<table2>)paramMap.get( "table2Data" );
} catch (SqlException e)
{
{
throw e;
}
System. out .println(dataList1);
System. out .println(dataList2);
}
自己写个测试用例就可以测试了。