欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

使用JdbcTemplate调用输出参数为结果集(REF游标)的集合

程序员文章站 2022-03-09 19:10:56
...
//例子一:只有输出参数

<!-- DB2 dataSource-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>



<!-- jdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>




@Test
public void testRegisterCustomer() {
List<Map<String, Object>> locationInfo = (List<Map<String, Object>>) jdbcTemplate.execute(
"{call sp_rpt_satisfybyskill_new(?)}", new CallableStatementCallback() {
@Override
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
// 设置第一个输出参数的参数类型为游标类型
cs.registerOutParameter(1, OracleTypes.CURSOR);
// 执行存储过程
cs.execute();

// 获取第一个输出参数,从1开始
ResultSet rs = (ResultSet) cs.getObject(1);

List<Map<String, Object>> csList = new ArrayList<Map<String, Object>>();
// 转换每行的返回值到Map中
while (rs.next()) {
Map<String, Object> rowMap = new HashMap<String, Object>();
rowMap.put("zsnattachmentid", rs.getString("zsnattachmentid"));
rowMap.put("attachmentname", rs.getString("attachmentname"));
csList.add(rowMap);
}

rs.close();
return csList;
}

});
System.out.println(locationInfo);
}





--下面是REF游标和存储过程
CREATE OR REPLACE PACKAGE Pack_Service AS
TYPE t_RetDataSet IS REF CURSOR;
END Pack_Service;

CREATE OR REPLACE PROCEDURE Sp_Rpt_SatisfyBySkill_new
(
rCursor OUT Pack_Service.t_Retdataset -- 返回结果集
)
AS
exc_Param EXCEPTION;
BEGIN

OPEN rCurSor FOR
select t.zsnattachmentid as "zsnattachmentid",t.attachmentname "attachmentname" from zsnattachment t where t.zsnattachmentid in(2000000236,2000000239);
RETURN;
END;




//例子二:输入输出参数都有
@Test
public void testRegisterCustomer() {
List<Map<String, Object>> locationInfo = (List<Map<String, Object>>) jdbcTemplate.execute(
"{call sp_rpt_satisfybyskill_new(?,?,?)}", new CallableStatementCallback() {
@Override
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
//设置第一个输入参数的值
cs.setLong(1, 2000000236);
//设置第二个输入参数的值
cs.setLong(2, 2000000256);

// 设置第三个参数的参数类型为游标类型(registerOutParameter用来注册输出参数)
cs.registerOutParameter(3, OracleTypes.CURSOR);
// 执行存储过程
cs.execute();

// 获取第一个输出参数,3代表的是输出参数所在的位置
ResultSet rs = (ResultSet) cs.getObject(3);

List<Map<String, Object>> csList = new ArrayList<Map<String, Object>>();
// 转换每行的返回值到Map中
while (rs.next()) {
Map<String, Object> rowMap = new HashMap<String, Object>();
rowMap.put("zsnattachmentid", rs.getString("zsnattachmentid"));
rowMap.put("attachmentname", rs.getString("attachmentname"));
csList.add(rowMap);
}

rs.close();
return csList;
}

});
System.out.println(locationInfo);
}



CREATE OR REPLACE PROCEDURE Sp_Rpt_SatisfyBySkill_new(id_start number,
id_end number,
rCursor OUT Pack_Service.t_Retdataset -- 返回结果集
) AS
exc_Param EXCEPTION;
BEGIN

OPEN rCurSor FOR
select t.zsnattachmentid as "zsnattachmentid",
t.attachmentname "attachmentname"
from zsnattachment t
where t.zsnattachmentid between id_start and id_end;
RETURN;
END;


相关标签: 常见问题