67,512
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE t_user
(
user_id character(32) NOT NULL,
user_name character varying(30),
user_birthday date,
user_salary double precision
)
WITH (
OIDS=FALSE
);
ALTER TABLE t_user
OWNER TO postgres;
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;
<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>
void getUserByID(Map<String, Object> map);
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;
}
@Test
public void testGetUser() {
UserService userService = new UserService();
List<UserStruct> us = userService.getUserById("2");
System.out.println(us);
}
ResultSet rs = stmt.executeQuery("select * from t_user_func('2')");
if (rs.next()) {
// first result set returned
Object o = rs.getObject(1);
if (o instanceof ResultSet) {
ResultSet rs1 = (ResultSet) o;
while (rs1.next()) {
int id = rs1.getInt(1);
System.out.println(id);
}
}
}
if (rs.next()) {
// process second ResultSet
Object o = rs.getObject(1);
if (o instanceof ResultSet) {
ResultSet rs2 = (ResultSet) o;
while (rs2.next()) {
String id = rs2.getString(1);
String name = rs2.getString(2);
System.out.println("id=" + id + ";name=" + name);
}
}
}
但是,通过mybatis结果集映射的方式,却获取不了我想要的结果。