Spring 调用ORACLE存储过程的结果集

oracle 对于高级特性总是与众不同,大对象存取一要定用它自己的LOB对象,所幸我还能通过LONG RAW来代替.以便能使程序不需要特定的编码.但对于存储过程(返回结果集的存储过程),还没有什么方法能用一个通用的程序来处理ORACLE.太多的教材或文章在讲存储过程的调用只是不负责任地简单执行一些涵数或无结果返回的存储过程,使大多数读者根本不知道到底如何调用存储过程的结果集.而在Spring中,根本就没有真正完全地介绍对存储过程的结果集的处理,更别说象oracle这种"特别"的存储过程的结果集处理.

先来简单看一下我们如何在JDBC中处理存储过程的结果集的通用流程:

1.获取CallableStatement语句:

CallableStatement cs = conn.prepareCall("{call spName(?,?,?)}");

2.传入输入参数和注册输出参数

cs.setXXX(index,value);//输入参数

cs.registerOutParameter(index,type);//输出参数

3.执行存储过程:

cs.execute();

对于一个存诸过程,如果返回的结果是我们预先知道的,那么可以根据存储过程定义的顺序来进行处理(事实上一般不会这样),但对于复杂的多结果集的处理,如何定义一个通用的流程?

我们先看一下JDBC本身能为我们提供什么?

一个存储过程执行后返回的是boolean型:boolean flag = callableStatement.execute();

如果flag为true,那么说明返回了一个结果集(ResultSet)类型,你可以用getResultSet()得到当前行所在的结果,而如果返回为flase,说明什么呢?

如果你不进行处理,什么也不能说明,只能说明当前指针不是ResultSet,有可能是更新计数(updateCount)也可能什么也没有反因.

那么如果当前指针为flase时如何处理?我们应该先getUpdateCount();如果返回-1,既不是结果集,又不是更新计数了.说明没的返回了.而如果getUpdateCount()返回0或大于0,则说明当前指针是更新计数(0的时候有可能是DDL指令).无论是返回结果集或是更新计数,那么则可能还继续有其它返回.只有在当前指指针getResultSet()==null && getUpdateCount() == -1才说明没有再多的返回.

存储过程的返回和ResultSet类似,每次处理的返回结果相当于是ResultSet的Row,只不过存储过程的Row最先在第一行而不是象ResultSet要先next才到第一行,存储过程向下移动一行用getMoreResults(),相当于ResultSet的next().同样它返回boolean和上面的flag一样,只是说明当前行是不是ResultSet,如果是flase,你还是要判断是不是updateCount,在每一行,都要先同时判断是否为ResultSet还是UpdateCount,如果是其中一种则要继续getMoreResults(),当不是ResultSet也不是updateCount时,说明没有返回结果了,这时再获取输出参数.

看明白了吗?

那我们就根据上面的规则来写一个通用的流程吧:

首先,我们要确定什么时候没说结果集了:if(cs.getResultSet() == null && cs.getUpdateCount() == -1).现在我们做一个循环:

