使用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;
上一篇: 常用的JavaScript代码片段
下一篇: Collections(集合工具类)