spring 调用存储过程

由于项目中想调用存储过程，写了一个调用存储过程公共类;

注意一下：过程名要加数据库访问用户(有权限的用户)
格式为：
(1)dbuser + procedure
(2)dbuser + package + function

基类：
XbaseJdbcDaoImpl.java 的源程序如下：
/**
* 执行存储过程
* @param sql
* @param declaredParameters
* @param inPara
* @return map
*/
public Map executeCall(String sql, List declaredParameters, Map inPara) {
return this.executeCall(sql,declaredParameters,inPara,false);
}

/**
*
* @param sql
* @param declaredParameters
* @param inPara
* @param function true 是调用函数 false 调用过程
* @return map
*/
public Map executeCall(String sql, List declaredParameters, Map inPara,boolean function) {
XbaseStoreProcedure xsp = new XbaseStoreProcedure(this.getJdbcTemplate(),sql,declaredParameters);
xsp.setFunction(function);
return xsp.execute(inPara);
}

/**
* 返回结果集
* @param sql
* @param declaredParameters
* @param inPara
* @return map
*/
public List<HashMap> executeCallResultList(String sql, List declaredParameters, Map inPara){
XbaseStoreProcedure xsp = new XbaseStoreProcedure(this.getJdbcTemplate(),sql);
for(int i=0;i<declaredParameters.size();i++){
SqlParameter parameter = (SqlParameter)declaredParameters.get(i);
if(parameter instanceof SqlOutParameter){
xsp.setOutParameter(parameter.getName(),parameter.getSqlType());
}else xsp.setParameter(parameter.getName(),parameter.getSqlType());
}
xsp.SetInParam(inPara);
xsp.execute();

return xsp.set;
}

XbaseStoreProcedure.java 的源程序
package com.xjj.app.jdbc.xbase;

import org.springframework.jdbc.object.StoredProcedure;
import org.springframework.jdbc.core.*;

import javax.sql.DataSource;
import java.util.*;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
* Created by IntelliJ IDEA.
* User: Administrator_xusg
* Date: 2008-2-21
* Time: 11:34:01
* DESP:
* To change this template use File | Settings | File Templates.
*/
public class XbaseStoreProcedure extends StoredProcedure {
private Map initParameterData = new Hashtable(); //数据传入

private boolean callFunction = false; //是过程还是函数

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 RowMapperResultSetExtractor callback = new RowMapperResultSetExtractor (rm){
public Object extractData(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);
}
return null;
}
};

//设置输出集的分类(游标)
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);
}

/**
* 构造存储过程的参数
* @param dataSource
* @param sql
*/
public XbaseStoreProcedure(DataSource dataSource,String sql){
super(dataSource,sql);
this.compile();
}

protected XbaseStoreProcedure(JdbcTemplate jdbcTemplate, String sql) {
setJdbcTemplate(jdbcTemplate);
setSql(sql);
}

/**
*
* @param jdbcTemplate
* @param sql
* @param declareParameterList 输入值及输出值
*/
protected XbaseStoreProcedure(JdbcTemplate jdbcTemplate, String sql,List declareParameterList) {
setJdbcTemplate(jdbcTemplate);
setSql(sql);
for(int i=0;i<declareParameterList.size();i++){
SqlParameter parameter = (SqlParameter)declareParameterList.get(i);
this.declareParameter(parameter);
}
this.compile();
}

public Map getInitParameterData() {
return initParameterData;
}

public void setInitParameterData(Map initParameterData) {
this.initParameterData = initParameterData;
}

public void setCallFunction(boolean callFunction) {
this.callFunction = callFunction;
this.setFunction(callFunction);
}
}


使用实例如下：
package com.xjj.app.jdbc.test;

import com.xjj.app.jdbc.xbase.XbaseJdbcDaoImpl;
import com.xjj.app.jdbc.xbase.DbConfig;

import java.util.*;
import java.sql.Types;

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlOutParameter;
import oracle.jdbc.OracleTypes;

/**
* Created by IntelliJ IDEA.
* User: Administrator_xusg
* Date: 2008-2-21
* Time: 14:16:38
* DESP:
* To change this template use File | Settings | File Templates.
*/
public class TestClassDao extends XbaseJdbcDaoImpl {

/**
* create or replace procedure insertValue(vid in varchar,vname in varchar,vemail in varchar) as
begin
insert into callableTest(id,name,email) values(vid,vname,vemail);
end;

*/
public void insert(){
String sql = DbConfig.DBUSRE+".insertValue";

List list = new ArrayList();
list.add(new SqlParameter("id",Types.VARCHAR));
list.add(new SqlParameter("name",Types.VARCHAR));
list.add(new SqlParameter("email", Types.VARCHAR));

Map map = new HashMap();
map.put("id","12121210121212100000");
map.put("name","nameSpring");
map.put("email","emailSpring");
this.executeCall(sql,list,map);
}

/**
* create or replace procedure findvalueById(vid in varchar2,vname out varchar2,vemail out varchar2) as
begin
select t.name,t.email into vname,vemail from callableTest t where t.id= vid;
end;
*/
public void findValueById(){
String sql = DbConfig.DBUSRE+".findvalueById";
List list = new ArrayList();
list.add(new SqlParameter("id",Types.VARCHAR));
list.add(new SqlOutParameter("name",Types.VARCHAR));
list.add(new SqlOutParameter("email",Types.VARCHAR));

Map map = new HashMap();
map.put("id","12121210121212100000");
Map a = this.executeCall(sql,list,map);
System.out.println(""+a.get("name"));
System.out.println(""+a.get("email"));
}

//返回游标
public void findlist(){
String sql = DbConfig.DBUSRE+".text_a2";
List list = new ArrayList();
list.add(new SqlOutParameter("cur_result_out", OracleTypes.CURSOR));
List listresult = this.executeCallResultList(sql,list,new HashMap());
for(int i=0;i<listresult.size();i++){
Map m = (HashMap)listresult.get(i);
Iterator iterator = m.keySet().iterator();
while(iterator.hasNext()){
System.out.println(""+m.get(iterator.next()));
}
}
}


}