ResultSet rs = null; int updateCount = -1; flag = cs.execute(); do{ updateCount = cs.getUpdateCount(); if(updateCount != -1){//说明当前行是一个更新计数 //处理. cs.getMoreResults(); continue;//已经是更新计数了,处理完成后应该移动到下一行 //不再判断是否是ResultSet } rs = cs.getResultSet(); if(rs != null){//如果到了这里,说明updateCount == -1 //处理rs cs.getMoreResults(); continue; //是结果集,处理完成后应该移动到下一行 } //如果到了这里,说明updateCount == -1 && rs == null,什么也没的了   }while(!(updateCount == -1 && rs == null)); cs.getXXX(int);//获取输出参数

以上是对于通用的存储过程返回的结果集的处理,而oracle,它不能返回结果集,只能在输出参数中返回一个cursor,所以通用的流程中你获取不到任何结果:

package PK_AREA_PUBLIC is TYPE serarch_result IS REF CURSOR; PROCEDURE area_search(vTarget_in IN VARCHAR2 , cur_result_out OUT serarch_result) ; end PK_AREA_PUBLIC;package body PK_AREA_PUBLIC is PROCEDURE area_search(vTarget_in IN VARCHAR2 , cur_result_out OUT serarch_result) IS sqlstr VARCHAR2(1000); BEGIN sqlstr:='select .................................'; OPEN cur_result_out FOR sqlstr USING vTarget_in; END area_search; end PK_AREA_PUBLIC;

对于上面的例子,存储过程有一个输入参数,一个输出参数,我们要接受输出参数作为结果集处理.所以注册的时候应该注册为:

cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);//输出参数

这样在存储过程执行后,获取输出数造型为ResultSet就可以处理:

ResultSet rs = (ResultSet)cs.getObject(2);

如果有多个结果集就用多个输出参数.明白了ORACLE的特殊性,我们再看看在spring中如果处理它的存储过程的结果集:spring在处理复杂对象的时候,大都采用回调的方法,要求程序员自己实现接口方法.也就是它提供了程序运行时的参数,要求你自己对这些参数进行处理.对于JdbcTemplate,它在很多地方提供了ResultSet参数供程序员处理.在Spring文档中提供了对于通用流程,也就是从存储过程执行结果中获取结果集的例程:

Map out = execute(new HashMap());

其实它是默认实现了上面JDBC通用流程中对ResuleSet到Map的封装.而对于Oracle,我们就必须自己手实现对输出参数中ResultSet的回调:

public class SpringStoredProcedure extends StoredProcedure { public ArrayList<HashMap> set = new ArrayList<HashMap>(); //声明一个用于接收结果集的数据结构,其中的元素为row,用map存放   private Map inParam;//输入参数 private RowMapper rm = new RowMapper(){ public Object mapRow(ResultSet rs,int rowNum) throws SQLException{ return null;//不用从存储过程本身获取结果 } }; private RowMapperResultReader callback = new RowMapperResultReader(rm ){ public void processRow(ResultSet rs) //回调处理 throws SQLException{ int count = rs.getMetaData().getColumnCount(); String[] header = new String[count]; for(int i=0;i<count;i++) header[i] = rs.getMetaData().getColumnName(i+1); while(rs.next()){ HashMap<String,String> row = new HashMap(count+7); for(int i=0;i<count;i++) row.put(header[i],rs.getString(i+1)); set.add(row); } } }; //RowMapperResultReader作为输出参数的回调句柄 public SpringStoredProcedure(DataSource ds, String SQL) { setDataSource(ds); setSql(SQL); } public void setOutParameter(String column,int type){ declareParameter(new SqlOutParameter(column, type,callback)); //利用回调句柄注册输出参数 } public void setParameter(String column,int type){ declareParameter(new SqlParameter(column, type)); } public void SetInParam(Map inParam){ this.inParam = inParam; } public Map execute() { compile(); return execute(this.inParam); } }

下面我们看一下调用过程:

DriverManagerDataSource ds = .......; SpringStoredProcedure sp = new SpringStoredProcedure(ds,"PK_AREA_PUBLIC.area_search"); //注册参数类型,输入参数和输出参数同时注册,否则不能正确编译存储过程 sp.setParameter("vTarget_in",java.sql.Types.VARCHAR); sp.setOutParameter("cur_result_out",oracle.jdbc.OracleTypes.CURSOR); sp.compile(); //传入输入参数值 Map in = new HashMap(); in.put("vTarget_in","一个内容"); sp.SetInParam(in); //执行存储过程 sp.execute(); Map m = sp.set.get(0);//ReultSet的第一条记录 //set定义为SpringStoredProcedure的属性用于接收回调时的数据 //如果有多个输出参数,应该在每个输出参数的回调方法中生成该输出 //参数对应的ArrayList,然后加到一个成员变量的数据结构中. Iterator i = m.keySet().iterator(); while(i.hasNext()){ String key = i.next().toString(); System.out.println(key + "=>" + m.get(key)); }

总之,上面的方法虽然解决了Spring中对Oracle存储过程的调用,但我极力不推荐这程复杂的处理