使用JdbcTemplate调用输出参数为结果集(REF游标)的集合 博客分类: 常见问题 常见问题
程序员文章站
2024-03-23 11:18:16
...
//例子一:只有输出参数
<!-- 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;