问题描述

Mybatis调用存储过程返回多个结果集

存储过程返回两个结果集（游标）。

 CREATE?OR?REPLACE?FUNCTION?t_user_func(id?varchar(20))
??RETURNS?SETOF?refcursor?AS
$BODY$
Declare
code?refcursor;
result?refcursor;
BEGIN?????
??--返回错误码
??OPEN?code?FOR?select?1010?as?code;
??RETURN?NEXT?code;
??--返回查询结果集
??OPEN?result?FOR?EXECUTE?'select?*?from?t_user?where?user_id?='''?||id||''''?;
??RETURN?NEXT?result;
??RETURN;
End;
$BODY$
??LANGUAGE?plpgsql?VOLATILE;

Mybatis映射文件是这样写的：

<select id="getUserByID" parameterType="map" statementType="CALLABLE">
    {call t_user_func(
    #{id,jdbcType=INTEGER,mode=IN},
    #{refcursor, mode=OUT,
    jdbcType=OTHER, javaType=ResultSet, resultMap=userResultMap}
    )
    }
</select>

<resultMap id="userResultMap" type="com.cet.ngp.model.UserStruct">
    <collection property="codes" ofType="com.cet.ngp.model.ResultCode">
        <id column="code" property="code" jdbcType="INTEGER" />
    </collection>
    <collection property="users" ofType="com.cet.ngp.model.User">
        <id column="user_id" property="userId" jdbcType="VARCHAR" />
        <result column="user_name" property="userName" jdbcType="VARCHAR" />
        <result column="user_birthday" property="userBirthday"
            jdbcType="DATE" />
        <result column="user_salary" property="userSalary" jdbcType="INTEGER" />
    </collection>
</resultMap>

只能获取到，code结果集，无法获取user结果集。
要怎么改，才能获取到user结果集。

解决方案

实现代码是这样的：

 public?List<UserStruct>?getUserById(String?id)?{
????????ApplicationContext?ctx=?DBModule.getConfigContext();
????????UserMapper?userDao=(UserMapper)?ctx.getBean("userMapper");????????
?????????
????????Map<String,?Object>?map?=?new?HashMap<String,?Object>();
????????map.put("id",?id);???
????????userDao.getUserByID(map);
????????????????//此处获取结果集
????????List<UserStruct>?resultList?=?(ArrayList<UserStruct>)?map.get("refcursor");
?????????
????????return?resultList;
????}???